DataSet Fundamentals

Team-Fly    

 
Application Development Using Visual Basic and .NET
By Robert J. Oberg, Peter Thorsteinson, Dana L. Wyatt
Table of Contents
Chapter 13.  Programming with ADO.NET


You can also fetch a subset of the data in the DataSet . The Select method on a DataTable uses the same syntax as a 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 Function GetHotels(ByVal city As String) _  As ArrayList Implements IHotelInfo.GetHotels    ...    Dim t As DataTable = hotelsDataset.Tables("Hotels")    Dim rows() As DataRow = t.Select(_       "City = '" & city & "'")    Dim hl As HotelListItem    Dim hotels As New ArrayList()    Dim i As Integer    For i = 0 To rows.Length - 1       hl.HotelName = rows(i)("HotelName").ToString().Trim()       hl.City = rows(i)("City").ToString().Trim()       hl.NumberRooms = CInt(rows(i)("NumberRooms"))       hl.Rate = CDec(rows(i)("RoomRate"))       hotels.Add(hl)    Next    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 Function AddHotel(_  ByVal city As String, ByVal name As String, _  ByVal number As Integer, ByVal rate As Decimal) _  As String Implements IHotelAdmin.AddHotel    ...    Dim t As DataTable = hotelsDataset.Tables("Hotels")    Dim r As DataRow = 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 Function DeleteHotel(_  ByVal city As String, ByVal name As String) _  As String Implements IHotelAdmin.DeleteHotel    ...    t = hotelsDataset.Tables("Hotels")    r = t.Select("City = '" & city & "' and HotelName = '" _       & name & "'")    If r.Length = 0 Then       Throw (New HotelNotFoundException(_       "Hotel " & name & " in " & city & " was not found."))    End If    For i = 0 To r.Length - 1       r(i).Delete()    Next    ... 

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 Function ChangeRooms(_  ByVal city As String, ByVal name As String, _  ByVal numberRooms As Integer, ByVal rate As Decimal) _  As String Implements IHotelAdmin.ChangeRooms    ...    t = hotelsDataset.Tables("Hotels")    Dim r() As DataRow = t.Select(_     "City = '" & city & "' and HotelName = '" & name & "'")    If r.Length = 0 Then       Throw (New HotelNotFoundException(_       "Hotel " & name & " in " & city & " was not found."))    End If    Dim i As Integer    For i = 0 To r.Length - 1       r(i)("NumberRooms") = numberRooms       r(i)("RoomRate") = rate    Next    ... 

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 a 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 create 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.

 Dim cmd As 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, and @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.

 Dim param As 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.

 graphics/codeexample.gif 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.

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.


Team-Fly    
Top
 


Application Development Using Visual BasicR and .NET
Application Development Using Visual BasicR and .NET
ISBN: N/A
EAN: N/A
Year: 2002
Pages: 190

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