C#连接mysql数据库学习
来源：互联网 发布：淘宝差评可以删除吗 编辑：程序博客网 时间：2024/04/27 23:32
<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">由于数据库设计完毕之后，既要支持asp.net，又要支持php，所有最终选择了mysql数据库，这样通用性更广泛一些。</span>
C#连接mysql数据库需要用到一个mysql数据库的连接驱动，两种方式可以安装。
1. mysql官网下载
下载地址为：http://dev.mysql.com/downloads/connector/net/
可以现在有mono版本或者是windows版本，直接下载安装即可，安装完毕，可以直接在项目--引用--添加引用--mysql.data.dll
2. 还有个更简单的办法，直接通过nugut直接安装
3. 安装完了，直接引用，并调用即可。
4. web.config配置数据库连接字符串
<connectionStrings> <add name="connstring" connectionString="User Id=root;host=localhost;Database=cnseb_spider;password=;port=3306;persist security info=True;character set=utf8;" providerName="MySql.Data.MySqlClient"/> </connectionStrings>
#region 数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString; #endregion #region executeNonQuery /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="sqlstr">SQL语句</param> /// <returns>影响的记录数</returns> public static int executeNonQuery(string sqlstr) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(sqlstr, 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="sqlstr">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns>影响的记录数</returns> public static int executeNonQuery(string sqlstr, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { prepareCommand(cmd, connection, null, sqlstr, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="sqlstrlist">多条SQL语句</param> public static bool executeNoQueryTran(List<String> sqlstrlist) { 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 < sqlstrlist.Count; n++) { string strsql = sqlstrlist[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="sqlstr">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object executeScalar(string sqlstr) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(sqlstr, 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="sqlstr">计算查询结果语句</param> /// <param name="cmdParms">参数</param> /// <returns>查询结果（object）</returns> public static object executeScalar(string sqlstr, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { prepareCommand(cmd, connection, null, sqlstr, 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="sqlstr">查询语句</param> /// <returns>MySqlDataReader</returns> public static MySqlDataReader executeReader(string sqlstr) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand cmd = new MySqlCommand(sqlstr, 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="sqlstr">查询语句</param> /// <param name="cmdParms">参数</param> /// <returns>MySqlDataReader</returns> public static MySqlDataReader executeReader(string sqlstr, params MySqlParameter[] cmdParms) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand cmd = new MySqlCommand(); try { prepareCommand(cmd, connection, null, sqlstr, cmdParms); MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } #endregion #region ExecuteDataTable /// <summary> /// 执行查询语句，返回DataTable /// </summary> /// <param name="sqlstr">查询语句</param> /// <returns>DataTable</returns> public static DataTable executeDataTable(string sqlstr) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(sqlstr, connection); command.Fill(ds, "ds"); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds.Tables[0]; } } /// <summary> /// 执行查询语句，返回DataTable /// </summary> /// <param name="sqlstr">查询语句</param> /// <param name="cmdParms">参数</param> /// <returns>DataTable</returns> public static DataTable executeDataTable(string sqlstr, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(); prepareCommand(cmd, connection, null, sqlstr, 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]; } } } /// <summary> /// 执行查询语句，返回DataTable,获取起始页码和结束页码之间的数据 /// </summary> /// <param name="sqlstr">sql语句</param> /// <param name="startResord">起始数据</param> /// <param name="maxRecord">终止数据</param> /// <returns></returns> public static DataTable executeDataTable(string sqlstr, int startResord, int maxRecord) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(sqlstr, 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 /// <summary> /// 创建command /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> 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); } } } #endregion6. 测试调用
个人总结：其实mysql很方便的，体积小，又灵活。
1 0
- C#连接mysql数据库学习
- 学习：C#连接MySQL数据库
- C#连接mysql数据库
- C#连接mysql 数据库
- C# 连接MySQL数据库
- C#连接mysql数据库
- C#连接MySql数据库
- C#连接MySql数据库
- C#连接mysql数据库
- C#连接Mysql数据库
- c#连接mysql数据库
- c#连接mysql数据库
- C#连接mysql数据库
- C#连接Mysql数据库
- c# 连接mysql数据库
- c#连接mysql数据库
- C# 连接 mysql 数据库
- C#连接mysql数据库
- transient关键字的作用
- Web前端开发-5
- typedef和#define的作用范围
- OSCache操作详解+标签使用
- background-size和background-position用法
- C#连接mysql数据库学习
- ios之触摸事件
- 日本JATE认证
- 想成为 VR 开发者？不妨看看这位“女学霸”的经历
- JQuery选择器
- Android 编译resource.arsc资源Resource ID解析
- ajax，SEO，谁言鱼和熊掌不可兼得
- Window中常用的属性
- ubuntu14 64位安装LAMP环境
