数据表合并2

来源：互联网发布：java递归简单例子编辑：程序博客网时间：2024/05/16 11:59
  
http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html
 
 
privatevoid button1_Click(objectsender, EventArgs e) 
       {//避免增加除主键外的相同记录
          stringMyCompanyName="深圳唯佳物流公司";
          stringMyPhone="0589-86523158";
          stringMySQL="SELECT * FROM Shippers";
          stringMyConnectionString="Data Source = .;Database =    Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";           
          SqlDataAdapter  MyAdapter=newSqlDataAdapter(MySQL,MyConnectionString); 
          SqlCommandBuilder MyBuilder=newSqlCommandBuilder(MyAdapter); 
          DataSet MySet=newDataSet(); 
          MyAdapter.Fill(MySet,"Shippers");
          DataColumn []MyKeys=newDataColumn[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);                   
       }
 
       privatevoid button2_Click(objectsender, EventArgs e) 
       {//以参数化方式增加数据库记录
          stringMyCompanyName="深圳唯佳物流公司";
          stringMyPhone="0589-86523158";
          stringMyConnectionString = "Data Source = .;Database =  Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";           
          SqlConnection MyConnection=null;
          try
          {
            MyConnection=newSqlConnection(MyConnectionString); 
            MyConnection.Open();
            SqlCommand MyCommand=MyConnection.CreateCommand();
            MyCommand.CommandText ="INSERT INTO Shippers VALUES(@CompanyName, @Phone)";
            MyCommand.Parameters.Add(newSqlParameter("@CompanyName", SqlDbType.VarChar,30));
            MyCommand.Parameters.Add(newSqlParameter("@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();
          }
       }
 
       privatevoid button3_Click(objectsender, EventArgs e) 
       {//使用事务实现更新多个数据表
           try
           {
               stringMyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
               stringMySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
               stringMySQL2 = "INSERT Orders (CustomerID, EmployeeID,  ShipVia) VALUES ('BOGUS', 1, 1)";
               stringMySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
               stringMySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
               SqlConnection MyConnection =new SqlConnection(MyConnectionString);
               SqlTransaction MyTransaction =null; 
               intMyAffectedCount = 0; 
               stringMyTitle = "";
               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();
                   stringMyInfo; 
                   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);             
           }
       }
 
       privatevoid button4_Click(objectsender, EventArgs e) 
       {//创建隐式事务管理数据库更新
           //在【添加引用】对话框的【.NET】标签页列表视图中选择“System.Transactions”选项
           using(TransactionScope MyScope =new TransactionScope())
           {
               stringMyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
               stringMySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
               stringMySQL2 = "INSERT Orders (CustomerID, EmployeeID,  ShipVia) VALUES ('BOGUS', 1, 1)";
               stringMySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
               stringMySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
               SqlConnection MyConnection =new SqlConnection(MyConnectionString);
               intMyAffectedCount = 0; 
               stringMyTitle = "";                
               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);
                }
           }
       }
 
       privatevoid button5_Click(objectsender, EventArgs e) 
       {//以批量方式导入导出数据库记录
           stringMyConnectionString = "Data Source=.;Initial Catalog=shop;uid=sa;pwd=top-each123;Integrated Security=True";
           stringMySQL = "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=newDataSet(); 
               SqlDataAdapter MyAdapter=newSqlDataAdapter("Select top 1000 * From tbl_order",MyConnection);
               MyAdapter.Fill(MySet);
               SqlBulkCopy MyBulkCopy=newSqlBulkCopy(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();
           }
       }
 
       privatevoid button6_Click(objectsender, EventArgs e) 
       {//合并两个数据表的数据库记录
           stringMyConnectionString = "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();
           }
       }
 
       privatevoid button7_Click(objectsender, EventArgs e) 
       {//使用数据表获取数据读取器内容
           stringMyConnectionString = "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();
           }   
       }
 
       privatevoid button8_Click(objectsender, EventArgs e) 
       {//使用数据读取器获取多个结果集
           stringMyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
           SqlConnection MyConnection =null; 
           try
           {
              //定义并打开SqlConnection 对象
              MyConnection=newSqlConnection(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 =newSqlCommand(MySQL, MyConnection); 
              MyCommand.CommandType = CommandType.Text;
              //定义并创建SqlDataReader
              //当关闭SqlDataReader时关闭数据连接
              stringMyInfo="";
              SqlDataReader MyReader= MyCommand.ExecuteReader(CommandBehavior. CloseConnection);
             if(MyReader.HasRows)
             {
               MyInfo+="\n第1个结果集的第一个字段所有记录数据是：";
               while(MyReader.Read())
               {                
                 MyInfo+="\n"+MyReader[0].ToString();
               }
               intMyCount= 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();
           }   
       }
 
       privatevoid button9_Click(objectsender, EventArgs e) 
       {//以参数化方式查询数据库记录   
           SqlConnection MyConnection =null; 
           try
           {
               stringMySQL = "Select * From Customers Where Country=@MyCountry";
               stringMyConnectionString = "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();
           }
       }
 
       privatevoid button10_Click(objectsender, 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);
           }
       }
 
　　