C # data table (Dataset) Operations merge and query a basket

Private void button#click (object sender, EventArgs e) {// avoid adding the same record except the primary key string MyCompanyName = "Shenzhen Proview logistics company"; 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 SqlComma NdBuilder (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 ("added record operation successful! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. Information);} else MessageBox. Show (" this record already exists! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. information);} private void button2_Click (object sender, EventArgs e) {// Add the database record string MyCompanyName = "Shenzhen Weijia logistics company" in parameterization mode "; 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 (MyConnectionSt Ring); 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; MyCom Mand. Parameters ["@ Phone"]. Value = MyPhone; MyCommand. ExecuteNonQuery (); MessageBox. Show ("added record operation successful! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. information);} catch (Exception ex) {MessageBox. show ("add record error:" + ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {MyConnection. close () ;}} private void button3_Click (object sender, EventArgs e) {// update multiple Data tables using transactions try {string MyConnectionString = "Data Source = .; initial Catalog = Northwind; uid = sa; pwd = top-each123; Integr Ated Security = True "; string MySQL1 =" INSERT MERs (CustomerID, CompanyName) VALUES ('bogg', 'bogg' Company ') "; string MySQL2 =" INSERT Orders (CustomerID, employeeID, ShipVia) VALUES ('bogg', 1, 1) "; string MySQL3 =" delete from Orders WHERE CustomerID = 'bogg '"; string MySQL4 = "delete from MERs WHERE CustomerID = 'bogg'"; SqlConnection MyConnection = new SqlConnection (MyConnectionSt Ring); SqlTransaction MyTransaction = null; int MyAffectedCount = 0; string MyTitle = ""; MyConnection. open (); try {MyTransaction = MyConnection. beginTransaction (System. data. isolationLevel. repeatableRead); MyTitle = "insert operation prompt"; SqlCommand MyCommand = new SqlCommand (MySQL1, MyConnection); MyCommand. commandType = CommandType. text; MyCommand. transaction = MyTransaction; MyAffectedCount = MyCommand. exe CuteNonQuery (); MyCommand. commandText = MySQL2; MyAffectedCount + = MyCommand. executeNonQuery (); MyTransaction. commit (); MyTitle = "delete operation prompt"; 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) My Info = "successful transaction insertion and deletion operations"; else MyInfo = "failed to insert and delete transactions. Check the Customers and Orders Data Tables"; MessageBox. show (MyInfo, "message prompt", MessageBoxButtons. OK, MessageBoxIcon. information) ;}} catch (Exception ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information) ;}} private void button4_Click (object sender, EventArgs e) {// create an implicit transaction management database update 【.. NET] Select "System. transactions "Option using (TransactionScope MyScope = new TransactionScope () {string MyConnectionString =" Data Source = .; initial Catalog = Northwind; uid = sa; pwd = top-each123; Integrated Security = True "; string MySQL1 =" INSERT MERs (mermerid, CompanyName) VALUES ('bogu ', 'bogus Company ') "; string MySQL2 =" INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('boggs', 1, 1) "; string MySQL3 =" delete from Orde Rs WHERE CustomerID = 'bogg' "; string MySQL4 =" delete from Customers WHERE CustomerID = 'bogg' "; SqlConnection MyConnection = new SqlConnection (MyConnectionString); int MyAffectedCount = 0; string MyTitle = ""; try {MyConnection. open (); MyTitle = "insert operation prompt"; SqlCommand MyCommand = new SqlCommand (MySQL1, MyConnection); MyCommand. commandType = CommandType. text; MyAffectedCount = MyCommand. executeNo NQuery (); MyCommand. commandText = MySQL2; MyAffectedCount + = MyCommand. executeNonQuery (); MyTitle = "delete operation prompt"; 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. bytes Ion);} finally {MyConnection. close (); string MyInfo; if (MyAffectedCount = 4) MyInfo = "successful transaction insertion and deletion operations"; else MyInfo = "failed to insert or delete transactions, check the Customers and Orders Data Tables "; MessageBox. show (MyInfo, "message prompt", MessageBoxButtons. OK, MessageBoxIcon. information) ;}} private void button5_Click (object sender, EventArgs e) {// Import and Export Database records in batch string MyConnectionString = "Data Source = .; initial Catalog = shop; uid = sa; pwd = P-each123; Integrated Security = True "; string MySQL =" SELECT * into new customer table From tbl_order Where 1 <> 1 "; SqlConnection MyConnection = null; try {// create a data table "new customer table" MyConnection = new SqlConnection (MyConnectionString); SqlCommand MyCommand = new SqlCommand (MySQL, MyConnection); MyCommand. connection. open (); MyCommand. executeNonQuery (); // import database records in batches from the Customers data table to the new customer table DataSet MySet = new DataSet (); SqlDataA Dapter MyAdapter = new SqlDataAdapter ("Select top 1000 * From tbl_order", MyConnection); MyAdapter. fill (MySet); SqlBulkCopy MyBulkCopy = new SqlBulkCopy (MyConnection); MyBulkCopy. destinationTableName = "new customer table"; MyBulkCopy. writeToServer (MySet. tables [0]); MessageBox. show ("batch import database records from" MERs "data table to" new customer table "operation successful! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. information);} catch (SqlException ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {MyConnection. close () ;}} private void button6_Click (object sender, EventArgs e) {// merge the database records of the two Data tables string MyConnectionString = "Data Source = .; initial Catalog = Northwind; uid = sa; pwd = top-each123; Integrated Security = Tr Ue "; SqlConnection MyConnection = null; try {// create the Germany able MyGermanyTable = new DataTable (" Germany "); MyConnection = new SqlConnection (MyConnectionString ); sqlDataAdapter MyAdapter = new SqlDataAdapter ("Select * From MERs Where Country = 'Germany '", MyConnection); MyAdapter. fill (MyGermanyTable); // create the Mexico data table DataTable MyMexicoTable = new DataTable ("Mexico"); MyConnection = new SqlConnection (MyConnectionString); MyAdapter = new SqlDataAdapter ("Select * From MERs Where Country = 'docker'", MyConnection); MyAdapter. fill (MyMexicoTable); // merge the two data tables. merge (MyGermanyTable); this. dataGridView1.DataSource = MyMexicoTable; MessageBox. show ("two data tables are merged successfully! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. information);} catch (SqlException ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {MyConnection. close () ;}} private void button7_Click (object sender, EventArgs e) {// use the Data table to obtain the Data reader content string MyConnectionString = "Data Source = .; initial Catalog = Northwind; uid = sa; pwd = top-each123; Integrated Security = T Rue "; SqlConnection MyConnection = null; try {MyConnection = new SqlConnection (MyConnectionString); SqlCommand MyCommand = new SqlCommand (" Select * From MERs ", MyConnection); MyConnection. open (); SqlDataReader MyReader = MyCommand. executeReader (); DataTable MyTable = new DataTable (); MyTable. load (MyReader); this. dataGridView1.DataSource = MyTable; MessageBox. show ("getting data reader content from data table is successful! "," Message prompt ", MessageBoxButtons. OK, MessageBoxIcon. information);} catch (SqlException ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {MyConnection. close () ;}} private void button8_Click (object sender, EventArgs e) {// use a Data reader to obtain multiple result sets string MyConnectionString = "Data Source = .; initial Catalog = Northwind; uid = sa; pwd = top-each123; Integrated Security = True "; SqlConnection MyConnection = null; try {// define and open the SqlConnection object MyConnection = new SqlConnection (MyConnectionString); MyConnection. open (); // define SqlCommand to obtain multiple result sets String MySQL = "Select top 4 CompanyName From MERs MERS; Select top 5 City, Region From Employees; Select top 6 ProductName From Products "; sqlCommand MyCommand = new SqlCommand (MySQL, MyConnection); MyCommand. commandType = CommandType. text; // define and create SqlDataReader // close the data connection string MyInfo = "" When closing SqlDataReader; SqlDataReader MyReader = MyCommand. executeReader (CommandBehavior. closeConnection); if (MyReader. hasRows) {MyInfo + = "\ n all records of the first field of the 1st result set are:"; while (MyReader. read () {MyInfo + = "\ n" + MyReader [0]. toString ();} int MyCount = 1; while (MyReader. nextResult () {MyCount = MyCount + 1; MyInfo + = "\ n no." + MyCount + "the first field of the result set contains the following data records :"; while (MyReader. read () {MyInfo + = "\ n" + MyReader [0]. toString () ;}} MyReader. close (); MessageBox. show (MyInfo, "message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} catch (SqlException ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {if (MyConnection. state = ConnectionState. open) MyConnection. close () ;}} private void button9_Click (object sender, EventArgs e) {// query the database record SqlConnection MyConnection = null in parameterization mode; try {string MySQL = "Select * From MERs 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, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {if (MyConnection. state = ConnectionState. open) MyConnection. close () ;}} private void button10_Click (object sender, EventArgs e) {// create and use a connectionless data table try {// create a data table 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); // Add a record DataRow MyRow = MyTable in the data table. newRow (); MyRow ["ID"] = 87121; MyRow ["Name"] = "Luo bin"; MyRow ["Tel"] = "023-40231026 "; myRow ["MP"] = "13036371686"; MyRow ["Company"] = "Wuxi baote software Co., Ltd."; MyTable. rows. add (MyRow); // Add record 2 MyRow = MyTable in the data table. newRow (); MyRow ["ID"] = "87123"; MyRow ["Name"] = "Jiang lankun"; MyRow ["Tel"] = "023-68015059 "; myRow ["MP"] = "13062308583"; MyRow ["Company"] = "Chongqing Department Store Co., Ltd."; MyTable. rows. add (MyRow); // Add record 3 to the data table MyRow = MyTable. newRow (); MyRow ["ID"] = 87124; MyRow ["Name"] = "Wang Bin"; MyRow ["Tel"] = "023-40513307 "; myRow ["MP"] = "13883070418"; MyRow ["Company"] = "Chongqing daily printing factory"; MyTable. rows. add (MyRow); this. dataGridView1.DataSource = MyTable;} catch (Exception ex) {MessageBox. show (ex. message, "Message prompt", MessageBoxButtons. OK, MessageBoxIcon. information );}}