数据表合并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); } }