ÇÔ¸Õ¡ÒûÃѺ»Ãا°Ò¹¢éÍÁÙÅ SQL Server â´Â¡ÒÃãªéÇѵ¶Ø SqlDataAdapter ã¹ Visual .NET c#

º·¤ÇÒÁ¹Õé»ÃСͺ´éǵÑÇÍÂèÒ§ÃËÑÊ Microsoft Visual c# .NET ·ÕèáÊ´§ãËéàË繶֧ÇÔ¸Õ¡ÒÃãªé¡ÒÃSqlDataAdapterÇѵ¶Ø¡ÒûÃѺ»Ãا°Ò¹¢éÍÁÙÅ SQL Server ´éÇ¡ÒûÃѺà»ÅÕè¹¢éÍÁÙÅ·Õè·Ó§Ò¹º¹µÑǪش¢éÍÁÙÅÇѵ¶Ø·ÕèÁÕ¡ÒÃàµÔÁ¢éÍÁÙÅãËé¡Ñº¢éÍÁÙŨҡµÒÃҧ㹰ҹ¢éÍÁÙÅ

¤ÇÒÁµéͧ¡ÒÃ

ÃÒ¡ÒõèÍ仹ÕéáÊ´§ÎÒÃì´áÇÃì·Õèá¹Ð¹Ó «Í¿µìáÇÃì â¤Ã§ÊÃéÒ§¾×é¹°Ò¹¢Í§à¤Ã×Í¢èÒ ·Ñ¡ÉÐ áÅзÃÒº áÅÐà«ÍÃìÇÔÊá¾ç¤·Õè¨Óà»ç¹µéͧãªé:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 ¢Ñé¹ÊÙ§à«ÔÃì¿àÇÍÃì ËÃ×Íà«ÔÃì¿àÇÍÃì Windows NT 4.0
• Microsoft Visual Studio .NET
• Microsoft Visual c# .NET
• Microsoft SQL Server ÃØè¹ 7.0 ËÃ×ÍÃØè¹ãËÁè¡ÇèÒ

º·¤ÇÒÁ¹Õé͹ØÁÒ¹ÇèÒ ¤Ø³äÁè¤Øé¹à¤Â¡ÑºËÑÇ¢é͵èÍ仹Õé:

• ÀÒ¾.NET c#
• ¢éÍÁÙž×é¹°Ò¹¢Í§à´Ê¡ì·çÍ» ADO.NET áÅÐäÇÂҡóì

¤Ó͸ԺÒÂà¡ÕèÂǡѺ෤¹Ô¤

¡Ãкǹ¡ÒÃSqlDataAdapterÇѵ¶Ø·Õè·Ó˹éÒ·Õèà»ç¹ºÃÔ´¨ìÃÐËÇèÒ§¡Òà ADO.NETªØ´¢éÍÁÙÅÇѵ¶ØáÅаҹ¢éÍÁÙÅ SQL ServerSqlDataAdapterÁÕÇѵ¶Ø·Õè populates ADO.NET à»ç¹Ê×èÍ¡ÅÒ§ªØ´¢éÍÁÙÅÇѵ¶Ø·Õè ÁÕ¢éÍÁÙÅ·Õè´Ö§ÁÒ¨Ò¡°Ò¹¢éÍÁÙÅ SQL Server ¨Ò¡°Ò¹¢éÍÁÙÅà¾×èÍãËéÊзé͹¶Ö§¡ÒÃà»ÅÕè¹á»Å§ (àªè¹á·Ã¡ »ÃѺ»Ãا áÅÐź) ·Õèà¡Ô´¢Ö鹡Ѻ¢éÍÁÙÅ â´Âãªé¡ÒûÃѺ»Ãا ¹ÕéªØ´¢éÍÁÙÅÇѵ¶Ø

¡Ãкǹ¡ÒÃInsertCommand¡Ãкǹ¡ÒÃUpdateCommandáÅÐDeleteCommand¤Ø³ÊÁºÑµÔ¢Í§SqlDataAdapterÇѵ¶Ø·Õè»ÃѺ»Ãا°Ò¹¢éÍÁÙÅ ´éÇ¡ÒûÃѺà»ÅÕè¹¢éÍÁÙÅ·ÕèàÃÕ¡ãªéº¹µÑǪش¢éÍÁÙÅÇѵ¶Ø ¤Ø³ÊÁºÑµÔàËÅèÒ¹Õéä´éSqlCommandÇѵ¶Ø·ÕèÃÐºØ INSERT, UPDATE áÅФÓÊÑè§ DELETE Transact-SQL ·Õèãªé㹡ÒÃŧÃÒ¡ÒúѭªÕ¡ÒûÃѺà»ÅÕ蹪ش¢éÍÁÙÅã¹°Ò¹¢éÍÁÙÅà»éÒËÁÒ ¡Ãкǹ¡ÒÃSqlCommandÇѵ¶Ø·Õèä´éÃѺ¡ÒáÓ˹´¤Ø³ÊÁºÑµÔàËÅèÒ¹ÕéÊÒÁÒö¶Ù¡ÊÃéÒ§¢Öé¹´éǵ¹àͧã¹ÃËÑÊ ËÃ×ÍÊÃéÒ§¢Öé¹â´ÂÍѵâ¹ÁÑµÔ â´Âãªé¡ÒÃSqlCommandBuilderÇѵ¶Ø

