C # example of MYSQ connection (Comprehensive)

C # example of MYSQ connection (Comprehensive)

Find MySQLDriver. dll in the installation folder, and add the MySQLDriver. dll to the project.

(ADD) insert data:

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // SQL statement string SQL = insert into tbl_sysuser (usercode, username, password, usertype, isActived) values (c02, Cai qianqian, w1251314, real estate Bureau administrator, YES ); // prevent garbled characters MySQLCommand commn = new MySQLCommand (set names gb2312, conn); commn. executeNonQuery (); // execute the SQL statement MySQLCommand cmd = new MySQLCommand (SQL, conn); // return the number of affected rows int number = cmd. executeNonQuery (); // closes the database conn. close (); Console. writeLine (affected number of rows: + number );}

(Delete) delete data

 

 

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // SQL statement string SQL = delete from tbl_sysuser where usercode = c02; // prevent garbled MySQLCommand commn = new MySQLCommand (set names gb2312, conn); commn. executeNonQuery (); // execute the SQL statement MySQLCommand cmd = new MySQLCommand (SQL, conn); // return the number of affected rows int number = cmd. executeNonQuery (); Console. writeLine (affected number of rows: + number); // closes the database conn. close ();}

(Change) modify data [injection value]

 

 

 

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // prevent garbled characters MySQLCommand commn = new MySQLCommand (set names gb2312, conn); commn. executeNonQuery (); // SQL statement string SQL = update tbl_sysuser set isActived = @ isActived where id = @ id; // execute the SQL statement MySQLCommand cmd = new MySQLCommand (SQL, conn); // The injection value cmd. parameters. add (new MySQLParameter (@ isActived, YES); cmd. parameters. add (new MySQLParameter (@ id, 1); // returns the number of affected rows int number = cmd. executeNonQuery (); Console. writeLine (affected number of rows: + number); // closes the database conn. close ();}

(Change) modify data

 

 

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // SQL statement string SQL = update tbl_sysuser set isActived = YES where id = 1; // prevent garbled MySQLCommand commn = new MySQLCommand (set names gb2312, conn ); commn. executeNonQuery (); // execute the SQL statement MySQLCommand cmd = new MySQLCommand (SQL, conn); // return the number of affected rows int number = cmd. executeNonQuery (); Console. writeLine (affected number of rows: + number); // closes the database conn. close ();}

(Query) query data:

 

 

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // prevent garbled characters MySQLCommand commn = new MySQLCommand (set names gb2312, conn); commn. executeNonQuery (); // SQL statement string SQL = select * from tbl_sysuser; // query MySQLDataAdapter mda = new MySQLDataAdapter (SQL, conn) using the DataAdapter ); // The queried data exists in the able. DataTable can be understood as a virtual table. One row in the DataTable table is a record, and one column is a database Field DataTable dt = new DataTable (); mda. fill (dt); for (int I = 0; I <dt. rows. count; I ++) {Console. writeLine (extracted row: + dt. rows [I] [usercode] + | + dt. rows [I] [username] + | + dt. rows [I] [password] + | + dt. rows [I] [usertype] + | + dt. rows [I] [isActived]);} // closes the database conn. close ();}

(Query) query data 2

 

 

Using (MySQLConnection conn = new MySQLConnection (new MySQLConnectionString (localhost, housing, root, root ). asString) {conn. open (); // prevent garbled characters MySQLCommand commn = new MySQLCommand (set names gb2312, conn); commn. executeNonQuery (); // SQL statement string SQL = select * from tbl_sysuser; MySQLCommand cmd = new MySQLCommand (SQL, conn); MySQLDataReader reader = cmd. executeReaderEx (); while (reader. read () {if (reader. hasRows) {Console. writeLine (usercode: + reader. getString (1) + -- username: + reader. getString (2) ;}/// closes the database conn. close ();}