Dapper 入门案例：Demo下载点我(密码：2020)
Dapper 一款轻量级的ORM和ADO结合的灵活化框架，作为一名用过EF和ADO的开发，使用Dapper也很是简单。
开发环境：①、VS2015、②、Sql Server 2012；③、win10；（系统影响不大。）
项目简介：①、Entity（存放实体）；②、Repository（存放接口和逻辑类）；③、Utility（公用类存放处）；④、WebApi（启动项目）
其中 Entity与Repository由 自定义仿T4工具（暂时没时间放代码下载） 生成，也是些基础的增删改查方法。
一、WebApi（启动项目）：
二、 Entity（实体库）：
①、Models：存放数据库表映射的实体类。
②、ViewModels：存放数据库视图所映射的实体类；（当前项目并没有使用视图，所以自定义仿T4工具也没有添加生成视图实体类这一功能。）
③、CustomModels：存放自定义实体类；（可将新建实体类装载复杂的sql连接查询结果。）
using System; namespace Entity.Models { public class Test_User { public int id {get;set;} public string user_name {get;set;} public int age {get;set;} public string sex {get;set;} public string phone {get;set;} public string address {get;set;} public DateTime create_time {get;set;} public DateTime update_time {get;set;} } }
三、Utility（公用库）：
引用：Newtonsoft.Json（字符串转Json）、System.Data（ADO连接、操作数据库）
①、Helper ：存放DbHelper和其他帮助类。
②、Log ：日志配置。
注：类库创建按照Entity的创建方法。
Helper文件
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Utility.Helper { public enum EffentNextType { /// <summary> /// 对其他语句无任何影响 /// </summary> None, /// <summary> /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务 /// </summary> WhenHaveContine, /// <summary> /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务 /// </summary> WhenNoHaveContine, /// <summary> /// 当前语句影响到的行数必须大于0，否则回滚事务 /// </summary> ExcuteEffectRows, /// <summary> /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务 /// </summary> SolicitationEvent } public class CommandInfo { public object ShareObject = null; public object OriginalData = null; event EventHandler _solicitationEvent; public event EventHandler SolicitationEvent { add { _solicitationEvent += value; } remove { _solicitationEvent -= value; } } public void OnSolicitationEvent() { if (_solicitationEvent != null) { _solicitationEvent(this, new EventArgs()); } } public string CommandText; public System.Data.Common.DbParameter[] Parameters; public EffentNextType EffentNextType = EffentNextType.None; public CommandInfo() { } public CommandInfo(string sqlText, SqlParameter[] para) { this.CommandText = sqlText; this.Parameters = para; } public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type) { this.CommandText = sqlText; this.Parameters = para; this.EffentNextType = type; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Security.Cryptography; using System.Text; using System.Threading.Tasks; namespace Utility.Helper { public static class CommonHelper { //MD5散列 public static string GetMD5(string myString) { if (!string.IsNullOrWhiteSpace(myString)) { MD5 md5 = new MD5CryptoServiceProvider(); byte[] fromData = System.Text.Encoding.Unicode.GetBytes(myString); byte[] targetData = md5.ComputeHash(fromData); string byte2String = null; for (int i = 0; i < targetData.Length; i++) { byte2String += targetData[i].ToString("x"); } return byte2String; } return ""; } } }
using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace Utility.Helper { /// <summary> /// 数据访问抽象基础类 /// Copyright (C) Maticsoft /// </summary> public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现. public static readonly string connectionString = ConfigurationManager.ConnectionStrings["OzyLive"].ConnectionString; public DbHelperSQL() { } #region 公用方法 /// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tableName">表名称</param> /// <param name="columnName">列名称</param> /// <returns>是否存在</returns> public static bool ColumnExists(string tableName, string columnName) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; object res = GetSingle(sql); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } //获取排序或主键 public static int GetMaxID(string fieldName, string tableName) { int num = 0; string strsql = "select max(" + fieldName + ")+1 from " + tableName + " where IsDel=0"; object obj = GetSingle(strsql); if (fieldName == "sort") { if (obj == null) { return 0; } else { num = int.Parse(obj.ToString()); } } else { if (obj == null) { return 1; } else { num = int.Parse(obj.ToString()); } } return num; } public static int GetMaxTypeSort(string fieldName, string tableName, string strWhere) { int num = 0; string strsql = "select max(" + fieldName + ")+1 from " + tableName + " where IsDel=0 " + strWhere; object obj = DbHelperSQL.GetSingle(strsql); if (fieldName == "sort") { if (obj == null) { return 0; } else { num = int.Parse(obj.ToString()) + 1; } } else { if (obj == null) { return 1; } else { num = int.Parse(obj.ToString()); } } return num; } 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()); //也可能=0 } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 表是否存在 /// </summary> /// <param name="TableName"></param> /// <returns></returns> public static bool TabExists(string TableName) { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; 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; } } public static bool Exists(string strSql, params SqlParameter[] 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 (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行Sql和Oracle滴混合事务 /// </summary> /// <param name="list">SQL命令行列表</param> /// <param name="oracleCmdSqlList">Oracle命令行列表</param> /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo myDE in list) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, tx, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.SolicitationEvent) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (isHave) { //引发事件 myDE.OnSolicitationEvent(); } } if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); //return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); //return 0; } continue; } int val = cmd.ExecuteNonQuery(); if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); //return 0; } cmd.Parameters.Clear(); } //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); //if (!res) //{ // tx.Rollback(); // throw new Exception("Oracle执行失败"); // // return -1; //} tx.Commit(); return 1; } catch (System.Data.SqlClient.SqlException e) { tx.Rollback(); throw e; } catch (Exception e) { tx.Rollback(); throw e; } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(List<String> SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction 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 (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException 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 (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@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 (System.Data.SqlClient.SqlException 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 (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException 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 (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(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 (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } public static object GetSingle(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(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 (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, connection); try { connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw e; } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } public static DataSet Query(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException 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 SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } public static int ExecuteSqlList(string SQLString, List<SqlParameter[]> cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommandList(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static bool ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); return false; } } } return true; } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int count = 0; //循环 foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return 0; } continue; } int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return 0; } cmd.Parameters.Clear(); } trans.Commit(); return count; } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (CommandInfo myDE in SQLStringList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter 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 SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { 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 (System.Data.SqlClient.SqlException e) { throw e; } } } } /// <summary> /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (System.Data.SqlClient.SqlException 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 SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) { try { if (conn.State != ConnectionState.Open) conn.Open(); } catch { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } private static void PrepareCommandList(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, List<SqlParameter[]> cmdParms) { try { if (conn.State != ConnectionState.Open) conn.Open(); } catch { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms.Count != 0) { foreach (var item in cmdParms) { if (item != null) { foreach (SqlParameter parameter in item) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } } } #endregion #region 存储过程操作 /// <summary> /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); return returnReader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); try { sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); } catch (Exception e) { throw e; } connection.Close(); return dataSet; } } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.CommandTimeout = Times; sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; } /// <summary> /// 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// <summary> /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand 对象实例</returns> private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } #endregion #region >>删除<< /// <summary> /// 执行删除 /// </summary> /// <param name="table">表名</param> /// <param name="keyValue">主键值</param> /// <param name="keyName">主键字段名称</param> /// <returns>删除结果</returns> public static int Delete(string table, string keyValue, string keyName = "Id") { string sql = string.Format("DELETE FROM {0} WHERE {1}=@Id", table, keyName); var args = new ArrayList() { new SqlParameter("@Id", keyValue) }; return ExecuteSql(sql, args); } /// <summary> /// 根据查询条件删除 /// </summary> /// <param name="table"></param> /// <param name="htCondition"></param> /// <returns></returns> public static int Delete(string table, Hashtable htCondition) { ArrayList args; string conditionStr = GetConditionString(htCondition, out args); string sql = string.Format("DELETE FROM {0} WHERE 1=1{1}", table, conditionStr); return ExecuteSql(sql, args); } #endregion #region >>新增<< /// <summary> /// 执行插入 /// </summary> /// <param name="table">表名</param> /// <param name="htFilds">字段和值</param> /// <returns>结果</returns> public static int Insert(string table, Hashtable htField) { ArrayList args; string sql = GetInsertSql(table, htField, out args); return ExecuteSql(sql, args); } /// <summary> /// 执行插入，返回ID /// </summary> /// <param name="table">表名</param> /// <param name="htField">字段和值</param> /// <param name="keyValue">ID</param> public static void Insert(string table, Hashtable htField, out string keyValue) { ArrayList args; string sql = string.Format("{0};SELECT @@IDENTITY", GetInsertSql(table, htField, out args)); keyValue = Convert.ToString(ExecuteScalar(sql, args)); } /// <summary> /// 使用DataTable插入数据 /// </summary> /// <param name="dt"></param> /// <param name="table"></param> /// <returns></returns> public static int Insert(DataTable dt, string table = "") { if (table == "") { table = dt.TableName; } int r = 0; foreach (DataRow dr in dt.Rows) { Hashtable htField = new Hashtable(); foreach (DataColumn dc in dt.Columns) { htField.Add(dc.ColumnName, dr[dc.ColumnName]); } r += Insert(table, htField); } return r; } /// <summary> /// 使用DataTable插入数据，只适合插入一条 /// </summary> /// <param name="dt"></param> /// <param name="keyValue">返回主键</param> /// <param name="table"></param> public static void Insert(DataTable dt, out string keyValue, string table = "") { keyValue = ""; if (table == "") { table = dt.TableName; } foreach (DataRow dr in dt.Rows) { Hashtable htField = new Hashtable(); foreach (DataColumn dc in dt.Columns) { htField.Add(dc.ColumnName, dr[dc.ColumnName]); } Insert(table, htField, out keyValue); } } /// <summary> /// 获取插入语句Sql /// </summary> /// <param name="table"></param> /// <param name="htField"></param> /// <param name="args"></param> /// <returns></returns> private static string GetInsertSql(string table, Hashtable htField, out ArrayList args) { args = new ArrayList(); var sbField = new StringBuilder(); var sbValue = new StringBuilder(); foreach (DictionaryEntry de in htField) { // 参数集合 args.Add(new SqlParameter(de.Key.ToString(), de.Value)); // 字段和值 sbField.AppendFormat("[{0}],", de.Key.ToString()); sbValue.AppendFormat("@{0},", de.Key.ToString()); } return string.Format ( "INSERT INTO {0}({1})VALUES({2});", table, sbField.ToString().TrimEnd(','), sbValue.ToString().TrimEnd(',') ); } #endregion #region >>更新<< /// <summary> /// 执行更新 /// </summary> /// <param name="table">表名</param> /// <param name="htFilds">字段和值</param> /// <param name="keyValue">主键值</param> /// <param name="keyName">主键字段名称</param> /// <returns>结果</returns> public static int Update(string table, Hashtable htField, string keyValue, string keyName = "Id") { var args = new ArrayList(); var sbField = new StringBuilder(); foreach (DictionaryEntry de in htField) { // 参数集合 args.Add(new SqlParameter(de.Key.ToString(), de.Value)); // 要更新的字段 sbField.AppendFormat("{0}=@{0},", de.Key); } // 添加ID args.Add(new SqlParameter(keyName, keyValue)); string sql = string.Format("UPDATE {0} SET {1} WHERE {2}=@{2} ", table, sbField.ToString().TrimEnd(','), keyName); return ExecuteSql(sql, args); } /// <summary> /// 执行更新 /// </summary> /// <param name="table">表名</param> /// <param name="htField">字段和值</param> /// <param name="htCondition">查询条件</param> /// <returns></returns> public static int Update(string table, Hashtable htField, Hashtable htCondition) { // 更新字段和值 ArrayList args; var sbField = new StringBuilder(); // 查询字段和值 string condition = GetConditionString(htCondition, out args); foreach (DictionaryEntry de in htField) { // 参数集合 args.Add(new SqlParameter("@AutoKey_" + de.Key, de.Value)); // 要更新的字段 sbField.AppendFormat("{0}=@AutoKey_{0},", de.Key); } // 拼接查询结果 string sql = string.Format ( "UPDATE {0} SET {1} WHERE 1=1{2}", table, sbField.ToString().TrimEnd(','), condition ); return ExecuteSql(sql, args); } /// <summary> /// 执行更新 /// </summary> /// <param name="table">表名</param> /// <param name="htField">字段和值</param> /// <param name="htCondition">查询条件</param> /// <returns></returns> public static int Update(string table, Hashtable htField, string condition) { // 更新字段和值 ArrayList args = new ArrayList(); var sbField = new StringBuilder(); // 查询字段和值 //string condition = GetConditionString(htCondition, out args); foreach (DictionaryEntry de in htField) { // 参数集合 args.Add(new SqlParameter(de.Key.ToString(), de.Value)); // 要更新的字段 sbField.AppendFormat("{0}=@{0},", de.Key); } // 拼接查询结果 string sql = string.Format ( "UPDATE {0} SET {1} WHERE 1=1 AND {2}", table, sbField.ToString().TrimEnd(','), condition ); return ExecuteSql(sql, args); } public static int Update(DataTable dt, string keyName, string keyValue, string table = "") { int num = 0; if (table == "") { table = dt.TableName; } string condition = keyName + "='" + keyValue + "'"; foreach (DataRow dr in dt.Rows) { Hashtable htField = new Hashtable(); foreach (DataColumn dc in dt.Columns) { htField.Add(dc.ColumnName, dr[dc.ColumnName]); } num += Update(table, htField, condition); } return num; } #endregion #region >>获取单个字段的值<< /// <summary> /// 获取单个字段值 /// </summary> /// <param name="tableName">表名</param> /// <param name="field">字段名</param> /// <param name="keyValue">主键值</param> /// <param name="keyName">主键字段名称</param> /// <returns></returns> public static string GetFieldValue(string table, string field, string keyValue, string keyName = "Id") { // 执行查询 var args = new ArrayList { new SqlParameter(keyName, keyValue) }; var sql = string.Format("SELECT {0} FROM {1} WHERE {2}=@{2}", field, table, keyName); // 执行查询 var result = ExecuteScalar(sql, args); // 没有查到任何结果 if (result == null) return null; // 如果结果中包含多表连接查询，去除(.)，如(a.Name)去除(a.) return result.ToString(); } /// <summary> /// /// </summary> /// <param name="table"></param> /// <param name="field"></param> /// <param name="htCondition"></param> /// <param name="order"></param> /// <returns></returns> public static string GetFieldValue(string table, string field, Hashtable htCondition, string order) { // 查询条件和参数 ArrayList args; var sql = string.Format("SELECT {0} FROM {1} WHERE 1=1{2}", field, table, GetConditionString(htCondition, out args)); // 查询条件 if (!string.IsNullOrEmpty(order)) sql += " " + order; // 执行查询 object result = ExecuteScalar(sql, args); if (result == null) return null; return result.ToString(); } #endregion #region >>获取记录数量<< /// <summary> /// 获取记录数量 /// </summary> /// <param name="table">表名</param> /// <param name="htCondtion">查询条件</param> /// <returns></returns> public static int GetRecordCount(string table, Hashtable htCondition) { // 查询条件和参数 ArrayList args; string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE 1=1{1}", table, GetConditionString(htCondition, out args)); // 执行查询 return Convert.ToInt32(ExecuteScalar(sql, args)); } #endregion #region >>判断是否存在<< /// <summary> /// 判断是否存在 /// </summary> /// <param name="tableName">表名</param> /// <param name="htCondtion">查询条件</param> /// <returns></returns> public static bool Exists(string table, Hashtable htCondition) { // 查询条件和参数 ArrayList args; string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE 1=1{1}", table, GetConditionString(htCondition, out args)); // 执行查询 object result = ExecuteScalar(sql, args); // 返回结果 if (result == null) { return false; } if (result.ToString() == "0") { return false; } return true; } /// <summary> /// 判断是否存在 /// </summary> /// <param name="table">表名</param> /// <param name="keyValue">主键值</param> /// <param name="keyName">主键字段名称</param> /// <returns></returns> public static bool Exists(string table, string keyValue, string keyName = "Id") { var args = new ArrayList { new SqlParameter(keyName, keyValue) }; string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}=@{1}", table, keyName); object result = ExecuteScalar(sql, args); if (result == null) { return false; } if (result.ToString() == "0") { return false; } return true; } #endregion #region >>获取查询条件字符串<< /// <summary> /// 获取查询条件字符串 /// </summary> /// <param name="htCondition">查询条件</param> /// <param name="args">参数集合</param> /// <returns></returns> public static string GetConditionString(Hashtable htCondition, out ArrayList args) { args = new ArrayList(); var sbCondition = new StringBuilder(); // 拼接查询条件 foreach (DictionaryEntry de in htCondition) { if (de.Key.ToString().IndexOf(' ') != -1) { sbCondition.AppendFormat(" AND {0}", de.Key); if (de.Value != null && !string.IsNullOrWhiteSpace(de.Value.ToString())) { args.Add(de.Value as SqlParameter); } continue; } sbCondition.AppendFormat(" AND {0}=@{0}", de.Key); args.Add(new SqlParameter(de.Key.ToString(), de.Value)); } // 返回查询条件字符串 return sbCondition.ToString(); } #endregion #region >>多表查询<< /// <summary> /// 多表查询 - 通过ID获取 /// </summary> /// <param name="field">要查询的字段，如：* | Id,Field</param> /// <param name="table">表名</param> /// <param name="keyValue">主键值</param> /// <param name="keyName">主键字段名称</param> /// <returns></returns> public static DataTable GetDataTable(string field, string table, string keyValue, string keyName = "Id") { ArrayList args = new ArrayList() { new SqlParameter(keyName, keyValue) }; string sql = string.Format("SELECT {0} FROM {1} WHERE {2}=@{2}", field, table, keyName); return GetDataTable(sql, args); } /// <summary> /// 多表查询 - 通过传入where 字符串 条件 来查询 /// </summary> /// <param name="field">要查询的字段，如：* | Id,Field</param> /// <param name="table">表名</param> /// <param name="strWhere">查询条件 多个条件注意用and拼接</param> /// <returns></returns> public static DataTable GetDataTable(string field, string table, string strWhere) { string sql = string.Format("SELECT {0} FROM {1} {2}", field, table, strWhere); return GetDataTable(sql); } /// <summary> /// 多表查询 - 通过查询条件获取 /// </summary> /// <param name="field">查询字段，如： A.Field, B.Field</param> /// <param name="table">表名，如： TableOne as A, TableTwo as B</param> /// <param name="htCondition">查询条件</param> /// <param name="order">排序,包括字段及排序方式(如：ordernum desc),也可以用""不排序</param> /// <returns></returns> public static DataTable GetDataTable(string field, string table, Hashtable htCondition, string order) { ArrayList args; string condition = GetConditionString(htCondition, out args); string sql = string.Format("SELECT {0} FROM {1} WHERE 1=1 {2}{3}", field, table, condition, string.IsNullOrWhiteSpace(order) ? "" : " ORDER BY " + order); return GetDataTable(sql, args); } /// <summary> /// 多表分组查询 - 通过查询条件获取 /// </summary> /// <param name="field">查询字段，如： A.Field, B.Field</param> /// <param name="table">表名，如： TableOne as A, TableTwo as B</param> /// <param name="htCondition">查询条件</param> /// <param name="order">排序,包括字段及排序方式(如：ordernum desc),也可以用""不排序</param> /// <returns></returns> public static DataTable GetDataTable(string field, string table, Hashtable htCondition, string group, string order, bool IsAddNumber = false) { ArrayList args; string condition = GetConditionString(htCondition, out args); string sql = string.Format("SELECT {0} FROM {1} WHERE 1=1 {2} {3} {4}", field, table, condition, string.IsNullOrWhiteSpace(group) ? "" : " group by " + group, string.IsNullOrWhiteSpace(order) ? "" : order); if (IsAddNumber) { sql = string.Format("SELECT {0} FROM {1} WHERE 1=1 {2} {3}", field, table, condition, string.IsNullOrWhiteSpace(group) ? "" : " group by " + group); sql = string.Format("SELECT ROW_NUMBER() OVER({0}) AS '序号',mydata.* FROM ({1}) mydata {0}", order, sql); } return GetDataTable(sql, args); } /// <summary> /// 多表查询 - 分页 /// </summary> /// <param name="keyName">主键字段</param> /// <param name="field">要查询的字段，如：* | Id,Field</param> /// <param name="table">表名</param> /// <param name="htCondition">条件字符串</param> /// <param name="order">排序，包括排序字段和排序方式(如：id desc)</param> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示行数</param> /// <param name="recordCount">总行数</param> /// <param name="pageCount">总共页码数</param> /// <returns></returns> public static DataTable GetDataTable(string keyName, string field, string table, Hashtable htCondition, string order, int pageIndex, int pageSize, out int recordCount, out int pageCount) { ArrayList args; string condition = GetConditionString(htCondition, out args); return GetPageNavigateDataTable(keyName, field, table, condition, order, args, pageIndex, pageSize, out recordCount, out pageCount); } /// <summary> /// 多表查询 - 通过存储过程获取 /// </summary> /// <param name="procName">存储过程</param> /// <param name="htCondition">查询条件</param> /// <returns></returns> public static DataTable GetDataTableByProcName(string procName, Hashtable htCondition) { ArrayList args; string condition = GetConditionString(htCondition, out args); return ExecuteProcedureToDt(procName, args); } #endregion #region >>执行SQL语句<< /// <summary> /// 执行SQL语句,返回受影响的行数 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="args">参数</param> /// <returns></returns> public static int ExecuteSql(string sql, ArrayList args) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, connection)) { try { int result = 0; connection.Open(); cmd.Parameters.AddRange(args.ToArray()); result = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return result; } catch (Exception ex) { throw new Exception(ex.Message); } } } } #endregion #region >>执行查询返回单个对象<< /// <summary> /// 执行查询返回单个对象 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public static object ExecuteScalar(string sql) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, 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 (Exception ex) { throw new Exception(ex.Message); } } } } /// <summary> /// 执行查询返回单个对象 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="arg">参数</param> /// <returns></returns> public static object ExecuteScalar(string sql, ArrayList args) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, connection)) { try { connection.Open(); cmd.Parameters.AddRange(args.ToArray()); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value)) { return null; } else { return obj; } } catch (Exception ex) { throw new Exception(ex.Message); } } } } #endregion #region >>执行存储过程<< /// <summary> /// 执行存储过程,返回单个对象 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="args">参数</param> /// <returns>单个结果</returns> public static object ExecuteProcedure(string procName, ArrayList args) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(procName, connection)) { try { connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(args.ToArray()); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value)) { return null; } return obj; } catch (SqlException ex) { throw new Exception(ex.Message); } } } } /// <summary> /// 执行存储过程,返回DataTable /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="args">参数</param> /// <returns></returns> public static DataTable ExecuteProcedureToDt(string procName, ArrayList args) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(procName, connection)) { DataTable dt = new DataTable(); try { cmd.CommandType = CommandType.StoredProcedure; var adapter = new SqlDataAdapter(cmd); adapter.SelectCommand.Parameters.AddRange(args.ToArray()); adapter.Fill(dt); adapter.SelectCommand.Parameters.Clear(); return dt; } catch (SqlException ex) { throw new Exception(ex.Message); } } } } #endregion #region >>执行查询获取DataTable<< /// <summary> /// 执行SQL语句 返回DataTable /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>DataTable</returns> public static DataTable GetDataTable(string sql) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connection)) { var dt = new DataTable(); try { adapter.Fill(dt); return dt; } catch (SqlException ex) { throw new Exception(ex.Message); } } } } /// <summary> /// 执行SQL语句 返回DataTable /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="args">参数集合</param> /// <returns></returns> public static DataTable GetDataTable(string sql, ArrayList args) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connection)) { DataTable dt = new DataTable(); try { adapter.SelectCommand.Parameters.AddRange(args.ToArray()); adapter.Fill(dt); adapter.SelectCommand.Parameters.Clear(); return dt; } catch (SqlException ex) { throw new Exception(ex.Message); } } } } /// <summary> /// 多表查询 - 分页 /// </summary> /// <param name="keyName">主键字段</param> /// <param name="field">要查询的字段，如：* | Id,Field</param> /// <param name="table">表名</param> /// <param name="htCondition">条件字符串</param> /// <param name="order">排序，包括排序字段和排序方式(如：id desc)</param> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示行数</param> /// <param name="recordCount">总行数</param> /// <param name="pageCount">总共页码数</param> /// <returns></returns> public static DataTable GetPageNavigateDataTable(string keyName, string field, string table, string condition, string order, ArrayList args, int pageIndex, int pageSize, out int recordCount, out int pageCount) { // 获取记录数 string sql = string.Format("SELECT COUNT (1) FROM {0} WHERE 1=1{1}", table, condition); recordCount = (int)ExecuteScalar(sql, args); // 获取页数 pageCount = recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1; // 显示当前页的范围，下面两行上下顺序不可颠倒，如果颠倒，记录为0时会出现 负值 if (pageIndex > pageCount) pageIndex = pageCount; if (pageIndex < 1) pageIndex = 1; // 判断排序参数order是否为空 order = string.IsNullOrWhiteSpace(order) ? "ORDER BY " + keyName : order; // 执行查询 if (pageIndex == 1) { sql = string.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1{3} {4}", pageSize, field, table, condition, order); return GetDataTable(sql, args); } string innerSql = string.Format("SELECT ROW_NUMBER() OVER ({0}) AS TempRowNumber,{1} FROM {2} WHERE 1=1{3}", order, field, table, condition); int startRowNumber = (pageIndex - 1) * pageSize + 1; int endRowNumber = startRowNumber + pageSize - 1; sql = string.Format("SELECT * FROM ({0}) AS TempTable WHERE TempRowNumber BETWEEN {1} AND {2}", innerSql, startRowNumber, endRowNumber); /* 原始的排序方式 sql = "SELECT TOP " + pageSize + " " + field + " FROM " + table + " WHERE " + key + " NOT IN"; sql += "(SELECT TOP " + (pageIndex - 1) * pageSize + " " + key + " FROM " + table + " WHERE 1=1" + condition + orderNew + ")"; sql += condition + orderNew;*/ // 返回查询结果 return GetDataTable(sql, args); } /// <summary> /// 多表查询 - 分页扩展 /// </summary> /// <param name="keyName">主键字段</param> /// <param name="field">要查询的字段，如：* | Id,Field</param> /// <param name="table">表名</param> /// <param name="htCondition">条件字符串</param> /// <param name="order">排序，包括排序字段和排序方式(如：id desc)</param> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示行数</param> /// <param name="recordCount">总行数</param> /// <param name="pageCount">总共页码数</param> /// <returns></returns> public DataTable GetPageNavigateDataTable(string keyName, string field, string table, string condition, string group, string order, ArrayList args, int pageIndex, int pageSize, out int recordCount, out int pageCount) { // 获取记录数 string sql = string.Format("SELECT COUNT(1) from(select {0} FROM {1} WHERE 1=1{2} group by {3}) tmp", field, table, condition, group); recordCount = (int)ExecuteScalar(sql, args); // 获取页数 pageCount = recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1; // 显示当前页的范围，下面两行上下顺序不可颠倒，如果颠倒，记录为0时会出现 负值 if (pageIndex > pageCount) pageIndex = pageCount; if (pageIndex < 1) pageIndex = 1; //判断分组是否为空 group = string.IsNullOrWhiteSpace(group) ? " " : " Group by " + group; // 判断排序参数order是否为空 order = string.IsNullOrWhiteSpace(order) ? "ORDER BY " + keyName : order; // 执行查询 if (pageIndex == 1) { sql = string.Format("SELECT TOP {0} * from (select {1} FROM {2} WHERE 1=1{3} {4}) tmp {5}", pageSize, field, table, condition, group, order); return GetDataTable(sql, args); } string innerSql = string.Format("SELECT ROW_NUMBER() OVER ({0}) AS TempRowNumber,* from (select {1} FROM {2} WHERE 1=1{3} {4}) tmp", order, field, table, condition, group); int startRowNumber = (pageIndex - 1) * pageSize + 1; int endRowNumber = startRowNumber + pageSize - 1; sql = string.Format("SELECT * FROM ({0}) AS TempTable WHERE TempRowNumber BETWEEN {1} AND {2}", innerSql, startRowNumber, endRowNumber); /* 原始的排序方式 sql = "SELECT TOP " + pageSize + " " + field + " FROM " + table + " WHERE " + key + " NOT IN"; sql += "(SELECT TOP " + (pageIndex - 1) * pageSize + " " + key + " FROM " + table + " WHERE 1=1" + condition + orderNew + ")"; sql += condition + orderNew;*/ // 返回查询结果 return GetDataTable(sql, args); } #endregion } }
using Newtonsoft.Json; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Utility.Helper { public static class HelperConfig { /// <summary> /// object对象转string /// </summary> /// <param name="obj"></param> /// <returns></returns> public static string JsonConvertString(object obj) { return JsonConvert.SerializeObject(obj); } } }
Log(自定义)文件
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Web; namespace Utility.log { public static class WriteLogConfig { /// <summary> /// 写入自定义日志 /// </summary> /// <param name="file">文件</param> /// <param name="fileName">文件</param> /// <param name="source">来源备注</param> /// <param name="content">内容</param> public static void WriteLogInfo(string file, string fileName, string source, string content) { var filePath = AppDomain.CurrentDomain.BaseDirectory + file; if (!Directory.Exists(filePath)) Directory.CreateDirectory(filePath); //判断是否要创建txt var path = HttpContext.Current.Server.MapPath(@"/" + file + "/" + fileName + ".txt"); if (!File.Exists(path)) File.Create(path).Close(); using (StreamWriter w = File.AppendText(path)) { w.WriteLine(" 自定义日志: "); w.WriteLine("记录时间：{0}", DateTime.Now); w.WriteLine("记录来源：{0}", source); w.WriteLine("记录内容：{0}", content); w.WriteLine("________________________________________________________"); w.Flush(); w.Close(); } } } }
四、Repository（仓储库）：
引用：Entity（实例化实体装数据）、Utility（调用Helper）、Dapper
①、IRepositories ：存放声明方法的接口文件，主要用来声明 Repositories 中的方法；
②、Repositories ：存放实现方法的类文件，主要内容Sql操作数据库和业务逻辑；
注：类库创建按照Entity的创建方法，将 自定义仿T4工具 生成的Repositories 和 IRepositories 代码。
添加关联类库引用
添加Dapper插件
添加WebApi autofac 依赖注入，需添加Autofac、Autofac.WebApi2的扩展包和一个配置类。
using Autofac; using Autofac.Integration.WebApi; using System; using System.Collections.Generic; using System.Linq; using System.Reflection; using System.Web; using System.Web.Http; namespace TestDapper.App_Start { //注册依赖注入 public class AutofacConfig_WebApi { public static void Register() { // 实例化一个Autofac的创建容器 var builder = new ContainerBuilder(); // 注册Api容器需要使用HttPConfiguration对象 HttpConfiguration config = GlobalConfiguration.Configuration; SetupResolveRules(builder); //注册所有的ApiControllers builder.RegisterApiControllers(Assembly.GetExecutingAssembly()).PropertiesAutowired(); // 创建一个Autofac的容器 var container = builder.Build(); // api的控制器对象由autofac来创建 config.DependencyResolver = new AutofacWebApiDependencyResolver(container); } //设置配置规则 public static void SetupResolveRules(ContainerBuilder builder) { //依赖注入Repository程序集（实例化里面所有的类） Assembly RepositoryAssembly = Assembly.Load("Repository"); //创建所有类的instance以此类的实现接口存储 builder.RegisterTypes(RepositoryAssembly.GetTypes()).Where(a => a.Name.Contains("Repository")).AsImplementedInterfaces(); } } }
其中加了些自己的小想法。
using System; using Entity.Models; using System.Collections.Generic; namespace Repository.IRepositories { public interface ITest_UserRepository { /// <summary> /// 新增单个Test_User对象(若主键是自增，需删除@主键) /// </summary> /// <param name="model">新增的对象</param> /// <returns>Test_User对象的主键</returns> int AddModel(Test_User model); /// <summary> /// 删除单个或多个Test_User对象 /// </summary> /// <param name="where">查询需要删除的对象条件</param> /// <returns>sql操作的影响行数</returns> int DeleteModel(string where); /// <summary> /// 修改单个或多个Test_User对象 /// </summary> /// <param name="model">修改的对象</param> /// <param name="where">查询需要修改的对象条件</param> /// <returns>sql操作的影响行数</returns> int UpdateModel(Test_User model,string where); /// <summary> /// 获取单个Test_User对象 /// </summary> /// <param name="columnHead">显示列头</param> /// <param name="where">查询条件</param> /// <param name="order">排序条件</param> /// <returns>Test_User对象</returns> Test_User GetModel(string columnHead, string where, string order); /// <summary> /// 获取多个Test_User对象 /// </summary> /// <param name="columnHead">显示列头</param> /// <param name="where">查询条件</param> /// <param name="order">排序条件</param> /// <returns>List<Test_User>对象</returns> List<Test_User> GetModelList(string columnHead, string where, string order); } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Entity.Models; using Utility.Helper; using System.Data.SqlClient; using Repository.IRepositories; using Dapper; namespace Repository.Repositories { public class Test_UserRepository:ITest_UserRepository { /// <summary> /// 新增单个Test_User对象(若主键是自增，需删除@主键) /// </summary> /// <param name="model">新增的对象</param> /// <returns>Test_User对象的主键</returns> public int AddModel(Test_User model) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { var sql = new StringBuilder(); sql.Append(" insert into Test_User values(@id,@user_name,@age,@sex,@phone,@address,@create_time,@update_time) "); sql.Append(" SELECT CAST(SCOPE_IDENTITY() as int) "); return conn.QueryFirstOrDefault<int>(sql.ToString(), new { id=model.id,user_name=model.user_name,age=model.age,sex=model.sex,phone=model.phone,address=model.address,create_time=model.create_time,update_time=model.update_time }); } } /// <summary> /// 删除单个或多个Test_User对象 /// </summary> /// <param name="where">查询需要删除的对象条件</param> /// <returns>sql操作的影响行数</returns> public int DeleteModel(string where) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { var sql = new StringBuilder(); sql.Append(" delete Test_User where 1 = 1 "); if (!string.IsNullOrWhiteSpace(where)) sql.Append(where); return conn.Execute(sql.ToString()); } } /// <summary> /// 修改单个或多个Test_User对象 /// </summary> /// <param name="model">修改的对象</param> /// <param name="where">查询需要修改的对象条件</param> /// <returns>sql操作的影响行数</returns> public int UpdateModel(Test_User model,string where) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { var sql = new StringBuilder(); sql.Append(" update Test_User set id=@id,user_name=@user_name,age=@age,sex=@sex,phone=@phone,address=@address,create_time=@create_time,update_time=@update_time where 1=1 "); if (!string.IsNullOrWhiteSpace(where)) sql.Append(where); return conn.Execute(sql.ToString(),new{user_name=model.user_name,age=model.age,sex=model.sex,phone=model.phone,address=model.address,create_time=model.create_time,update_time=model.update_time}); } } /// <summary> /// 获取单个Test_User对象 /// </summary> /// <param name="columnHead">显示列头</param> /// <param name="where">查询条件</param> /// <param name="order">排序条件</param> /// <returns>Test_User对象</returns> public Test_User GetModel(string columnHead, string where, string order) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { var sql = new StringBuilder(); sql.Append(" select "); sql.Append(string.IsNullOrWhiteSpace(columnHead)?" * " : columnHead); sql.Append(" from Test_User where 1=1 "); if (!string.IsNullOrWhiteSpace(where)) sql.Append(where); sql.Append(string.IsNullOrWhiteSpace(order)?" order by id " : order); return conn.QueryFirstOrDefault<Test_User>(sql.ToString()); } } /// <summary> /// 获取多个Test_User对象 /// </summary> /// <param name="columnHead">显示列头</param> /// <param name="where">查询条件</param> /// <param name="order">排序条件</param> /// <returns>List<Test_User>对象</returns> public List<Test_User> GetModelList(string columnHead, string where, string order) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { var sql = new StringBuilder(); sql.Append(" select "); sql.Append(string.IsNullOrWhiteSpace(columnHead)?" * " : columnHead); sql.Append(" from Test_User where 1=1 "); if (!string.IsNullOrWhiteSpace(where)) sql.Append(where); sql.Append(string.IsNullOrWhiteSpace(order)?" order by id " : order); return conn.Query<Test_User>(sql.ToString()).ToList(); } } } }
添加Log4Net
webApi项目下新建log4net.config ，并在Global.asax 中添加初始化配置的方法。
注：此处测试Log4Net 不区分Mvc和和ApiController，都可以使用
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" /> </configSections> <log4net> <!-- OFF, FATAL, ERROR, WARN, INFO, DEBUG, ALL --> <!-- Set root logger level to ERROR and its appenders --> <root> <level value="ALL" /> <appender-ref ref="SysAppender" /> </root> <!-- Print only messages of level DEBUG or above in the packages --> <logger name="WebLogger"> <level value="DEBUG" /> </logger> <appender name="SysAppender" type="log4net.Appender.RollingFileAppender,log4net"> <!--log存在地址--> <param name="File" value="App_Data/" /> <param name="AppendToFile" value="true" /> <param name="RollingStyle" value="Date" /> <param name="DatePattern" value=""Logs_"yyyyMMdd".txt"" /> <param name="StaticLogFileName" value="false" /> <layout type="log4net.Layout.PatternLayout,log4net"> <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" /> </layout> </appender> <appender name="consoleApp" type="log4net.Appender.ConsoleAppender,log4net"> <layout type="log4net.Layout.PatternLayout,log4net"> <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" /> </layout> </appender> </log4net> </configuration>
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Web; using System.Web.Http; using System.Web.Mvc; using System.Web.Optimization; using System.Web.Routing; namespace OZYShopWebApi { public class WebApiApplication : System.Web.HttpApplication { protected void Application_Start() { //注册log4net,配置从log4net.config中读取 log4net.Config.XmlConfigurator.Configure(new System.IO.FileInfo(Server.MapPath("~/log4net.config"))); AreaRegistration.RegisterAllAreas(); GlobalConfiguration.Configure(WebApiConfig.Register); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); } //抓取全局错误 protected void Application_Error(Object sender, EventArgs e) { //在出现未处理的错误时运行的代码 Exception objError = Server.GetLastError().GetBaseException(); lock (this) { //判断是否要创建SystemLog文件 var filePath = AppDomain.CurrentDomain.BaseDirectory + @"SystemLog"; if (!Directory.Exists(filePath)) Directory.CreateDirectory(filePath); //判断是否要创建txt var path = HttpContext.Current.Server.MapPath(@"/SystemLog/" + DateTime.Now.ToString("yyyy-MM-dd") + "错误日志.txt"); if (!File.Exists(path)) File.Create(path).Close(); using (StreamWriter w = File.AppendText(path)) { w.WriteLine(" 系统发生错误: "); w.WriteLine("发生时间：{0}", DateTime.Now); w.WriteLine("发生异常页：{0}", HttpContext.Current.Request.Url.ToString()); w.WriteLine("发生错误的类名：{0}", objError.TargetSite.DeclaringType.FullName); w.WriteLine("发生错误的方法名：{0}", objError.TargetSite.Name); w.WriteLine("异常信息：{0}", objError.Message); w.WriteLine("错误源：{0}", objError.Source); w.WriteLine("堆栈信息：{0}", objError.StackTrace); w.WriteLine("________________________________________________________"); w.Flush(); w.Close(); } } //清除当前异常 使之不返回到请求页面 Server.ClearError(); Response.Write("程序出现异常状态，请联系管理员"); } } }
using log4net; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using Utility.log; namespace TestDapper.Controllers { public class HomeController : Controller { //声明log接口 ； System.Reflection.MethodBase.GetCurrentMethod().DeclaringType 当前类名 private readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); public ActionResult Index() { //log4net写入日志 log.Info("123"); log.Error("123"); log.Fatal("123"); log.Debug("123"); log.Warn("123"); //自定义写入日志 WriteLogConfig.WriteLogInfo("TestSystem", "aaa", "Test", "123"); ViewBag.Title = "Home Page"; return View(); } } }
测试依赖注入
注：WebApid的请求路由和Mvc的不一样，若需更改。请进入App_Start=>打开WebApiConfig.cs
//旧
config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{action}/{id}", defaults: new { id = RouteParameter.Optional } );
更改路由模板后
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }//若需设置默认路径请参考App_Start=>RouteConfig.cs
);
using Repository.IRepositories; using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Net.Http; using System.Web.Http; namespace TestDapper.Controllers { public class DefaultController : ApiController { //声明 private readonly ITest_UserRepository itur; //创建有参的构造函数 public DefaultController(ITest_UserRepository itur) { this.itur = itur; } public string GetNum() { return "123"; } } }
设置Cors跨域，需要引用 Microsoft.AspNet.Cors v5.2.7 和 Microsoft.AspNet.WebApi.Cors v5.2.7 插件，同时新增Cors自定义配置。如何添加扩展插件，我就不截图了，手酸了。。。
using System; using System.Collections.Generic; using System.Linq; using System.Net.Http; using System.Web.Http; using Microsoft.Owin.Security.OAuth; using Newtonsoft.Json.Serialization; using System.Configuration; using System.Web.Http.Cors; namespace OZYShopWebApi { public static class WebApiConfig { public static void Register(HttpConfiguration config) { //跨域配置 var cors = new EnableCorsAttribute(ConfigurationManager.AppSettings["Origins"], "*", "*"); config.EnableCors(cors); // Web API 配置和服务 // 将 Web API 配置为仅使用不记名令牌身份验证。 config.SuppressDefaultHostAuthentication(); config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType)); // Web API 路由 config.MapHttpAttributeRoutes(); config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{action}/{id}", defaults: new { id = RouteParameter.Optional } ); } } }
测试方法很简单，将代码发布本地IIS绑定端口，然后本地代码请求绑定的端口上的控制器方法就可以看效果了，F12可查看错误。
以上都是个人陋闻，如有问题有劳指正，大家共同学习共同进步。如果有帮助到您，希望您能给动个小手点一下右下方的推荐，谢谢。