DataSet Fundamentals

for RuBoard

You can also fetch a subset of the data in the DataSet . The Select method on a DataTable uses the same syntax as an SQL statement where clause. Column names are used to access the data for a particular row. This example comes for the HotelBroker class, where it is used to get the hotels for a particular city.

 public ArrayList GetHotels(string city)  ...    DataTable t = hotelsDataset.Tables["Hotels"];    DataRow[] rows = t.Select("City = '" + city + "'");    HotelListItem hl;   ArrayList hotels = new ArrayList();  for (int i = 0; i < rows.Length; i++)  {    hl.HotelName = rows[i]["HotelName"].ToString();    hl.City = rows[i]["City"].ToString().Trim();    hl.NumberRooms = (int) rows[i]["NumberRooms"];    hl.Rate = (decimal) rows[i]["RoomRate"];    hotels.Add(hl);  }  return hotels; 

The AddHotel method of the HotelBroker class demonstrates how to add a new row to a DataSet . A new DataRow instance is created, and the column names are used to add the data to the columns in the row.

To propagate your new row back to a database, you have to add it to the row collection of the table, and then use the Update method on the SqlDataAdapter class to do so. It is the data adapter that mediates between the DataSet and the database. We will discuss later how to do perform edits on the dataset in order to accept or reject changes before propagating them back to the database.

 public string AddHotel(string city, string name,                                   int number, decimal rate)      ...  DataTable t = hotelsDataset.Tables["Hotels"];  DataRow r = t.NewRow();  r["HotelName"] = name;  r["City"] = city;  r["NumberRooms"] = number;  r["RoomRate"] = rate;  t.Rows.Add(r);      hotelsAdapter.Update(hotelsDataset, "Hotels"); 

To delete rows from the DataSet , you first find the particular row or rows you want to delete and then invoke the Delete method on each DataRow instance. When the Update method on the data adapter is called, it will be deleted from the database.

The Remove method removes the DataRow from the collection. It is not marked as deleted, since it is no longer in the DataSet . When the Update method on the data adapter is called, it will not be deleted from the database.

The DeleteHotel method in the HotelBroker class illustrates deleting rows from a DataSet .

 public string DeleteHotel(string city, string name)    ...    t = hotelsDataset.Tables["Hotels"];   r = t.Select("City = '" + city + "' and HotelName = '"                                            + name + "'");  ...  for (i = 0; i < r.Length; i++)    r[i].Delete();  ... 

To update a row in a dataset, you just find it and modify the appropriate columns. This example comes from the ChangeRooms method in the HotelBroker class. When the Update method on the data adapter is called, the modification will be propagated back to the database.

 public string ChangeRooms(string city, string name,                             int numberRooms, decimal rate)    ...    DataTable t = hotelsDataset.Tables["Hotels"];    DataRow[] r = t.Select(    "City = '" + city + "' and HotelName = '" + name + "'");    ...    for (int i = 0; i < r.Length; i++)    {      r[i]["NumberRooms"] = numberRooms;      r[i]["RoomRate"] = rate;    }    ... 

Updating the Data Source

How does the SqlDataAdapter.Update method propagate changes back to the data source? Changes to the DataSet are placed back based on the InsertCommand , UpdateCommand , and DeleteCommand properties of the SqlDataAdapter class. Each of these properties takes an SqlCommand instance that can be parameterized to relate the variables in the program to the parts of the related SQL statement. The code fragment we use to show this comes from the HotelBroker constructor.

A SqlCommand instance is created to represent the parameterized SQL statement that will be used when the SqlDataAdapter.Update command is invoked to add a new row to the database. At that point, the actual values will be substituted for the parameters.

 SqlCommand cmd = new SqlCommand("insert Hotels(City,        HotelName, NumberRooms, RoomRate)        values(@City, @Name, @NumRooms, @RoomRate)", conn); 

