数据表合并2

http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html     private void button1_Click( object sender, EventArgs e)         { //避免增加除主键外的相同记录            string MyCompanyName= "深圳唯佳物流公司" ;            string MyPhone= "0589-86523158" ;            string MySQL= "SELECT * FROM Shippers" ;            string MyConnectionString= "Data Source = .;Database =    Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI" ;                       SqlDataAdapter  MyAdapter= new SqlDataAdapter(MySQL,MyConnectionString);            SqlCommandBuilder MyBuilder= new SqlCommandBuilder(MyAdapter);            DataSet MySet= new DataSet();            MyAdapter.Fill(MySet, "Shippers" );            DataColumn []MyKeys= new DataColumn[2];            MyKeys[0] = MySet.Tables[ "Shippers" ].Columns[ "CompanyName" ];            MyKeys[1] = MySet.Tables[ "Shippers" ].Columns[ "Phone" ];            MySet.Tables[ "Shippers" ].PrimaryKey = MyKeys;            string [] MySupplier = {MyCompanyName ,MyPhone};            DataRow MyFindRow = MySet.Tables[ "Shippers" ].Rows.Find(MySupplier);            if (MyFindRow == null )            {                DataRow MyNewRow =MySet.Tables[ "Shippers" ].NewRow();                MyNewRow[ "CompanyName" ] = MySupplier[0];                MyNewRow[ "Phone" ] = MySupplier[1];                MySet.Tables[ "Shippers" ].Rows.Add(MyNewRow);                MyAdapter.Update(MySet, "Shippers" );                MessageBox.Show( "增加记录操作成功！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);            }            else                MessageBox.Show( "该记录已经存在！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);                            }           private void button2_Click( object sender, EventArgs e)         { //以参数化方式增加数据库记录            string MyCompanyName= "深圳唯佳物流公司" ;            string MyPhone= "0589-86523158" ;            string MyConnectionString = "Data Source = .;Database =  Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI" ;                       SqlConnection MyConnection= null ;            try            {              MyConnection= new SqlConnection(MyConnectionString);              MyConnection.Open();              SqlCommand MyCommand=MyConnection.CreateCommand();              MyCommand.CommandText = "INSERT INTO Shippers VALUES(@CompanyName, @Phone)" ;              MyCommand.Parameters.Add( new SqlParameter( "@CompanyName" , SqlDbType.VarChar,30));              MyCommand.Parameters.Add( new SqlParameter( "@Phone" , SqlDbType.VarChar, 30));              MyCommand.Prepare();              MyCommand.Parameters[ "@CompanyName" ].Value =MyCompanyName;              MyCommand.Parameters[ "@Phone" ].Value =MyPhone;              MyCommand.ExecuteNonQuery();              MessageBox.Show( "增加记录操作成功！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);            }            catch (Exception ex)            {              MessageBox.Show( "增加记录出现错误：" + ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);            }            finally            {                MyConnection.Close();            }         }           private void button3_Click( object sender, EventArgs e)         { //使用事务实现更新多个数据表             try             {                 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True" ;                 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')" ;                 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID,  ShipVia) VALUES ('BOGUS', 1, 1)" ;                 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'" ;                 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'" ;                 SqlConnection MyConnection = new SqlConnection(MyConnectionString);                 SqlTransaction MyTransaction = null ;                 int MyAffectedCount = 0;                 string MyTitle = "" ;                 MyConnection.Open();                 try                 {                                       MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);                     MyTitle = "插入操作提示" ;                     SqlCommand MyCommand = new SqlCommand(MySQL1, MyConnection);                     MyCommand.CommandType = CommandType.Text;                     MyCommand.Transaction = MyTransaction;                     MyAffectedCount = MyCommand.ExecuteNonQuery();                     MyCommand.CommandText = MySQL2;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyTransaction.Commit();                     MyTitle = "删除操作提示" ;                     MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);                     MyCommand.CommandText = MySQL3;                     MyCommand.Transaction = MyTransaction;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyCommand.CommandText = MySQL4;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyTransaction.Commit();                 }                 catch (SqlException ex)                 {                     MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);                     try                     {                         MyTransaction.Rollback();                     }                     catch (SqlException MyEx)                     {                         MessageBox.Show(MyEx.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);                     }                 }                               finally                 {                     MyConnection.Close();                     string MyInfo;                     if (MyAffectedCount == 4)                         MyInfo = "成功实现插入和删除事务操作" ;                     else                         MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表" ;                     MessageBox.Show(MyInfo, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);                 }             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);                          }         }           private void button4_Click( object sender, EventArgs e)         { //创建隐式事务管理数据库更新             //在【添加引用】对话框的【.NET】标签页列表视图中选择“System.Transactions”选项             using (TransactionScope MyScope = new TransactionScope())             {                 string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True" ;                 string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')" ;                 string MySQL2 = "INSERT Orders (CustomerID, EmployeeID,  ShipVia) VALUES ('BOGUS', 1, 1)" ;                 string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'" ;                 string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'" ;                 SqlConnection MyConnection = new SqlConnection(MyConnectionString);                 int MyAffectedCount = 0;                 string MyTitle = "" ;                                 try                 {                     MyConnection.Open();                      MyTitle = "插入操作提示" ;                                SqlCommand MyCommand= new SqlCommand(MySQL1, MyConnection);                     MyCommand.CommandType = CommandType.Text;                     MyAffectedCount = MyCommand.ExecuteNonQuery();                     MyCommand.CommandText = MySQL2;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyTitle = "删除操作提示" ;                     MyCommand.CommandText = MySQL3;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyCommand.CommandText = MySQL4;                     MyAffectedCount += MyCommand.ExecuteNonQuery();                     MyScope.Complete();                  }                  catch (SqlException ex)                  {                     MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);                  }                  finally                  {                     MyConnection.Close();                     string  MyInfo ;                     if (MyAffectedCount == 4)                         MyInfo = "成功实现插入和删除事务操作" ;                     else                         MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表" ;                     MessageBox.Show(MyInfo, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);                  }             }         }           private void button5_Click( object sender, EventArgs e)         { //以批量方式导入导出数据库记录             string MyConnectionString = "Data Source=.;Initial Catalog=shop;uid=sa;pwd=top-each123;Integrated Security=True" ;             string MySQL = "SELECT * into 新客户表 From tbl_order Where 1<>1" ;             SqlConnection MyConnection = null ;             try             {                 //新建一个数据表“新客户表”                 MyConnection = new SqlConnection(MyConnectionString);                 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);                 MyCommand.Connection.Open();                 MyCommand.ExecuteNonQuery();                 //从“Customers”数据表批量导入数据库记录到“新客户表”                 DataSet MySet= new DataSet();                 SqlDataAdapter MyAdapter= new SqlDataAdapter( "Select top 1000 * From tbl_order" ,MyConnection);                 MyAdapter.Fill(MySet);                 SqlBulkCopy MyBulkCopy= new SqlBulkCopy(MyConnection);                 MyBulkCopy.DestinationTableName = "新客户表" ;                 MyBulkCopy.WriteToServer(MySet.Tables[0]);                 MessageBox.Show( "从“Customers”数据表批量导入数据库记录到“新客户表”操作成功！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             catch (SqlException ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally             {                 MyConnection.Close();             }         }           private void button6_Click( object sender, EventArgs e)         { //合并两个数据表的数据库记录             string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True" ;             SqlConnection MyConnection = null ;             try             {                 //创建“Germany”数据表                 DataTable MyGermanyTable = new DataTable( "Germany" );                 MyConnection = new SqlConnection(MyConnectionString);                 SqlDataAdapter MyAdapter = new SqlDataAdapter( "Select * From Customers Where Country='Germany'" , MyConnection);                 MyAdapter.Fill(MyGermanyTable);                 //创建“Mexico”数据表                 DataTable MyMexicoTable = new DataTable( "Mexico" );                 MyConnection = new SqlConnection(MyConnectionString);                 MyAdapter = new SqlDataAdapter( "Select * From Customers Where Country='Mexico'" , MyConnection);                 MyAdapter.Fill(MyMexicoTable);                 //合并两个数据表                 MyMexicoTable.Merge(MyGermanyTable);                 this .dataGridView1.DataSource = MyMexicoTable;                 MessageBox.Show( "合并两个数据表操作成功！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             catch (SqlException ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally             {                 MyConnection.Close();             }         }           private void button7_Click( object sender, EventArgs e)         { //使用数据表获取数据读取器内容             string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True" ;             SqlConnection MyConnection = null ;             try             {                 MyConnection = new SqlConnection(MyConnectionString);                 SqlCommand MyCommand = new SqlCommand( "Select * From Customers" , MyConnection);                 MyConnection.Open();                 SqlDataReader MyReader = MyCommand.ExecuteReader();                 DataTable MyTable = new DataTable();                 MyTable.Load(MyReader);                 this .dataGridView1.DataSource = MyTable;                 MessageBox.Show( "使用数据表获取数据读取器内容操作成功！" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             catch (SqlException ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally             {                 MyConnection.Close();             }            }           private void button8_Click( object sender, EventArgs e)         { //使用数据读取器获取多个结果集             string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True" ;             SqlConnection MyConnection = null ;             try             {                //定义并打开SqlConnection 对象                MyConnection= new SqlConnection(MyConnectionString);                MyConnection.Open();                //定义SqlCommand 获取多结果集                String MySQL = "Select top 4  CompanyName From Customers;Select top 5  City,Region From Employees;Select top 6 ProductName From Products" ;                SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);                MyCommand.CommandType = CommandType.Text;                //定义并创建SqlDataReader                //当关闭SqlDataReader时关闭数据连接                string MyInfo= "" ;                SqlDataReader MyReader= MyCommand.ExecuteReader(CommandBehavior. CloseConnection);               if (MyReader.HasRows)               {                 MyInfo+= "\n第1个结果集的第一个字段所有记录数据是：" ;                 while (MyReader.Read())                 {                                   MyInfo+= "\n" +MyReader[0].ToString();                 }                 int MyCount= 1;                               while (MyReader.NextResult())                 {                   MyCount = MyCount + 1;                   MyInfo+= "\n第" +MyCount+ "个结果集的第一个字段所有记录数据是：" ;                   while (MyReader.Read())                   {                       MyInfo+= "\n" +MyReader[0].ToString();                   }                                   }               }               MyReader.Close();               MessageBox.Show(MyInfo, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             catch (SqlException ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally             {                 if (MyConnection.State ==ConnectionState.Open)                     MyConnection.Close();             }            }           private void button9_Click( object sender, EventArgs e)         { //以参数化方式查询数据库记录                SqlConnection MyConnection = null ;             try             {                 string MySQL = "Select * From Customers Where Country=@MyCountry" ;                 string MyConnectionString = "Data Source = localhost;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI" ;                 SqlParameter MySqlParameter = new SqlParameter();                 MyConnection = new SqlConnection(MyConnectionString);                 MyConnection.Open();                 SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);                 MySqlParameter.ParameterName = "@MyCountry" ;                 MySqlParameter.Value = "Germany" ;                 MyCommand.Parameters.Clear();                 MyCommand.Parameters.Add(MySqlParameter);                 DataTable MyTable = new DataTable();                 SqlDataAdapter MyAdapter = new SqlDataAdapter(MyCommand);                 MyAdapter.Fill(MyTable);                 this .dataGridView1.DataSource = MyTable;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally             {                 if (MyConnection.State == ConnectionState.Open)                     MyConnection.Close();             }         }           private void button10_Click( object sender, EventArgs e)         { //创建和使用无连接数据表             try             {                 //创建数据表                 DataColumn[] MyKey = new DataColumn[1];                 DataTable MyTable = new DataTable( "MyClassmate" );                 DataColumn MyColumn = new DataColumn();                 MyColumn.DataType = System.Type.GetType( "System.Int32" );                 MyColumn.ColumnName = "ID" ;                 MyTable.Columns.Add(MyColumn);                 MyKey[0] = MyColumn;                 MyTable.PrimaryKey = MyKey;                 MyTable.Columns.Add( "Name" , typeof (String));                 MyTable.Columns.Add( "Tel" , typeof (String));                 MyTable.Columns.Add( "MP" , typeof (String));                 MyTable.Columns.Add( "Company" , typeof (String));                 //在数据表中添加记录一                 DataRow MyRow = MyTable.NewRow();                 MyRow[ "ID" ] = 87121;                 MyRow[ "Name" ] = "罗斌" ;                 MyRow[ "Tel" ] = "023-40231026" ;                 MyRow[ "MP" ] = "13036371686" ;                 MyRow[ "Company" ] = "无锡宝特软件有限公司" ;                 MyTable.Rows.Add(MyRow);                 //在数据表中添加记录二                 MyRow = MyTable.NewRow();                 MyRow[ "ID" ] = "87123" ;                 MyRow[ "Name" ] = "蒋兰坤" ;                 MyRow[ "Tel" ] = "023-68015059" ;                 MyRow[ "MP" ] = "13062308583" ;                 MyRow[ "Company" ] = "重庆百货大楼股份有限公司" ;                 MyTable.Rows.Add(MyRow);                 //在数据表中添加记录三                 MyRow = MyTable.NewRow();                 MyRow[ "ID" ] = 87124;                 MyRow[ "Name" ] = "王彬" ;                 MyRow[ "Tel" ] = "023-40513307" ;                 MyRow[ "MP" ] = "13883070418" ;                 MyRow[ "Company" ] = "重庆日报印刷厂" ;                 MyTable.Rows.Add(MyRow);                 this .dataGridView1.DataSource = MyTable;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information);             }         }