c#连接数据库mysql_在C＃中连接到MySQL数据库

c#连接数据库mysql

To connect with MySQL database using C#, we have some requirement to prepare setup are the following:

要使用C＃与MySQL数据库连接 ，我们需要准备以下设置：

Installation of MySQL in your PC.
在您的PC中安装MySQL。
Installation of MySQL connector according to PC (32-bit, 64 bit) or according to installed Windows Operating System.
根据PC(32位，64位)或已安装的Windows操作系统安装MySQL连接器。
Installation of Visual Studio.
安装Visual Studio。

Now, we are assuming you made all the above installations. First, we will open the MySQL terminal window, that appears like this,

现在，我们假设您已完成所有上述安装。首先，我们将打开MySQL终端窗口 ，如下所示：

Now, we will execute below SQL command to create database.

现在，我们将执行下面SQL命令来创建数据库。

    mysql> create database mysqltest;

Using "show databases" command we can see how many databases is available in MySQL.

使用“显示数据库”命令，我们可以查看MySQL中有多少个数据库可用。

Now, we have to create an application in Visual Studio, that demonstrates the connectivity with MySQL. Here, we will develop a windows application. (Note: Before creating an application first we need to add the reference for MySQL connector in solution explorer, see the image below)

现在，我们必须在Visual Studio中创建一个应用程序，以演示与MySQL的连接。在这里，我们将开发一个Windows应用程序。 ( 注意：首先创建应用程序之前，我们需要在解决方案资源管理器中添加MySQL连接器的参考 ，请参见下图)

In solution explorer window, we have added a reference "MySql.Data", because does not come by default, we need it manually, if MySql connector is installed in your PC then right-click on "Reference" and then you can add it, without adding MySql.data reference we cannot connect with MySql database using C# program.

在解决方案资源管理器窗口中，我们添加了一个引用“ MySql.Data” ，因为默认情况下不提供该引用，因此我们手动需要它，如果您的PC中安装了MySql连接器 ，则右键单击“ Reference” ，然后可以添加它，如果不添加MySql.data引用 ，则无法使用C＃程序连接到MySql数据库。

具有MySQL数据库连接的C＃项目 (C# project with MySQL database connectivity)

Now, we look to the application. Here, we took windows form with one command button.

现在，我们看一下该应用程序。在这里，我们采用带有一个命令按钮的Windows窗体。

In the above example, we changed two properties of both window form and command button are the following:

在上面的示例中，我们更改了窗口窗体和命令按钮的两个属性，如下所示：

Name
名称
Text
文本

Forms properties

表格属性

    Name:  "frmMySqlConn"
    Text:	"MY-SQL Connection test"

Button properties

按钮属性

    Name:	"btnConnect"
    Text:	"Connect"

用于连接MySQL数据库的C＃源代码 (C# source code to connect with MySQL database)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using MySql.Data.MySqlClient;


namespace MySqlConnect
{
    public partial class frmMySqlConn : Form
    {
        public frmMySqlConn()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            string MyConStr = "Server=localhost;Database=mysqltest;uid=root;pwd=root";

            MySqlConnection conn = new MySqlConnection(MyConStr);

            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                MessageBox.Show("Connection Opened Successfully");
                conn.Close();
            }
        }
    }
}

In the above code, most of the code is auto-generated we have to make the following changes,

在上面的代码中，大多数代码是自动生成的，我们必须进行以下更改，

Added namespace
添加名称空间
Wrote code in button click event
在按钮单击事件中编写代码

Here, we added one extra namespace to use classes regarding MySQL connectivity.

在这里，我们添加了一个额外的名称空间，以使用有关MySQL连接的类。

    using MySql.Data.MySqlClient;

On button click event, we created a connection string that contains SERVER, SERVER could be "localhost" or we can also give the IP address of the server, in our case MySQL is installed on our PC. Then we are using the "localhost" as a SERVER and the database name is "mysqltest" along with the username and password of MySQL database.

在button click event上 ，我们创建了一个包含SERVER的连接字符串，SERVER可以是“ localhost”，或者我们也可以提供服务器的IP地址，在本例中是在PC上安装了MySQL。然后，我们使用“ localhost”作为服务器，数据库名称为“ mysqltest”，以及MySQL数据库的用户名和密码。

Here, we have MySqlConnection class, then we pass the connection string while object creation, it is also possible to give connection string after object creation. Then we are using the "Open() method" for the database connection, if we pass the correct connection string with correct credentials, it will be connected to the database successfully, otherwise, it will generate an exception at the run time like this.

在这里，我们有MySqlConnection类 ，然后在创建对象时传递连接字符串，也可以在创建对象后给出连接字符串。然后，我们使用“ Open()方法”进行数据库连接，如果传递带有正确凭据的正确连接字符串，它将成功连接到数据库，否则，它将在运行时生成异常。

ERROR Message on connection fail:

错误连接失败消息：

Connection Successful message:

连接成功消息：

To check the successful connection, we can check the connection state, if it is connected successfully, we can display the error message and can close the connection.

要检查连接是否成功，我们可以检查连接状态，如果连接成功，则可以显示错误消息并可以关闭连接。