ASP. NET accessing MySQL

To use ASP. NET to connect to the MySQL database,

You must have a driver to access MySQL.ProgramOf course, you can use ODBC to access MySQL,

However, like accessing other databases, using ODBC will be less efficient to some extent,

Therefore, you generally do not use ODBC to access the MySQL database. In contrast,

You can choose to use the. NET driver provided by MySQL to access MySQL,

That is,. Net connector. What about this? Give a download link,

Http://dev.mysql.com/downloads/connector/net/1.0.html

The latest version for. netMysql-connector-net-6.3.1,

This version supports. NET Framework 2.0 and later versions,

Earlier versions of. Net ctor provided by MySQL only support earlier versions of. NET Framework,

For example, the 1.x version is not very useful. If you want to download the latest version, you can download it directly,

After downloading, You can decompress and install the MySQL database installed on your machine and bind it to Visual Studio,

After binding, you can easily access the MySQL database in Visual Studio,

MySQL 5.1 on my machine,

Then Visual Studio team System 2008,

After installing. Net connector, you can access the database through the following simple operations,

Open "server resource manager" --> "add connection"->

After you install. Net connector, it is automatically bound to the driver (provided) Program in Visual Studio,

Above, the server name is the address of the PC where your MySQL is located. It can be the machine name or IP address,

The following user name and password are the user name and password,

After that, you need to select a database to be accessed. This blogs is the database I first created in MySQL,

In fact, you can also click "advanced" to view the database connection string corresponding to the settings above,

After that, you can see it in "server resource manager ".

The above is the connection to the MySQL database that I added to Visual Studio,

After completing the preceding steps, you can easily use sqldatasource to access the MySQL database,

Of course, the above is done through settings, and below,

I also wrote a demo to complete the usage.CodeTo access the MySQL database,

I will use the data table blogsusers in the above database blogs. In fact,

My data table is used to store users. The fields are described as follows:

Let's take a look at the effect of this demo. After the demo is finished, let's look at the code,

The above indicates registering a user. After successful registration,

Check the data in the MySQL database to verify that the registration was successful !!!

This indicates that the data is successfully inserted !!!

To use some classes provided by. Net connector in the Demo code to access MySQL,

You must also perform the following operations,

Is to add a reference,

The referenced component is installed with. Net connector and bound to Visual Studio,

Then, if you want to use some classes provided by. Net connector in code-behind,

You must reference two namespaces. These two namespaces are provided by the mysql. Data component added above,

Using mysql. Data. mysqlclient;
Using mysql. Data. Types

Let's take a look at the code below. Some comments will be written in these codes, and all those should be noted,

Let's take a look at the JavaScript code.

Function onsuccesscallback (result, usercontext, methodname ){
// If WebServices returns true
// Indicates that the user name you entered does not exist in the database.
// Indicates that the entered user name can be used to register a user
If (result ){
Document. getelementbyid ("imgvalidate"). src = "image/yes.png ";
}
Else {
Document. getelementbyid ("imgvalidate"). src = "image/no.png ";
}
}

Function onfailedcallback (error, usercontext, methodname ){
Document. getelementbyid ("imgvalidate"). src = "image/no.png ";
VaR MSG = "";
MSG + = "error message:" + error. get_message ();
MSG + = "exception type:" + error. get_exceptiontype ();
MSG + = "error code:" + error. get_statuscode ();
MSG + = "stack information:" + error. get_stacktrace ();
MSG + = "timeout information:" + error. get_timedout ();
Alert (MSG );
}
// Onclientclick event of the Registration button
Function checkpwd (){
If (document. getelementbyid ("txtpwdfirst"). value! =
Document. getelementbyid ("txtpwdsecond"). Value ){
Alert ("the two passwords are inconsistent !!! ");
Return false;
}
}
</SCRIPT>

The above JavaScript code is very simple,

The following describes the code of WebServices called by JavaScript,

Using system;
Using system. Web. Services;
Using mysql. Data. mysqlclient;

