Because DataSets are always disconnected from their data sources, making changes in a DataSet never has any effect on the data source. To propagate changes, additions, and deletions back to a data source, call the SqlDataAdapter class's Update method, passing the DataSet and the name of the table that is to be updated. For example, the following call to Update writes changes from the DataTable named Categories back to the SQL Server table of the same name:
Before using the Update method, however, you should understand how an SqlDataAdapter object performs updates. To change, add, or delete records, an SqlDataAdapter object must send SQL
DELETE statements, respectively, to SQL Server. The forms of these statements either can be inferred from the
SELECT statement that was provided to the SqlDataAdapter object or can be explicitly provided to the SqlDataAdapter object.
Example 8-2 shows an example of allowing an SqlDataAdapter object to infer the SQL
DELETE statements required for applying updates to a database.
Example 8-2: Allowing an SqlDataAdapter object to infer SQL UPDATE, INSERT, and DELETE statements from a SELECT statement
' Open a database connection. Dim strConnection As String = _ "Data Source=localhost;Initial Catalog=Northwind;" _ & "Integrated Security=True" Dim cn As SqlConnection = New SqlConnection(strConnection) cn.Open( ) ' Create a data adapter object and set its SELECT command. Dim strSelect As String = _ "SELECT * FROM Categories" Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, cn) ' Set the data adapter object's UPDATE, INSERT, and DELETE ' commands. Use the SqlCommandBuilder class's ability to auto- ' generate these commands from the SELECT command. Dim autogen As New SqlCommandBuilder(da) ' Load a data set. Dim ds As DataSet = New DataSet( ) da.Fill(ds, "Categories") ' Get a reference to the "Categories" DataTable. Dim dt As DataTable = ds.Tables("Categories") ' Modify one of the records. Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0) row("Description") = "Milk and stuff" ' Add a record. row = dt.NewRow( ) row("CategoryName") = "Software" row("Description") = "Fine code and binaries" dt.Rows.Add(row) ' Delete a record. row = dt.Select("CategoryName = 'MyCategory'")(0) row.Delete( ) ' Update the database. da.Update(ds, "Categories") ' Close the database connection. cn.Close( )
Note the following in Example 8-2:
- A SqlDataAdapter object is constructed with an argument of
"SELECT * FROM
Categories". This initializes the value of the SqlDataAdapter object's SelectCommand property.
- A SqlCommandBuilder object is constructed with the SqlDataAdapter object passed as an argument to its constructor. This step hooks the SqlDataAdapter object to the SqlCommandBuilder object so that later, when the SqlDataAdapter object's Update method is called, the SqlDataAdapter object can obtain SQL
DELETEcommands from the SqlCommandBuilder object.
- The SqlDataAdapter object is used to fill a DataSet object. This results in the DataSet object containing a DataTable object, named "Categories", that contains all the rows from the Northwind database's Categories table.
- One record each in the table is modified, added, or deleted.
- The SqlDataAdapter object's Update method is called to propagate the changes back to the database.
Step 5 forces the SqlCommandBuilder object to generate SQL statements for performing the database update, insert, and delete operations.When the Update method is called, the SqlDataAdapter object notes that no values have been set for its UpdateCommand, InsertCommand, and DeleteCommand prperties, and therefore queries the SqlCommandBuilder object for these commands. If any of these properties had been set on the SqlDataAdapter object, those values would have been used instead.
The SqlCommandBuildObject can be examined to see what commands were created. To see the commands that are generated in Example 8-2, add the following lines anywhere after the declaration and assignment of the autogen variable:
Console.WriteLine("UpdateCommand: " & autogen.GetUpdateCommand.CommandText)
Console.WriteLine("InsertCommand: " & autogen.GetInsertCommand.CommandText)
Console.WriteLine("DeleteCommand: " & autogen.GetDeleteCommand.CommandText)
UPDATE command contains the following text (note that line breaks have been added for clarity in the book):
SET CategoryName = @p1 , Description = @p2 , Picture = @p3
(CategoryID = @p4)
((CategoryName IS NULL AND @p5 IS NULL) OR (CategoryName = @p6)) )
INSERT command is:
INSERT INTO Categories( CategoryName , Description , Picture )
VALUES ( @p1 , @p2 , @p3)
DELETE command is:
DELETE FROM Categories
(CategoryID = @p1)
((CategoryName IS NULL AND @p2 IS NULL) OR (CategoryName = @p3)) )
Note the use of formal parameters (
@p1, etc.) in each of these statements. For each row that is to be changed, added, or deleted, the parameters are replaced with values from the row, and the resulting SQL statement is issued to the database. The choice of which value from the row to use for which parameter is controlled by the SqlCommand object's Parameters property. This property contains an SqlParameterCollection object that in turn contains one SqlParameter object for each formal parameter. The SqlParameter object's ParameterName property matches the name of the formal parameter (including the "
@"), the SourceColumn property contains the name of the column from which the value is to come, and the SourceVersion property specifies the version of the value that is to be used. Row versions were discussed in the previous section, "Changing, Adding, and Deleting Rows."
If desired, a DataSet object's UpdateCommand, InsertCommand, and DeleteCommand properties can be set directly. Example 8-3 sets the value of UpdateCommand and then performs an update using this command.
Example 8-3: Setting a DataSet object's UpdateCommand property
' Open a database connection. Dim strConnection As String = _ "Data Source=localhost;Initial Catalog=Northwind;" _ & "Integrated Security=True" Dim cn As SqlConnection = New SqlConnection(strConnection) cn.Open( ) ' Set up a data adapter object. Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Categories", cn) ' Create an UPDATE command. ' ' This is the command text. ' Note the parameter names: @Description and @CategoryID. Dim strUpdateCommand As String = _ "UPDATE Categories" _ & " SET Description = @Description" _ & " WHERE CategoryID = @CategoryID" ' ' Create a SqlCommand object and assign it to the UpdateCommand property. da.UpdateCommand = New SqlCommand(strUpdateCommand, cn) ' ' Set up parameters in the SqlCommand object. Dim param As SqlParameter ' ' @CategoryID param = da.UpdateCommand.Parameters.Add( _ New SqlParameter("@CategoryID", SqlDbType.Int)) param.SourceColumn = "CategoryID" param.SourceVersion = DataRowVersion.Original ' ' @Description param = da.UpdateCommand.Parameters.Add( _ New SqlParameter("@Description", SqlDbType.NChar, 16)) param.SourceColumn = "Description" param.SourceVersion = DataRowVersion.Current ' Load a data set. Dim ds As DataSet = New DataSet( ) da.Fill(ds, "Categories") ' Get the table. Dim dt As DataTable = ds.Tables("Categories") ' Get a row. Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0) ' Change the value in the Description column. row("Description") = "Milk and stuff" ' Perform the update. da.Update(ds, "Categories") ' Close the database connection. cn.Close( )
In the next installment from this book chapter, learn about the relations between DataTables in a DataSet.
View catalog information for Programming Visual Basic .NET
Return to the .NET DevCenter.