Asp. NET and MySQL database Concise Diagram Introduction Tutorial _ Practical Tips

Author: Crystal Compilation

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

　　What is a MySQL database?

MySQL database is an open source database, by obtaining authorization to maintain the official source code support, while free to modify the source code, many companies and organizations have adopted this database. You will have access to the official MySQL site for this detailed information.

　　Download and install the first step

Like most software, the first is to get and install MySQL database software, access to the MySQL database is very simple in the http://dev.mysql.com/downloads/index.html. Free to download, this article is Mysql4.1, of course, the reader can also be under other versions, but I would recommend to the reader is the best download precompiled binary files. My operating platform is Windwos XP, so I chose a Windows version that includes the installation files, which is about 35M, so the download time is not very long unless you have a network problem.

Installation is very simple, follow the installation steps will not cause any problems, the following figure:

MySQL Server Installation MySQL Server Installation MySQL Server Installation MySQL Server Installation

As shown in the figure, the system will ask you if you want to configure the MySQL server at the end of the installation, and if you choose to configure the MySQL server now, the MySQL Instance Configuration Wizard will automatically run, so you will go to the next step.

　　Second Step configuration

The MySQL server Instance Configuration Wizard makes the configuration server very simple, follow the configuration Wizard step-by-step, and most of the time choose the default configuration.

MySQL Server Configuration Wizard MySQL Server Configuration Wizard

As shown above, the reader can choose to install on a dedicated database server or a shared server, because I use the laptop, so I choose the simplest "Developer Machine", this setting does not provide the same performance, and can not use a lot of system resources

MySQL Server Configuration Wizard-Select the purpose of the database MySQL Server Configuration Wizard-Specify TCP/IP and port number

You must ensure that you respond to TCP/IP network protocols to ensure that the Web server can connect to the database when the Web page is needed, and 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 default string type

MySQL Server Configuration Wizard MySQL Server Configuration Wizard-Installation Services MySQL Server Configuration Wizard-Set SQL connection password MySQL Server Configuration Wizard-ready to complete configuration

　　Third Step MySQL Administrator

You might think you might not need this, but I would recommend that you download and use the MySQL administrator, which provides a graphical interface to help you manage the MySQL database, and Windows users can run the MySQL administrator through the command prompt. For the rest of the time I assume you have the MySQL Administrator installed and will use the relevant legend.

MySQL Administrator Main interface

　　Step fourth Create a database

To create a database, we 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 the MySQL administrator, and then on the right side of the manager, the database directory that is already on the server will appear, right-click the Schema window's "MySQL" and choose "Create New schema".

To create a new database

You will be prompted to enter the name of the database, where we use "mydatabase", which will also be used later in this article.

Enter database name

Once created, the new database appears in the schema window along with the other databases in the server, and after it is selected, its details are available in the right window.

To complete the creation of a new database:

The new database creation is complete.

There's nothing more here now, because the database is still empty. Let's add something to the database below.

　　Step Fifth Create a table

Creating a table is simple, just click the Create Table button, and the following dialog box appears:

Create a table

As shown, we have named the table "MyTable" with four fields, where the ID field is an AutoIncrement primary key, an integer field, a text field, and a Time/date field.

After completing these, click on the "Apply Changes" button, the following image will appear in the window, the window is the SQL statement to create the table, while asking whether to execute, of course, click "Execute".

Confirm and execute the SQL statements in the table

So far, we've created a database named "MyDatabase" with a table named "MyTable," and we're going to add point data to the database.

　　Step Sixth Add data

In real life, adding data is implemented through the application, but now it's just a few sample data to add, so I'll use the SQL statement INSERT statement in the MySQL client command, and if you're still in the MySQL administrator, you can go through the menu "Tools" to access the command-line (Tools-> MySQL command line Client), otherwise it can be accessed through the MySQL group on the Start menu.

To add data by using command line statements

The first line in the diagram tells the server that I will use that database, and the second and third are simply inserting data into the database.

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

　　Step seventh Create a new MySQL user account

To add a user account you need to run again and log on to the MySQL administrator, select "User Administration" on the left side of the MySQL administrator window. At the same time, the right will display the server's current account information (this is usually called root), right click on the small window below the account, select "Add new User."

Run the 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", the password is set to "password."

Set general options for user name, password, and so on

When you are done, click the Apply Changes button to save the input.

　　The eighth step gives the user account authorization

By default, new users have almost nothing to do, to allow new users to connect to the MySQL database must be authorized in the schema privileges, which will be done in the "schema privileges" in MySQL Administrator.

Set permissions for new account opening

Note the image above does not have any authorization for the user, because the following code needs to query the database, so you need to give the user "select" Permission, and then click the "Apply Changes" button to save.

To authorize:

To authorize

Perhaps the application needs more permissions, such as "INSERT", "UPDATE", "DELETE", and so on, and you can give it to the user in the same way, but be aware that the more permissions you have, the less secure you have to control each user.

　　Nineth step ASP. NET page Connection database server

There are two ways to connect to a MySQL database in. NET: the MySQL connector/odbc and MySQL CONNECTOR/NET,ODBC connectors are ODBC-compliant interactive platforms and the best choice for. NET to access the MySQL database.

Download the 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 the register connector/net in the Global Assembly Cache, but I found that it has been installed, but I cannot use the import statement to find the Connector/net namespace, This problem is not resolved until the MySql.Data.dll file is copied to the/bin directory, and the system reports the error:

Bc30466:namespace or type specified in the Imports ' MySql.Data.MySqlClient ' cannot to be found

I believe this problem still exists, can only temporarily copy the file from the installation location to the appropriate directory, such as: C:\Program files\mysql\mysql Connector Net 1.0.4\bin\.net 1.1\, copy to C:\Inetpub \wwwroot\bin\, we can solve this problem.

　　 Step Tenth sample program

At this point our database finally completed, MySQL Connector/net also installed successfully, we should do something else. Below I will provide a simple script in which we will connect and query the data in the database, and 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=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> <title> simple MySQL Database Query </title> <body> <form runat= "Server" > <asp:datagrid id= "Mysqldatagrid" runat= "Server"/> </form> </body>

In the script above, the bold part will be modified with the platform you are using, such as using SQL Server application System.Data.SQLClient to replace MySql.Data.MySqlClient, this is just a little more detailed this article is not discussed. The following figure is the result of the script run:

Script Run Results

　　 Conclusion:

I hope this article will help readers understand the database server, and when you plan to upgrade from Access or SQL Server, MySQL is a good choice, although it is open source, and there are some flaws, but it and. NET can do a lot of work, in addition MySQL administrator makes the Management database server is no longer a nightmare for administrators.