µÑÇÍÂèÒ§ÃËÑÊáá㹺·¤ÇÒÁ¹Õé͸ԺÒÂÇÔ¸Õãªé¹ÕéSqlCommandBuilderÇѵ¶Ø·ÕèÊÃéÒ§â´ÂÍѵâ¹ÁѵÔUpdateCommand¤Ø³ÊÁºÑµÔ¹ÕéSqlDataAdapterÇѵ¶Ø µÑÇÍÂèÒ§·ÕèÊͧãªéã¹Ê¶Ò¹¡Òóì·Õè¤Ø³äÁèÊÒÁÒöãªé¡ÒÃÊÃéÒ§¤ÓÊÑè§â´ÂÍѵâ¹ÁÑµÔ ÇÔ¸Õ¡ÒÃÊÃéÒ§´éǵ¹àͧ áÅÐãªéáÊ´§µÑÇÍÂèÒ§ÊͧµÑÇSqlCommandÇѵ¶Øà»ç¹áººUpdateCommand¤Ø³ÊÁºÑµÔ¢Í§¤ÓSqlDataAdapterÇѵ¶Ø

ÊÃéÒ§µÒÃÒ§à«ÔÃì¿àÇÍÃì SQL µÑÇÍÂèÒ§

àÁ×è͵éͧ¡ÒÃÊÃéÒ§µÒÃÒ§ SQL Server µÑÇÍÂèÒ§·Õè¤Ø³ÊÒÁÒöãªéã¹µÑÇÍÂèÒ§ÃËÑÊ.NET c# áÊ´§¼Å·Õèä´éÃѺ¡Òúѹ·Ö¡äÇé㹺·¤ÇÒÁ¹Õé ´Óà¹Ô¹¡ÒôѧµèÍ仹Õé:

1. à»Ô´µÑÇÇÔà¤ÃÒÐËì¤Ó¶ÒÁ SQL áÅСÒÃàª×èÍÁµè͡Ѻ°Ò¹¢éÍÁÙÅ·Õè¤Ø³µéͧ¡ÒÃÊÃéÒ§µÒÃÒ§µÑÇÍÂèÒ§ µÑÇÍÂèÒ§ÃËÑÊã¹¹Õé¡ÒÃãªéº·¤ÇÒÁ¹Õénorthwind°Ò¹¢éÍÁÙÅ·ÕèÁÒ¾ÃéÍÁ¡Ñº Microsoft SQL Server
2. àÁ×è͵éͧ ¡ÒÃÊÃéÒ§µÒÃÒ§µÑÇÍÂèÒ§·Õèª×èÍ CustTest áÅÐá·Ã¡àá¤ÍÃì´ã¹µÒÃÒ§ àÃÕ¡ãªé¤ÓÊÑè§ Transact SQL µèÍ仹Õé:

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

µÑÇÍÂèÒ§ÃËÑÊ 1: ¤ÓÊÑ觷ÕèÊÃéÒ§â´ÂÍѵâ¹ÁѵÔ

¶éÒ¤ÓÊÑè§ SELECT ·Õè¤Ø³ãªé㹡Òô֧¢éÍÁÙÅ·Õè populates à»ç¹ªØ´¢éÍÁÙÅãªéã¹µÒÃÒ§°Ò¹¢éÍÁÙÅà´ÕÂÇ ¤Ø³ÊÒÁÒöãªé¡ÒÃCommandBuilderÇѵ¶Ø·ÕèÊÃéÒ§â´ÂÍѵâ¹ÁѵÔDeleteCommand¡Ãкǹ¡ÒÃInsertCommandáÅÐUpdateCommand¤Ø³ÊÁºÑµÔ¢Í§DataAdapter. simplifies áÅÐÅ´ÃËÑÊ·Õè¨Óà»ç¹µéͧãªéà¾×èÍ·Ó¡ÒôÓà¹Ô¹§Ò¹ INSERT, UDPATE áÅÐź

à»ç¹¤ÇÒÁµéͧ¡ÒõèÓÊØ´ ¤Ø³µéͧµÑ駤èÒ¹ÕéSelectCommand¤Ø³ÊÁºÑµÔà¾×èÍÊÃéÒ§¤ÓÊÑè§â´ÂÍѵâ¹ÁÑµÔ schema ¢Í§µÒÃÒ§·Õè¨ÐSelectCommand´Ö§¡Ó˹´äÇÂҡóì¢Í§ INSERT, UPDATE áÅСÒÃźÃÒ¡Ò÷Õè¶Ù¡ÊÃéÒ§¢Öé¹â´ÂÍѵâ¹ÁѵÔ

