1 private void button1_Click(object sender, EventArgs e)
2
3 {//避免增加除主键外的相同记录
4 string MyCompanyName="深圳唯佳物流公司";
5 string MyPhone="0589-86523158";
6 string MySQL="SELECT * FROM Shippers";
7 string MyConnectionString="Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
8 SqlDataAdapter MyAdapter=new SqlDataAdapter(MySQL,MyConnectionString);
9 SqlCommandBuilder MyBuilder=new SqlCommandBuilder(MyAdapter);
10 DataSet MySet=new DataSet();
11 MyAdapter.Fill(MySet, "Shippers");
12 DataColumn []MyKeys=new DataColumn[2];
13 MyKeys[0] = MySet.Tables["Shippers"].Columns["CompanyName"];
14 MyKeys[1] = MySet.Tables["Shippers"].Columns["Phone"];
15 MySet.Tables["Shippers"].PrimaryKey = MyKeys;
16 string[] MySupplier = {MyCompanyName ,MyPhone};
17 DataRow MyFindRow = MySet.Tables["Shippers"].Rows.Find(MySupplier);
18 if (MyFindRow == null)
19 {
20 DataRow MyNewRow =MySet.Tables["Shippers"].NewRow();
21 MyNewRow["CompanyName"] = MySupplier[0];
22 MyNewRow["Phone"] = MySupplier[1];
23 MySet.Tables["Shippers"].Rows.Add(MyNewRow);
24 MyAdapter.Update(MySet, "Shippers");
25 MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
26 }
27 else
28 MessageBox.Show("该记录已经存在！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
29 }
30
31 private void button2_Click(object sender, EventArgs e)
32 {//以参数化方式增加数据库记录
33 string MyCompanyName="深圳唯佳物流公司";
34 string MyPhone="0589-86523158";
35 string MyConnectionString = "Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
36 SqlConnection MyConnection=null;
37 try
38 {
39 MyConnection=new SqlConnection(MyConnectionString);
40 MyConnection.Open();
41 SqlCommand MyCommand=MyConnection.CreateCommand();
42 MyCommand.CommandText = "INSERT INTO Shippers VALUES(@CompanyName, @Phone)";
43 MyCommand.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.VarChar,30));
44 MyCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 30));
45 MyCommand.Prepare();
46 MyCommand.Parameters["@CompanyName"].Value =MyCompanyName;
47 MyCommand.Parameters["@Phone"].Value =MyPhone;
48 MyCommand.ExecuteNonQuery();
49 MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
50 }
51 catch(Exception ex)
52 {
53 MessageBox.Show("增加记录出现错误：" + ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
54 }
55 finally
56 {
57 MyConnection.Close();
58 }
59 }
60
61 private void button3_Click(object sender, EventArgs e)
62 {//使用事务实现更新多个数据表
63 try
64 {
65 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
66 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
67 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
68 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
69 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
70 SqlConnection MyConnection = new SqlConnection(MyConnectionString);
71 SqlTransaction MyTransaction = null;
72 int MyAffectedCount = 0;
73 string MyTitle = "";
74 MyConnection.Open();
75 try
76 {
77 MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
78 MyTitle = "插入操作提示";
79 SqlCommand MyCommand = new SqlCommand(MySQL1, MyConnection);
80 MyCommand.CommandType = CommandType.Text;
81 MyCommand.Transaction = MyTransaction;
82 MyAffectedCount = MyCommand.ExecuteNonQuery();
83 MyCommand.CommandText = MySQL2;
84 MyAffectedCount += MyCommand.ExecuteNonQuery();
85 MyTransaction.Commit();
86 MyTitle = "删除操作提示";
87 MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
88 MyCommand.CommandText = MySQL3;
89 MyCommand.Transaction = MyTransaction;
90 MyAffectedCount += MyCommand.ExecuteNonQuery();
91 MyCommand.CommandText = MySQL4;
92 MyAffectedCount += MyCommand.ExecuteNonQuery();
93 MyTransaction.Commit();
94 }
95 catch (SqlException ex)
96 {
97 MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
98 try
99 {
100 MyTransaction.Rollback();
101 }
102 catch (SqlException MyEx)
103 {
104 MessageBox.Show(MyEx.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
105 }
106 }
107 finally
108 {
109 MyConnection.Close();
110 string MyInfo;
111 if (MyAffectedCount == 4)
112 MyInfo = "成功实现插入和删除事务操作";
113 else
114 MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
115 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
116 }
117 }
118 catch (Exception ex)
119 {
120 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
121 }
122 }
123
124 private void button4_Click(object sender, EventArgs e)
125 {//创建隐式事务管理数据库更新
126 //在【添加引用】对话框的【.NET】标签页列表视图中选择“System.Transactions”选项
127 using(TransactionScope MyScope = new TransactionScope())
128 {
129 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
130 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
131 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
132 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
133 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
134 SqlConnection MyConnection = new SqlConnection(MyConnectionString);
135 int MyAffectedCount = 0;
136 string MyTitle = "";
137 try
138 {
139 MyConnection.Open();
140 MyTitle = "插入操作提示";
141 SqlCommand MyCommand= new SqlCommand(MySQL1, MyConnection);
142 MyCommand.CommandType = CommandType.Text;
143 MyAffectedCount = MyCommand.ExecuteNonQuery();
144 MyCommand.CommandText = MySQL2;
145 MyAffectedCount += MyCommand.ExecuteNonQuery();
146 MyTitle = "删除操作提示";
147 MyCommand.CommandText = MySQL3;
148 MyAffectedCount += MyCommand.ExecuteNonQuery();
149 MyCommand.CommandText = MySQL4;
150 MyAffectedCount += MyCommand.ExecuteNonQuery();
151 MyScope.Complete();
152 }
153 catch (SqlException ex)
154 {
155 MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
156 }
157 finally
158 {
159 MyConnection.Close();
160 string MyInfo ;
161 if(MyAffectedCount == 4)
162 MyInfo = "成功实现插入和删除事务操作";
163 else
164 MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
165 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
166 }
167 }
168 }
169
170 private void button5_Click(object sender, EventArgs e)
171 {//以批量方式导入导出数据库记录
172 string MyConnectionString = "Data Source=.;Initial Catalog=shop;uid=sa;pwd=top-each123;Integrated Security=True";
173 string MySQL = "SELECT * into 新客户表 From tbl_order Where 1<>1";
174 SqlConnection MyConnection = null;
175 try
176 {
177 //新建一个数据表“新客户表”
178 MyConnection = new SqlConnection(MyConnectionString);
179 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
180 MyCommand.Connection.Open();
181 MyCommand.ExecuteNonQuery();
182 //从“Customers”数据表批量导入数据库记录到“新客户表”
183 DataSet MySet=new DataSet();
184 SqlDataAdapter MyAdapter=new SqlDataAdapter("Select top 1000 * From tbl_order",MyConnection);
185 MyAdapter.Fill(MySet);
186 SqlBulkCopy MyBulkCopy=new SqlBulkCopy(MyConnection);
187 MyBulkCopy.DestinationTableName = "新客户表";
188 MyBulkCopy.WriteToServer(MySet.Tables[0]);
189 MessageBox.Show("从“Customers”数据表批量导入数据库记录到“新客户表”操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
190 }
191 catch (SqlException ex)
192 {
193 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
194 }
195 finally
196 {
197 MyConnection.Close();
198 }
199 }
200
201 private void button6_Click(object sender, EventArgs e)
202 {//合并两个数据表的数据库记录
203 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
204 SqlConnection MyConnection = null;
205 try
206 {
207 //创建“Germany”数据表
208 DataTable MyGermanyTable = new DataTable("Germany");
209 MyConnection = new SqlConnection(MyConnectionString);
210 SqlDataAdapter MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Germany'", MyConnection);
211 MyAdapter.Fill(MyGermanyTable);
212 //创建“Mexico”数据表
213 DataTable MyMexicoTable = new DataTable("Mexico");
214 MyConnection = new SqlConnection(MyConnectionString);
215 MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Mexico'", MyConnection);
216 MyAdapter.Fill(MyMexicoTable);
217 //合并两个数据表
218 MyMexicoTable.Merge(MyGermanyTable);
219 this.dataGridView1.DataSource = MyMexicoTable;
220 MessageBox.Show("合并两个数据表操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
221 }
222 catch (SqlException ex)
223 {
224 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
225 }
226 finally
227 {
228 MyConnection.Close();
229 }
230 }
231
232 private void button7_Click(object sender, EventArgs e)
233 {//使用数据表获取数据读取器内容
234 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
235 SqlConnection MyConnection = null;
236 try
237 {
238 MyConnection = new SqlConnection(MyConnectionString);
239 SqlCommand MyCommand = new SqlCommand("Select * From Customers", MyConnection);
240 MyConnection.Open();
241 SqlDataReader MyReader = MyCommand.ExecuteReader();
242 DataTable MyTable = new DataTable();
243 MyTable.Load(MyReader);
244 this.dataGridView1.DataSource = MyTable;
245 MessageBox.Show("使用数据表获取数据读取器内容操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
246 }
247 catch (SqlException ex)
248 {
249 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
250 }
251 finally
252 {
253 MyConnection.Close();
254 }
255 }
256
257 private void button8_Click(object sender, EventArgs e)
258 {//使用数据读取器获取多个结果集
259 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
260 SqlConnection MyConnection = null;
261 try
262 {
263 //定义并打开SqlConnection 对象
264 MyConnection=new SqlConnection(MyConnectionString);
265 MyConnection.Open();
266 //定义SqlCommand 获取多结果集
267 String MySQL = "Select top 4 CompanyName From Customers;Select top 5 City,Region From Employees;Select top 6 ProductName From Products";
268 SqlCommand MyCommand =new SqlCommand(MySQL, MyConnection);
269 MyCommand.CommandType = CommandType.Text;
270 //定义并创建SqlDataReader
271 //当关闭SqlDataReader时关闭数据连接
272 string MyInfo="";
273 SqlDataReader MyReader= MyCommand.ExecuteReader(CommandBehavior. CloseConnection);
274 if(MyReader.HasRows)
275 {
276 MyInfo+="\n第1个结果集的第一个字段所有记录数据是：";
277 while(MyReader.Read())
278 {
279 MyInfo+="\n"+MyReader[0].ToString();
280 }
281 int MyCount= 1;
282 while(MyReader.NextResult())
283 {
284 MyCount = MyCount + 1;
285 MyInfo+="\n第"+MyCount+"个结果集的第一个字段所有记录数据是：";
286 while(MyReader.Read())
287 {
288 MyInfo+="\n"+MyReader[0].ToString();
289 }
290 }
291 }
292 MyReader.Close();
293 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
294 }
295 catch (SqlException ex)
296 {
297 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
298 }
299 finally
300 {
301 if(MyConnection.State ==ConnectionState.Open)
302 MyConnection.Close();
303 }
304 }
305
306 private void button9_Click(object sender, EventArgs e)
307 {//以参数化方式查询数据库记录
308 SqlConnection MyConnection = null;
309 try
310 {
311 string MySQL = "Select * From Customers Where Country=@MyCountry";
312 string MyConnectionString = "Data Source = localhost;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
313 SqlParameter MySqlParameter = new SqlParameter();
314 MyConnection = new SqlConnection(MyConnectionString);
315 MyConnection.Open();
316 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
317 MySqlParameter.ParameterName = "@MyCountry";
318 MySqlParameter.Value = "Germany";
319 MyCommand.Parameters.Clear();
320 MyCommand.Parameters.Add(MySqlParameter);
321 DataTable MyTable = new DataTable();
322 SqlDataAdapter MyAdapter = new SqlDataAdapter(MyCommand);
323 MyAdapter.Fill(MyTable);
324 this.dataGridView1.DataSource = MyTable;
325 }
326 catch (Exception ex)
327 {
328 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
329 }
330 finally
331 {
332 if (MyConnection.State == ConnectionState.Open)
333 MyConnection.Close();
334 }
335 }
336
337 private void button10_Click(object sender, EventArgs e)
338 {//创建和使用无连接数据表
339 try
340 {
341 //创建数据表
342 DataColumn[] MyKey = new DataColumn[1];
343 DataTable MyTable = new DataTable("MyClassmate");
344 DataColumn MyColumn = new DataColumn();
345 MyColumn.DataType = System.Type.GetType("System.Int32");
346 MyColumn.ColumnName = "ID";
347 MyTable.Columns.Add(MyColumn);
348 MyKey[0] = MyColumn;
349 MyTable.PrimaryKey = MyKey;
350 MyTable.Columns.Add("Name", typeof(String));
351 MyTable.Columns.Add("Tel", typeof(String));
352 MyTable.Columns.Add("MP", typeof(String));
353 MyTable.Columns.Add("Company", typeof(String));
354 //在数据表中添加记录一
355 DataRow MyRow = MyTable.NewRow();
356 MyRow["ID"] = 87121;
357 MyRow["Name"] = "罗斌";
358 MyRow["Tel"] = "023-40231026";
359 MyRow["MP"] = "13036371686";
360 MyRow["Company"] = "无锡宝特软件有限公司";
361 MyTable.Rows.Add(MyRow);
362 //在数据表中添加记录二
363 MyRow = MyTable.NewRow();
364 MyRow["ID"] = "87123";
365 MyRow["Name"] = "蒋兰坤";
366 MyRow["Tel"] = "023-68015059";
367 MyRow["MP"] = "13062308583";
368 MyRow["Company"] = "重庆百货大楼股份有限公司";
369 MyTable.Rows.Add(MyRow);
370 //在数据表中添加记录三
371 MyRow = MyTable.NewRow();
372 MyRow["ID"] = 87124;
373 MyRow["Name"] = "王彬";
374 MyRow["Tel"] = "023-40513307";
375 MyRow["MP"] = "13883070418";
376 MyRow["Company"] = "重庆日报印刷厂";
377 MyTable.Rows.Add(MyRow);
378 this.dataGridView1.DataSource = MyTable;
379 }
380 catch (Exception ex)
381 {
382 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
383 }
384 }
385
386 转 http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html
387 ---恢复内容结束---
388
389 private void button1_Click(object sender, EventArgs e)
390 {//避免增加除主键外的相同记录
391 string MyCompanyName="深圳唯佳物流公司";
392 string MyPhone="0589-86523158";
393 string MySQL="SELECT * FROM Shippers";
394 string MyConnectionString="Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
395 SqlDataAdapter MyAdapter=new SqlDataAdapter(MySQL,MyConnectionString);
396 SqlCommandBuilder MyBuilder=new SqlCommandBuilder(MyAdapter);
397 DataSet MySet=new DataSet();
398 MyAdapter.Fill(MySet, "Shippers");
399 DataColumn []MyKeys=new DataColumn[2];
400 MyKeys[0] = MySet.Tables["Shippers"].Columns["CompanyName"];
401 MyKeys[1] = MySet.Tables["Shippers"].Columns["Phone"];
402 MySet.Tables["Shippers"].PrimaryKey = MyKeys;
403 string[] MySupplier = {MyCompanyName ,MyPhone};
404 DataRow MyFindRow = MySet.Tables["Shippers"].Rows.Find(MySupplier);
405 if (MyFindRow == null)
406 {
407 DataRow MyNewRow =MySet.Tables["Shippers"].NewRow();
408 MyNewRow["CompanyName"] = MySupplier[0];
409 MyNewRow["Phone"] = MySupplier[1];
410 MySet.Tables["Shippers"].Rows.Add(MyNewRow);
411 MyAdapter.Update(MySet, "Shippers");
412 MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
413 }
414 else
415 MessageBox.Show("该记录已经存在！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
416 }
417
418 private void button2_Click(object sender, EventArgs e)
419 {//以参数化方式增加数据库记录
420 string MyCompanyName="深圳唯佳物流公司";
421 string MyPhone="0589-86523158";
422 string MyConnectionString = "Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
423 SqlConnection MyConnection=null;
424 try
425 {
426 MyConnection=new SqlConnection(MyConnectionString);
427 MyConnection.Open();
428 SqlCommand MyCommand=MyConnection.CreateCommand();
429 MyCommand.CommandText = "INSERT INTO Shippers VALUES(@CompanyName, @Phone)";
430 MyCommand.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.VarChar,30));
431 MyCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 30));
432 MyCommand.Prepare();
433 MyCommand.Parameters["@CompanyName"].Value =MyCompanyName;
434 MyCommand.Parameters["@Phone"].Value =MyPhone;
435 MyCommand.ExecuteNonQuery();
436 MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
437 }
438 catch(Exception ex)
439 {
440 MessageBox.Show("增加记录出现错误：" + ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
441 }
442 finally
443 {
444 MyConnection.Close();
445 }
446 }
447
448 private void button3_Click(object sender, EventArgs e)
449 {//使用事务实现更新多个数据表
450 try
451 {
452 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
453 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
454 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
455 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
456 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
457 SqlConnection MyConnection = new SqlConnection(MyConnectionString);
458 SqlTransaction MyTransaction = null;
459 int MyAffectedCount = 0;
460 string MyTitle = "";
461 MyConnection.Open();
462 try
463 {
464 MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
465 MyTitle = "插入操作提示";
466 SqlCommand MyCommand = new SqlCommand(MySQL1, MyConnection);
467 MyCommand.CommandType = CommandType.Text;
468 MyCommand.Transaction = MyTransaction;
469 MyAffectedCount = MyCommand.ExecuteNonQuery();
470 MyCommand.CommandText = MySQL2;
471 MyAffectedCount += MyCommand.ExecuteNonQuery();
472 MyTransaction.Commit();
473 MyTitle = "删除操作提示";
474 MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
475 MyCommand.CommandText = MySQL3;
476 MyCommand.Transaction = MyTransaction;
477 MyAffectedCount += MyCommand.ExecuteNonQuery();
478 MyCommand.CommandText = MySQL4;
479 MyAffectedCount += MyCommand.ExecuteNonQuery();
480 MyTransaction.Commit();
481 }
482 catch (SqlException ex)
483 {
484 MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
485 try
486 {
487 MyTransaction.Rollback();
488 }
489 catch (SqlException MyEx)
490 {
491 MessageBox.Show(MyEx.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
492 }
493 }
494 finally
495 {
496 MyConnection.Close();
497 string MyInfo;
498 if (MyAffectedCount == 4)
499 MyInfo = "成功实现插入和删除事务操作";
500 else
501 MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
502 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
503 }
504 }
505 catch (Exception ex)
506 {
507 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
508 }
509 }
510
511 private void button4_Click(object sender, EventArgs e)
512 {//创建隐式事务管理数据库更新
513 //在【添加引用】对话框的【.NET】标签页列表视图中选择“System.Transactions”选项
514 using(TransactionScope MyScope = new TransactionScope())
515 {
516 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
517 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
518 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
519 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
520 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
521 SqlConnection MyConnection = new SqlConnection(MyConnectionString);
522 int MyAffectedCount = 0;
523 string MyTitle = "";
524 try
525 {
526 MyConnection.Open();
527 MyTitle = "插入操作提示";
528 SqlCommand MyCommand= new SqlCommand(MySQL1, MyConnection);
529 MyCommand.CommandType = CommandType.Text;
530 MyAffectedCount = MyCommand.ExecuteNonQuery();
531 MyCommand.CommandText = MySQL2;
532 MyAffectedCount += MyCommand.ExecuteNonQuery();
533 MyTitle = "删除操作提示";
534 MyCommand.CommandText = MySQL3;
535 MyAffectedCount += MyCommand.ExecuteNonQuery();
536 MyCommand.CommandText = MySQL4;
537 MyAffectedCount += MyCommand.ExecuteNonQuery();
538 MyScope.Complete();
539 }
540 catch (SqlException ex)
541 {
542 MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
543 }
544 finally
545 {
546 MyConnection.Close();
547 string MyInfo ;
548 if(MyAffectedCount == 4)
549 MyInfo = "成功实现插入和删除事务操作";
550 else
551 MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
552 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
553 }
554 }
555 }
556
557 private void button5_Click(object sender, EventArgs e)
558 {//以批量方式导入导出数据库记录
559 string MyConnectionString = "Data Source=.;Initial Catalog=shop;uid=sa;pwd=top-each123;Integrated Security=True";
560 string MySQL = "SELECT * into 新客户表 From tbl_order Where 1<>1";
561 SqlConnection MyConnection = null;
562 try
563 {
564 //新建一个数据表“新客户表”
565 MyConnection = new SqlConnection(MyConnectionString);
566 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
567 MyCommand.Connection.Open();
568 MyCommand.ExecuteNonQuery();
569 //从“Customers”数据表批量导入数据库记录到“新客户表”
570 DataSet MySet=new DataSet();
571 SqlDataAdapter MyAdapter=new SqlDataAdapter("Select top 1000 * From tbl_order",MyConnection);
572 MyAdapter.Fill(MySet);
573 SqlBulkCopy MyBulkCopy=new SqlBulkCopy(MyConnection);
574 MyBulkCopy.DestinationTableName = "新客户表";
575 MyBulkCopy.WriteToServer(MySet.Tables[0]);
576 MessageBox.Show("从“Customers”数据表批量导入数据库记录到“新客户表”操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
577 }
578 catch (SqlException ex)
579 {
580 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
581 }
582 finally
583 {
584 MyConnection.Close();
585 }
586 }
587
588 private void button6_Click(object sender, EventArgs e)
589 {//合并两个数据表的数据库记录
590 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
591 SqlConnection MyConnection = null;
592 try
593 {
594 //创建“Germany”数据表
595 DataTable MyGermanyTable = new DataTable("Germany");
596 MyConnection = new SqlConnection(MyConnectionString);
597 SqlDataAdapter MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Germany'", MyConnection);
598 MyAdapter.Fill(MyGermanyTable);
599 //创建“Mexico”数据表
600 DataTable MyMexicoTable = new DataTable("Mexico");
601 MyConnection = new SqlConnection(MyConnectionString);
602 MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Mexico'", MyConnection);
603 MyAdapter.Fill(MyMexicoTable);
604 //合并两个数据表
605 MyMexicoTable.Merge(MyGermanyTable);
606 this.dataGridView1.DataSource = MyMexicoTable;
607 MessageBox.Show("合并两个数据表操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
608 }
609 catch (SqlException ex)
610 {
611 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
612 }
613 finally
614 {
615 MyConnection.Close();
616 }
617 }
618
619 private void button7_Click(object sender, EventArgs e)
620 {//使用数据表获取数据读取器内容
621 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
622 SqlConnection MyConnection = null;
623 try
624 {
625 MyConnection = new SqlConnection(MyConnectionString);
626 SqlCommand MyCommand = new SqlCommand("Select * From Customers", MyConnection);
627 MyConnection.Open();
628 SqlDataReader MyReader = MyCommand.ExecuteReader();
629 DataTable MyTable = new DataTable();
630 MyTable.Load(MyReader);
631 this.dataGridView1.DataSource = MyTable;
632 MessageBox.Show("使用数据表获取数据读取器内容操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
633 }
634 catch (SqlException ex)
635 {
636 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
637 }
638 finally
639 {
640 MyConnection.Close();
641 }
642 }
643
644 private void button8_Click(object sender, EventArgs e)
645 {//使用数据读取器获取多个结果集
646 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
647 SqlConnection MyConnection = null;
648 try
649 {
650 //定义并打开SqlConnection 对象
651 MyConnection=new SqlConnection(MyConnectionString);
652 MyConnection.Open();
653 //定义SqlCommand 获取多结果集
654 String MySQL = "Select top 4 CompanyName From Customers;Select top 5 City,Region From Employees;Select top 6 ProductName From Products";
655 SqlCommand MyCommand =new SqlCommand(MySQL, MyConnection);
656 MyCommand.CommandType = CommandType.Text;
657 //定义并创建SqlDataReader
658 //当关闭SqlDataReader时关闭数据连接
659 string MyInfo="";
660 SqlDataReader MyReader= MyCommand.ExecuteReader(CommandBehavior. CloseConnection);
661 if(MyReader.HasRows)
662 {
663 MyInfo+="\n第1个结果集的第一个字段所有记录数据是：";
664 while(MyReader.Read())
665 {
666 MyInfo+="\n"+MyReader[0].ToString();
667 }
668 int MyCount= 1;
669 while(MyReader.NextResult())
670 {
671 MyCount = MyCount + 1;
672 MyInfo+="\n第"+MyCount+"个结果集的第一个字段所有记录数据是：";
673 while(MyReader.Read())
674 {
675 MyInfo+="\n"+MyReader[0].ToString();
676 }
677 }
678 }
679 MyReader.Close();
680 MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
681 }
682 catch (SqlException ex)
683 {
684 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
685 }
686 finally
687 {
688 if(MyConnection.State ==ConnectionState.Open)
689 MyConnection.Close();
690 }
691 }
692
693 private void button9_Click(object sender, EventArgs e)
694 {//以参数化方式查询数据库记录
695 SqlConnection MyConnection = null;
696 try
697 {
698 string MySQL = "Select * From Customers Where Country=@MyCountry";
699 string MyConnectionString = "Data Source = localhost;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
700 SqlParameter MySqlParameter = new SqlParameter();
701 MyConnection = new SqlConnection(MyConnectionString);
702 MyConnection.Open();
703 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
704 MySqlParameter.ParameterName = "@MyCountry";
705 MySqlParameter.Value = "Germany";
706 MyCommand.Parameters.Clear();
707 MyCommand.Parameters.Add(MySqlParameter);
708 DataTable MyTable = new DataTable();
709 SqlDataAdapter MyAdapter = new SqlDataAdapter(MyCommand);
710 MyAdapter.Fill(MyTable);
711 this.dataGridView1.DataSource = MyTable;
712 }
713 catch (Exception ex)
714 {
715 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
716 }
717 finally
718 {
719 if (MyConnection.State == ConnectionState.Open)
720 MyConnection.Close();
721 }
722 }
723
724 private void button10_Click(object sender, EventArgs e)
725 {//创建和使用无连接数据表
726 try
727 {
728 //创建数据表
729 DataColumn[] MyKey = new DataColumn[1];
730 DataTable MyTable = new DataTable("MyClassmate");
731 DataColumn MyColumn = new DataColumn();
732 MyColumn.DataType = System.Type.GetType("System.Int32");
733 MyColumn.ColumnName = "ID";
734 MyTable.Columns.Add(MyColumn);
735 MyKey[0] = MyColumn;
736 MyTable.PrimaryKey = MyKey;
737 MyTable.Columns.Add("Name", typeof(String));
738 MyTable.Columns.Add("Tel", typeof(String));
739 MyTable.Columns.Add("MP", typeof(String));
740 MyTable.Columns.Add("Company", typeof(String));
741 //在数据表中添加记录一
742 DataRow MyRow = MyTable.NewRow();
743 MyRow["ID"] = 87121;
744 MyRow["Name"] = "罗斌";
745 MyRow["Tel"] = "023-40231026";
746 MyRow["MP"] = "13036371686";
747 MyRow["Company"] = "无锡宝特软件有限公司";
748 MyTable.Rows.Add(MyRow);
749 //在数据表中添加记录二
750 MyRow = MyTable.NewRow();
751 MyRow["ID"] = "87123";
752 MyRow["Name"] = "蒋兰坤";
753 MyRow["Tel"] = "023-68015059";
754 MyRow["MP"] = "13062308583";
755 MyRow["Company"] = "重庆百货大楼股份有限公司";
756 MyTable.Rows.Add(MyRow);
757 //在数据表中添加记录三
758 MyRow = MyTable.NewRow();
759 MyRow["ID"] = 87124;
760 MyRow["Name"] = "王彬";
761 MyRow["Tel"] = "023-40513307";
762 MyRow["MP"] = "13883070418";
763 MyRow["Company"] = "重庆日报印刷厂";
764 MyTable.Rows.Add(MyRow);
765 this.dataGridView1.DataSource = MyTable;
766 }
767 catch (Exception ex)
768 {
769 MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
770 }
771 }
转 http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html
