Lm th? no ? c?p nh?t c s? d? li?u SQL Server b?ng cch s? d?ng cc ?i t?ng SqlDataAdapter trong Visual C#.NET

D?ch tiu ? D?ch tiu ?
ID c?a bi: 308507 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Bi vi?t ny ch?a Microsoft Visual C#.M?u NET m? ch?ng minh lm th? no ? s? d?ng cc SqlDataAdapter ?i t?ng ? c?p nh?t c s? d? li?u SQL Server v?i s?a ?i d? li?u ang ch?y trn m?t S? li?u ?i t?ng l dn c v?i d? li?u t? m?t b?ng trong c s? d? li?u.

Yu c?u

Danh sch sau v?ch ra ?c ? ngh? ph?n c?ng, ph?n m?m, c s? h? t?ng m?ng, k? nng v ki?n th?c, v gi d?ch v? ?c yu c?u:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, ho?c my ch? Windows NT 4.0
  • Microsoft Visual Studio.NET
  • Microsoft Visual C#.NET
  • Microsoft SQL Server Phin b?n 7.0 ho?c cao hn
Bi vi?t ny gi? ?nh r?ng b?n ? quen thu?c v?i cc ch? ? sau:
  • Visual C#.NET
  • ADO.Nguyn t?c c b?n NET v c php

M t? k? thu?t

Cc SqlDataAdapter ?i t?ng ph?c v? nh m?t c?u n?i gi?a m?t ADO.NET S? li?u ?i t?ng v c s? d? li?u SQL Server. SqlDataAdapter l m?t ?i t?ng trung gian populates m?t ADO.NET S? li?u ?i t?ng v?i d? li?u L?y t? c s? d? li?u SQL Server, sau C?p Nh?t c s? d? li?u ? ph?n nh nh?ng thay ?i (ch?ng h?n nh chn, C?p Nh?t v xo) ?c th?c hi?n cho d? li?u b?ng cch s? d?ng cc S? li?u ?i t?ng.

Cc InsertCommand, cc UpdateCommand, v cc DeleteCommand thu?c tnh c?a cc SqlDataAdapter ?i t?ng C?p Nh?t c s? d? li?u v?i nh?ng s?a ?i d? li?u ang ch?y trn m?t S? li?u ?i t?ng. Cc ?c tnh ny SqlCommand cc ?i t?ng m ch? ?nh CHN, C?p Nh?t v cc l?nh DELETE Transact-SQL ?c s? d?ng ? g?i cc s?a ?i t?p d? li?u c s? d? li?u m?c tiu. Cc SqlCommand cc ?i t?ng ang ?c ch? ?nh cho cc thu?c tnh ny c th? ?c t?o b?ng tay trong m? ho?c t? ?ng t?o ra b?ng cch s? d?ng cc SqlCommandBuilder ?i t?ng.

M?u m? ?u tin trong bi vi?t ny ch?ng t? lm th? no ? s? d?ng cc SqlCommandBuilder ?i t?ng ? t? ?ng t?o ra cc UpdateCommand ti s?n c?a cc SqlDataAdapter ?i t?ng. M?u th? 2 s? d?ng m?t k?ch b?n m b?n khng th? s? d?ng l?nh t? ?ng th? h?. M?u th? hai ch?ng t? lm th? no ? t? t?o v s? d?ng m?t SqlCommand ?i t?ng l cc UpdateCommand ti s?n c?a m?t SqlDataAdapter ?i t?ng.

T?o m?u SQL Server b?ng

? t?o m?t b?ng SQL Server m?u m b?n c th? s? d?ng trong cc Visual C#.M?u NET m? ? ?c di?n t? trong bi vi?t ny, h?y lm theo cc b?c sau:
  1. M? SQL Query Analyzer, v sau k?t n?i c s? d? li?u m b?n mu?n t?o ra b?ng m?u. Bi vi?t m?u m? ? y s? d?ng cc Northwind c s? d? li?u ?c bao g?m v?i Microsoft SQL Server.
  2. ? t?o ra m?t b?ng m?u ?c ?t tn theo CustTest v chn m?t b?n ghi vo b?ng, ch?y cu Transact-SQL sau y:
    Create Table CustTest
    (
     CustID int primary key,
     CustName varchar(20)
    )
    
    Insert into CustTest values(1,'John')
    					

M?u m? 1: T? ?ng t?o ra l?nh

N?u nh?ng tuyn b? ch?n m b?n s? d?ng ? l?y d? li?u populates m?t S? li?u d?a trn m?t b?ng c s? d? li?u duy nh?t, b?n c th? s? d?ng cc CommandBuilder ?i t?ng ? t? ?ng t?o ra cc DeleteCommand, cc InsertCommand, v cc UpdateCommand thu?c tnh c?a cc DataAdapter. i?u ny gip n gi?n ho v lm gi?m m? l c?n thi?t ? th?c hi?n ho?t ?ng CHN, UDPATE v xa.

