教你Asp.net下使用mysql数据库的步骤

1. 首先需要安装mysql，
下载地址： 或者去mysql.com官网都可以,一路next，安装好后，有个简单配置,提示有个设置登录密码和服务名称，
默认localhost，用户名root，密码自己设置。

2. 安装了mysql数据库后，需要一个管理工具，就像sqlserver的sql server management studio一样，推荐使用navicat for mysql，
下载地址：，安装后，打开界面如下图

确定后，右键新建的连接名字，就可以建数据库，建表了。字段类型和sqlserver基本一致。

3. asp.net连接mysql 不推荐使用odbc,推荐是用mysql官网提供的组件动)mysql.data.dll，官网地址，
；可能需要简单的注册下用户，看好版本，如果嫌弃麻烦可以直接下载一个相应版本的mysql.data.dll ，放入bin下，增加引用即可
4. 我自己写的一个简单mysqlhelper.cs类，操作和sqlserver 很相似。有了这个类，你就知道怎么用了，很简单
代码如下

mysqlhelper类
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
{
//数据库连接字符串(web.config来配置)，可以动态更改connectionstring支持多数据库.
// 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
//执行sql语句，返回影响的记录数
/// <summary>
/// 执行sql语句，返回影响的记录数
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <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>
/// 执行sql语句，返回影响的记录数
/// </summary>
/// <param name="sqlstring">sql语句</param>
/// <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;
}
}
}
}
//执行多条sql语句，实现数据库事务。
/// <summary>
/// 执行多条sql语句，实现数据库事务。
/// </summary>
/// <param name="sqlstringlist">多条sql语句</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>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="sqlstring">计算查询结果语句</param>
/// <returns>查询结果（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>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="sqlstring">计算查询结果语句</param>
/// <returns>查询结果（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>
/// 执行查询语句，返回mysqldatareader ( 注意：调用该方法后，一定要对mysqldatareader进行close )
/// </summary>
/// <param name="strsql">查询语句</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>
/// 执行查询语句，返回mysqldatareader ( 注意：调用该方法后，一定要对mysqldatareader进行close )
/// </summary>
/// <param name="strsql">查询语句</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>
/// 执行查询语句，返回datatable
/// </summary>
/// <param name="sqlstring">查询语句</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>
/// 执行查询语句，返回dataset
/// </summary>
/// <param name="sqlstring">查询语句</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];
}
}
}
//获取起始页码和结束页码
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>
/// 获取分页数据 在不用存储过程情况下
/// </summary>
/// <param name="recordcount">总记录条数</param>
/// <param name="selectlist">选择的列逗号隔开,支持top num</param>
/// <param name="tablename">表名字</param>
/// <param name="wherestr">条件字符 必须前加 and</param>
/// <param name="orderexpression">排序 例如 id</param>
/// <param name="pageidex">当前索引页</param>
/// <param name="pagesize">每页记录数</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);//含有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) //含有top的时候
{
datatable dttemp = executedatatable(sqlstr);
rows = dttemp.rows.count;
}
else //不含有top的时候
{
string sqlcount = string.format("select count(*) from {0} where 1=1 {1} ", tablename, wherestr);
//获取行数
object obj = executescalar(sqlcount);
if (obj != null)
{
rows = convert.toint32(obj);
}
}
dt = executedatatable(sqlstr, (pageidex-1)*pagesize, pagesize);
recordcount = rows;
return dt;
}
#region 创建command
private static void preparecommand(mysqlcommand cmd, mysqlconnection conn, mysqltransaction trans, string cmdtext, mysqlparameter[] 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. 需要注意的地方有如下几点
1）在sqlserver中参数化sql语句是用“@”符号，在mysql里面需要是用？号，切记，切记.
2 ）还有就是sqlserver中删除表可以这样写delete news where id=12，但是在mysql里面 是delete from news where id=12，收了from报错
3 ）我在使用过程中遇到了中文乱码，网上大部分解决办法是把表的设置成utf-8字符集。以及 c:\program files\mysql\mysql server 5.1路径下my.ini 打开找到两处
default-character-set 都设置成=utf8，但是我的还是乱码，最后

6.以上是我的个人总结，有可能很肤浅，不要见笑，有问题共同解决，谢谢~