[ASP. NET]. Net Method for connecting to the MySQL database

Suddenly, I was a little interested in. net connection to the MySQL database, so I went to the Internet to find information, learn how to install MySQL, and how to use MySQL. I finally got it done!

The final result is to display the data of the data table in the database:

First of all, you must have a MySQL database. MySQL: www.mysql.com/downloads/. I downloaded the latest MySQL version.

For how to install MySQL 5.6, refer to this article.

If the following files are not found after the installation is complete, it means there is no. to download and install the driver for the net platform, go to the official website. For details, refer to this blog :.. Net to connect to MySQL.

 

It is actually a reference to the. NET platform mysql-related class libraries.

 

Then edit the Code:

1. Configure the database connection field in the configuration file, which is the same as that of SQL Server.

Web. config

<? XML version = "1.0" encoding = "UTF-8"?> <! -- Access the http://go.microsoft.com/fwlink/ for detailed messages about how to configure ASP. NET applications? Linkid = 169433 --> <configuration> <connectionstrings> <Add name = "connstr" connectionstring = "Server = localhost; Port = 3306; user id = root; Password = 111111; database = yc_test "/> </connectionstrings> <system. web> <compilation DEBUG = "true" targetframework = "4.0"/> </system. web> </configuration>

2. I wrote a simple MySQL Assistant class O (helper _ helper) O Based on others' SQL assistant classes. Refer to blog: Self-encapsulated sqlhelper

Mysqlhelper. CS

Using system; using system. collections. generic; using system. LINQ; using system. web; using MySQL. data. mysqlclient; using system. configuration; using system. data;/*** created by: YC * Description: Database Assistant class */namespace mysql_try {public class mysqlhelper {private mysqlconnection conn = NULL; private mysqlcommand cmd = NULL; private mysqldatareader SDR; private mysqldataadapter SDA = NULL; Public mysqlhelper () {string connstr = configurationmanager. connectionstrings ["connstr"]. connectionstring; // obtain the MySQL database connection string conn = new mysqlconnection (connstr ); // database connection} // <summary> // open the database link // </Summary> /// <returns> </returns> private mysqlconnection getconn () {If (Conn. state = connectionstate. closed) {Conn. open ();} return conn;} // <summary> // close the database link /// </Summary> private void getconnclose () {If (Conn. state = connectionstate. open) {Conn. close ();}} /// <summary> /// execute the SQL statement or stored procedure without parameters. /// </Summary> /// <Param name = "plain text"> Add/delete modify the SQL statement or stored procedure string </param> /// <Param name = "CT"> command type </param> /// <returns> affected function </ returns> Public int executenonquery (string plain text, commandtype CT) {int res; using (cmd = new mysqlcommand (plain text, getconn () {cmd. commandtype = CT; Res = cmd. executenonquery ();} return res ;} /// <summary> /// execute the SQL statement or stored procedure with parameters. /// </Summary> /// <Param name = "plain text"> add, delete, and modify SQL statement or stored procedure string </param> /// <Param name = "paras"> set of parameters assigned to a stored procedure or SQL statement </param> /// <Param name = "CT"> command type </param> // <returns> affected function </returns> Public int executenonquery (string plain text, mysqlparameter [] paras, commandtype CT) {int res; using (cmd = new mysqlcommand (plain text, getconn () {cmd. commandtype = CT; cmd. parameters. addrange (paras); Res = cmd. executenonquery ();} return res ;} /// <summary> /// execute an SQL statement or stored procedure without parameters /// </Summary> /// <Param name = "plain text"> query an SQL statement statement or stored procedure string </param> /// <Param name = "CT"> command type </param> /// <returns> the queried able object </ returns> Public datatable executequery (string plain text, commandtype CT) {datatable dt = new datatable (); cmd = new mysqlcommand (plain text, getconn (); cmd. commandtype = CT; using (SDR = cmd. executereader (commandbehavior. closeconnection) {DT. load (SDR) ;}return DT ;} /// <summary> /// execute the SQL statement or stored procedure with parameters. /// </Summary> /// <Param name = "plain text"> query the SQL statement or the Stored Procedure string </param> /// <Param name = "paras"> parameter set </param> /// <Param name = "CT"> command type </param> // <returns> </returns> Public datatable executequery (string plain text, mysqlparameter [] paras, commandtype CT) {datatable dt = new datatable (); cmd = new mysqlcommand (plain text, getconn (); cmd. commandtype = CT; cmd. parameters. addrange (paras); Using (SDR = cmd. executereader (commandbehavior. closeconnection) {DT. load (SDR) ;}return DT ;}/// <summary> /// execute the command for the specified database connection string and return dataset. /// </Summary> /// <Param name = "strsql"> A valid database connection string </param> /// <returns> Returns a result set containing dataset </returns> Public dataset executedataset (string strsql) {dataset DS = new dataset (); SDA = new mysqldataadapter (strsql, getconn (); try {SDA. fill (DS) ;}catch (exception ex) {Throw ex ;}finally {getconnclose () ;}return DS ;}}}

3. The front-end and back-end of the web page

Mysql_toconnect.aspx

<% @ Page Language = "C #" autoeventwireup = "true" codebehind = "mysql_toconnect.aspx.cs" inherits = "mysql_try.mysql_toconnect" %> <! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <HTML xmlns = "http://www.w3.org/1999/xhtml"> Mysql_toconnect.aspx.cs

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using MySql.Data.MySqlClient;using System.Configuration;using System.Data;namespace MySql_Try{    public partial class MySql_ToConnect : System.Web.UI.Page    {        protected void Page_Load(object sender, EventArgs e)        {            MySQlHelper h = new MySQlHelper();            string sql = "select * from users";            DataTable ds = h.ExecuteQuery(sql,CommandType.Text);            //DataSet ds = h.ExecuteDataset(sql);            GridView1.DataSource = ds;            GridView1.DataBind();                  }           }}