Common operation of MySQL based on C # programming language

Tag: Client timeout invalid calling SQL statement APT Comm begins begin

First, you need to add MySQL-related references in C # in

using MySql.Data.MySqlClient;

Second, connect MySQL, create a Connection object, and then open its start object

New Mysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database= Test"); // Create the MySQL connection object m_conn. Open (); // Open Connection

Third, query

Mysqlconnection M_conn =NewMysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database=test"); M_conn. Open ();D atatable DT=NewDataTable ("DS"); DataSet DS=NewDataSet ();//Create a dataset to hold the returned results of the queryMysqldataadapter da =NewMysqldataadapter ("SELECT * from TEST. person", M_conn);//Create a data adapter to find dataDa.SelectCommand.CommandTimeout =12000;d A. Fill (DS,"DS");//populate the dataset with the data that you are querying into dataset,Dt=ds. tables[0];

Iv. inserting

New Mysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database= Test"); M_conn. Open ();
New Mysqlcommand ("INSERT into Test.person (personcode,personname1) VALUES (' 123456 ', ' Test Old king ')"  12000; int irecordaffected = cmd. ExecuteNonQuery ();//Returns a few data inserted

Five, update

New Mysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database= Test"new Mysqlcommand ("update Test.person set personname1= ' beta-er-leper ' where personcode= ' 123456 '"12000; int irecordaffected = cmd. ExecuteNonQuery (); // returns a few updated data

Vi. deletion of

New Mysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database= Test"new Mysqlcommand ("test.person where personname1= ' Test King two leper ' and personcode= ' 123456 '"12000; int irecordaffected = cmd. ExecuteNonQuery (); // returns a few data deleted

Vii. Transaction Query

Mysqlconnection M_conn =NewMysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database=test"); M_conn. Open (); Mysqltransaction M_trans=M_conn. BeginTransaction ();D atatable DT=NewDataTable ("DS");D ataset ds=NewDataSet ();if(M_trans! =NULL) {Mysqldataadapter MDA=NewMysqldataadapter ("SELECT personname1 from person", M_conn); Mda.SelectCommand.CommandTimeout=12000; Mda. Fill (DS,"DS"); if(ds. Tables.count >0) {DT= ds. tables[0]; }
M_trans.commit (); The sign of the end of the transaction is the commit method of the calling transaction commit ()}

Eight, transaction insert, transaction update, transaction delete except SQL statement the rest are the same

Mysqlconnection M_conn =NewMysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database=test"); M_conn. Open (); Mysqlcommand cmd=NewMysqlcommand ("INSERT into person (personcode,personname1) VALUES (' 123456 ', ' Test Old king ')", M_conn); Mysqltransaction M_trans=M_conn. BeginTransaction (); cmd. Transaction=M_trans;cmd.commandtimeout=12000;if(M_trans! =NULL){    intirecordaffected =Cmd. ExecuteNonQuery ();
M_trans.commit (); The sign of the end of the transaction is the commit method that invokes the transaction commit ()}

IX. transaction Rollback

Mysqlconnection M_conn =NewMysqlconnection ("server=localhost;user id=root;password=root;persist Security info=true;database=test"); M_conn. Open (); Mysqlcommand cmd=NewMysqlcommand ("INSERT into person (personcode,personname1) VALUES (' 123456 ', ' Test Old king ')", M_conn); Mysqltransaction M_trans=M_conn. BeginTransaction (); cmd. Transaction=M_trans;cmd.commandtimeout=12000;if(M_trans! =NULL){    intirecordaffected =cmd. ExecuteNonQuery ();
M_trans.      Rollback (); The rollback method of the transaction is called before the transaction commits, and can be returned to the database state before the transaction, which is equivalent to the operation of the database in this transaction is invalid. }

Common operation of MySQL based on C # programming language