Author: CRYSTAL Compiler
In the ASP era, if we want to build a database-driven web site, then you can choose the Microsoft SQL SERVER database, which costs a lot of money, or you can choose to spend a lot of time looking for the ACCESS database that achieves unified performance and stability, but In the .NET era you have another option, and that is: MySQL database
What is a MySQL database?
MySQL database is an open source database that maintains official support for the source code by obtaining authorization and can freely modify the source code. Currently, many companies and organizations have adopted this database. For this detailed information you can visit the official site of MySQL.
The first step is to download and install
Like most software, the first step is to obtain and install the mysql database software. Obtaining the Mysql database is very simple. It can be downloaded freely at http://dev.mysql.com/downloads/index.html . This article uses Mysql4 .1, of course readers can also download other versions, but what I would recommend to readers is that it is best to download precompiled binary files. My operating platform is Windwos XP, so I chose the Windows version including the installation file. This version is about 35M, so the download time is not very long, unless there is a problem with your network.
The installation is very simple and following the installation steps will not cause any problems, as shown below:
MySQL Server installation
MySQL Server installation
MySQL Server installation 
MySQL Server installation
As shown in the figure, in the last step of the installation, the system will ask you whether you want to configure the MySQL server. If you choose to configure the MySQL server now, the system will automatically run the MySQL instance configuration wizard, and you will proceed to the next step.
Second step configuration
The MySQL server instance configuration wizard makes configuring the server very simple. Follow the configuration wizard step by step and choose the default configuration most of the time.
 MySQL Server Configuration Wizard  MySQL Server Configuration Wizard |
 MySQL Server Configuration Wizard—Choose the purpose of the database  MySQL Server Configuration Wizard—Specify TCP/IP and Port Number |
You must ensure that you respond to the TCP/IP network protocol to ensure that the web server can connect to the database when the web page is loaded; if your database and web server are installed on the same server, you can disable this option to prevent access from the network.
MySQL Server Configuration Wizard—Set the Default String Type
 MySQL Server Configuration Wizard  MySQL Server Configuration Wizard—Installation Service  MySQL Server Configuration Wizard—Set SQL Connection Password  MySQL Server Configuration Wizard—Configuration is about to be completed |
Step 3MySQL Administrator
Maybe you think you don’t need this thing, but I still recommend that you download and use MySQL Administrator. It provides a graphical interface to help you manage MySQL databases. Windows users can run MySQL Administrator through the command prompt. For the rest of the time, I assume that you MySQL Administrator has been installed and the relevant legend will be used.
 MySQL Administrator main interface |
Step 4 Create database
To create a database we must first connect to the server. Run MySQL Administrator and log in to the server.
 Run MySQL Administrator and log in to the server |
Select "Catalogs" at the bottom of the left side of MySQL Administrator, and then the database catalog that currently exists on the server will appear on the right side of the manager. Right-click "mysql" in the Schema window and select "Create New Schema".
 Create a new database |
You will be prompted for a name for your database, here we use "mydatabase" and we will use this name throughout this article.
 Enter database name |
Once created, the new database will appear in the Schema window along with the other databases in the server. After selecting it, its details will appear in the right window.
Complete the creation of the new database:
 The new database creation is completed |
There is nothing more in there right now because the database is currently empty. Next we will add something to the database.
Step 5 Create table
Creating a table is very simple, just click the "Create Table" button, and the following dialog box will appear:
 Create table |
As shown in the figure, we have named the table "mytable" and has four fields. The id field is an auto-incrementing primary key, an integer field, a text field and a time/date field.
After completing these, click the "Apply Changes" button, and the window as shown below will appear. In the window is the SQL statement to create the table, and it will ask whether to execute it. Of course, click "Execute".
 Confirm and execute the SQL statements in the table |
So far, we have created a database named "mydatabase" that contains a table named "mytable". All we have to do next is add some data to the database.
Step 6 Add data
In the real situation, adding data is implemented through the application, but now I just want to add a few sample data, so I will use the insert statement of the SQL statement in the MySQL client command to achieve it. If you are still in MySQL now Administrator, then you can access the command line (Tools -> MySQL Command Line Client) through the menu "tools", otherwise you can access it through the MySQL group in the start menu.
 Add data through command line statements |
The first line in the diagram tells the server which database I will use, the second and third simply insert data into the database.
Now there are two sample data in the data table. So far, our database server has been established and running, with a database, a data table, and some data.
Step 7 Create a new MySQL user account
To add a user account, you need to run and log in to MySQL Administrator again. Select "User Administration" on the left side of the MySQL Administrator window. At the same time, the current account information of the server will be displayed on the right side (this is usually the so-called root). Right-click in the small window below. Account, select "Add new User".
 Run MySQL Administrator again and add a user account |
Next, you will be prompted to enter the details of the new user. I named the new user "15secs" and set the password to "password".
 Set general options such as username and password |
Once you have completed this, click the "Apply Changes" button to save your entries.
Step 8: Authorize user account
By default, new users can do almost nothing. To allow new users to connect to the MySQL database, they must be authorized in "Schema Privileges". This will be done in "Schema Privileges" in MySQL Administrator.
 Set permissions for new accounts |
Note that the above picture does not provide any authorization for the user. Since the following code needs to query the database, it is necessary to grant the user "select" permission, and then click the "Apply Changes" button to save.
To authorize:
 Authorize |
Maybe the application needs more permissions, such as: "INSERT", "UPDATE", and "DELETE", etc. You can use the same method to grant them to users, but be aware that the more permissions, the less secure you are. Control must be implemented for each user.
Step 9: Connect the ASP.NET page to the database server
There are two ways to connect to the MySQL database in .NET: MySQL Connector/ODBC and MySQL Connector/Net. The ODBC connector is an interactive platform that complies with the ODBC standard and is the best choice for .NET to access the MySQL database.
Download MySQL Connector/Net and install it directly, as shown below:
MySQL Connector/Net installation diagram
MySQL Connector/Net installation diagram
MySQL Connector/Net installation diagram
MySQL Connector/Net installation diagram
Note: I chose to register Connector/NET in the Global Assembly Cache, but I found that it was already installed, but I could not use the import statement and could not find the namespace of Connector/NET until the MySql.Data.dll file was copied. This problem was solved after going to the /bin directory. The error reported by the system is:
| ＜%@ Page Language="VB" debug="true" %＞ ＜%@ Import Namespace = "System.Data" %＞ ＜%@ Import Namespace = "MySql.Data.MySqlClient" %＞ ＜script language="VB" runat="server"＞ Sub Page_Load(sender As Object, e As EventArgs) Dim myConnection As MySqlConnection Dim myDataAdapter As MySqlDataAdapter Dim myDataSet As DataSet Dim strSQL As String Dim iRecordCount As Integer myConnection = New MySqlConnection("server=localhost; user id=15secs; password=password; database=mydatabase; pooling=false;") strSQL = "SELECT * FROM mytable;" myDataAdapter = New MySqlDataAdapter(strSQL, myConnection) myDataSet = New Dataset() myDataAdapter.Fill(myDataSet, "mytable") MySQLDataGrid.DataSource = myDataSet MySQLDataGrid.DataBind() End Sub ＜/script＞ <html> ＜head＞ ＜title＞Simple MySQL Database Query＜/title＞ ＜/head＞ <body> ＜form runat="server"＞ ＜asp:DataGrid id="MySQLDataGrid" runat="server" /＞ ＜/form＞ ＜/body＞ ＜/html＞ |
 Script running results |