利用CommandBuilder 透過DataAdapter進行資料庫操作。
需在資料表設定Primary Key
01 
private void ImportData()
02
{
03
SqlConnection cn = new SqlConnection(@"Password=xxxx;Persist Security Info=True;User ID=sa;Initial Catalog=xxx;Data Source=xxx\SQLEXPRESS");
04
05 SqlCommand cmd = new SqlCommand();
06 SqlDataAdapter da = new SqlDataAdapter();
07 SqlCommandBuilder cb = new SqlCommandBuilder(); // DataAadpter用此類別控制新增、刪除、修改語法。
08 DataTable dt = new DataTable();
09
10 try
11
{
12 cn.Open();
13 cmd.CommandText = "SELECT * FROM TABLENAME"; // 需先給SELECT語法
14 cmd.Connection = cn;
15 da.SelectCommand = cmd;
16 cb.DataAdapter = da; // 指定DataAdapter
17 da.Fill(dt);
18
19 foreach (DataRow dr in dtData.Rows)
20 {
21 if (dt.Select("PK = '" + dr["PK"].ToString() + "'").Length == 0)
22 {
23 DataRow dr_New = dt.NewRow();
24
25 foreach (DataColumn dc in dtData.Columns)
26 {
27 dr_New[dc.ColumnName] = dr[dc.ColumnName];
28
}
29 dt.Rows.Add(dr_New);
30 }
31 }
32
33 da.Update(dt);
34 }
35 catch(Exception ex)
36 {
37 MessageBox.Show(ex.Message + "匯入失敗！");
38 }
39 finally
40 {
41 cn.Dispose();
42 cmd.Dispose();
43 da.Dispose();
44 cb.Dispose();
45 dt = null;
46 }
47
48 MessageBox.Show("匯入成功！");
49 }
50
}
private void ImportData() 02
{ 
03
SqlConnection cn = new SqlConnection(@"Password=xxxx;Persist Security Info=True;User ID=sa;Initial Catalog=xxx;Data Source=xxx\SQLEXPRESS"); 04
05
SqlCommand cmd = new SqlCommand(); 06
SqlDataAdapter da = new SqlDataAdapter(); 07
SqlCommandBuilder cb = new SqlCommandBuilder(); // DataAadpter用此類別控制新增、刪除、修改語法。 08
DataTable dt = new DataTable(); 09
10
try 11
{ 
12
cn.Open(); 13
cmd.CommandText = "SELECT * FROM TABLENAME"; // 需先給SELECT語法14
cmd.Connection = cn; 15
da.SelectCommand = cmd; 16
cb.DataAdapter = da; // 指定DataAdapter17
da.Fill(dt); 18
19
foreach (DataRow dr in dtData.Rows) 20
{ 21
if (dt.Select("PK = '" + dr["PK"].ToString() + "'").Length == 0) 22
{ 23
DataRow dr_New = dt.NewRow(); 24
25
foreach (DataColumn dc in dtData.Columns) 26
{ 27
dr_New[dc.ColumnName] = dr[dc.ColumnName]; 28
} 29
dt.Rows.Add(dr_New); 30
} 31
} 32
33
da.Update(dt); 34
} 35
catch(Exception ex) 36
{ 37
MessageBox.Show(ex.Message + "匯入失敗！"); 38
} 39
finally 40
{ 41
cn.Dispose(); 42
cmd.Dispose(); 43
da.Dispose(); 44
cb.Dispose(); 45
dt = null; 46
} 47
48
MessageBox.Show("匯入成功！"); 49
} 50
}