Date: February 6, 2004
Download the code.
Updating with the SQLDataAdapter...
Using the SQLDataAdapter's Update method is a very valuable and timesaving approach when synching a DataSet with a database table.
Date: February 6, 2004
Download the code.
Using the SQLDataAdapter's Update method is a very valuable and timesaving approach when synching a DataSet with a database. The SQLDataAdapter updates the SQL Server data source through the DataSet object, which tracks changes at the row level, through the RowState property. There are several methods for updating a DataSet against the data source. The first option is by hard-coding a parameterized SQL string, such as:
|
Another approach is to pass a select statement into the SQLCommandBuilder object. It uses a SQL select statement to generate the parameterized insert, update, and delete statements.
|
Dim objConnection As New SqlConnection("Connection String") Dim objAdapter As New SqlDataAdapter objAdapter.SelectCommand = New SqlCommand("select * from Books", objConnection) Dim objBuilder As New SqlCommandBuilder(objAdapter) objConnection.Open() Dim objDataSet As New DataSet objAdapter.Fill(objDataSet, "Books") . . . objAdapter.Update(objDataSet, "Books") objConnection.Close() |
These two options for database updates will not be discussed here. Instead, the option we will use for calling the update method is to use SQL Server’s stored procedures. The code below will generate a mapping between SQL Server and the DataSet. Two mappings will take place; between the source and destination columns, and between the source columns and the destination stored procedure parameters. The difference will be elaborated on below. To further illustrate this example, the following table declaration will be used:
|
CREATE TABLE [dbo].[Books] ( [BookID] [int] IDENTITY (1, 1) NOT NULL , [BookName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Description] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ISBN] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Discontinued] [bit] NOT NULL CONSTRAINT [DF_Books_Discontined] DEFAULT (0), PRIMARY KEY CLUSTERED ( [BookID] ) ON [PRIMARY] ) ON [PRIMARY] GO |
The columns in the DataSet's table, to stand out in the ADO.NET code, will be prefixed with 'DS_.' Also, assume that the code has an "Imports System.Data" declaration. To map the DataSet's DataTable to the SQL Server, a table and column mapping is created. The objects to create this mapping are available in the System.Data.Common namespace. The last step is for the DataTableMapping object to be assigned to the SQLDataAdapter.
|
Dim objMappings as new Common.DataTableMapping("Books", "DS_Books") objMappings.Columns.Add("BookID", "DS_BookID") objMappings.Columns.Add("BookName", "DS_BookName") objMappings.Columns.Add("Description", "DS_Description") objMappings.Columns.Add("ISBN", "DS_ISBN") objMappings.Columns.Add("Discontinued", "DS_Discontinued") objAdapter.TableMappings.Add(objMappings) |
The DataTableMapping object is used to create the table and column mappings between the source and destination. The DataTable name specified in the table mapping is established by the SQLDataAdapter's fill method, the DataTable's TableName property, or through the default value of "Table1".
The next step is the creation of the parameters (SQLParameter and SQLParameters objects) for the InsertCommand, UpdateCommand, and DeleteCommand objects. The parameter list must also exist in the stored procedure definition. The only exception is when the parameter specifies a default value. By not specifying a parameter in the Command object, the default value is used.
|
'Insert the rows with the RowState = "Added" objAdapter.InsertCommand.Parameters.Add(new SQLParameter("@BookName", SQLDbType.VarChar, 255, "DS_BookName")) objAdapter.InsertCommand.Parameters.Add(new SQLParameter( "@Description", SQLDbType.VarChar, 2000, "DS_Description")) objAdapter.InsertCommand.Parameters.Add(new SQLParameter( "@ISBN", SQLDbType.Char, 13, "DS_ISBN")) objAdapter.InsertCommand.Parameters.Add(new SQLParameter( "@Discontinued", SQLDbType.Bit, 1, "DS_Discontinued")) 'Insert the rows with the RowState = "Modified" objAdapter.UpdateCommand.Parameters.Add(new SQLParameter( "@BookID", SQLDbType.int, 10, "DS_BookID")) objAdapter.UpdateCommand.Parameters.Add(new SQLParameter( "@BookName", SQLDbType.VarChar, 255, "DS_BookName")) objAdapter.UpdateCommand.Parameters.Add(new SQLParameter( "@Description", SQLDbType.VarChar, 2000, "DS_Description")) objAdapter.UpdateCommand.Parameters.Add(new SQLParameter( "@ISBN", SQLDbType.Char, 13, "DS_ISBN")) objAdapter.UpdateCommand.Parameters.Add(new SQLParameter( "@Discontinued", SQLDbType.Bit, 1, "DS_Discontinued")) 'Insert the rows with the RowState = "Deleted" objAdapter.DeleteCommand.Parameters.Add(new SQLParameter( "@BookID", SQLDbType.int, 10, "DS_BookID")) |
This set of parameter statements is the second set of mappings that need to take place. The stored procedure parameter name must be mapped to the DataSet name in order for the SQLDataAdapter to know which fields to use to perform the update.
Please note that the BookID isn't included in the InsertCommand object's parameter list because it is an identity field. When the Update method is generated, any added rows in the DataSet will be inserted into the SQL Server database, an identity value will be created for the row, and the DataSet will be refreshed with the new ID value. Also note that you can't shortcut the parameter list by creating a single SQLParameter object and adding it to multiple command objects. Because it doesn't support cloning, the first command object that the parameter is assigned to is considered the "owner".
The SQLDataAdapter will execute the appropriate command object based on the RowState property. The DataSet will be updated to reflect the identity values (and any other values that may have been changed by a stored procedure or trigger). Also, the RowState properties will be set to the default value of "Unchanged".
|
ObjConnection.Open() objAdapter.Update(objDataSet) objConnection.Close() |
The complete code for the SQL Server script to create the test table and stored procedures as well as a sample aspx page may be downloaded from this site.
You may download the code here.