当前位置：Gxlcms > asp.net > 教你Asp.net下使用mysql数据库的步骤

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

时间：2021-07-01 10:21:17 帮助过：35人阅读

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

2. 安装了mysql数据库后，需要一个管理工具，就像sqlserver的SQL Server Management Studio一样，推荐使用Navicat for MySQL，
下载地址：//www.gxlcms.com/database/2223.html，安装后，打开界面如下图

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

3. Asp.net连接mysql 不推荐使用ODBC,推荐是用mysql官网提供的组件动)MySQL.Data.Dll，官网地址，
http://dev.mysql.com/downloads/connector/net/5.1.html；可能需要简单的注册下用户，看好版本，如果嫌弃麻烦可以直接下载一个相应版本的MySQL.Data.Dll ，放入bin下，增加引用即可
4. 我自己写的一个简单MysqlHelper.cs类，操作和sqlserver 很相似。有了这个类，你就知道怎么用了，很简单
代码如下
代码如下:
  1. <br>MysqlHelper类 <br>using System; <br>using System.Collections; <br>using System.Collections.Specialized; <br>using System.Data; <br>using MySql.Data.MySqlClient; <br>using System.Configuration; <br>using System.Data.Common; <br>using System.Collections.Generic; <br>using System.Text.RegularExpressions; <br>namespace LOAF.DAL <br>{ <br>public class MysqlHelper <br>{ <br>//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. <br>// public static string connectionString = ConfigurationManager.ConnectionStrings["ConnDB"].ConnectionString; <br>public static string connectionString = ConfigurationManager.AppSettings["MySQL"]; <br>//public string m = ConfigurationManager.AppSettings["MySQL"]; <br>public MysqlHelper() { } <br>#region ExecuteNonQuery <br>//执行SQL语句，返回影响的记录数 <br>/// <summary> <br>/// 执行SQL语句，返回影响的记录数 <br>/// </summary> <br>/// <param name="SQLString">SQL语句</param> <br>/// <returns>影响的记录数</returns> <br>public static int ExecuteNonQuery(string SQLString) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) <br>{ <br>try <br>{ <br>connection.Open(); <br>int rows = cmd.ExecuteNonQuery(); <br>return rows; <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>connection.Close(); <br>throw e; <br>} <br>} <br>} <br>} <br>/// <summary> <br>/// 执行SQL语句，返回影响的记录数 <br>/// </summary> <br>/// <param name="SQLString">SQL语句</param> <br>/// <returns>影响的记录数</returns> <br>public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>using (MySqlCommand cmd = new MySqlCommand()) <br>{ <br>try <br>{ <br>PrepareCommand(cmd, connection, null, SQLString, cmdParms); <br>int rows = cmd.ExecuteNonQuery(); <br>cmd.Parameters.Clear(); <br>return rows; <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>throw e; <br>} <br>} <br>} <br>} <br>//执行多条SQL语句，实现数据库事务。 <br>/// <summary> <br>/// 执行多条SQL语句，实现数据库事务。 <br>/// </summary> <br>/// <param name="SQLStringList">多条SQL语句</param> <br>public static bool ExecuteNoQueryTran(List<String> SQLStringList) <br>{ <br>using (MySqlConnection conn = new MySqlConnection(connectionString)) <br>{ <br>conn.Open(); <br>MySqlCommand cmd = new MySqlCommand(); <br>cmd.Connection = conn; <br>MySqlTransaction tx = conn.BeginTransaction(); <br>cmd.Transaction = tx; <br>try <br>{ <br>for (int n = 0; n < SQLStringList.Count; n++) <br>{ <br>string strsql = SQLStringList[n]; <br>if (strsql.Trim().Length > 1) <br>{ <br>cmd.CommandText = strsql; <br>PrepareCommand(cmd, conn, tx, strsql, null); <br>cmd.ExecuteNonQuery(); <br>} <br>} <br>cmd.ExecuteNonQuery(); <br>tx.Commit(); <br>return true; <br>} <br>catch <br>{ <br>tx.Rollback(); <br>return false; <br>} <br>} <br>} <br>#endregion <br>#region ExecuteScalar <br>/// <summary> <br>/// 执行一条计算查询结果语句，返回查询结果（object）。 <br>/// </summary> <br>/// <param name="SQLString">计算查询结果语句</param> <br>/// <returns>查询结果（object）</returns> <br>public static object ExecuteScalar(string SQLString) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) <br>{ <br>try <br>{ <br>connection.Open(); <br>object obj = cmd.ExecuteScalar(); <br>if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) <br>{ <br>return null; <br>} <br>else <br>{ <br>return obj; <br>} <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>connection.Close(); <br>throw e; <br>} <br>} <br>} <br>} <br>/// <summary> <br>/// 执行一条计算查询结果语句，返回查询结果（object）。 <br>/// </summary> <br>/// <param name="SQLString">计算查询结果语句</param> <br>/// <returns>查询结果（object）</returns> <br>public static object ExecuteScalar(string SQLString, params MySqlParameter[] cmdParms) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>using (MySqlCommand cmd = new MySqlCommand()) <br>{ <br>try <br>{ <br>PrepareCommand(cmd, connection, null, SQLString, cmdParms); <br>object obj = cmd.ExecuteScalar(); <br>cmd.Parameters.Clear(); <br>if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) <br>{ <br>return null; <br>} <br>else <br>{ <br>return obj; <br>} <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>throw e; <br>} <br>} <br>} <br>} <br>#endregion <br>#region ExecuteReader <br>/// <summary> <br>/// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) <br>/// </summary> <br>/// <param name="strSQL">查询语句</param> <br>/// <returns>MySqlDataReader</returns> <br>public static MySqlDataReader ExecuteReader(string strSQL) <br>{ <br>MySqlConnection connection = new MySqlConnection(connectionString); <br>MySqlCommand cmd = new MySqlCommand(strSQL, connection); <br>try <br>{ <br>connection.Open(); <br>MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); <br>return myReader; <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>throw e; <br>} <br>} <br>/// <summary> <br>/// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) <br>/// </summary> <br>/// <param name="strSQL">查询语句</param> <br>/// <returns>MySqlDataReader</returns> <br>public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms) <br>{ <br>MySqlConnection connection = new MySqlConnection(connectionString); <br>MySqlCommand cmd = new MySqlCommand(); <br>try <br>{ <br>PrepareCommand(cmd, connection, null, SQLString, cmdParms); <br>MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); <br>cmd.Parameters.Clear(); <br>return myReader; <br>} <br>catch (MySql.Data.MySqlClient.MySqlException e) <br>{ <br>throw e; <br>} <br>// finally <br>// { <br>// cmd.Dispose(); <br>// connection.Close(); <br>// } <br>} <br>#endregion <br>#region ExecuteDataTable <br>/// <summary> <br>/// 执行查询语句，返回DataTable <br>/// </summary> <br>/// <param name="SQLString">查询语句</param> <br>/// <returns>DataTable</returns> <br>public static DataTable ExecuteDataTable(string SQLString) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>DataSet ds = new DataSet(); <br>try <br>{ <br>connection.Open(); <br>MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); <br>command.Fill(ds, "ds"); <br>} <br>catch (MySql.Data.MySqlClient.MySqlException ex) <br>{ <br>throw new Exception(ex.Message); <br>} <br>return ds.Tables[0]; <br>} <br>} <br>/// <summary> <br>/// 执行查询语句，返回DataSet <br>/// </summary> <br>/// <param name="SQLString">查询语句</param> <br>/// <returns>DataTable</returns> <br>public static DataTable ExecuteDataTable(string SQLString, params MySqlParameter[] cmdParms) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>MySqlCommand cmd = new MySqlCommand(); <br>PrepareCommand(cmd, connection, null, SQLString, cmdParms); <br>using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) <br>{ <br>DataSet ds = new DataSet(); <br>try <br>{ <br>da.Fill(ds, "ds"); <br>cmd.Parameters.Clear(); <br>} <br>catch (MySql.Data.MySqlClient.MySqlException ex) <br>{ <br>throw new Exception(ex.Message); <br>} <br>return ds.Tables[0]; <br>} <br>} <br>} <br>//获取起始页码和结束页码 <br>public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord) <br>{ <br>using (MySqlConnection connection = new MySqlConnection(connectionString)) <br>{ <br>DataSet ds = new DataSet(); <br>try <br>{ <br>connection.Open(); <br>MySqlDataAdapter command = new MySqlDataAdapter(cmdText, connection); <br>command.Fill(ds, startResord, maxRecord, "ds"); <br>} <br>catch (MySql.Data.MySqlClient.MySqlException ex) <br>{ <br>throw new Exception(ex.Message); <br>} <br>return ds.Tables[0]; <br>} <br>} <br>#endregion <br>/// <summary> <br>/// 获取分页数据 在不用存储过程情况下 <br>/// </summary> <br>/// <param name="recordCount">总记录条数</param> <br>/// <param name="selectList">选择的列逗号隔开,支持top num</param> <br>/// <param name="tableName">表名字</param> <br>/// <param name="whereStr">条件字符 必须前加 and</param> <br>/// <param name="orderExpression">排序 例如 ID</param> <br>/// <param name="pageIdex">当前索引页</param> <br>/// <param name="pageSize">每页记录数</param> <br>/// <returns></returns> <br>public static DataTable getPager(out int recordCount, string selectList, string tableName, string whereStr, string orderExpression, int pageIdex, int pageSize) <br>{ <br>int rows = 0; <br>DataTable dt = new DataTable(); <br>MatchCollection matchs = Regex.Matches(selectList, @"top\s+\d{1,}", RegexOptions.IgnoreCase);//含有top <br>string sqlStr = sqlStr = string.Format("select {0} from {1} where 1=1 {2}", selectList, tableName, whereStr); <br>if (!string.IsNullOrEmpty(orderExpression)) { sqlStr += string.Format(" Order by {0}", orderExpression); } <br>if (matchs.Count > 0) //含有top的时候 <br>{ <br>DataTable dtTemp = ExecuteDataTable(sqlStr); <br>rows = dtTemp.Rows.Count; <br>} <br>else //不含有top的时候 <br>{ <br>string sqlCount = string.Format("select count(*) from {0} where 1=1 {1} ", tableName, whereStr); <br>//获取行数 <br>object obj = ExecuteScalar(sqlCount); <br>if (obj != null) <br>{ <br>rows = Convert.ToInt32(obj); <br>} <br>} <br>dt = ExecuteDataTable(sqlStr, (pageIdex-1)*pageSize, pageSize); <br>recordCount = rows; <br>return dt; <br>} <br>#region 创建command <br>private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) <br>{ <br>if (conn.State != ConnectionState.Open) <br>conn.Open(); <br>cmd.Connection = conn; <br>cmd.CommandText = cmdText; <br>if (trans != null) <br>cmd.Transaction = trans; <br>cmd.CommandType = CommandType.Text;//cmdType; <br>if (cmdParms != null) <br>{ <br>foreach (MySqlParameter parameter in cmdParms) <br>{ <br>if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && <br>(parameter.Value == null)) <br>{ <br>parameter.Value = DBNull.Value; <br>} <br>cmd.Parameters.Add(parameter); <br>} <br>} <br>} <br>#endregion <br>} <br>} <br> <br>5. 需要注意的地方有如下几点 <br>1）在sqlserver中参数化sql语句是用“@”符号，在mysql里面需要是用？号，切记，切记. <br>2 ）还有就是sqlserver中删除表可以这样写delete news where ID=12，但是在mysql里面 是delete from news where ID=12，收了from报错 <br>3 ）我在使用过程中遇到了中文乱码，网上大部分解决办法是把表的设置成utf-8字符集。以及 C:\Program Files\MySQL\MySQL Server 5.1路径下my.ini 打开找到两处 <br>default-character-set 都设置成=utf8，但是我的还是乱码，最后 <br><img height="138" alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/201205231202445.png" width="1072" border="0" longdesc=""><br>6.以上是我的个人总结，有可能很肤浅，不要见笑，有问题共同解决，谢谢~
  2.             
  3.

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！