The parameters have to be associated with the appropriate columns in a DataRow . In the AddHotel method code fragment discussed previously, columns were referenced by the column names: "HotelName," "City," "NumberRooms," and "RoomRate." Notice how they are related to the SQL statement parameters @Name, @City, @NumRooms, @RoomRate in the SqlParameter constructor This last argument sets the Source property of the SqlParameter . The Source property sets the DataSet column to which the parameter corresponds. The Add method places the parameter in the Parameters collection associated with the SqlCommand instance.

 SqlParameter param = new SqlParameter("@City",                               SqlDbType.Char, 20, "City");  cmd.Parameters.Add(param);  cmd.Parameters.Add(new SqlParameter("@Name",                         SqlDbType.Char, 20, "HotelName"));  cmd.Parameters.Add(new SqlParameter("@NumRooms",                         SqlDbType.Int, 4, "NumberRooms"));  cmd.Parameters.Add(new SqlParameter("@RoomRate",                         SqlDbType.Money, 8, "RoomRate")); 

Finally the SqlDataAdapters' InsertCommand property is set to the SqlCommand instance. Now this command will be used whenever the adapter has to insert a new row in the database.

 hotelsAdapter.InsertCommand = cmd; 

Similar code appears in the HotelBroker constructor for the UpdateCommand and DeleteCommand properties to be used whenever a row has to be updated or deleted.

 hotelsAdapter.UpdateCommand = new SqlCommand(      "update Hotels set NumberRooms = @NumRooms, RoomRate =         @RoomRate where City = @City and HotelName =         @Name", conn);  hotelsAdapter.UpdateCommand.Parameters.Add(new       SqlParameter("@City", SqlDbType.Char, 20, "City"));  hotelsAdapter.UpdateCommand.Parameters.Add(new       SqlParameter("@Name", SqlDbType.Char, 20,               "HotelName"));  hotelsAdapter.UpdateCommand.Parameters.Add(new       SqlParameter("@NumRooms", SqlDbType.Int, 4,                "NumberRooms"));  hotelsAdapter.UpdateCommand.Parameters.Add(new       SqlParameter("@RoomRate", SqlDbType.Money, 8,                "RoomRate"));  hotelsAdapter.DeleteCommand = new SqlCommand(  "delete from Hotels where City = @City and HotelName =                @Name", conn);  hotelsAdapter.DeleteCommand.Parameters.Add(new       SqlParameter("@City", SqlDbType.Char, 20, "City"));  hotelsAdapter.DeleteCommand.Parameters.Add(new       SqlParameter("@Name", SqlDbType.Char, 20,                "HotelName")); 

Whatever changes you have made to the rows in the DataSet will be propagated to the database when SqlDataAdapter.Update is executed. How to accept and reject changes made to the rows before issuing the SqlDataAdapter.Update command is discussed in a later section.

Auto Generated Command Properties

The SqlCommandBuilder class can be used to automatically generate any InsertCommand , UpdateCommand , and DeleteCommand properties that have not been defined. Since the SqlCommandBuilder needs to derive the necessary information to build those properties dynamically, it requires an extra round trip to the database and more processing at runtime. Therefore, if you know your database layout in the design phase, you should explicitly set the InsertCommand , UpdateCommand , and DeleteCommand properties to avoid the performance hit. If the database layout is not known in advance, and a query is specified by the user , the SqlCommandBuilder can be used if the user subsequently wants to update the results.

This technique works for DataTable instances that correspond to single tables. If the data in the DataTable is generated by a query that uses a join, then the autogeneration mechanism cannot generate the logic to update multiple tables. The SqlCommandBuilder uses the SelectCommand property to generate the command properties.

A primary key or unique column must exist on the table in the DataSet . This column must be returned by the SQL statement set in the SelectCommand property. The unique columns are used in a where clause for update and delete.

Column names cannot contain special characters such as spaces, commas, periods, quotation marks, or nonalphanumeric characters . This is true even if the name is delimited by brackets. You can specify a fully qualified table name such as SchemaName.OwnerName.TableName .

A simple way to use the SqlCommandBuilder class is to pass the SqlDataAdapter instance to its constructor. The SqlCommandBuilder then registers itself as a listener for RowUpdating events. It can then generate the needed InsertCommand , UpdateCommand , or DeleteCommand properties before the row update occurs.

The CommandBuilder example demonstrates how to use the SqlCommandBuilder class.

for RuBoard


Application Development Using C# and .NET
Application Development Using C# and .NET
ISBN: 013093383X
EAN: 2147483647
Year: 2001
Pages: 158

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net