How can MySQL database and ASP. NET work together?

Because of its competitive prices and ease of use, MySQL has gradually increased its market share. The open source code community has developed a database connector that can be used in. Net Framework (. NET Framework) to extend the scope of use of MySQL. Let's learn how to use MySQL in. Net applications.

　　Data integration with. NET

The MySQL Community has developed MySQL Data interfaces, which provide basic functions for connecting data sources and program code. On Windows, the following MySQL connector is available:

• MySQL Connector/Net 1.0 (formerly called ByteFX. Data): It is an open source code. NET Data interface designed for MySQL. It is fully developed using C #. We can find it on MySQL.com. (Note: In this example, we will use the MySQL Connector/Net 1.0 data interface to connect to the database. you can install it easily by using the Windows installer, its code examples and documents are also included .)
• MySQLDirect. NET Data Provider: A commercial Data interface developed by Core Lab. The price is determined by the type of the purchased license, but we can download its trial version.

If you use Mono, you can find the PHP connector on the Mono website. Download . If you run Mono on Windows, the connector you downloaded contains the installer. If not, it depends on your Operating System To download the appropriate connector.

　　Use the MySQL Data Interface

After installing the MySQL data interface, you must introduce it in your code to use it. You can use MySql. Data. MySqlClient to connect to MySQL. Server . In C #, you can use the using statement to introduce the MySQL data interface:

Using MySql. Data. MySqlClient;

In addition, you can use the Import command in an ASP. NET Web Form to introduce the MySQL data interface:

Alternatively, you can use this namespace in your code to write the complete path of all classes, however, this will waste more bytes than using the Import command to Import more characters. After the namespace is specified, we can Database Data interaction. MySql. Data. MySqlClient this namespace provides many classes for processing MySQL Data. Below is a sample of these classes:

• MySqlConnection: Manages connections to MySQL servers/databases;
• MySqlDataAdapter: a set of commands and connections used to fill DataSet objects and update MySQL databases;
• MySqlDataReader: allows you to read data from a MySQL database. It is a one-way data stream;
• MySqlCommand: Provides the function of sending commands to the database server;
• MySqlException: Provides exception handling when a problem occurs.

　　Connect to the MySQL database

The first step to use the MySQL database is to establish a connection with the database through the MySQLConnection class. Using a connection string, MySqlConnection is instantiated as an example. The connection string tells the Code where to find the MySQL server and other options.

A connection string tells the code to use the specified user name and password to connect to a MySQL server named MySQLTestServer and access the techrepublic database. On my test machine, I set to allow anonymous login (this setting has a very large security vulnerability, so we do not recommend that you do this on the production server ), therefore, the following connection string will be used in the example:

"Server = localhost; database = sitepoint ;"

After the connection string is specified, the Open method of the MySqlConnection object is called and the connection is opened. After the connection is established, you can send commands to the MySQL database or obtain data from the database.

　　Combination of ASP. NET and MySQL

Let's further discuss how to combine the MySqlConnection class and other classes to generate a database list on a MySQL server. Table B lists a web form written in C # using ASP. NET. It establishes a connection, then runs a show databases command on the server, and then displays the result through the MySqlReader object.

Sending the show databases command to the MySQL server using the MySqlCommand object is the same as inputting this command directly in the MySQL management tool. The only difference is that we must use another object in the code to obtain the result set. The MySqlDataReader object is instantiated when obtaining results (through the ExecuteReader method of the MySqlCommand class ). The GetString method of the MySqlDataReader object is used to display data in the result set through ASP. NET label control. The pointer 0 of the GetString method specifies the first column of data that shows the current row of the result set (in the while LOOP.

　　Mono prompt

If you use the open-source Mono development platform, the code in the example can run normally with only a few changes. The Data interface of MySQL is in the space name ByteFX. Data. MySqlClient, rather than the MySql. Data. MySqlClient space name on Windows. In fact, MySQL's data interface was originally developed by ByteFX, but was subsequently acquired by MySQL. Therefore, if you use Mono, you must declare the space name as follows:

Using ByteFX. Data. MySqlClient;

The combination of MySQL and. NET provides a powerful development platform. MySQL has received powerful technical support in the open-source community, and. NET is also accepted by the open-source community through Mono. This combination provides a highly flexible development platform in Windows and other languages such as UNIX or Linux.