The release number of this article has been CHS308507
This task content
Summary requirements technical description
Create an example SQL Server Table Code Example 1 - Automatically generated command code example 2 - Manually create and initialize the updateCommand properties
For Microsoft Visual Basic .NET versions of this article, see
Q308055.
SUMMARY This article contains the Microsoft Visual C # .NET code example, these examples demonstrate how to use
SqlDataAdapter object, running
Data Modify Update SQL Server Database on DataSet Objects,
DataSet objects are filled with data in this database.
Back to top
The following items are required to introduce the recommended hardware, software, network infrastructure, techniques, knowledge, and service packs.
Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server or Windows NT 4.0 Server Microsoft Visual Studio .Net Microsoft SQL Server 7.0 or later This article assumes that you are familiar with the following topics:
Visual Basic .NET ADO .NET Foundation and Syntax
Back to top
Technical Description
SqlDataAdapter object is ActiveX Data Objects (ADO) .NET
The bridge between the DataSet object and the SQL Server database. It is an intermediate object that can be used to perform the following: populate the ADO .NET dataset with data retrieved from the SQL Server database, then update the database to reflect
DataSet object changes to data (such as insert, update, and delete).
SqlDataAdapter object
InsertCommand,
UpdateCommand and
DeleteCommand property is running
Data modifications on the DataSet object to update the database. These properties are used to specify INSERT, UPDATE, and DELETE TRANSACT-SQL commands.
SQLCommand objects, these commands are used to pass the dataset to the target database. Assigned to these properties
SQLCOMMAND objects can be created manually with code, or by using
The SQLCommandBuilder object is automatically generated.
The first code example in this article demonstrates how to use
SQLCOMMAndBuilder object is automatically generated
SqlDataAdapter object
UpdateCommand properties. The second example uses a solution that cannot be generated using an automatic command to demonstrate how manually
SQLCOMMAND object and use it as
SqlDataAdapter object
UpdateCommand properties.
Back to top
Creating an Example SQL Server Table To create an example SQL Server table (available in the Visual Basic .NET code example described in this article), follow these steps:
Open the SQL Server query analyzer and then connect to the database you want to create. The code examples in this article use the Northwind database included with SQL Server. Run the following Transact-SQL statement to create a sample table named CustTest, then insert a record in this table: Create Table CustTest
(
Custid Int Primary Key,
Custname varchar (20)
)
INSERT INTO CustTest Values (1, 'John')
Back to top
Code Example 1 - Automatically generated commands If used to retrieve fill
The SELECT statement of DataSet's data is based on a single database table, and the CommandBuilder object can be automatically generated.
DataAdapter
DeleteCommand,
INSERTCOMMAND and
UpdateCommand properties. This will simplify and reduce the code necessary to perform insert, update, and delete the operation.
As the minimum requirement, you must set
SELECTCOMMAND attributes enables automatic commands to be active.
SelectCommand retrieve the table architecture determines the syntax of the automatically generated INSERT, UPDATE, and DELETE statements.
SelectCommand must also return at least one primary key or unique column. If nothing is,
InvalidOperation is exception and does not generate these commands.
To create an example Visual C # .NET console application (how to use
SQLCOMMAndBuilder object is automatically generated
SqlDataAdapter object
DeleteCommand,
INSERTCOMMAND and
UpdateCommand
SQLCOMMAND object properties, follow these steps:
Create a new Visual C # .NET console application. Replace the default content of Class1 to the following code: use system.data;
Using system.data.sqlclient;
Using system;
Namespace Q308507 {
Class class1 {
Static void main (string [] args) {
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.connectionstring = "Server = server; database = northwind; uid = login; pwd = password;";
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 model.
Da.Update (CustomersDataSet, "Customers");
Console.writeline ("Customer Name Updated SuccessFully);
// Close The Database Connection.
Cn.close ();
// PAUSE
Console.readline ();
}
}
} Modify the following code to reflect your SQL Server and credentials: cn.connectionstring = "Server = server; database = northwind; uid = login; pwd = password;"; saved and run the application. A console window will open and display the following: Update Command Generated by the Command Builder:
=============================================================================================================================================================================================================
Update CustTest Set CustId = @ p1, custname = @ p2 where (custom = @ p3 and custom = @ 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 customtest where (custom = @ p1 and customAme = @ p2)
Customer Name Before Update: John
Customer Name Updated SuccessFully Press any key to close the console window and stop the application.
Back to top
Code Example 2 - Manually Creating and Initializing UpdateCommand Properties Code Example 1 The resulting output indicates that the logic of commands for automatically generating an UPDATE statement is based on optimism. That is, the records are not locked for editing, and can be modified by other users or processes at any time. Because the record is after returning from the SELECT statement, the UPDATE statement may have been modified before, so the automatically generated UPDATE statement contains the WHERE clause so that only the row contains all the original values and not being deleted. The purpose of this is to avoid rewritten new data. If the auto-generated update attempt is updated, the row has been deleted or not included.
The original value found in the DataSet, then the command does not affect any record and generate
DBConcurrencyException.
If you want the UPDATE statement, no matter whether the original value is complete, you must explicitly set
DataAdapter
UpdateCommand instead of relying on auto command generation.
To manually create and initialize the code example 1
SqlDataAdapter object
UpdateCommand Properties, follow these steps:
Replace existing code in the main function of CLASS1 is the following code, Class1 is located in the C # .NET console application created in Code Example 1: SqlConnection CN = New SqlConnection ();
DataSet CustomersDataSet = New DataSet ();
SqlDataAdapter Da;
Sqlcommand daupdatecmd;
Cn.connectionstring = "Server = server; database = northwind; uid = login; pwd = password;";
Cn.open ();
DA = New SqldataAdapter ("Select * from CustTest Order By Custid", CN);
.
// Note That the where clause uss Only the custid find to locate the record to beddated.
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);
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 (); Modify the following code to reflect your SQL Server and credentials: cn.connectionstring = "server = server; data = northwind; uid = login; pwd = password;"; Run "code example 1 - Automatically generated Step 1 to 4 in the command section. Note that it is no longer born into DBConcurrencyException error message.
Back to top
The information in this article applies to:
Microsoft ADO .NET (included in .NET Framework) Microsoft Visual C # .net (2002)
Recent Updated: 2002-2-24 (1.0) Keyword kbdsupport kbgrpdsvbdb kbhowto kbhowtomaster KB308507