¡Ãкǹ¡ÒÃSelectCommand¤Ø³ÊÁºÑµÔµéͧÊ觤׹¹Í¡¨Ò¡¹Õé¤ÕÂìËÅÑ¡ÍÂèÒ§¹éÍÂ˹Öè§ËÃ×ͤÍÅÑÁ¹ì·ÕèäÁè«éӡѹ ¶éÒäÁèÁÕÍÂÙè ÁÕÊÃéÒ§¢éÍ¡àÇé¹ InvalidOperation áÅШÐäÁèÁÕÊÃéÒ§¤ÓÊÑè§

àÁ×è͵éͧ¡ÒÃÊÃéÒ§â»Ãá¡ÃÁ»ÃÐÂØ¡µì¤Í¹â«Å Visual .NET c# µÑÇÍÂèÒ§·ÕèáÊ´§ÇÔ¸Õãªé¹ÕéSqlCommandBuilderÇѵ¶Ø·ÕèÊÃéÒ§â´ÂÍѵâ¹ÁѵÔDeleteCommand¡Ãкǹ¡ÒÃInsertCommandáÅÐUpdateCommand¤Ø³ÊÁºÑµÔ¢Í§SqlCommandÇѵ¶ØÊÓËÃѺ¤ÓSqlDataAdapterÇѵ¶Ø ·ÓµÒÁ¢Ñ鹵͹àËÅèÒ¹Õé:

1. àÁ×è͵éͧ¡ÒÃÊÃéÒ§â»Ãá¡ÃÁ»ÃÐÂØ¡µì.NET c# Visual ¤Í¹â«ÅãËÁè ´Óà¹Ô¹¡ÒôѧµèÍ仹Õé:
   1. àÃÔèÁ Microsoft Visual Studio .NET
   2. 㹡ÒÃá¿éÁ:àÁ¹Ù ãËéªÕéä»·ÕèãËÁèáÅéÇ ¤ÅÔ¡Project.
   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 ºè§ªÕéÇèÒ µÃáÐà¾×èÍÊÃéÒ§¤ÓÊÑè§â´ÂÍѵâ¹ÁѵÔÊÓËÃѺãºá¨é§ÂÍ´ UPDATE ¨Ð¢Öé¹ÍÂÙè¡Ñº¡ÒÃà¡Ô´¾ÃéÍÁ¡Ñ¹ optimistic ¹Ñ蹤×Í àÃ硤ÍÃì´·ÕèäÁèä´é¶Ù¡ÅçÍ¡à¾×èÍá¡éä¢ áÅмÙéãªéÍ×è¹ËÃ×Í¡ÃкǹÊÒÁÒöá¡éä¢àÃ硤ÍÃì´·Ø¡¤ÃÑ駷Õè

à¹×èͧ¨Ò¡ÃÐàºÕ¹ÊÒÁÒö¶Ù¡»ÃѺà»ÅÕè¹ËÅѧ¨Ò¡·ÕèÁÕ¡ÒÃÊ觤׹¨Ò¡¤ÓÊÑè§ SELECT áµè¡è͹ UPDATE ã¹ ¡ÒÃÍÍ¡ãºá¨é§ÂÍ´ §º UPDATE ·Õè¶Ù¡ÊÃéÒ§¢Öé¹â´ÂÍѵâ¹ÁѵԻÃСͺ´éÇÂ͹ػÃÐâ¤à¾×èÍãËéá¶Ç¶Ù¡»ÃѺ»Ãا੾ÒÐàÁ×èÍ»ÃСͺ´éǤèÒàÃÔèÁµé¹·Ñé§ËÁ´ ¹Õè¤×Í¡ÒÃËÅÕ¡àÅÕ觡ÒÃà¢Õ¹·Ñº¢éÍÁÙÅãËÁè 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ªØ´¢éÍÁÙÅ, 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ªØ´¢éÍÁÙÅhas been filled but before the database is updated, and then delete the one row in the table from SQL Query Analyzer. ¡Ãкǹ¡ÒáÒûÃѺ»Ãاcall then throws the exception.

If you want the UPDATE statement to complete regardless of original values, you must explicitly set theUpdateCommandÊÓËÃѺ¡ÒÃDataAdapterand not rely on automatic command generation.

To manually create and initialize theUpdateCommand¤Ø³ÊÁºÑµÔ¹ÕéSqlDataAdapterobject that is used in Code Sample 1, follow these steps:

1. Replace the existing code in theËÅÑ¡function of Class1 in the Visual C# .NET console application that you created in theCode Sample 1: Automatically Generated Commandssection with the following code:

   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. Modify the connection string as appropriate for your environment.
3. Repeat steps 1 through 4 in theCode Sample 1: Automatically Generated CommandsÊèǹ Note that a DBConcurrencyException exception is no longer generated.