C# 数据表(Dataset)操作合并查询一箩筐

  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+="
第1个结果集的第一个字段所有记录数据是：";
277              while(MyReader.Read())
278              {               
279                MyInfo+="
"+MyReader[0].ToString();
280              }
281              int MyCount= 1;             
282              while(MyReader.NextResult())
283              {
284                MyCount = MyCount + 1;
285                MyInfo+="
第"+MyCount+"个结果集的第一个字段所有记录数据是：";
286                while(MyReader.Read())
287                {
288                    MyInfo+="
"+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+="
第1个结果集的第一个字段所有记录数据是：";
664              while(MyReader.Read())
665              {               
666                MyInfo+="
"+MyReader[0].ToString();
667              }
668              int MyCount= 1;             
669              while(MyReader.NextResult())
670              {
671                MyCount = MyCount + 1;
672                MyInfo+="
第"+MyCount+"个结果集的第一个字段所有记录数据是：";
673                while(MyReader.Read())
674                {
675                    MyInfo+="
"+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
C# 数据表(Dataset)操作 合并 查询一箩筐

C# 数据表(Dataset)操作合并查询一箩筐
