ASP. NET and MySQL Databases

Prepared by crystal

In the ASP era, if we want to build a database-driven Web site, then you can choose a Microsoft SQL Server database with a lot of money or choose to spend a lot of time looking for an Access database that achieves unified performance and stability, but in. in the net era, you have another option: MySQL database

What is a MySQL database?

MySQL database is an openSource codeTo maintain the sourceCodeAt the same time, the source code can be freely modified. Currently, many companies and organizations use this database. For more information, visit the official site of MySQL.

Step 1 download and install

Like most software, the first is to get and install the MySQL database software, obtain the MySQL database side is very simple, In the http://dev.mysql.com/downloads/index.html. this document uses mysql4.1. Of course, you can download the pre-compiled binary file. However, we recommend that you download the pre-compiled binary file. My operating platform is windwos XP, so I chose the Windows version that includes the installation file. This version is about 35 MB, so the download time is not very long, unless your network is faulty.

The installation process is very simple and does not cause any problems, such:

Install MySQL Server Install MySQL Server Install MySQL Server Install MySQL Server

In the last step of installation, the system will ask you if you want to configure the MySQL server. If you select to configure the MySQL server now, the system will automatically run the MySQL instance Configuration Wizard, so you will proceed to the next step.

Step 2 Configuration

The MySQL server instance Configuration Wizard makes it very easy to configure the server. Follow the Configuration Wizard step by step and select the default configuration most of the time.

MySQL Server Configuration Wizard MySQL Server Configuration Wizard

For example, you can choose to install it on a dedicated database server or a shared server. Because of the laptop I use, I chose the simplest "Developer Machine ", this setting does not provide the same performance and does not support many system resources.

MySQL Server Configuration Wizard-select database usage MySQL Server Configuration Wizard-specifying TCP/IP and port number

Make sure to respond to the TCP/IP network protocol to ensure that the Web server can connect to the database when the web page is required. If your database and web server are installed on the same server, 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-install services MySQL Server Configuration Wizard-set the SQL connection password MySQL Server Configuration Wizard-configuration coming soon

Step 3: MySQL Administrator

Maybe you don't think you need this, but I suggest you download and use MySQL administrator. It provides a graphical interface to help you manage MySQL databases, for Windows, you can run MySQL Administrator through the command prompt line. For the rest of the time, I assume that you have installed MySQL administrator and the related legends will be used.

MySQL administrator Main Interface

Step 4: Create a database

To create a database, you must first connect to the server. Run MySQL administrator and log on to the server.

Run MySQL administrator and log on to the server

Select "catalogs" at the bottom left of MySQL administrator, and the existing database directory on the server will appear on the right of the manager. Right-click "MySQL" in the schema window ", select "create new schema ".

Create a new database

The system will prompt you to enter the database name. Here we use "mydatabase", which will be used later in this article.

Enter Database Name

Once created, the new database will appear in the schema window with other databases on the server. After you select it, its details will appear in the window on the right.

Create a new database:

The new database has been created.

There is nothing more here, because the database is still empty. Next we will add something for the database.

Step 5: Create a table

To create a table, you only need to click "create table". The following dialog box is displayed:

Create a table

We have named the table "mytable", which has four fields. The ID field is the primary key for auto increment, an integer field, a text field, and a time/Date Field.

After these steps are completed, click the "Apply changes" button. In the window that appears, the SQL statement for creating the table is displayed, and you are asked whether to execute the statement. Of course, click "execute ".

Confirm and execute the SQL statement in the table

So far, we have created a database named "mydatabase" that contains a table named "mytable". Next, we need to add point data to the database.

Step 6 add data

In actual situations, the data is added through the ApplicationProgramBut now only a few samples need to be added, so I will use the insert statement of the SQL statement in the mysql client command. If you are still in MySQL administrator, you can access the command line (tools> MySQL command line client) through the menu "Tools". Otherwise, you can access the command line through the MySQL group in the Start Menu.

Add data using command line statements

The first line in the figure is to tell the server that I will use the database, and the second and third are simply inserting data into the database.

Now there are two sample data in the data table. So far, our database server has been established and run, and there is a database, a data table, and some data.

Step 7 create a new MySQL user account

To add a user account, run it again and log on to MySQL administrator. On the left side of the MySQL administrator window, select "user administration ", at the same time, the information about the current account of the server (usually called root) is displayed on the right. Right-click the account in the window below and select "Add new user ".

Run MySQL administrator again and add a user account

Next, the system will prompt you to enter the details of the new user. I will name the new user "15 secs" and set the password to "password ".

Set common options such as user name and password

Click "Apply changes" to save the input.

Step 8 authorize the 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. in "schema privileges.

Set permissions for new accounts

Note that you have not performed any authorization for the user. Because the subsequent code needs to query the database, you need to grant the user the "select" permission and click "Apply changes" to save the permission.

Authorization:

Authorize

Maybe the application requires more permissions, such as "insert", "Update", and "delete". You can grant them to users in the same way, however, the more permissions you have, the less secure you are. You must control each user.

Step 9 connect to the database server on the ASP. NET page

In. there are two methods to connect to the MySQL database in. Net: MySQL connector/ODBC and MySQL connector/net. ODBC connector is an interaction platform conforming to the ODBC standard. net to access the MySQL database.

Download MySQL connector/net and install it directly, for example:

MySQL connector/net installation Diagram MySQL connector/net installation Diagram MySQL connector/net installation Diagram MySQL connector/net installation Diagram

Note: I have selected register connector/net in the Global Assembly Cache, but I found it has been installed, but I cannot use the Import Statement and cannot find the namespace of connector/net, until MySQL. data. this problem is solved only after the DLL file is copied to the/bin directory. The system reports the following error:

Bc30466: namespace or type specified in the imports 'mysql. Data. mysqlclient' cannot be found

I believe this problem persists. You can only manually copy files from the installation location to the corresponding directory, for example, c: \ Program Files \ mysql connector net 1.0.4 \ bin \. NET 1.1 \, copy to c: \ Inetpub \ wwwroot \ bin \ to solve this problem.

Step 10 sample programs

So far our database has finally been completed, and MySQL connector/net has been installed successfully. We should do something else. Next I will provide a simple script. In this script, we will connect to and query the data in the database. The input data is not the main point of the script. MySQL. aspx

<% @ 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 = 15 secs; 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>

In the above script, the black text part will be modified based on your platform, for example, the SQL Server application system. data. use sqlclient to replace MySQL. data. mysqlclient, this is just one point. I will not discuss it in more detail. Is the script running result:

Script running result

Conclusion:

I hope this article will help readers understand the database server. When you plan to upgrade from access or SQL Server, MySQL is a good choice, although it is open-source, there are still some defects, but it is different from. net can do a lot of work, and MySQL administrator makes managing database servers no longer a nightmare for administrators.