How does C # connect to the MySQL database? Detailed errors and additions and deletions to change the search

This article is about the C # connection MySQL database, the following is attached to a detailed case, the connection error when mysqlconnection will return a mysqlexception,

These include 2 variables: Message, number.

1. Download mysql-connector-net-8.0.12 and install and add Mysql.data to the reference.

2. Using MySql.Data.MySqlClient; This sentence is to be written.

Based on the premise that the MySQL database has been installed and installed by default in C:\Program Files (x86) \mysql, it is recommended to check the installation of Connector.net 8.0.12 at installation with a dynamic link library of MySQL and C # connection.

Help document C:\Program Files (x86) \mysql\connector.net 8.0.12\documentation\connectornet.chm is my main basis for writing this article. Under the Users Guide, programming is a description of the 8 classes of the dynamic link library, and tutorial is the case code.

Connecting the database and manipulating the database is essentially using the dynamic link library MySql.Data.dll provided by the database to operate. MySql.Data.dll offers the following 8 classes:

• Mysqlconnection: Connect to the MySQL server database.

• Mysqlcommand: Executes an SQL statement.

• Mysqldatareader: Contains the result of the SQL statement execution and provides a way to read a row from the result.

• Mysqltransaction: Represents a SQL transaction in a MySQL database.

• Mysqlexception:mysql returns the exception when an error occurs.

• 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.

1. Add a dynamic link library file

Method One: Visual Studio, in Project (right-click)-Manage NuGet package (N) and then search for Mysql.data in the browser and install it.

Method Two: Install the database MySQL to select Connector.net 6.9 installation, will C:\Program Files (x86) \mysql\connector.net 8.0.12\ A reference to a project that is added to the MySql.Data.dll 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 Class)

= =

3. Catching exceptions (Mysqlexception Class)

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 class, Mysqldatareader Class)

　　The executereader--is used to query the database. The result of the query is to return the Mysqldatareader object, Mysqldatareader contains the result of the SQL statement execution, and provides a way to read a row from the result.

executenonquery--is used to insert, update, and delete data.

when executescalar--is used to query data, returns the value of the first column in the first row of the query result set, which returns only one value.

(1) Enquiry

A. Fixed query conditions

String Sql= "SELECT * from user"; Mysqlcommand cmd = new Mysqlcommand (sql,conn); Mysqldatareader Reader =cmd. ExecuteReader ();//Executes ExecuteReader () returns a Mysqldatareader object while (reader. Read ())//The initial index is-1, execution reads the next row 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"));//"userid" is the database corresponding to the column name, recommended this way}

B. The query condition is not fixed

String sql = "SELECT * from user where username= '" +username+ "' and password= '" +password+ "'"; We go through the query to set the string sql = "SELECT * from user where username= @para1 and password= @para2";//define parameter in the SQL statement, The parameter is then assigned the value mysqlcommand cmd = new Mysqlcommand (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, a statement can be queried, that is, reading the next line returns true{return    true;

C. require a query to return a value

String sql = "SELECT COUNT (*) from user"; Mysqlcommand cmd = new Mysqlcommand (SQL, conn), Object Result=cmd. ExecuteScalar ();//Executes the query and returns the first column of the first row in the query result set. All other columns rows will be ignored. When no records are returned for the SELECT statement, ExecuteScalar () returns a null value if (result! = null) {    int count = Int. Parse (result. ToString ());}

(2) Insert, delete, change

String sql = "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 = new Mysqlcommand (sql,conn); int result =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 Class)

String connetstr = "SERVER=127.0.0.1;USER=ROOT;PASSWORD=ROOT;DATABASE=MINECRAFTDB;"; Mysqlconnection conn = new mysqlconnection (CONNETSTR); Conn. Open ();//The channel must be opened before the transaction can start mysqltransaction transaction = conn. BeginTransaction ();//The transaction must be assigned a value outside the try or the transaction in the catch will error: Unassigned Console.WriteLine ("Connection established"); try{string date =    DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;    String sql1= "insert into User (Username,password,registerdate) VALUES (' Ah-wide ', ' 123 ', '" + Date + "')";    Mysqlcommand cmd1 = new Mysqlcommand (sql1,conn); Cmd1.    ExecuteNonQuery ();    String sql2 = "INSERT into user (Username,password,registerdate) VALUES (' Ah-wide ', ' 123 ', '" + Date + "')";    Mysqlcommand cmd2 = new Mysqlcommand (SQL2, conn); Cmd2. ExecuteNonQuery ();} catch (Mysqlexception ex) {Console.WriteLine (ex.    Message); Transaction. Rollback ();//transaction ExecuteNonQuery () execution failure error, username is set to unique Conn. Close ();} finally{if (Conn. state = connectionstate.closed) {transaction.commit ();//transaction either rolls back or commits, that is, ROLlback () and commit () can only perform one conn.    Close (); }}

Conclusion: Connecting the database and manipulating the database, the essence is to use the dynamic link library MySql.Data.dll provided by the database to operate. The 8 classes in the dynamic link library use only the class 1-5, the related operation of class 6-8 is not involved, you can go to see the Help document C:\Program Files (x86) \mysql\connector.net 8.0.12\documentation\ Connectornet.chm study.

Related articles:

C # Using MySQL

MySQL Connector/C + + multithreaded encapsulation