際際滷

際際滷Share a Scribd company logo

04 chuong 4 - cap nhat du lieu

Download as PPT, PDF
truong le hung
truong le hung

ch動董ng 4 ado.net

1 of 47
Downloaded 38 times
C畉P NH畉T D畛 LI畛U Ch動董ng 4
2 N畛i dung C但u l畛nh SQL D湛ng 畛i t動畛ng Command Ch畛nh s畛a d畛 li畛u Disconnected D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet D湛ng 畛i t動畛ng DataAdapter D湛ng 畛i t動畛ng CommandBuilder C畉u h狸nh DataAdapter b畉ng tay
3 C但u l畛nh SQL C但u l畛nh Insert C但u l畛nh Update C但u l畛nh Delete
4 C但u l畛nh SQL Insert C但u l畛nh Insert d湛ng 畛 th棚m 1 d嘆ng d畛 li畛u vo b畉ng Th棚m 1 d嘆ng hon ch畛nh Th棚m 1 s畛 ph畉n d畛 li畛u c畛a 1 d嘆ng Th棚m d畛 li畛u 動畛c l畉y t畛 1 b畉ng kh叩c Th棚m 1 d嘆ng hon ch畛nh Insert into t棚nBang Values(giatri1, giatri2, , giatriN) Insert into t棚nBang(cot1, cot2, , cotN) Values(giatri1, giatri2, , giatriN)
5 C但u l畛nh SQL Insert Th棚m 1 s畛 ph畉n d畛 li畛u c畛a 1 d嘆ng Insert into t棚nBang(cotK1, cotK2, ) Values(giatriK1, giatriK2, ) C畛t kh担ng li畛t k棚 ra s畉 nh畉n gi叩 tr畛 NULL Th棚m d畛 li畛u 動畛c l畉y t畛 1 b畉ng kh叩c Insert into t棚nBang1(cot1, cot2, , CotN) Select cot1, Cot2, , CotN From t棚nBang2 Where
6 C但u l畛nh SQL Insert Ch炭 箪: T畛 kho叩 Into trong c但u l畛nh Insert c坦 th畛 b畛, nh動ng kh担ng n棚n b畛 N棚n li畛t k棚 c叩c c畛t d畛 li畛u khi d湛ng c但u l畛nh insert d畛 li畛u
7 C但u l畛nh SQL Insert Copy t畛 1 b畉ng sang 1 b畉ng kh叩c Select * into t棚nBangMoi From t棚nBang1 Select cotK1, cotK2, into t棚nBangMoi From t棚nBang1, t棚nBang2, Where Group By Ho畉t 畛ng: T畉o b畉ng t棚nBangMoi Ch辿p d畛 li畛u vo t棚nBangMoi
8 C但u l畛nh SQL Update C但u l畛nh Update d湛ng 畛 c畉p nh畉t (ch畛nh s畛a) d畛 li畛u Update t棚nBang Set cot1=newValue1, , cotN=newValueN Where cot1 = oldValue1 and and cotN=oldValueN 畛 x坦a gi叩 tr畛 c畛a 1 担 d畛 li畛u trong b畉ng, ch炭ng ta thi畉t l畉p gi叩 tr畛 坦 b畉ng null Update t棚nBang Set cot1=null, Where cotA = oldValueA
9 C但u l畛nh SQL Delete C但u l畛nh Delete d湛ng 畛 x坦a 1 s畛 d嘆ng d畛 li畛u trong b畉ng Delete From t棚nBang Where cot1=giatri1 and cot2=giatri2 and and cotN=giatriN Ch炭 箪: Delete d湛ng 畛 x坦a c畉 1 d嘆ng, ch畛 kh担ng x坦a 1 c畛t c畛a d嘆ng Delete kh担ng x坦a b畉ng, ch畛 x坦a d畛 li畛u 畛 x坦a m畛i d嘆ng d畛 li畛u trong b畉ng n棚n d湛ng l畛nh Truncate Table T畛 kh坦a From trong c但u l畛nh delete c坦 th畛 b畛, nh動ng kh担ng n棚n b畛
10 D湛ng 畛i t動畛ng Command C叩c b動畛c th畛c hi畛n 畛i t動畛ng Command kh担ng c坦 tham s畛 畛i t動畛ng Command c坦 tham s畛
11 D湛ng 畛i t動畛ng Command C叩c b動畛c th畛c hi畛n 3 b動畛c s畛 d畛ng 畛i t動畛ng command 畛 c畉p nh畉t d畛 li畛u B動畛c 1: T畉o c但u SQL B動畛c 2: T畉o 畛i t動畛ng Command ch畛a c但u SQL B動畛c 3: G畛i ph動董ng th畛c ExecuteNonQuery() c畛a 畛i t動畛ng Command
12 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "INSERT INTO t棚nBang(cot1, ) " + " VALUES (giatri1, )"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Insert
13 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "UPDATE t棚nBang" + " SET cot1 = newValue1,, cotN=giatriN" + " WHERE cot1=oldValue1 and and cotN=oldValueN"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Update
14 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "DELETE FROM t棚nBang" + " WHERE cot1=giatri1 and and cotN=giatriN"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Delete
15 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Insert SqlCommand CreateInsertCommand() { string strSQL; strSQL = "Insert t棚nBang(cot1, ) " + " values(@cot1, )"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); return cmd; }
16 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Update SqlCommand CreateUpdateCommand() { string strSQL; strSQL = "Update t棚nBang " + " set cot1=@cot1, , cotN=@cotN " + " where cot1=@OrigCot1 and and cotN=@OrigCotN"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); pc.Add(@OrigCot1, SqlDbType.Kieu) return cmd; }
17 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Delete SqlCommand CreateDeleteCommand() { string strSQL; strSQL = "Delete from t棚nBang " + " where @cot1=giatri1 and and @cotN=giatriN"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); return cmd; }
18 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 S畛 d畛ng c叩c 畛i t動畛ng command c坦 tham s畛 int SubmitInsert/Update/Delete() { SqlCommand cmd = CreateInsertCommand(); //SqlCommand cmd = CreateUpdateCommand(); //SqlCommand cmd = CreateDeleteCommand(); cmd.Parameters[@tenThamSo] = giatri; return cmd.ExecuteNonQuery(); }
19 Ch畛nh s畛a d畛 li畛u Disconnected Tr畉ng th叩i c畛a DataRow Th棚m c叩c d嘆ng d畛 li畛u m畛i Ch畛nh s畛a c叩c d嘆ng d畛 li畛u X坦a c叩c d嘆ng d畛 li畛u Phi棚n b畉n d畛 li畛u trong DataRow
20 Ch畛nh s畛a d畛 li畛u Disconnected Tr畉ng th叩i c畛a DataRow Khi ch炭ng ta thao t叩c tr棚n 畛i t動畛ng DataRow, DataRow s畉 t畛 畛ng ghi nh畉n l畉i thao t叩c 坦 vo property tr畉ng th叩i tenRow.RowState. RowState thu畛c ki畛u DataRowState DataRowState.Added DataRowState.Deleted DataRowState.Detached DataRowState.Modified DataRowState.Unchanged
21 Ch畛nh s畛a d畛 li畛u Disconnected Th棚m c叩c d嘆ng d畛 li畛u m畛i C叩ch 1: DataRow row; row = tenBang.NewRow(); row["cot1"] = giatri1; row["cot2"] = giatri2; tenBang.Rows.Add(row); C叩ch 2: object[] rowValues = {giatri1,giatri2,}; tenBang.LoadDataRow(rowValues, false);
22 Ch畛nh s畛a d畛 li畛u Disconnected Ch畛nh s畛a c叩c d嘆ng d畛 li畛u C叩ch 1: DataRow row; int index; row = tenBang.Rows[index]; row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; C叩ch 2: DataRow row; int index; row = tenBang.Rows[index]; row.BeginEdit(); row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; row.EndEdit();
23 Ch畛nh s畛a d畛 li畛u Disconnected Ch畛nh s畛a c叩c d嘆ng d畛 li畛u C叩ch 3: DataRow row; int index; row = tenBang.Rows[index]; row.ItemArray = new object[]{giatri1, giatri2, };
24 Ch畛nh s畛a d畛 li畛u Disconnected X坦a c叩c d嘆ng d畛 li畛u C叩ch 1: DataRow row; int index; row = tenBang.Rows[index]; row.Delete(); C叩ch 2: DataRow row; int index; row = tenBang.Rows[index]; tenBang.Remove(row);
25 Ch畛nh s畛a d畛 li畛u Disconnected X坦a c叩c d嘆ng d畛 li畛u C叩ch 3: DataRow row; int index; tenBang.RemoveAt(index);
26 Ch畛nh s畛a d畛 li畛u Disconnected Phi棚n b畉n d畛 li畛u trong DataRow Truy c畉p c叩c phi棚n b畉n d畛 li畛u kh叩c nhau c畛a DataRow DataRow row; int index; row = tenBang.Rows[index]; row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; string strOldData; strOldData = row["tenCot", DataRowVersion.Original];
27 Ch畛nh s畛a d畛 li畛u Disconnected Phi棚n b畉n d畛 li畛u trong DataRow C叩c gi叩 tr畛 trong enum DataRowVersion Current - L畉y gi叩 tr畛 hi畛n tai Original - L畉y gi叩 tr畛 g畛c Proposed - L畉y gi叩 tr畛 sau BeginEdit v tr動畛c EndEdit Default - L畉y gi叩 tr畛 nh動 khi tham s畛 ny ch畛 ra
28 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet C叩c b動畛c th畛c hi畛n B動畛c 1: T畉o 3 畛i t動畛ng Command: insert, update, delete B動畛c 2: X叩c 畛nh c叩c d嘆ng b畛 thay 畛i trong DataTable B動畛c 3: X叩c 畛nh lo畉i thay 畛i c畛a m畛i d嘆ng ny (l insert, update hay delete) B動畛c 4: D湛ng c叩c gi叩 tr畛 trong d嘆ng 畛 g叩n cho c叩c tham s畛 trong 畛i t動畛ng command B動畛c 5: G畛i ExecuteNonQuery() 畛 th畛c thi c但u truy v畉n 動畛c l動u trong 畛i t動畛ng command B動畛c 6: D湛ng ki畛u tr畉 v畛 c畛a ExecuteNonQuery() 畛 x叩c 畛nh l畛nh c畉p nh畉t c坦 thnh c担ng kh担ng N畉u c畉p nh畉t thnh c担ng th狸 ch炭ng ta g畛i AcceptChanges() N畉u c畉p nh畉t kh担ng thnh c担ng th狸 ch炭ng ta thi畉t l畉p l畛i cho thu畛c t鱈nh RowError c畛a 畛i t動畛ng DataRow
29 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet void SubmitChanges() { SqlCommand cmdInsert = CreateInsertCommand(); SqlCommand cmdUpdate = CreateUpdateCommand(); SqlCommand cmdDelete = CreateDeleteCommand(); DataViewRowState states = DataViewRowState.Added | DataViewRowState.Deleted | DataViewRowState.ModifiedCurrent; // C嘆n ti畉p } C畉p nh畉t m畛i d畛 li畛u trong DataTable vo CSDL
30 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet void SubmitChanges() { int num = 0; foreach (DataRow row in tenBang.Select("","",states)) { switch (row.RowState) { case DateRowState.Modified: num = SubmitUpdate(row, cmdUpdate); break; case DateRowState.Added: num = SubmitInsert(row, cmdInsert); break; case DateRowState.Deleted: num = SubmitDelete(row, cmdDelete); break; } if (num == 1) row.AcceptChange(); else row.Error = L畛i c畉p nh畉t"; } }
31 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitInsert(DataRow row, SqlCommand cmdInsert) { SqlParameterCollection pc = cmdInsert.Parameters; pc["@Cot1"].Value = row["Cot1"]; pc["@Cot2"].Value = row["Cot2"]; return cmdInsert.ExecuteNonQuery(); } Th棚m 1 DataRow vo CSDL
32 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitDelete(DataRow row, SqlCommand cmdDelete) { SqlParameterCollection pc = cmdDelete.Parameters; pc["@Cot1"].Value = row["Cot1", DataRowVersion.Original]; pc["@Cot2"].Value = row["Cot2", DataRowVersion.Original]; return cmdDelete.ExecuteNonQuery(); } X坦a 1 DataRow trong CSDL
33 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitUpdate(DataRow row, SqlCommand cmdUpdate) { SqlParameterCollection pc = cmdUpdate.Parameters; pc["@Cot1"].Value = row["Cot1"]; pc["@Cot2"].Value = row["Cot2"]; pc["@OrginCot1"].Value = row["Cot1", DataRowVersion.Original]; pc["@OriginCot2"].Value = row["Cot2", DataRowVersion.Original]; return cmdUpdate.ExecuteNonQuery(); } C畉p nh畉t 1 DataRow vo CSDL
34 D湛ng 畛i t動畛ng DataAdapter 畛i t動畛ng DataAdapter c坦 2 nhi畛m v畛 L畉y d畛 li畛u t畛 data source v l動u tr畛 d畛 li畛u 坦 vo trong c叩c 畛i t動畛ng Disconnected (DataSet, DataTable) C畉p nh畉t d畛 li畛u t畛 c叩c 畛i t動畛ng Disconnected vo data source
35 D湛ng 畛i t動畛ng DataAdapter Data sourceDataAdapterDataTable DataTable DataSet DataAdapter FillFill UpdateUpdate FillFill UpdateUpdate
36 D湛ng 畛i t動畛ng DataAdapter 畛 c畉p nh畉t d畛 li畛u, DataAdapter c滴ng d湛ng c叩c c但u l畛nh SQL Insert/Update/Delete 動畛c l動u trong 3 畛i t動畛ng Command c畛a DataAdapter InsertCommand UpdateCommand DeleteCommand
37 D湛ng 畛i t動畛ng DataAdapter Ch炭ng ta c坦 3 l畛a ch畛n 畛 t畉o c叩c 畛i t動畛ng Command trong DataAdapter D湛ng 畛i t動畛ng SqlCommandBuilder 畛 sinh 3 畛i t動畛ng Command l炭c th畛c thi (run time) C畉u h狸nh 3 畛i t動畛ng Command b畉ng tay D湛ng Data Adapter Configuration Wizard l炭c thi畉t k畉 (design time)
38 D湛ng 畛i t動畛ng CommandBuilder SqlCommandBuilder s畉 sinh c叩c c但u l畛nh Insert/Update/Delete d畛a tr棚n c但u l畛nh select ch炭ng ta cung c畉p SqlCommandBuilder ch畛 sinh 動畛c c但u l畛nh Insert/Update/Delete khi c叩c i畛u ki畛n sau th畛a m達n C但u l畛nh Select ch畛 truy v畉n tr棚n 1 b畉ng C但u l畛nh Select c坦 ch畛a kh坦a ch鱈nh
39 D湛ng 畛i t動畛ng CommandBuilder X但y d畛ng 3 畛i t動畛ng Command v c畉p nh畉t d畛 li畛u string strSQL = "Select cot1, cot2," + " From tenBang "; SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Update(tenBang);
40 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m T畉o Command cho c但u l畛nh Insert T畉o Command cho c但u l畛nh Update T畉o Command cho c但u l畛nh Delete C畉p nh畉t d畛 li畛u
41 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m 畛 v動畛t qua gi畛i h畉n c畛a SqlCommandBuilder, ch炭ng ta c坦 th畛 t畛 x但y d畛ng c叩c 畛i t動畛ng command cho data adapter Ch炭 箪: Khi ch炭ng ta th棚m c叩c parameter cho c叩c 畛i t動畛ng command c畛a DataAdapter, ch炭ng ta s畉 d湛ng 2 thu畛c t鱈nh c畛a 畛i t動畛ng Parameter 動畛c thi畉t k畉 cho vi畛c c畉p nh畉t d畛 li畛u: param.SourceColumn, param.SourceVersion
42 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m param.SourceColumn = t棚nC畛t Ch畛 ra k畉t n畛i gi畛a 1 paramter (param) v畛i 1 c畛t (t棚nC畛t) trong DataTable param.SourceVersion = DataRowVersion.Current (default) DataRowVersion.Original Cho bi畉t Phi棚n b畉n d畛 li畛u trong DataRow no 動畛c s畛 d畛ng cho 1 parameter
43 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Insert private SqlCommand CreateInsertCommand() { string strSQL = "insert into tenBang(cot1, cot2, )" + " values(@p1,@p2,)"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramP1 = new SqlParameter("@p1", SqlDbType.Kieu,); paramP1.SourceColumn = cot1; pc.Add(paramP1); return cmd; }
44 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Update private SqlCommand CreateUpdateCommand() { string strSQL = update tenBang" + " set cot1=@p1, cot2=@p2,+ where cot1=@q1 and cot2=q2 ; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramP1 = new SqlParameter("@p1", SqlDbType.Kieu,); paramP1.SourceColumn = cot1; SqlParameter paramQ1 = new SqlParameter("@q1", SqlDbType.Kieu,); paramQ1.SourceColumn = cot1; paramQ1.SourceVersion = DataRowVersion.Original; pc.Add(paramP1); pc.Add(paramQ1); return cmd; }
45 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Delete private SqlCommand CreateDeleteCommand() { string strSQL = delete from tenBang" + " where cot1=@q1 and cot2=q2 ; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramQ1 = new SqlParameter("@q1", SqlDbType.Kieu,); paramQ1.SourceColumn = cot1; paramQ1.SourceVersion = DataRowVersion.Original; pc.Add(paramQ1); return cmd; }
46 C畉u h狸nh DataAdapter b畉ng tay C畉p nh畉t d畛 li畛u void SubmitChanges() { da.InsertCommand = CreateInsertCommand(); da.UpdateCommand = CreateUpdateCommand(); da.DeleteCommand = CreateDeleteCommand(); da.Update(dt); }
47 T坦m t畉t ch動董ng 4

More Related Content

04 chuong 4 - cap nhat du lieu

  • 1. C畉P NH畉T D畛 LI畛U Ch動董ng 4
  • 2. 2 N畛i dung C但u l畛nh SQL D湛ng 畛i t動畛ng Command Ch畛nh s畛a d畛 li畛u Disconnected D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet D湛ng 畛i t動畛ng DataAdapter D湛ng 畛i t動畛ng CommandBuilder C畉u h狸nh DataAdapter b畉ng tay
  • 3. 3 C但u l畛nh SQL C但u l畛nh Insert C但u l畛nh Update C但u l畛nh Delete
  • 4. 4 C但u l畛nh SQL Insert C但u l畛nh Insert d湛ng 畛 th棚m 1 d嘆ng d畛 li畛u vo b畉ng Th棚m 1 d嘆ng hon ch畛nh Th棚m 1 s畛 ph畉n d畛 li畛u c畛a 1 d嘆ng Th棚m d畛 li畛u 動畛c l畉y t畛 1 b畉ng kh叩c Th棚m 1 d嘆ng hon ch畛nh Insert into t棚nBang Values(giatri1, giatri2, , giatriN) Insert into t棚nBang(cot1, cot2, , cotN) Values(giatri1, giatri2, , giatriN)
  • 5. 5 C但u l畛nh SQL Insert Th棚m 1 s畛 ph畉n d畛 li畛u c畛a 1 d嘆ng Insert into t棚nBang(cotK1, cotK2, ) Values(giatriK1, giatriK2, ) C畛t kh担ng li畛t k棚 ra s畉 nh畉n gi叩 tr畛 NULL Th棚m d畛 li畛u 動畛c l畉y t畛 1 b畉ng kh叩c Insert into t棚nBang1(cot1, cot2, , CotN) Select cot1, Cot2, , CotN From t棚nBang2 Where
  • 6. 6 C但u l畛nh SQL Insert Ch炭 箪: T畛 kho叩 Into trong c但u l畛nh Insert c坦 th畛 b畛, nh動ng kh担ng n棚n b畛 N棚n li畛t k棚 c叩c c畛t d畛 li畛u khi d湛ng c但u l畛nh insert d畛 li畛u
  • 7. 7 C但u l畛nh SQL Insert Copy t畛 1 b畉ng sang 1 b畉ng kh叩c Select * into t棚nBangMoi From t棚nBang1 Select cotK1, cotK2, into t棚nBangMoi From t棚nBang1, t棚nBang2, Where Group By Ho畉t 畛ng: T畉o b畉ng t棚nBangMoi Ch辿p d畛 li畛u vo t棚nBangMoi
  • 8. 8 C但u l畛nh SQL Update C但u l畛nh Update d湛ng 畛 c畉p nh畉t (ch畛nh s畛a) d畛 li畛u Update t棚nBang Set cot1=newValue1, , cotN=newValueN Where cot1 = oldValue1 and and cotN=oldValueN 畛 x坦a gi叩 tr畛 c畛a 1 担 d畛 li畛u trong b畉ng, ch炭ng ta thi畉t l畉p gi叩 tr畛 坦 b畉ng null Update t棚nBang Set cot1=null, Where cotA = oldValueA
  • 9. 9 C但u l畛nh SQL Delete C但u l畛nh Delete d湛ng 畛 x坦a 1 s畛 d嘆ng d畛 li畛u trong b畉ng Delete From t棚nBang Where cot1=giatri1 and cot2=giatri2 and and cotN=giatriN Ch炭 箪: Delete d湛ng 畛 x坦a c畉 1 d嘆ng, ch畛 kh担ng x坦a 1 c畛t c畛a d嘆ng Delete kh担ng x坦a b畉ng, ch畛 x坦a d畛 li畛u 畛 x坦a m畛i d嘆ng d畛 li畛u trong b畉ng n棚n d湛ng l畛nh Truncate Table T畛 kh坦a From trong c但u l畛nh delete c坦 th畛 b畛, nh動ng kh担ng n棚n b畛
  • 10. 10 D湛ng 畛i t動畛ng Command C叩c b動畛c th畛c hi畛n 畛i t動畛ng Command kh担ng c坦 tham s畛 畛i t動畛ng Command c坦 tham s畛
  • 11. 11 D湛ng 畛i t動畛ng Command C叩c b動畛c th畛c hi畛n 3 b動畛c s畛 d畛ng 畛i t動畛ng command 畛 c畉p nh畉t d畛 li畛u B動畛c 1: T畉o c但u SQL B動畛c 2: T畉o 畛i t動畛ng Command ch畛a c但u SQL B動畛c 3: G畛i ph動董ng th畛c ExecuteNonQuery() c畛a 畛i t動畛ng Command
  • 12. 12 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "INSERT INTO t棚nBang(cot1, ) " + " VALUES (giatri1, )"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Insert
  • 13. 13 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "UPDATE t棚nBang" + " SET cot1 = newValue1,, cotN=giatriN" + " WHERE cot1=oldValue1 and and cotN=oldValueN"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Update
  • 14. 14 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command kh担ng c坦 tham s畛 string strSQL = "DELETE FROM t棚nBang" + " WHERE cot1=giatri1 and and cotN=giatriN"; SqlCommand cmd = new SqlCommand(strSQL, conn); int numberOfRows = cmd.ExecuteNonQuery(); C但u l畛nh Delete
  • 15. 15 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Insert SqlCommand CreateInsertCommand() { string strSQL; strSQL = "Insert t棚nBang(cot1, ) " + " values(@cot1, )"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); return cmd; }
  • 16. 16 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Update SqlCommand CreateUpdateCommand() { string strSQL; strSQL = "Update t棚nBang " + " set cot1=@cot1, , cotN=@cotN " + " where cot1=@OrigCot1 and and cotN=@OrigCotN"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); pc.Add(@OrigCot1, SqlDbType.Kieu) return cmd; }
  • 17. 17 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 Ph動董ng th畛c t畉o c但u l畛nh Delete SqlCommand CreateDeleteCommand() { string strSQL; strSQL = "Delete from t棚nBang " + " where @cot1=giatri1 and and @cotN=giatriN"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; pc.Add("@cot1", SqlDbType.Kieu); return cmd; }
  • 18. 18 D湛ng 畛i t動畛ng Command 畛i t動畛ng Command c坦 tham s畛 S畛 d畛ng c叩c 畛i t動畛ng command c坦 tham s畛 int SubmitInsert/Update/Delete() { SqlCommand cmd = CreateInsertCommand(); //SqlCommand cmd = CreateUpdateCommand(); //SqlCommand cmd = CreateDeleteCommand(); cmd.Parameters[@tenThamSo] = giatri; return cmd.ExecuteNonQuery(); }
  • 19. 19 Ch畛nh s畛a d畛 li畛u Disconnected Tr畉ng th叩i c畛a DataRow Th棚m c叩c d嘆ng d畛 li畛u m畛i Ch畛nh s畛a c叩c d嘆ng d畛 li畛u X坦a c叩c d嘆ng d畛 li畛u Phi棚n b畉n d畛 li畛u trong DataRow
  • 20. 20 Ch畛nh s畛a d畛 li畛u Disconnected Tr畉ng th叩i c畛a DataRow Khi ch炭ng ta thao t叩c tr棚n 畛i t動畛ng DataRow, DataRow s畉 t畛 畛ng ghi nh畉n l畉i thao t叩c 坦 vo property tr畉ng th叩i tenRow.RowState. RowState thu畛c ki畛u DataRowState DataRowState.Added DataRowState.Deleted DataRowState.Detached DataRowState.Modified DataRowState.Unchanged
  • 21. 21 Ch畛nh s畛a d畛 li畛u Disconnected Th棚m c叩c d嘆ng d畛 li畛u m畛i C叩ch 1: DataRow row; row = tenBang.NewRow(); row["cot1"] = giatri1; row["cot2"] = giatri2; tenBang.Rows.Add(row); C叩ch 2: object[] rowValues = {giatri1,giatri2,}; tenBang.LoadDataRow(rowValues, false);
  • 22. 22 Ch畛nh s畛a d畛 li畛u Disconnected Ch畛nh s畛a c叩c d嘆ng d畛 li畛u C叩ch 1: DataRow row; int index; row = tenBang.Rows[index]; row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; C叩ch 2: DataRow row; int index; row = tenBang.Rows[index]; row.BeginEdit(); row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; row.EndEdit();
  • 23. 23 Ch畛nh s畛a d畛 li畛u Disconnected Ch畛nh s畛a c叩c d嘆ng d畛 li畛u C叩ch 3: DataRow row; int index; row = tenBang.Rows[index]; row.ItemArray = new object[]{giatri1, giatri2, };
  • 24. 24 Ch畛nh s畛a d畛 li畛u Disconnected X坦a c叩c d嘆ng d畛 li畛u C叩ch 1: DataRow row; int index; row = tenBang.Rows[index]; row.Delete(); C叩ch 2: DataRow row; int index; row = tenBang.Rows[index]; tenBang.Remove(row);
  • 25. 25 Ch畛nh s畛a d畛 li畛u Disconnected X坦a c叩c d嘆ng d畛 li畛u C叩ch 3: DataRow row; int index; tenBang.RemoveAt(index);
  • 26. 26 Ch畛nh s畛a d畛 li畛u Disconnected Phi棚n b畉n d畛 li畛u trong DataRow Truy c畉p c叩c phi棚n b畉n d畛 li畛u kh叩c nhau c畛a DataRow DataRow row; int index; row = tenBang.Rows[index]; row["cot1"] = giatrimoi1; row["cot2"] = giatrimoi2; string strOldData; strOldData = row["tenCot", DataRowVersion.Original];
  • 27. 27 Ch畛nh s畛a d畛 li畛u Disconnected Phi棚n b畉n d畛 li畛u trong DataRow C叩c gi叩 tr畛 trong enum DataRowVersion Current - L畉y gi叩 tr畛 hi畛n tai Original - L畉y gi叩 tr畛 g畛c Proposed - L畉y gi叩 tr畛 sau BeginEdit v tr動畛c EndEdit Default - L畉y gi叩 tr畛 nh動 khi tham s畛 ny ch畛 ra
  • 28. 28 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet C叩c b動畛c th畛c hi畛n B動畛c 1: T畉o 3 畛i t動畛ng Command: insert, update, delete B動畛c 2: X叩c 畛nh c叩c d嘆ng b畛 thay 畛i trong DataTable B動畛c 3: X叩c 畛nh lo畉i thay 畛i c畛a m畛i d嘆ng ny (l insert, update hay delete) B動畛c 4: D湛ng c叩c gi叩 tr畛 trong d嘆ng 畛 g叩n cho c叩c tham s畛 trong 畛i t動畛ng command B動畛c 5: G畛i ExecuteNonQuery() 畛 th畛c thi c但u truy v畉n 動畛c l動u trong 畛i t動畛ng command B動畛c 6: D湛ng ki畛u tr畉 v畛 c畛a ExecuteNonQuery() 畛 x叩c 畛nh l畛nh c畉p nh畉t c坦 thnh c担ng kh担ng N畉u c畉p nh畉t thnh c担ng th狸 ch炭ng ta g畛i AcceptChanges() N畉u c畉p nh畉t kh担ng thnh c担ng th狸 ch炭ng ta thi畉t l畉p l畛i cho thu畛c t鱈nh RowError c畛a 畛i t動畛ng DataRow
  • 29. 29 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet void SubmitChanges() { SqlCommand cmdInsert = CreateInsertCommand(); SqlCommand cmdUpdate = CreateUpdateCommand(); SqlCommand cmdDelete = CreateDeleteCommand(); DataViewRowState states = DataViewRowState.Added | DataViewRowState.Deleted | DataViewRowState.ModifiedCurrent; // C嘆n ti畉p } C畉p nh畉t m畛i d畛 li畛u trong DataTable vo CSDL
  • 30. 30 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet void SubmitChanges() { int num = 0; foreach (DataRow row in tenBang.Select("","",states)) { switch (row.RowState) { case DateRowState.Modified: num = SubmitUpdate(row, cmdUpdate); break; case DateRowState.Added: num = SubmitInsert(row, cmdInsert); break; case DateRowState.Deleted: num = SubmitDelete(row, cmdDelete); break; } if (num == 1) row.AcceptChange(); else row.Error = L畛i c畉p nh畉t"; } }
  • 31. 31 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitInsert(DataRow row, SqlCommand cmdInsert) { SqlParameterCollection pc = cmdInsert.Parameters; pc["@Cot1"].Value = row["Cot1"]; pc["@Cot2"].Value = row["Cot2"]; return cmdInsert.ExecuteNonQuery(); } Th棚m 1 DataRow vo CSDL
  • 32. 32 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitDelete(DataRow row, SqlCommand cmdDelete) { SqlParameterCollection pc = cmdDelete.Parameters; pc["@Cot1"].Value = row["Cot1", DataRowVersion.Original]; pc["@Cot2"].Value = row["Cot2", DataRowVersion.Original]; return cmdDelete.ExecuteNonQuery(); } X坦a 1 DataRow trong CSDL
  • 33. 33 D湛ng 畛i t動畛ng Command v畛i DataTable/DataSet int SubmitUpdate(DataRow row, SqlCommand cmdUpdate) { SqlParameterCollection pc = cmdUpdate.Parameters; pc["@Cot1"].Value = row["Cot1"]; pc["@Cot2"].Value = row["Cot2"]; pc["@OrginCot1"].Value = row["Cot1", DataRowVersion.Original]; pc["@OriginCot2"].Value = row["Cot2", DataRowVersion.Original]; return cmdUpdate.ExecuteNonQuery(); } C畉p nh畉t 1 DataRow vo CSDL
  • 34. 34 D湛ng 畛i t動畛ng DataAdapter 畛i t動畛ng DataAdapter c坦 2 nhi畛m v畛 L畉y d畛 li畛u t畛 data source v l動u tr畛 d畛 li畛u 坦 vo trong c叩c 畛i t動畛ng Disconnected (DataSet, DataTable) C畉p nh畉t d畛 li畛u t畛 c叩c 畛i t動畛ng Disconnected vo data source
  • 35. 35 D湛ng 畛i t動畛ng DataAdapter Data sourceDataAdapterDataTable DataTable DataSet DataAdapter FillFill UpdateUpdate FillFill UpdateUpdate
  • 36. 36 D湛ng 畛i t動畛ng DataAdapter 畛 c畉p nh畉t d畛 li畛u, DataAdapter c滴ng d湛ng c叩c c但u l畛nh SQL Insert/Update/Delete 動畛c l動u trong 3 畛i t動畛ng Command c畛a DataAdapter InsertCommand UpdateCommand DeleteCommand
  • 37. 37 D湛ng 畛i t動畛ng DataAdapter Ch炭ng ta c坦 3 l畛a ch畛n 畛 t畉o c叩c 畛i t動畛ng Command trong DataAdapter D湛ng 畛i t動畛ng SqlCommandBuilder 畛 sinh 3 畛i t動畛ng Command l炭c th畛c thi (run time) C畉u h狸nh 3 畛i t動畛ng Command b畉ng tay D湛ng Data Adapter Configuration Wizard l炭c thi畉t k畉 (design time)
  • 38. 38 D湛ng 畛i t動畛ng CommandBuilder SqlCommandBuilder s畉 sinh c叩c c但u l畛nh Insert/Update/Delete d畛a tr棚n c但u l畛nh select ch炭ng ta cung c畉p SqlCommandBuilder ch畛 sinh 動畛c c但u l畛nh Insert/Update/Delete khi c叩c i畛u ki畛n sau th畛a m達n C但u l畛nh Select ch畛 truy v畉n tr棚n 1 b畉ng C但u l畛nh Select c坦 ch畛a kh坦a ch鱈nh
  • 39. 39 D湛ng 畛i t動畛ng CommandBuilder X但y d畛ng 3 畛i t動畛ng Command v c畉p nh畉t d畛 li畛u string strSQL = "Select cot1, cot2," + " From tenBang "; SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Update(tenBang);
  • 40. 40 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m T畉o Command cho c但u l畛nh Insert T畉o Command cho c但u l畛nh Update T畉o Command cho c但u l畛nh Delete C畉p nh畉t d畛 li畛u
  • 41. 41 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m 畛 v動畛t qua gi畛i h畉n c畛a SqlCommandBuilder, ch炭ng ta c坦 th畛 t畛 x但y d畛ng c叩c 畛i t動畛ng command cho data adapter Ch炭 箪: Khi ch炭ng ta th棚m c叩c parameter cho c叩c 畛i t動畛ng command c畛a DataAdapter, ch炭ng ta s畉 d湛ng 2 thu畛c t鱈nh c畛a 畛i t動畛ng Parameter 動畛c thi畉t k畉 cho vi畛c c畉p nh畉t d畛 li畛u: param.SourceColumn, param.SourceVersion
  • 42. 42 C畉u h狸nh DataAdapter b畉ng tay Kh叩i ni畛m param.SourceColumn = t棚nC畛t Ch畛 ra k畉t n畛i gi畛a 1 paramter (param) v畛i 1 c畛t (t棚nC畛t) trong DataTable param.SourceVersion = DataRowVersion.Current (default) DataRowVersion.Original Cho bi畉t Phi棚n b畉n d畛 li畛u trong DataRow no 動畛c s畛 d畛ng cho 1 parameter
  • 43. 43 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Insert private SqlCommand CreateInsertCommand() { string strSQL = "insert into tenBang(cot1, cot2, )" + " values(@p1,@p2,)"; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramP1 = new SqlParameter("@p1", SqlDbType.Kieu,); paramP1.SourceColumn = cot1; pc.Add(paramP1); return cmd; }
  • 44. 44 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Update private SqlCommand CreateUpdateCommand() { string strSQL = update tenBang" + " set cot1=@p1, cot2=@p2,+ where cot1=@q1 and cot2=q2 ; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramP1 = new SqlParameter("@p1", SqlDbType.Kieu,); paramP1.SourceColumn = cot1; SqlParameter paramQ1 = new SqlParameter("@q1", SqlDbType.Kieu,); paramQ1.SourceColumn = cot1; paramQ1.SourceVersion = DataRowVersion.Original; pc.Add(paramP1); pc.Add(paramQ1); return cmd; }
  • 45. 45 C畉u h狸nh DataAdapter b畉ng tay T畉o Command cho c但u l畛nh Delete private SqlCommand CreateDeleteCommand() { string strSQL = delete from tenBang" + " where cot1=@q1 and cot2=q2 ; SqlCommand cmd = new SqlCommand(strSQL, conn); SqlParameterCollection pc = cmd.Parameters; SqlParameter paramQ1 = new SqlParameter("@q1", SqlDbType.Kieu,); paramQ1.SourceColumn = cot1; paramQ1.SourceVersion = DataRowVersion.Original; pc.Add(paramQ1); return cmd; }
  • 46. 46 C畉u h狸nh DataAdapter b畉ng tay C畉p nh畉t d畛 li畛u void SubmitChanges() { da.InsertCommand = CreateInsertCommand(); da.UpdateCommand = CreateUpdateCommand(); da.DeleteCommand = CreateDeleteCommand(); da.Update(dt); }

Editor's Notes

  • #5: Although this syntax is indeed simple, it is not at all safe and should generally be avoided at all costs. The above SQL statement is highly dependent on the order in which the columns are defined in the table. It also depends on information about that order being readily available. Even if it is available, there is no guarantee that the columns will be in the exact same order the next time the table is reconstructed. Therefore, writing SQL statements that depend on specific column ordering is very unsafe. If you do so, something will inevitably break at some point.
  • #6: Bang1 ph畉i t畛n t畉i tr動畛c
  • #9: UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005'; The UPDATE statement always begins with the name of the table being updated. In this example, it is the Customers table. The SET command is then used to assign the new value to a column. As used here, the SET clause sets the cust_email column to the specified value: SET cust_email = 'kim@thetoystore.com' The UPDATE statement finishes with a WHERE clause that tells the DBMS which row to update. Without a WHERE clause, the DBMS would update all the rows in the Customers table with this new email addressdefinitely not the desired effect.
  • #10: From trong delete l option nh動ng nen dung n坦 Faster Deletes If you really do want to delete all rows from a table, don't use DELETE. Instead, use the TRUNCATE TABLE statement which accomplished the same thing but does it much quicker (because data changes are not logged).
  • #21: Detached: D嘆ng 達 t畉o nh動ng ch動a th棚m vo Table Detached The row has been created but is not part of any DataRowCollection. A DataRow is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection. Unchanged The row has not changed since AcceptChanges was last called. Added The row has been added to a DataRowCollection, and AcceptChanges has not been called. Deleted The row was deleted using the Delete method of the DataRow. Modified The row has been modified and AcceptChanges has not been called.
  • #26: Xoa thi 動畛c nhu kh担ng the cap nhat dc
  • #38: C畉u h狸nh DataAdapter b畉ng tay
  • #39: Sinh Insert kh担ng c畉n c但u Select c坦 kh坦a ch鱈nh