1. First need to install MySQL,
Cloud Habitat Community Download address: http://www.jb51.net/softs/2193.html or go to mysql.com official website can, all the way next, after installation, there is a simple configuration, prompted a set login password and service name,
Default localhost, username root, password set yourself.
2. Once you have installed the MySQL database, you need a management tool, like SQL Server Management Studio, server, which recommends the use of navicat for MySQL,
Download Address: http://www.jb51.net/database/2223.html, after installation, open the interface as shown below
OK, the right key to the new connection name, you can build the database, build the table. The field type is basically consistent with SQL Server.
3. asp.net connection MySQL does not recommend the use of ODBC, recommended by the MySQL official website to provide the components to move MySQL.Data.Dll, official website address,
Http://dev.mysql.com/downloads/connector/net/5.1.html may need a simple registration of users, optimistic version, if you do not want trouble can download a corresponding version of the MYSQL.DATA.DLL, Put in bin, add reference
4. I wrote a simple MysqlHelper.cs class, which is similar to SQL Server. With this class, you know how to use it, it's simple.
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 strings (Web.config to configure), you can dynamically change connectionstring to support multiple databases.
public static string connectionString = configurationmanager.connectionstrings["Conndb"]. ConnectionString;
public static string connectionString = configurationmanager.appsettings["MySQL"];
public string m = configurationmanager.appsettings["MySQL"];
Public Mysqlhelper () {}
#region ExecuteNonQuery
Executes an SQL statement that returns the number of records affected
<summary>
Executes an SQL statement that returns the number of records affected
</summary>
<param name= "SqlString" >sql statement </param>
Number of records affected by <returns> </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>
Executes an SQL statement that returns the number of records affected
</summary>
<param name= "SqlString" >sql statement </param>
Number of records affected by <returns> </returns>
public static int ExecuteNonQuery (string sqlstring, params mysqlparameter[] cmdparms)
{
using (mysqlconnection connection = new Mysqlconnection (connectionString))
{
using (mysqlcommand cmd = new Mysqlcommand ())
{
Try
{
PrepareCommand (cmd, connection, NULL, SqlString, cmdparms);
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>
Executes a calculated query result statement that returns the query result (object).
</summary>
<param name= "SqlString" > Compute query Result statement </param>
<returns> query Results (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>
Executes a calculated query result statement that returns the query result (object).
</summary>
<param name= "SqlString" > Compute query Result statement </param>
<returns> query Results (object) </returns>
public static Object ExecuteScalar (string sqlstring, params mysqlparameter[] cmdparms)
{
using (mysqlconnection connection = new Mysqlconnection (connectionString))
{
using (mysqlcommand cmd = new Mysqlcommand ())
{
Try
{
PrepareCommand (cmd, connection, NULL, SqlString, cmdparms);
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>
Executes the query statement and returns Mysqldatareader (note: Be sure to close the Mysqldatareader after calling this method)
</summary>
<param name= "strSQL" > Query Statements </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>
Executes the query statement and returns Mysqldatareader (note: Be sure to close the Mysqldatareader after calling this method)
</summary>
<param name= "strSQL" > Query Statements </param>
<returns>MySqlDataReader</returns>
public static Mysqldatareader ExecuteReader (string sqlstring, params mysqlparameter[] cmdparms)
{
Mysqlconnection connection = new Mysqlconnection (connectionString);
Mysqlcommand cmd = new Mysqlcommand ();
Try
{
PrepareCommand (cmd, connection, NULL, SqlString, cmdparms);
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 Query statement, return DataTable
</summary>
<param name= "SqlString" > Query Statements </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 Query statement, return dataset
</summary>
<param name= "SqlString" > Query Statements </param>
<returns>DataTable</returns>
public static DataTable executedatatable (string sqlstring, params mysqlparameter[] cmdparms)
{
using (mysqlconnection connection = new Mysqlconnection (connectionString))
{
Mysqlcommand cmd = new Mysqlcommand ();
PrepareCommand (cmd, connection, NULL, SqlString, cmdparms);
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];
}
}
}
Get start and End page numbers
public static DataTable executedatatable (string cmdtext, int startresord, int maxrecord)
{
using (mysqlconnection connection = new Mysqlconnection (connectionString))
{
DataSet ds = new DataSet ();
Try
{
Connection. Open ();
Mysqldataadapter command = new Mysqldataadapter (cmdtext, connection);
Command. Fill (ds, Startresord, Maxrecord, "DS");
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Exception (ex. message);
}
Return DS. Tables[0];
}
}
#endregion
<summary>
To get paging data without stored procedures
</summary>
<param name= "RecordCount" > Total record number </param>
<param name= "SelectList" > the selected column comma is separated to support top num</param>
<param name= "tablename" > table name </param>
<param name= "WHERESTR" > Conditional characters must be added before and</param>
<param name= "orderexpression" > Sort for example 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 Orderex pression, 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 = 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 included
{
DataTable dttemp = executedatatable (SQLSTR);
rows = DtTemp.Rows.Count;
}
else//does not contain top
{
String SqlCount = String. Format ("SELECT count (*) from {0} where 1=1 {1}", TableName, WHERESTR);
Get 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 cmdtext, MYSQLP Arameter[] cmdparms)
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
if (trans!= null)
Cmd. Transaction = trans;
Cmd.commandtype = Commandtype.text;//cmdtype;
if (cmdparms!= null)
{
foreach (mysqlparameter parameter in cmdparms)
{
if (parameter. Direction = = Parameterdirection.inputoutput | | Parameter. Direction = ParameterDirection.Input) &&
(parameter. Value = = null))
{
Parameter. Value = DBNull.Value;
}
Cmd. Parameters.Add (parameter);
}
}
}
#endregion
}
}
5. The following points need to be noted
1 the parameterized SQL statement in SQL Server is the "@" symbol, which is needed in MySQL? Number, remember, remember.
2 There is the delete table in SQL Server can write delete news where id=12, but inside MySQL is delete from news where id=12, received from error
3 I encountered in the use of Chinese garbled, most of the online solution is to set the table into the Utf-8 character set. and C:\Program files\mysql\mysql Server 5.1 path My.ini open to find two places
6. The above is my personal summary, there may be very superficial, do not laugh, there are problems to solve together, thank you ~
