C # connect to MySQL Database

Connect to the database, execute the SQL statement required by the API, the database has provided the relevant dynamic link library.

Help documents Connectornet.chm under C:\Program Files (x86) \mysql\connector.net 6.9\documentation

1. Add a dynamic link library file:

To install the database MySQL check the installation of Connector.net 6.9, C:\Program Files (x86) \mysql\connector.net 6.9\ The MySql.Data.dll Import project in Assemblies v4.0 or v4.5. V4.0 and v4.5, corresponding to the version number of the. NET framework in the Visual Studio specific project properties-application-Target Framework

2. Establishing a connection (mysqlconnection)

usingMySql.Data.MySqlClient; String Connetstr="server=127.0.0.1;port=3306;user=root;password=root; database=minecraftdb;";//Server=127.0.0.1/localhost stands for native, port number port default is 3306 can not writeMysqlconnection Conn=Newmysqlconnection (CONNETSTR);Try{Conn. Open ();//open a channel, establish a connection, an exception may occurConsole.WriteLine ("Connection already established");  // use code here to make additions and deletions to the database }Catch(Mysqlexception ex) {Console.WriteLine (ex). Message);}finally{Conn. Close ();}

3. Catching exceptions (Mysqlexception)

When a connection error occurs, Mysqlconnection returns a mysqlexception that includes 2 variables:

MESSAGE:A message that describes the current exception.

Number:the MySQL error number. (0:cannot connect to server. 1045:invalid user name and/or password.)

Catch(Mysqlexception ex) {Switch(ex. Number) { Case 0: Console.WriteLine ("cannot connect to server. Contact Administrator");  Break;  Case 1045: Console.WriteLine ("Invalid Username/password, please try again");  Break; }}            

4. Adding and deleting the code (Mysqlcommand, Mysqldatareader)

(1) Enquiry

Fixed query conditions

stringSql="SELECT * from user"; Mysqlcommand cmd=NewMysqlcommand (Sql,conn); Mysqldatareader Reader=cmd.ExecuteReader ();//ExecuteReader () to generate a Mysqldatareader object while(Reader. Read ())//read the next page of data, the return value is bool{    //Console.WriteLine (Reader[0]. ToString () + reader[1]. ToString () + reader[2].    ToString ()); //Console.WriteLine (reader. GetInt32 (0) +reader. GetString (1) +reader. GetString (2));Console.WriteLine (reader. GetInt32 ("userid") + Reader. GetString ("username") + Reader. GetString ("Password"));//recommend this way}

A query is required to return a value

string " Select COUNT (*) from user "  New  mysqlcommand (SQL, conn); Object result=cmd.executescalar (); // executes the query and returns the first column of the first row in the result set returned by the query. All other columns rows will be ignored. ExecuteScalar () returns a null value if NULL if no record is returned for the SELECT statement {    int  int. Parse (result. ToString ());}

Query condition is not fixed

//String sql = "SELECT * from user where username= '" +username+ "' and password= '" +password+ "'";//we are going to set up our own search terms.stringsql ="SELECT * from user where [email protected] and [email protected]";//define the parameter in the SQL statement, and then assign a value to the parameterMysqlcommand cmd=Newmysqlcommand (SQL, conn); cmd. Parameters.addwithvalue ("para1", username); cmd. Parameters.addwithvalue ("Para2", password); Mysqldatareader Reader=cmd.ExecuteReader ();if(Reader. Read ())//if the user name and password are correct, you can query to a statement that reads the next line to return True{    return true;}

(2) Insert, delete, change

stringsql ="INSERT INTO User (Username,password,registerdate) VALUES (' Ah-wide ', ' 123 ', '"+datetime.now+"')";//String sql = "Delete from user where userid= ' 9 '";//String sql = "Update user set Username= ' aha ', password= ' 123 ' where userid= ' 8 '";Mysqlcommand cmd=NewMysqlcommand (sql,conn);intResult =cmd. ExecuteNonQuery ();//3. Execute INSERT, delete, change statement. The number of rows that were successfully returned by the execution of the affected data returned 1 to make a true judgment. Execution failure does not return any data, error, the following code is not executed

5. Transactions (mysqltransaction)

String Connetstr ="Server=127.0.0.1;user=root;password=root;database=minecraftdb;"; Mysqlconnection Conn=Newmysqlconnection (CONNETSTR); Conn. Open ();//You must open a channel before you can start a transactionmysqltransaction Transaction= Conn. BeginTransaction ();//The transaction must be assigned outside the try or the transaction in the catch will error: not assignedConsole.WriteLine ("Connection already established");Try{    stringDate = DateTime.Now.Year +"-"+ DateTime.Now.Month +"-"+DateTime.Now.Day; stringsql1="INSERT INTO User (Username,password,registerdate) VALUES (' Ah-wide ', ' 123 ', '"+ Date +"')"; Mysqlcommand cmd1=NewMysqlcommand (Sql1,conn); Cmd1.     ExecuteNonQuery (); stringSQL2 ="INSERT INTO User (Username,password,registerdate) VALUES (' Ah-wide ', ' 123 ', '"+ Date +"')"; Mysqlcommand CMD2=NewMysqlcommand (SQL2, conn); Cmd2. ExecuteNonQuery ();}Catch(Mysqlexception ex) {Console.WriteLine (ex).    Message); Transaction. Rollback ();//transaction ExecuteNonQuery () execution failure error, username is set to uniqueConn. Close ();}finally{    if(Conn. State! =connectionstate.closed) {transaction.commit ();//the transaction either rolls back or commits, that is, rollback () and commit () can only perform oneConn.    Close (); }}

Summary:

The MySQL dynamic link library provides 8 classes, which are used only for these 4 classes

(1) Mysqlconnection: Connect to the MySQL server database.

(2) Mysqlcommand: Executing an SQL statement

The a.executereader--is used to query the database. The result is usually returned in the Mysqldatareader object.

b.executenonquery--is used to insert and delete data.

The c.executescalar--is used to return a value.

(3) Mysqldatareader: Contains the result of the SQL statement execution and provides a way to read a row from the result

(4) Mysqltransaction: Represents a SQL transaction in a MySQL database.

(5) Mysqlexception:mysql returned when the error is exception

And the related operations of these 3 classes are not involved, you can go to see the help document Connectornet.chm in C:\Program Files (x86) \mysql\connector.net 6.9\documentation inside

Mysqlcommandbuilder:automatically generates single-table commands used to reconcile changes made to a DataSet with the AS sociated MySQL database.

Mysqldataadapter:represents a set of data commands and a database connection that is used to fill a data set and update A MySQL database.

Mysqlhelper:helper class that makes it easier to work with the provider.

C # connect to MySQL Database