How To Update a SQL Server Database by Using the SqlDataAdapter Object in Visual Basic .NET (308055)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual Basic .NET (2002)
  • Microsoft Visual Basic .NET (2003)
This article was previously published under Q308055
For a Microsoft Visual C# .NET version of this article, see 308507.
For a Microsoft Visual J# .NET version of this article, see 320633.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.SqlClient

IN THIS TASK

SUMMARY

This article contains Microsoft Visual Basic .NET code samples that demonstrate how to use the SqlDataAdapter object to update a SQL Server database with data modifications that are run on a DataSet object that is populated with data from a table in the database.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that are required:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server version 7.0 or later
This article assumes that you are familiar with the following topics:
  • Visual Basic .NET
  • ADO.NET fundamentals and syntax
back to the top

Description of the Technique

The SqlDataAdapter object serves as a bridge between an ADO.NET DataSet object and a SQL Server database. SqlDataAdapter is an intermediary object that populates an ADO.NET DataSet object with data that is retrieved from a SQL Server database and then updates the database to reflect the changes (such as inserts, updates, and deletes) that are made to the data by using the DataSet object.

The InsertCommand, the UpdateCommand, and the DeleteCommand properties of the SqlDataAdapter object update the database with the data modifications that are run on a DataSet object. These properties are SqlCommand objects that specify the INSERT, the UPDATE, and the DELETE Transact-SQL commands that are used to post the dataset modifications to the target database. The SqlCommand objects that are assigned to these properties can be created manually in code or automatically generated by using the SqlCommandBuilder object.

The first code sample in this article demonstrates how to use the SqlCommandBuilder object to automatically generate the UpdateCommand property of the SqlDataAdapter object. The second sample uses a scenario in which you cannot use automatic command generation. The second sample demonstrates how to manually create and use a SqlCommand object as the UpdateCommand property of a SqlDataAdapter object.

back to the top

Create the Sample SQL Server Table

To create a sample SQL Server table that you can use in the Visual Basic .NET code samples that are documented in this article, follow these steps:
  1. Open SQL Query Analyzer, and then connect to the database in which you want to create the sample table. The code samples in this article use the Northwind database that is included with SQL Server.
  2. To create a sample table that is named CustTest and to insert a record into the table, run the following Transact-SQL statements:
    Create Table CustTest
(
 CustID int primary key,
 CustName varchar(20)
)

Insert into CustTest values(1,'John')
back to the top

Code Sample 1: Automatically Generated Commands

If the SELECT statement that you use to retrieve the data that populates a DataSet is based on a single database table, you can use the CommandBuilder object to automatically generate the DeleteCommand, the InsertCommand, and the UpdateCommand properties of the DataAdapter. This simplifies and reduces the code that is required to perform INSERT, UDPATE, and DELETE operations.

As a minimum requirement, you must set the SelectCommand property to generate commands automatically. The table schema that the SelectCommand retrieves determines the syntax of the INSERT, the UPDATE, and the DELETE statements that are automatically generated.

The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated.

To create a sample Visual Basic .NET console application that demonstrates how to use the SqlCommandBuilder object to automatically generate the DeleteCommand, the InsertCommand, and the UpdateCommand properties of the SqlCommand object for a SqlDataAdapter object, follow these steps:
  1. To create a new Visual Basic .NET console application, follow these steps:
    1. Start Microsoft Visual Studio .NET.
    2. On the File menu, point to New, and then click Project.
    3. Click Visual Basic Projects under Project Types, and then click Console Application under Templates.
  2. Replace the default contents of Module1 with the following code:
    Imports System.Data.SqlClient
Imports Console = System.Console

Module Module1

    Sub Main()

        Dim cn As New SqlConnection()
        Dim CustomersDataSet As New DataSet()
        Dim da As SqlDataAdapter
        Dim dr As DataRow
        Dim cmdBuilder As SqlCommandBuilder  
  
        'Set the connection string of the SqlConnection object to connect to the
        'SQL Server database in which you created the sample table.

        cn.ConnectionString = "Server=.;Database=northwind;UID=sa;PWD=;"
        cn.Open()      

        
        'Initialize the SqlDataAdapter object by specifying a Select command 
        'that retrieves data from the sample table.

        da = New SqlDataAdapter("select * from CustTest order by CustId", cn)


        'Initialize the SqlCommandBuilder object to automatically generate and initialize
        'the UpdateCommand, InsertCommand and DeleteCommand properties of the SqlDataAdapter.

        cmdBuilder = New SqlCommandBuilder(da)

        'Populate the dataset by running the Fill method of the SqlDataAdapter.
        da.Fill(CustomersDataSet, "Customers")

       
        'Display the Update, Insert and Delete commands that were automatically generated
        'by the SqlCommandBuilder object. 

        Console.WriteLine("Update command Generated by the Command Builder : ")
        Console.WriteLine("==================================================")
        Console.WriteLine(cmdBuilder.GetUpdateCommand.CommandText)
        Console.WriteLine("         ")

        Console.WriteLine("Insert command Generated by the Command Builder : ")
        Console.WriteLine("==================================================")
        Console.WriteLine(cmdBuilder.GetInsertCommand.CommandText)
        Console.WriteLine("         ")        

        Console.WriteLine("Delete command Generated by the Command Builder : ")
        Console.WriteLine("==================================================")
        Console.WriteLine(cmdBuilder.GetDeleteCommand.CommandText)
        Console.WriteLine("         ")

        'Write out the value in the CustName field before updating the data using the DataSet.
        Console.WriteLine("Customer Name before Update : " & CustomersDataSet.Tables("Customers").Rows(0)("CustName"))

        'Modify the value of the CustName field.
        CustomersDataSet.Tables("Customers").Rows(0)("CustName") = "Jack"

        'Post the data modification to the database.
        da.Update(CustomersDataSet, "Customers")        

        Console.WriteLine("Customer Name updated successfully")

        'Close the database connection.
        cn.Close()

        Console.ReadLine()

    End Sub

