How to use mysql database in Asp.net

1. Install mysql first,
Feet House: http://www.jb51.net/softs/2193.html or go to mysql.com official website can, one way next, after installation, there is a simple configuration, prompt to have a set login password and service name,
The default value is localhost, the username is root, and the password is set by yourself.

2. After the mysql database is installed, a Management tool is required. Like SQL Server Management Studio of sqlserver, Navicat for MySQL is recommended,
: Http://www.jb51.net/database/2223.html. After installation, open the interface as shown in Figure

Right-click the new connection name to create a database and a table. The field type is basically the same as that of sqlserver.

3. ODBC is not recommended for connecting to mysql through Asp.net. We recommend using components provided by the mysql official website.) MySQL. Data. Dll, official website address,
Add references to the bin.
4. I wrote a simple MysqlHelper. cs class, which has similar operations with sqlserver. With this class, you will know how to use it. It is very simple.
The Code is as follows:Copy codeThe Code is as follows: MysqlHelper class
Using System;
Using System. Collections;
Using System. Collections. Specialized;
Using System. Data;
Using MySql. Data. MySqlClient;
Using System. Configuration;
Using System. Data. Common;
Using System. Collections. Generic;
Using System. Text. RegularExpressions;
Namespace LOAF. DAL
{
Public class MysqlHelper
{
// Database connection string (configured in web. config). You can dynamically change connectionString to support multiple databases.
// Public static string connectionString = ConfigurationManager. ConnectionStrings ["ConnDB"]. ConnectionString;
Public static string connectionString = ConfigurationManager. etettings ["MySQL"];
// Public string m = ConfigurationManager. deleettings ["MySQL"];
Public MysqlHelper (){}
# Region ExecuteNonQuery
// Execute the SQL statement and return the number of affected records
/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteNonQuery (string SQLString)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
Using (MySqlCommand cmd = new MySqlCommand (SQLString, connection ))
{
Try
{
Connection. Open ();
Int rows = cmd. ExecuteNonQuery ();
Return rows;
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Connection. Close ();
Throw e;
}
}
}
}
/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteNonQuery (string SQLString, params MySqlParameter [] extends parms)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
Using (MySqlCommand cmd = new MySqlCommand ())
{
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Int rows = cmd. ExecuteNonQuery ();
Cmd. Parameters. Clear ();
Return rows;
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Throw e;
}
}
}
}
// Execute multiple SQL statements to implement database transactions.
/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
Public static bool ExecuteNoQueryTran (List <String> SQLStringList)
{
Using (MySqlConnection conn = new MySqlConnection (connectionString ))
{
Conn. Open ();
MySqlCommand cmd = new MySqlCommand ();
Cmd. Connection = conn;
MySqlTransaction tx = conn. BeginTransaction ();
Cmd. Transaction = tx;
Try
{
For (int n = 0; n <SQLStringList. Count; n ++)
{
String strsql = SQLStringList [n];
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
PrepareCommand (cmd, conn, tx, strsql, null );
Cmd. ExecuteNonQuery ();
}
}
Cmd. ExecuteNonQuery ();
Tx. Commit ();
Return true;
}
Catch
{
Tx. Rollback ();
Return false;
}
}
}
# Endregion
# Region ExecuteScalar
/// <Summary>
/// Execute a query result statement and return the query result (object ).
/// </Summary>
/// <Param name = "SQLString"> calculate the Query Result Statement </param>
/// <Returns> query result (object) </returns>
Public static object ExecuteScalar (string SQLString)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
Using (MySqlCommand cmd = new MySqlCommand (SQLString, connection ))
{
Try
{
Connection. Open ();
Object obj = cmd. ExecuteScalar ();
If (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
{
Return null;
}
Else
{
Return obj;
}
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Connection. Close ();
Throw e;
}
}
}
}
/// <Summary>
/// Execute a query result statement and return the query result (object ).
/// </Summary>
/// <Param name = "SQLString"> calculate the Query Result Statement </param>
/// <Returns> query result (object) </returns>
Public static object ExecuteScalar (string SQLString, params MySqlParameter [] extends parms)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
Using (MySqlCommand cmd = new MySqlCommand ())
{
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Object obj = cmd. ExecuteScalar ();
Cmd. Parameters. Clear ();
If (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
{
Return null;
}
Else
{
Return obj;
}
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Throw e;
}
}
}
}
# Endregion
# Region ExecuteReader
/// <Summary>
/// Execute the query statement and return MySqlDataReader (Note: after calling this method, you must Close MySqlDataReader)
/// </Summary>
/// <Param name = "strSQL"> query statement </param>
/// <Returns> MySqlDataReader </returns>
Public static MySqlDataReader ExecuteReader (string strSQL)
{
MySqlConnection connection = new MySqlConnection (connectionString );
MySqlCommand cmd = new MySqlCommand (strSQL, connection );
Try
{
Connection. Open ();
MySqlDataReader myReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Return myReader;
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Throw e;
}
}
/// <Summary>
/// Execute the query statement and return MySqlDataReader (Note: after calling this method, you must Close MySqlDataReader)
/// </Summary>
/// <Param name = "strSQL"> query statement </param>
/// <Returns> MySqlDataReader </returns>
Public static MySqlDataReader ExecuteReader (string SQLString, params MySqlParameter [] extends parms)
{
MySqlConnection connection = new MySqlConnection (connectionString );
MySqlCommand cmd = new MySqlCommand ();
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
MySqlDataReader myReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Return myReader;
}
Catch (MySql. Data. MySqlClient. MySqlException e)
{
Throw e;
}
// Finally
//{
// Cmd. Dispose ();
// Connection. Close ();
//}
}
# Endregion
# Region ExecuteDataTable
/// <Summary>
/// Execute the query statement and return the DataTable
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Returns> DataTable </returns>
Public static DataTable ExecuteDataTable (string SQLString)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
DataSet ds = new DataSet ();
Try
{
Connection. Open ();
MySqlDataAdapter command = new MySqlDataAdapter (SQLString, connection );
Command. Fill (ds, "ds ");
}
Catch (MySql. Data. MySqlClient. MySqlException ex)
{
Throw new Exception (ex. Message );
}
Return ds. Tables [0];
}
}
/// <Summary>
/// Execute the query statement and return DataSet
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Returns> DataTable </returns>
Public static DataTable ExecuteDataTable (string SQLString, params MySqlParameter [] extends parms)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
MySqlCommand cmd = new MySqlCommand ();
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Using (MySqlDataAdapter da = new MySqlDataAdapter (cmd ))
{
DataSet ds = new DataSet ();
Try
{
Da. Fill (ds, "ds ");
Cmd. Parameters. Clear ();
}
Catch (MySql. Data. MySqlClient. MySqlException ex)
{
Throw new Exception (ex. Message );
}
Return ds. Tables [0];
}
}
}
// Obtain the start and end pages
Public static DataTable ExecuteDataTable (string plain text, int startResord, int maxRecord)
{
Using (MySqlConnection connection = new MySqlConnection (connectionString ))
{
DataSet ds = new DataSet ();
Try
{
Connection. Open ();
MySqlDataAdapter command = new MySqlDataAdapter (plain text, connection );
Command. Fill (ds, startResord, maxRecord, "ds ");
}
Catch (MySql. Data. MySqlClient. MySqlException ex)
{
Throw new Exception (ex. Message );
}
Return ds. Tables [0];
}
}
# Endregion
/// <Summary>
/// Retrieve the paging data without using the Stored Procedure
/// </Summary>
/// <Param name = "recordCount"> total number of records </param>
/// <Param name = "selectList"> the selected columns are separated by commas (,). top num is supported. </param>
/// <Param name = "tableName"> table name </param>
/// <Param name = "whereStr"> the condition character must be prefixed with and. </param>
/// <Param name = "orderExpression"> sort by ID </param>
/// <Param name = "pageIdex"> current index page </param>
/// <Param name = "pageSize"> Number of records per page </param>
/// <Returns> </returns>
Public static DataTable getPager (out int recordCount, string selectList, string tableName, string whereStr, string orderExpression, int pageIdex, int pageSize)
{
Int rows = 0;
DataTable dt = new DataTable ();
MatchCollection matchs = Regex. Matches (selectList, @ "top \ s + \ d {1,}", RegexOptions. IgnoreCase); // contains top
String sqlStr = string. Format ("select {0} from {1} where 1 = 1 {2}", selectList, tableName, whereStr );
If (! String. IsNullOrEmpty (orderExpression) {sqlStr + = string. Format ("Order by {0}", orderExpression );}
If (matchs. Count> 0) // when top is contained
{
DataTable dtTemp = ExecuteDataTable (sqlStr );
Rows = dtTemp. Rows. Count;
}
Else // when top is not included
{
String sqlCount = string. Format ("select count (*) from {0} where 1 = 1 {1}", tableName, whereStr );
// Obtain the number of rows
Object obj = ExecuteScalar (sqlCount );
If (obj! = Null)
{
Rows = Convert. ToInt32 (obj );
}
}
Dt = ExecuteDataTable (sqlStr, (pageIdex-1) * pageSize, pageSize );
RecordCount = rows;
Return dt;
}
# Region create command
Private static void PrepareCommand (MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string plain text, MySqlParameter [] partial parms)
{
If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd. CommandText = plain text;
If (trans! = Null)
Cmd. Transaction = trans;
Cmd. CommandType = CommandType. Text; // specify type;
If (partition parms! = Null)
{
Foreach (MySqlParameter parameter in parameter parms)
{
If (parameter. Direction = ParameterDirection. InputOutput | parameter. Direction = ParameterDirection. Input )&&
(Parameter. Value = null ))
{
Parameter. Value = DBNull. Value;
}
Cmd. Parameters. Add (parameter );
}
}
}
# Endregion
}
}

5. Note the following points:
1) in SQL Server, the parameterized SQL statement uses the "@" symbol, which is required in mysql? Number, remember, remember.
2) delete a table in sqlserver. You can write delete news where ID = 12 in this way, but delete from news where ID = 12 in mysql. If the from statement is received, an error is returned.
3) I encountered Chinese garbled characters during usage. Most of the solutions on the Internet are to set the table to the UTF-8 character set. And C: \ Program Files \ MySQL Server 5.1 path.
Default-character-set is set to = utf8, but I still garbled, and finally

6. The above is my personal summary. It may be superficial. Don't laugh. If you have any problems to solve together, thank you ~