C# 資料表(Dataset)操作 合并 查詢一籮筐

   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);            }        }