End Module
  3. Save and then run the application. A console window opens and displays the following output:

    Update command Generated by the Command Builder : 
==================================================
UPDATE CustTest SET CustID = @p1 , CustName = @p2 WHERE ( CustID = @p3 AND CustName = @p4 )
         
Insert command Generated by the Command Builder : 
==================================================
INSERT INTO CustTest( CustID , CustName ) VALUES ( @p1 , @p2 )
         
Delete command Generated by the Command Builder : 
==================================================
DELETE FROM  CustTest WHERE ( CustID = @p1 AND CustName = @p2 )   
      
Customer Name before Update : John
Customer Name updated successfully

  4. Press any key to dismiss the console window and to stop the application.
back to the top

Code Sample 2: Manually Create and Initialize the UpdateCommand Property

The output that Code Sample 1 generates indicates that the logic to generate commands automatically for UPDATE statements is based on optimistic concurrency. That is, records are not locked for editing, and other users or processes can modify records whenever.

Because a record can be modified after it is returned from the SELECT statement but before the UPDATE statement is issued, the UPDATE statement that is automatically generated contains a WHERE clause so that a row is updated only if it contains all of the original values. This is to avoid overwriting new data. If an UPDATE statement that is automatically generated tries to update a row that has been deleted or does not contain the original values that are found in the DataSet, the command does not affect any records, and a DBConcurrencyException exception is generated. To test this with the code in Code Sample 1, run the code in the Visual Studio Debugger, set a breakpoint after the DataSet has been filled but before the database is updated, and then delete the one row in the table from SQL Query Analyzer. The Update call then throws the exception.

If you want the UPDATE statement to complete regardless of original values, you must explicitly set the UpdateCommand for the DataAdapter and not rely on automatic command generation.

To manually create and initialize the UpdateCommand property of the SqlDataAdapter object that is used in Code Sample 1, follow these steps:
  1. Replace the existing code in Sub Main of Module1 in the Visual Basic .NET console application that you created in the Code Sample 1: Automatically Generated Commands section with the following code:
    Dim cn As New SqlConnection()
Dim CustomersDataSet As New DataSet()
Dim da As SqlDataAdapter
Dim dr As DataRow        
Dim DAUpdateCmd As SqlCommand


cn.ConnectionString = "Server=.;Database=northwind;UID=sa;PWD=;"
cn.Open()

da = New SqlDataAdapter("select * from CustTest order by CustId", cn)

'Initialize the SqlCommand object that will be used as the DataAdapter's UpdateCommand.
'Note that the WHERE clause uses only the CustId field to locate the record that is to be updated.

DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)


'Create and append the parameters for the Update command.

DAUpdateCmd.Parameters.Add(New SqlParameter("@pCustName", SqlDbType.VarChar))
DAUpdateCmd.Parameters("@pCustName").SourceVersion = DataRowVersion.Current
DAUpdateCmd.Parameters("@pCustName").SourceColumn = "CustName"

DAUpdateCmd.Parameters.Add(New SqlParameter("@pCustId", SqlDbType.Int))
DAUpdateCmd.Parameters("@pCustId").SourceVersion = DataRowVersion.Original
DAUpdateCmd.Parameters("@pCustId").SourceColumn = "CustId"


'Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
da.UpdateCommand = DAUpdateCmd        

da.Fill(CustomersDataSet, "Customers")        

Console.WriteLine("Customer Name before Update : " & CustomersDataSet.Tables("Customers").Rows(0)("CustName"))

CustomersDataSet.Tables("Customers").Rows(0)("CustName") = "Jack"
da.Update(CustomersDataSet, "Customers")        

Console.WriteLine("Customer Name updated successfully")

cn.Close()
Console.ReadLine()
  2. Repeat steps 1 through 4 in the Code Sample 1: Automatically Generated Commands section. Note that a DBConcurrencyException exception is no longer generated.
back to the top
Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbHOWTOmaster kbSqlClient kbSystemData KB308055 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.