C# - Creating a .NET application that connects to a MySQL database (Kết nối CSDL MySQL bằng C#)

Vũ Thanh Lai · 22-07-2010, 07:49 PM

Note that the code is written using .NET 2.0 and the project is created in Visual Studio 2005. However, this won't stop you from using the code inside Visual Studio 2003 with .NET 1.1, because the code doesn't need any changes between versions of the .NET Framework.

Let's look at the most important pieces in our code.

Code:

private System.Data.Odbc.OdbcConnection OdbcCon;

private System.Data.Odbc.OdbcCommand OdbcCom;

private System.Data.Odbc.OdbcDataReader OdbcDR;

private string ConStr;

Here we declared an ODBC connection, command and data reader. We will need all these to connect, query and read data from the MySQL Server. The ConStr variable is a normal string variable that holds the connection string, of course, we could have passed the connection string directly without using an additional variable, but the code is more organized this way.

Now let's have a look in the click event of the Connect button:

Code:

private void btnConnect_Click(object sender, EventArgs e)

{

 // Build the connection string

 ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + ";PORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + ";PWD=" + txtPassword.Text + ";OPTION=3";

 OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);

 // Enable the List Tables button

 btnListTables.Enabled = true;

 try

 {

  // Try openning the connection


  txtLog.AppendText("Openning connection...\r\n");

  // Make sure the connection is closed

  if (OdbcCon.State == ConnectionState.Closed)

  {

   OdbcCon.Open();

   txtLog.AppendText("Connection opened\r\n");


  }

 }

 catch (System.Data.Odbc.OdbcException Ex)

 {

  // An error occured, give details

  txtLog.AppendText(Ex.Message + "\r\n");

  MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try  again.\r\n\r\nMore  details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

 }

}

The connection string is initialized here, using data from the TextBoxes on the form. Note the version of the MySQL ODBC driver, inside. Carefully enclosing in try/catch blocks, the lines that open the connection, we change the connection state of the ODBC connection to Open. We first check to make sure the connection is not already open.

Now to list the tables inside the database:

Code:

private void btnListTables_Click(object sender, EventArgs e)

{

 if (OdbcCon.State == ConnectionState.Open)

 {

  // Execute the SHOW TABLES query on the MySQL database

  OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES", OdbcCon);

  OdbcDR = OdbcCom.ExecuteReader();

  txtLog.AppendText("Tables inside " + txtDatabase.Text + ":\r\n");

  // Loop through the list of tables and display each one

  while (OdbcDR.Read())

  {

   txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");

  }

 }

}

We first check the state of the connection. If it is closed, we don't want to try to execute a command on the MySQL Server. Then we execute the SHOW TABLES query, which is the MySQL query for getting a list of tables in the selected database. Using a DataReader we pass through each table and display it inside the txtLog TextBox.

To keep this tutorial simple, I haven't added any other commands to be executed on the MySQL server, however you can perform any operation on the server. You can create new databases, select values, create tables, add values, edit values, delete databases and any other operation that you can think of, that MySQL supports through a query.

Also I should mention that even though this is a Windows application, the ODBC connection method works great with web applications too. That means you can easily build ASP.NET applications that use a MySQL database.

Here is the application running on Windows Vista:

tamphamaz · 07-09-2010, 04:56 PM

HÌnh như cái này muon xài được thì phải cài driver cho nó phải ko vậy?
----> BỔ SUNG BÀI VIẾT<----
---------------------------------------------------------------
Ai biết hướng dẫn mình với.
Thanks.

Có thể bạn quan tâm

C# - Creating a .NET application that connects to a MySQL database (Kết nối CSDL MySQL bằng C#)

Ðiều Chỉnh