SUMMARY
This article contains Microsoft Visual J# .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 Visual J# .NET
- Microsoft SQL Server version 7.0 or later
This article assumes that you are familiar with the following
topics:
- Visual J# .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 can be generated automatically 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 J# .NET code samples in this article, follow these steps:
- 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 Microsoft SQL Server.
- 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
SqlCommandBuilder object to automatically generate the
DeleteCommand, the
InsertCommand, and the
UpdateCommand properties of the
SqlDataAdapter. 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 automatically generate commands. 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 property 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 J# .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:
- To create a new Visual J# .NET console application, follow
these steps:
- Start Microsoft Visual Studio .NET.
- On the File menu, point to New, and then click Project.
- Click Visual J# Project under Project Types, and then click Console Application under Templates.
- Add the following import statements below the package
statement at the top of the Code window:
import System.Data.*;
import System.Data.SqlClient.*;
import System.*;
- Add the following code to the main function in Class1:
SqlConnection cn = new SqlConnection();
DataSet CustomersDataSet = new DataSet();
SqlDataAdapter da;
SqlCommandBuilder cmdBuilder;
//Set the connection string of the SqlConnection object to connect to the SQL Server database in which you created the sample table.
cn.set_ConnectionString("Server=server;Database=Northwind;UID=login;PWD=password;");
cn.Open();
//To initialize the SqlDataAdapter object, specify 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, the InsertCommand, and the DeleteCommand
//properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
//To populate the DataSet, run the Fill method of the SqlDataAdapter.
da.Fill(CustomersDataSet, "Customers");
//Display the Update, the Insert, and the Delete commands that the
//SqlCommandBuilder object automatically generated.
System.Console.WriteLine("Update command Generated by the Command Builder : ");
System.Console.WriteLine("==================================================");
System.Console.WriteLine(cmdBuilder.GetUpdateCommand().get_CommandText());
System.Console.WriteLine(" ");
System.Console.WriteLine("Insert command Generated by the Command Builder : ");
System.Console.WriteLine("==================================================");
System.Console.WriteLine(cmdBuilder.GetInsertCommand().get_CommandText());
System.Console.WriteLine(" ");
System.Console.WriteLine("Delete command Generated by the Command Builder : ");
System.Console.WriteLine("==================================================");
System.Console.WriteLine(cmdBuilder.GetDeleteCommand().get_CommandText());
System.Console.WriteLine(" ");
//Write out the value in the CustName field before you update the data by using the DataSet.
System.Console.WriteLine("Customer Name before Update : " + CustomersDataSet.get_Tables().get_Item("Customers").get_Rows().get_Item(0).get_Item("CustName"));
//Modify the value of the CustName field.
CustomersDataSet.get_Tables().get_Item("Customers").get_Rows().get_Item(0).set_Item("CustName",(System.String)"Jack");
//Post the data modification to the database.
da.Update(CustomersDataSet, "Customers");
System.Console.WriteLine("Customer Name updated successfully");
//Close the database connection.
cn.Close();
//Pause.
Console.ReadLine();
- Modify the connection string as appropriate for your
environment.
- Save and then run the application. Notice that a console
window opens and then displays the following output:
Update command Generated by the Command Builder :
==================================================
UPDATE CustTest SET CustID = @p1 , CustName = @p2 WHERE ( (CustID = @p3) AND ((CustName IS NULL AND @p4 IS NULL) OR (CustName = @p5)) )
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 IS NULL AND @p2 IS NULL) OR (CustName = @p3)) )
Customer Name before Update : John
Customer Name updated successfully
- 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:
- Replace the existing code in the main function of Class1 in the Visual J# .NET console application that
you created in the Code Sample 1: Automatically
Generated Commands section with the following code:
SqlConnection cn = new SqlConnection();
DataSet CustomersDataSet = new DataSet();
SqlDataAdapter da;
SqlCommand DAUpdateCmd;
//Set the connection string of the SqlConnection object to connect to the SQL Server database in which you created the sample table.
cn.set_ConnectionString("Server=server;Database=Northwind;UID=login;PWD=password;");
cn.Open();
//To initialize the SqlDataAdapter object, specify a Select command that
//retrieves data from the sample table.
da = new SqlDataAdapter("select * from CustTest order by CustId", cn);
//Initialize the SqlCommand object that will be used as the UpdateCommand for the DataAdapter.
//Note that the WHERE clause uses only the CustId field to locate the record to be updated.
DAUpdateCmd = new SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.get_SelectCommand().get_Connection());
//Create the parameter collection.
SqlParameterCollection ParamColl = DAUpdateCmd.get_Parameters();
//Add the parameters to the collection.
ParamColl.Add("@pCustName", SqlDbType.VarChar);
DAUpdateCmd.get_Parameters().get_Item("@pCustName").set_SourceVersion(DataRowVersion.Current);
DAUpdateCmd.get_Parameters().get_Item("@pCustName").set_SourceColumn((System.String)"CustName");
ParamColl.Add("@pCustId", SqlDbType.Int);
DAUpdateCmd.get_Parameters().get_Item("@pCustId").set_SourceVersion(DataRowVersion.Current);
DAUpdateCmd.get_Parameters().get_Item("@pCustId").set_SourceColumn((System.String)"CustId");
//Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
da.set_UpdateCommand(DAUpdateCmd);
da.Fill(CustomersDataSet, "Customers");
System.Console.WriteLine("Customer Name before Update : " + CustomersDataSet.get_Tables().get_Item("Customers").get_Rows().get_Item(0).get_Item("CustName"));
CustomersDataSet.get_Tables().get_Item("Customers").get_Rows().get_Item(0).set_Item("CustName","Jack");
da.Update(CustomersDataSet, "Customers");
System.Console.WriteLine("Customer Name updated successfully");
cn.Close();
Console.ReadLine();
- Modify the connection string as appropriate for your
environment.
- Save and then run the application. Note that a
DBConcurrencyException exception is no longer generated if a row is updated
from another application while this code is running.
back to the top