Namespace webform. WebServices
{
[WebService (namespace = "http://tempuri.org/")]
[Webservicebinding (conformsto = wsiprofiles. basicprofile1_1)]
[System. componentmodel. toolboxitem (false)]
[System. Web. Script. Services. scriptservice]
Public class demo _ 44 _ Use: system. Web. Services. WebService
{
[Webmethod]
Public bool validateusername (string username)
{
Int Total = 0;
String constr = "Server = Xiaozhen-PC; user id = root; database = blogs ;" +
"Password = Xiaozhen; character set = gb2312 ;";
Using ( Mysqlconnection Mysqlcon = new mysqlconnection (constr ))
{
Mysqlcon. open ();
Using ( Mysqlcommand Mysqlcom = mysqlcon. createcommand ())
{
// What should be noted here is that there is a parameter in the SQL statement? Blogsname
// Obviously, this parameter is a bit odd. It is different from the @ parameter we have been using,
// This is because the. NET ctor provided by MySQL
// Are all used? To mark a parameter, but the current. Net ctor,
// Can you use either? To mark a parameter. You can also use a @ symbol to mark a parameter.
// The New. Net ctor version supports this feature.
String sqlstr = "select count (*) from blogsusers where blogsname =? Blogsname ";
Mysqlcom. commandtext = sqlstr;
Mysqlcom. Parameters. addwithvalue ("? Blogsname ", username );

Total = convert. toint32 (Mysqlcom. executescalar ());
}
}
// If the user already exists in the data table, it cannot be registered. Therefore, false is returned; otherwise, true is returned.
Return Total = 0? True: false;
}
}
}

The following is the code-behind of the page.

Using system;
Using mysql. Data. mysqlclient;

Namespace webform
{
Public partial class demo _ 44: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{

}

Protected void btnregister_click (Object sender, eventargs E)
{
Lblmsg. Text = string. empty;
String constr = "Server = Xiaozhen-PC; user id = root; database = blogs;" +
"Password = Xiaozhen; character set = gb2312 ;";

Using ( Mysqlconnection Mysqlcon = new mysqlconnection (constr ))
{
Mysqlcon. open ();
Using ( Mysqlcommand Mysqlcom = mysqlcon. createcommand ())
{
// As mentioned earlier, can you use. Net to access MySQL? To mark Parameters
// You can also use @ to mark a parameter.
// What is the previous use? To mark parameters,
// Here you will use @ to mark the Parameter
String sqlstr =
"Insert into blogsusers (blogsname, blogspassword, blogssex, blogsbirthday)" +
"Values (@ Name, @ password, @ sex, @ birthday )";
Mysqlcom. commandtext = sqlstr;

Mysqlcom. Parameters. addwithvalue ("@ name", txtname. Text. Trim ());
Mysqlcom. Parameters. addwithvalue ("@ password", txtpwdfirst. Text. Trim ());
If (rbtnman. Checked)
{
Mysqlcom. Parameters. addwithvalue ("@ sex", "man ");
}
Else
{
Mysqlcom. Parameters. addwithvalue ("@ sex", "Woman ");
}
Mysqlcom. Parameters. addwithvalue ("@ birthday", txtbirthday. Text. Trim ());

Lblmsg. Text = "congratulations, registration successful ~~~ ";
Mysqlcom. executenonquery ();
}
}
Txtname. Text = string. empty;
Txtpwdfirst. Text = string. empty;
Txtpwdsecond. Text = string. empty;
Txtbirthday. Text = string. empty;
}
}
}

Now, the entire demo is complete,

To sum up,

In this blog, we mainly use the. NET connector provided by MySQL to access MySQL,

Here, I also introduced how to use "server resource manager" in Visual Studio to access MySQL,

At the same time, I also introduced how to use code to access the MySQL database,

Note the two Mark parameters of MySQL. Is one of them used? To mark parameters,

This parameter is different from the flag parameter in Oracle, sqlserver, or DB2. It is worth noting !!!