Nh m?t yu c?u t?i thi?u, b?n ph?i thi?t l?p cc SelectCommand b?t ?ng s?n ? t? ?ng t?o ra l?nh. Gi?n ? b?ng m cc SelectCommand truy xc c php CHN, C?p Nh?t v bo co xa t? ?ng t?o ra.

Cc SelectCommand b?t ?ng s?n c?ng ph?i tr? l?i t nh?t m?t kha chnh ho?c c?t duy nh?t. N?u khng c l hi?n nay, m?t ngo?i l? InvalidOperation ?c t?o ra v cc l?nh khng ?c t?o ra.

? t?o ra m?t m?u Visual C#.?ng d?ng giao di?n i?u khi?n NET ch?ng t? lm th? no ? s? d?ng cc SqlCommandBuilder ?i t?ng ? t? ?ng t?o ra cc DeleteCommand, cc InsertCommand, v cc UpdateCommand thu?c tnh c?a cc SqlCommand ?i t?ng cho m?t SqlDataAdapter ?i t?ng, h?y lm theo cc b?c sau:
  1. ? t?o ra m?t m?i Visual C#.NET ?ng d?ng giao di?n i?u khi?n, h?y lm theo cc b?c sau:
    1. B?t ?u Microsoft Visual Studio.NET.
    2. Trn cc Tp tr?nh n, i?m ?n M?i, sau b?m D an.
    3. Nh?p vo Visual C# Project d?i Cc lo?i d? n, sau b?m Giao di?n i?u khi?n ?ng d?ng d?i Khun mu.
  2. Thay th? n?i dung m?c ?nh c?a Class1 v?i o?n m? sau:
    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. S?a ?i cc chu?i k?t n?i nh l thch h?p cho mi tr?ng c?a b?n.
  4. Lu v sau ch?y cc ?ng d?ng. Nh?n th?y r?ng m?t c?a s? giao di?n i?u khi?n s? m? ra v sau s? hi?n th? ?u ra sau y:
    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. B?m phm b?t k? ? b? qua c?a s? giao di?n i?u khi?n v ? ngn ch?n cc ?ng d?ng.

M?u m? 2: T? t?o v kh?i t?o cc ti s?n UpdateCommand

S?n l?ng m t?o ra cc m? m?u 1 ch? ra r?ng logic ? t?o ra l?nh t? ?ng ? c?p nh?t bo co ?c d?a trn concurrency l?c quan. l, h? s khng b? kha ? ch?nh s?a, v ng?i dng ho?c quy tr?nh khc c th? s?a ?i b?n ghi b?t c? khi no.

B?i v? m?t h? s c th? s?a ?i sau khi n ?c tr? l?i t? b?n tuyn b? ch?n nhng tr?c khi C?p Nh?t tuyn b? ?c ban hnh, tuyn b? b?n c?p nh?t t? ?ng t?o ra ch?a m?t WHERE kho?n do m?t hng ?c C?p Nh?t ch? n?u n c ch?a t?t c? cc gi tr? ban ?u. i?u ny l ? trnh ghi d? li?u m?i. N?u m?t tuyn b? b?n c?p nh?t t? ?ng t?o ra c? g?ng ? c?p nh?t m?t hng m ? b? xa ho?c khng ch?a cc gi tr? ban ?u ?c t?m th?y trong cc S? li?u, l?nh khng ?nh h?ng ?n b?t k? h? s, v m?t ngo?i l? DBConcurrencyException ?c t?o ra. ? th? nghi?m ny v?i m? trong m? m?u 1, ch?y m? trong Visual Studio Debugger, ?t m?t breakpoint sau khi cc S? li?u ? ?c l?p ?y nhng tr?c khi c s? d? li?u ?c C?p Nh?t, v sau xa m?t hng trong b?ng t? SQL Query Analyzer. Cc Cp nht g?i sau nm ngo?i l?.

N?u b?n mu?n bo co C?p Nh?t ? hon thnh b?t k? cc gi tr? ban ?u, b?n ph?i thi?t ?t t?ng minh cc UpdateCommand ?i v?i cc DataAdapter v khng d?a vo l?nh t? ?ng th? h?.

T? t?o v kh?i t?o cc UpdateCommand ti s?n c?a cc SqlDataAdapter ?i t?ng ?c s? d?ng trong m? m?u 1, lm theo cc b?c sau:
  1. Thay th? m? hi?n c trong cc Chnh ch?c nng Class1 trong cc Visual C#.?ng d?ng giao di?n i?u khi?n NET b?n ? t?o ? cc M?u m? 1: T? ?ng t?o ra l?nh ph?n v?i o?n m? sau:
    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. S?a ?i cc chu?i k?t n?i nh l thch h?p cho mi tr?ng c?a b?n.
  3. L?p l?i cc b?c 1 thng qua 4 trong cc M?u m? 1: T? ?ng t?o ra l?nh ke tiep. Lu ? r?ng m?t ngo?i l? DBConcurrencyException khng c?n ?c t?o ra.

Thu?c tnh

ID c?a bi: 308507 - L?n xem xt sau cng: 27 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
T? kha:
kbhowtomaster kbsqlclient kbsystemdata kbmt KB308507 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:308507

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com