????? C# .NET ??? SqlDataAdapter ???????? ?? ????? ?? ???? SQL Server ??????? ?? ?????? ???? ????

?? ???? ??? Microsoft Visual C# .NET ??? ????? ?? ????? ???? ?? ??? ???? demonstrate ??SqlDataAdapterobject to update a SQL Server database with data modifications that are run on aDataSetobject that is populated with data from a table in the database.

??????????

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 C# .NET
• Microsoft SQL Server version 7.0 or later

This article assumes that you are familiar with the following topics:

• Visual C# .NET
• ADO.NET fundamentals and syntax

Description of the Technique

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

TheInsertCommand,UpdateCommand, and theDeleteCommandproperties of theSqlDataAdapterobject update the database with the data modifications that are run on aDataSet???????? ??? These properties areSqlCommandobjects that specify the INSERT, the UPDATE, and the DELETE Transact-SQL commands that are used to post the dataset modifications to the target database. TheSqlCommandobjects that are assigned to these properties can be created manually in code or automatically generated by using theSqlCommandBuilder???????? ???

The first code sample in this article demonstrates how to use theSqlCommandBuilderobject to automatically generate theUpdateCommandproperty of theSqlDataAdapter???????? ??? The second sample uses a scenario in which you cannot use automatic command generation. The second sample demonstrates how to manually create and use aSqlCommandobject as theUpdateCommand?? ??? ????SqlDataAdapter???????? ???

????? SQL Server ?????? ?????

????? ?? ??? ??? ????? SQL Server ?????? ?? ????? ?? ???? ??? ?? ?? ???? ??? ????? ????? C# .NET ??? ????? ???, ????? ????? ?? ???? ????:

1. SQL ?????? ???????? ?????, ?? ?????? ?? ????? ?????? ????? ?? ??? ??????? ?? ?????? ????? ??? ????? ??? ?? ???? ????????????????????? ????? Microsoft SQL Server ?? ??? ????? ???
2. CustTest ??? ?? ?? ????? ?????? ????? ?? ??? ?? ?????? ??? ?????? ???????? ???? ?? ???, ????? Transact SQL ??? ?????:

   Create Table CustTest
   (
    CustID int primary key,
    CustName varchar(20)
   )
   
   Insert into CustTest values(1,'John')
   					

1 ??? ?????: ???? ?? ??????? ????

??? ??? ??? populates ???? ?? ???????????? ???? ?? ????? ???? ???? ????DataSet???? ??, ??? ??? ??????? ?????? ?? ?? ?? ????? ?? ???? ???CommandBuilder????: ????? ???? ?? ??? ????????DeleteCommand,InsertCommand, ??UpdateCommand?? ???DataAdapter. ?? simplifies ?? ??? ???????? ????, UDPATE ?? DELETE ???????? ???? ?? ??? ?????? ?? ?? ?? ?? ???? ???

??????? ????????, ?? ??? ??? ?? ??? ???? ????SelectCommand???? ?? ????: ????? ???? ?? ??? ???? ?????? ?????? ??SelectCommandretrieves determines the syntax of the INSERT, the UPDATE, and the DELETE statements that are automatically generated.

TheSelectCommandproperty 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 C# .NET console application that demonstrates how to use theSqlCommandBuilderobject to automatically generate theDeleteCommand,InsertCommand, and theUpdateCommandproperties of theSqlCommandobject for aSqlDataAdapterobject, follow these steps:

1. To create a new Visual C# .NET console application, follow these steps:
   1. Microsoft Visual Studio .NET ???? ????..
   2. ????? ???????????? ??,????? ????-????? ????, ?? ???? ????????????.
   3. ????? ????,????? C# ??????????? ???????????????? ???????? ????-????? ????, ?? ???? ???????? ??????????? ???????????????.
2. Class1 ?? ??????? ??????? ?? ????? ??? ?? ???????????? ????:

   using 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 modification to the database.
           da.Update(CustomersDataSet, "Customers");        
   
           Console.WriteLine("Customer Name updated successfully");
   
           //Close the database connection.
           cn.Close();
   
           //Pause
           Console.ReadLine();
         }
      }
   
   }

3. ???? ?????? ?? ??? ??????? ??????? ???????? ?? ??????? ?????
4. ??????, ?? ???? ??? ????????? ?????? ????? ??? ?? ?? ?????? ????? ????? ?? ?? ???? ??? ????? ?????? ????????? ???? ??:

   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
   						

5. ?????? ????? ?? ?????? ???? ?? ??? ?? ????????? ?? ??? ???? ?? ??? ??? ????? ??????

??? ????? 2: ????????? ????? ?? UpdateCommand ??? ??????? ????

?????? ??? ????? 1 ????? ?? ?? ????? ???? ?? ?? ????: ?????? ????? ?? ??? ???? ????? ???? ?? ??? ????? ???????????? concurrency ?? ?????? ??? ??????, ?????? ??????? ???? ?? ??? ??????? ???? ???, ?? ???? ???????????? ?? ??????????? ??????? ?? ???? ??? ??????? ???

?? ??? ??? ??? ?? ???? ?? ????? ?????? ???? ?? ???? ??? ???? ?? ??? ?????? ?? ??????? ???? ?? ????, ??????? ?????? ??? ?? ???????? ??? ?? ????? ?? ?? ??? ??? ??? ?? ???? ?? ??? ?? ??? ?? ??? ??? ??? ???? ?? ?????? ?????? ?? ??? ?? ?? ???? ?? ???????? ???? ?? ???? ?? ??? ??? 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 theDataSet, 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 theDataSethas been filled but before the database is updated, and then delete the one row in the table from SQL Query Analyzer. The??????call then throws the exception.

??? ?? ?????? ??? ?? ??? ??? ?? ???? ????? ???? ?? ???, ???? ?????? ??? ?? ??? ???? ?????? ??UpdateCommand?? ???DataAdapter?? ????: ???? ????? ?? ?????? ???? ???

???????? ??? ?? ????? ?? ??????? ???? ?? ???UpdateCommand?? ???SqlDataAdapter???????? ????? ??? 1, ??? ????? ???? ???? ?? ?? ????? ?? ???? ????:

1. ?????? ??? ??? ??????????????? C# .NET ????? ????????? ??? ???? ?????? ???? ?? ??? Class1 ?? ?????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);
   
   //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.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. ???? ?????? ?? ??? ??????? ??????? ???????? ?? ??????? ?????
3. ??? 4 ??? ?? 1 ???????1 ??? ?????: ???? ?? ??????? ???????? ??? ???? ?? ??? DBConcurrencyException ????? ??????? ???? ???