How to connect to Mysql database in C#? Detailed explanation of error reporting exceptions and addition, deletion, modification and query

This article talks about C# connecting to the Mysql database. Detailed cases are attached below. When the connection error occurs, MySqlConnection will return a MySqlException,

including 2 variables: Message and Number.

1. Download mysql-connector-net-8.0.12 and install it, add Mysql.Data in the reference.

2. using MySql.Data.MySqlClient; This sentence should be written. As shown in the figure

is established on the premise that the MySQL database has been installed. It is installed in C:\Program Files (x86)\MySQL by default. It is recommended to select it during installation. The installation of Connector.NET 8.0.12 contains the dynamic link library for connecting MySQL and C#.

Help document C:\Program Files (x86)\MySQL\Connector.NET 8.0.12\Documentation\ConnectorNET.chm is the main basis for me to write this article. Among them, under Users Guide, Programming is an introduction to the eight classes of the dynamic link library, and Tutorial is the case code.

The essence of connecting to and operating the database is to use the dynamic link library MySql.Data.dll provided by the database to operate. MySql.Data.dll provides the following 8 classes:

• MySqlConnection: Connect to the MySQL server database.

• MySqlCommand: Execute a sql statement.

• MySqlDataReader: Contains the results of SQL statement execution and provides a method to read a row from the results.

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

• MySqlException: Exception returned when MySQL reports an error.

• MySqlCommandBuilder: Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated MySQL database.

• MySqlDataAdapter: Represents a set of data commands and a database connection that are 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 dynamic link library file

Method 1: Visual Studio, in Project (right-click)-Manage NuGet Packages (N) Then search for MySql.Data in the browse and install it.

Method 2: When installing the database MySQL, select the installation of Connector.NET 6.9 and put v4 in C:\Program Files (x86)\MySQL\Connector.NET 8.0.12\Assemblies Add a reference to MySql.Data.dll from .0 or v4.5 to the project. v4.0 and v4.5 correspond to the .NET Framework version number in the specific Visual Studio project Properties-Application-Target Framework.

2. Establish a connection (MySqlConnection class)

= =

3. Catch exceptions (MySqlException class)

When there is a connection error, MySqlConnection will return a MySqlException, which 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. Add, delete, check and modify code (MySqlCommand Class, MySqlDataReader class)

　ExecuteReader - used to query the database. The query result is a MySqlDataReader object returned. MySqlDataReader contains the results of the SQL statement execution and provides a method to read a row from the results.

ExecuteNonQuery - used to insert, update and delete data.

## ExecuteScalar——When used to query data, return query The value of the first row and first column in the result set, that is, only one value is returned.

　(1) Query

a. Query conditions are fixed

string sql= "select * from user";
MySqlCommand cmd = new MySqlCommand(sql,conn);
MySqlDataReader reader =cmd.ExecuteReader();//执行ExecuteReader()返回一个MySqlDataReader对象while (reader.Read())//初始索引是-1，执行读取下一行数据，返回值是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"是数据库对应的列名，推荐这种方式}

b. Query conditions are not fixed

//string sql = "select * from user where username='"+username+"' and password='"+password+"'"; //我们自己按照查询条件去组拼string sql = "select * from user where username=@para1 and password=@para2";//在sql语句中定义parameter，然后再给parameter赋值MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("para1", username);
cmd.Parameters.AddWithValue("para2", password);

MySqlDataReader reader = cmd.ExecuteReader();if (reader.Read())//如果用户名和密码正确则能查询到一条语句，即读取下一行返回true{    return true;
}

c. The query needs to return a value

string sql = "select count(*) from user";
MySqlCommand cmd = new MySqlCommand(sql, conn);
Object result=cmd.ExecuteScalar();//执行查询，并返回查询结果集中第一行的第一列。所有其他的列和行将被忽略。select语句无记录返回时，ExecuteScalar()返回NULL值if (result != null)
{    int count = int.Parse(result.ToString());
}

　(2) Insert, delete, change

string sql = "insert into user(username,password,registerdate) values('啊宽','123','"+DateTime.Now+"')";//string sql = "delete from user where userid='9'";//string sql = "update user set username='啊哈',password='123' where userid='8'";MySqlCommand cmd = new MySqlCommand(sql,conn);int result =cmd.ExecuteNonQuery();//3.执行插入、删除、更改语句。执行成功返回受影响的数据的行数，返回1可做true判断。执行失败不返回任何数据，报错，下面代码都不执行

5.事务(MySqlTransaction类)

String connetStr = "server=127.0.0.1;user=root;password=root;database=minecraftdb;";
MySqlConnection conn = new MySqlConnection(connetStr);
conn.Open();//必须打开通道之后才能开始事务MySqlTransaction transaction = conn.BeginTransaction();//事务必须在try外面赋值不然catch里的transaction会报错:未赋值Console.WriteLine("已经建立连接");try{    string date = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;    string sql1= "insert into user(username,password,registerdate) values('啊宽','123','" + date + "')";
    MySqlCommand cmd1 = new MySqlCommand(sql1,conn);
    cmd1.ExecuteNonQuery();    string sql2 = "insert into user(username,password,registerdate) values('啊宽','123','" + date + "')";
    MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
    cmd2.ExecuteNonQuery();
}catch (MySqlException ex)
{
    Console.WriteLine(ex.Message);
    transaction.Rollback();//事务ExecuteNonQuery()执行失败报错，username被设置unique
    conn.Close();
}finally{    if (conn.State != ConnectionState.Closed)
    {
        transaction.Commit();//事务要么回滚要么提交，即Rollback()与Commit()只能执行一个
        conn.Close();
    }
}

　　结语：连接数据库、操作数据库，本质是利用数据库提供的动态链接库MySql.Data.dll进行操作。动态链接库中的8个类上面常用操作只用到了类1-5，类6-8 的相关操作未涉及， 大家可以去看帮助文档C:\Program Files (x86)\MySQL\Connector.NET 8.0.12\Documentation\ConnectorNET.chm学习。

相关文章：

C# Using MySQL

mysql Connector C/C++ 多线程封装

The above is the detailed content of How to connect to Mysql database in C#? Detailed explanation of error reporting exceptions and addition, deletion, modification and query. For more information, please follow other related articles on the PHP Chinese website!