C#工具:Ado.Net SqlServer数据库 MySql数据库
数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
SqlServer调用数据库
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace Dal { public static class DBHelper { public static string strCon = "Data Source=.;Initial Catalog=Week3;Integrated Security=True"; /// <summary> /// 增删改 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static int ExecuteNonQuery(string sql) { //实例化连接对象 SqlConnection conn = new SqlConnection(strCon); int result = 0; try { conn.Open(); //实例化命令对象 SqlCommand cmd = new SqlCommand(sql, conn); //执行命令 result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return result; } /// <summary> /// 获取表格 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static DataTable GetDataTable(string sql) { //实例化连接对象 SqlConnection conn = new SqlConnection(strCon); DataTable dt = new DataTable(); try { //实例化适配器 SqlDataAdapter sda = new SqlDataAdapter(sql, conn); sda.Fill(dt); } catch (Exception) { throw; } return dt; } /// <summary> /// /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static SqlDataReader GetDataReader(string sql) { //实例化连接对象 SqlConnection conn = new SqlConnection(strCon); SqlDataReader sdr; try { conn.Open(); //实例化命令对象 SqlCommand cmd = new SqlCommand(sql, conn); sdr = cmd.ExecuteReader(); } catch (Exception) { throw; } return sdr; } /// <summary> /// 返回单行单列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="str">连接字符串</param> /// <returns></returns> public static int ExecuteScalar(string sql) { //实例化连接对象 SqlConnection conn = new SqlConnection(strCon); int result = 0; try { conn.Open(); //实例化命令对象 SqlCommand cmd = new SqlCommand(sql, conn); result = Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception) { throw; } finally { conn.Close(); } return result; } /// <summary> /// 信息分页显示 /// </summary> /// <returns></returns> public static DataTable GetInfoByPage(int Pageindex, int PageSize) { string sql = string.Format(@"select top {0}* from( select XinXi.*,ROW_NUMBER() over(order by XinXi.XinXiId) pid ,Type.Name from Type inner join XinXi on Type.Id= XinXi.TypeId ) as temp where temp.pid>{1}", PageSize, (Pageindex - 1) * PageSize); return DBHelper.GetDataTable(sql); } /// <summary> /// 获取个数 /// </summary> /// <returns></returns> public static int GetCount() { string sql = "select count(XinXi.XinXiId) from Type inner join XinXi on Type.Id= XinXi.TypeId"; return DBHelper.ExecuteScalar(sql); } } }
SqlServer调用数据库存储过程
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; namespace Unit07_Service { public class DBHelper { public static string connStr = "Data Source=.;Initial Catalog=DB_News;Integrated Security=True"; public static SqlConnection cnn = new SqlConnection(connStr); /// <summary> /// 执行增删改的操作 /// </summary> /// <param name="sql">sql命令</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql) { Open(); SqlCommand command = new SqlCommand(sql, cnn); int result = command.ExecuteNonQuery(); cnn.Close(); return result; } /// <summary> /// 查询单个值 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static object ExecuteScalar(string sql) { Open(); SqlCommand command = new SqlCommand(sql, cnn); object result = command.ExecuteScalar(); cnn.Close(); return result; } /// <summary> /// 返回数据表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataTable GetDataTable(string sql) { SqlDataAdapter adapter = new SqlDataAdapter(sql, cnn); DataSet ds = new DataSet(); adapter.Fill(ds); return ds.Tables[0]; } /// <summary> /// 返回DataReader对象，使用结束后，勿忘关闭DataReader与数据库 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static SqlDataReader GetDataReader(string sql) { Open(); SqlCommand command = new SqlCommand(sql, cnn); return command.ExecuteReader(); } /// <summary> /// 打开数据库 /// </summary> public static void Open() { if (cnn.State == ConnectionState.Broken || cnn.State == ConnectionState.Open) { cnn.Close(); } cnn.Open(); } /// <summary> /// 打开数据库 /// </summary> public static void Close() { cnn.Close(); } /// <summary> /// 使用存储过程查询数据结果 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public static DataTable GetDataTable(string procName, SqlParameter[] paras = null) { Open(); SqlCommand command = new SqlCommand(procName, cnn); command.CommandType = CommandType.StoredProcedure; if (paras != null) { command.Parameters.AddRange(paras); } SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); adapter.Fill(ds); Close(); return ds.Tables[0]; } /// <summary> /// 使用存储过程执行增删改 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public static int ExecuteNonQuery(string procName, SqlParameter[] paras) { Open(); SqlCommand command = new SqlCommand(procName, cnn); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(paras); int result = command.ExecuteNonQuery(); Close(); return result; } } }
MySql调用数据库
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; namespace Student_API.Controllers { /// <summary> /// 数据访问抽象基础类 /// Copyright (C) 2004-2008 By LiTianPing /// </summary> public abstract class DbHelperMySQL { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = "server=127.0.0.1;user id=root;pwd=root;database=06a_exam"; public DbHelperMySQL() { } #region 公用方法 /// <summary> /// 得到最大值 /// </summary> /// <param name="FieldName"></param> /// <param name="TableName"></param> /// <returns></returns> public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } /// <summary> /// 是否存在 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public static bool Exists(string strSql) { object obj = GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 是否存在（基于MySqlParameter） /// </summary> /// <param name="strSql"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static bool Exists(string strSql, params MySqlParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(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; } } } } public static int ExecuteSqlByTime(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(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 { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object ExecuteSqlGet(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); 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) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(strSQL, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(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; } } } } public static object GetSingle(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; 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> /// 执行查询语句，返回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> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(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; } } public static DataSet Query(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(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; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(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; } } } } /// <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(); // } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(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; } } } 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 } }
MySql调用数据库存储过程
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; namespace Student_Client { /// <summary> /// 数据访问抽象基础类 /// Copyright (C) 2004-2008 By LiTianPing /// </summary> public abstract class DbHelperMySQL { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = "server=127.0.0.1;user id=root;pwd=root;database=06a_exam"; public DbHelperMySQL() { } #region 公用方法 /// <summary> /// 得到最大值 /// </summary> /// <param name="FieldName"></param> /// <param name="TableName"></param> /// <returns></returns> public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } /// <summary> /// 是否存在 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public static bool Exists(string strSql) { object obj = GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 是否存在（基于MySqlParameter） /// </summary> /// <param name="strSql"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static bool Exists(string strSql, params MySqlParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(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; } } } } public static int ExecuteSqlByTime(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(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 { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object ExecuteSqlGet(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); 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) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(strSQL, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(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; } } } } public static object GetSingle(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; 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> /// 执行查询语句，返回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> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(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; } } public static DataSet Query(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(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; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(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; } } } } /// <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(); // } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(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; } } } 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 #region 执行带参数的存储过程 /// <summary> /// 执行带参数的存储过程 /// </summary> /// <param name="SQLString"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static object ExecuteProc(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); cmd.CommandType = CommandType.StoredProcedure; int rows = cmd.ExecuteNonQuery(); Console.WriteLine("返回值为 " + cmdParms[1].Value); cmd.Parameters.Clear(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 存储过程查询DataTable /// </summary> /// <param name="SQLString"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static DataTable QueryProc(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); cmd.CommandType = CommandType.StoredProcedure; MySqlDataAdapter adapter = new MySqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); cmd.Parameters.Clear(); return dt; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } #endregion } }
