Dapper 入门案例：(密码：2020)
Dapper 一款轻量级的ORM和ADO结合的灵活化框架，作为一名用过EF和ADO的开发，使用Dapper也很是简单。
开发环境：①、VS2015、②、Sql Server 2012；③、win10；（系统影响不大。）
项目简介：①、Entity（存放实体）；②、Repository（存放接口和逻辑类）；③、Utility（公用类存放处）；④、WebApi（启动项目）
其中 Entity与Repository由 （暂时没时间放代码下载） 生成，也是些基础的增删改查方法。
一、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 { /// /// 对其他语句无任何影响 /// None, /// /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务 /// WhenHaveContine, /// /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务 /// WhenNoHaveContine, /// /// 当前语句影响到的行数必须大于0，否则回滚事务 /// ExcuteEffectRows, /// /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务 /// 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{ /// /// 数据访问抽象基础类 /// Copyright (C) Maticsoft /// public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现. public static readonly string connectionString = ConfigurationManager.ConnectionStrings["OzyLive"].ConnectionString; public DbHelperSQL() { } #region 公用方法 /// /// 判断是否存在某表的某个字段 /// /// 表名称 /// 列名称 /// 是否存在 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; } } /// /// 表是否存在 /// /// /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 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; } } } } /// /// 执行Sql和Oracle滴混合事务 /// /// SQL命令行列表 /// Oracle命令行列表 /// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功 public static int ExecuteSqlTran(List list, List 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; } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// 多条SQL语句 public static int ExecuteSqlTran(List 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; } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 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(); } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 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(); } } } /// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// /// SQL语句 /// 图像字节,数据库的字段类型为image的情况 /// 影响的记录数 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(); } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） 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; } } } } /// /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 查询语句 /// SqlDataReader 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; } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet 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语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 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 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; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 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; } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static int ExecuteSqlTran(System.Collections.Generic.List 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; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List 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; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 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; } } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） 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; } } } } /// /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 查询语句 /// SqlDataReader 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(); // } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet 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 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 存储过程操作 /// /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader 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; } /// /// 执行存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet结果中的表名 /// DataSet 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; } } /// /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand 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; } /// /// 执行存储过程，返回影响的行数 /// /// 存储过程名 /// 存储过程参数 /// 影响的行数 /// /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// /// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 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 >>删除<< /// /// 执行删除 /// /// 表名 /// 主键值 /// 主键字段名称 /// 删除结果 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); } /// /// 根据查询条件删除 /// /// /// /// /// 执行插入 /// /// 表名 /// 字段和值 /// 结果 public static int Insert(string table, Hashtable htField) { ArrayList args; string sql = GetInsertSql(table, htField, out args); return ExecuteSql(sql, args); } /// /// 执行插入，返回ID /// /// 表名 /// 字段和值 /// ID 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)); } /// /// 使用DataTable插入数据 /// /// /// /// /// 使用DataTable插入数据，只适合插入一条 /// /// /// 返回主键 /// 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); } } /// /// 获取插入语句Sql /// /// /// /// /// /// 执行更新 /// /// 表名 /// 字段和值 /// 主键值 /// 主键字段名称 /// 结果 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); } /// /// 执行更新 /// /// 表名 /// 字段和值 /// 查询条件 /// /// 执行更新 /// /// 表名 /// 字段和值 /// 查询条件 /// /// 获取单个字段值 /// /// 表名 /// 字段名 /// 主键值 /// 主键字段名称 /// /// /// /// /// /// /// /// /// 获取记录数量 /// /// 表名 /// 查询条件 /// /// 判断是否存在 /// /// 表名 /// 查询条件 /// /// 判断是否存在 /// /// 表名 /// 主键值 /// 主键字段名称 /// /// 获取查询条件字符串 /// /// 查询条件 /// 参数集合 /// /// 多表查询 - 通过ID获取 /// /// 要查询的字段，如：* | Id,Field /// 表名 /// 主键值 /// 主键字段名称 /// /// 多表查询 - 通过传入where 字符串 条件 来查询 /// /// 要查询的字段，如：* | Id,Field /// 表名 /// 查询条件 多个条件注意用and拼接 /// /// 多表查询 - 通过查询条件获取 /// /// 查询字段，如： A.Field, B.Field /// 表名，如： TableOne as A, TableTwo as B /// 查询条件 /// 排序,包括字段及排序方式(如：ordernum desc),也可以用""不排序 /// /// 多表分组查询 - 通过查询条件获取 /// /// 查询字段，如： A.Field, B.Field /// 表名，如： TableOne as A, TableTwo as B /// 查询条件 /// 排序,包括字段及排序方式(如：ordernum desc),也可以用""不排序 /// /// 多表查询 - 分页 /// /// 主键字段 /// 要查询的字段，如：* | Id,Field /// 表名 /// 条件字符串 /// 排序，包括排序字段和排序方式(如：id desc) /// 当前页码 /// 每页显示行数 /// 总行数 /// 总共页码数 /// /// 多表查询 - 通过存储过程获取 /// /// 存储过程 /// 查询条件 /// /// 执行SQL语句,返回受影响的行数 /// /// SQL语句 /// 参数 /// /// 执行查询返回单个对象 /// /// SQL语句 /// /// 执行查询返回单个对象 /// /// SQL语句 /// 参数 /// /// 执行存储过程,返回单个对象 /// /// 存储过程名称 /// 参数 /// 单个结果 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); } } } } /// /// 执行存储过程,返回DataTable /// /// 存储过程名称 /// 参数 /// /// 执行SQL语句 返回DataTable /// /// SQL语句 /// DataTable 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); } } } } /// /// 执行SQL语句 返回DataTable /// /// SQL语句 /// 参数集合 /// /// 多表查询 - 分页 /// /// 主键字段 /// 要查询的字段，如：* | Id,Field /// 表名 /// 条件字符串 /// 排序，包括排序字段和排序方式(如：id desc) /// 当前页码 /// 每页显示行数 /// 总行数 /// 总共页码数 /// /// 多表查询 - 分页扩展 /// /// 主键字段 /// 要查询的字段，如：* | Id,Field /// 表名 /// 条件字符串 /// 排序，包括排序字段和排序方式(如：id desc) /// 当前页码 /// 每页显示行数 /// 总行数 /// 总共页码数 /// 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 { /// /// object对象转string /// /// /// 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 { /// /// 写入自定义日志 /// /// 文件 /// 文件 /// 来源备注 /// 内容 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("\r\n自定义日志: "); 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的创建方法，将 生成的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 { /// /// 新增单个Test_User对象(若主键是自增，需删除@主键) /// /// 新增的对象 /// Test_User对象的主键 int AddModel(Test_User model); /// /// 删除单个或多个Test_User对象 /// /// 查询需要删除的对象条件 /// sql操作的影响行数 int DeleteModel(string where); /// /// 修改单个或多个Test_User对象 /// /// 修改的对象 /// 查询需要修改的对象条件 /// sql操作的影响行数 int UpdateModel(Test_User model,string where); /// /// 获取单个Test_User对象 /// /// 显示列头 /// 查询条件 /// 排序条件 /// Test_User对象 Test_User GetModel(string columnHead, string where, string order); /// /// 获取多个Test_User对象 /// /// 显示列头 /// 查询条件 /// 排序条件 /// List 对象 List 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 { /// /// 新增单个Test_User对象(若主键是自增，需删除@主键) /// /// 新增的对象 /// Test_User对象的主键 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 (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 }); } } /// /// 删除单个或多个Test_User对象 /// /// 查询需要删除的对象条件 /// sql操作的影响行数 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()); } } /// /// 修改单个或多个Test_User对象 /// /// 修改的对象 /// 查询需要修改的对象条件 /// sql操作的影响行数 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}); } } /// /// 获取单个Test_User对象 /// /// 显示列头 /// 查询条件 /// 排序条件 /// Test_User对象 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 (sql.ToString()); } } /// /// 获取多个Test_User对象 /// /// 显示列头 /// 查询条件 /// 排序条件 /// List 对象 public List 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 (sql.ToString()).ToList(); } } }} 添加Log4Net
webApi项目下新建log4net.config ，并在Global.asax 中添加初始化配置的方法。
注：此处测试Log4Net 不区分Mvc和和ApiController，都可以使用
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("\r\n系统发生错误: "); 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可查看错误。
以上都是个人陋闻，如有问题有劳指正，大家共同学习共同进步。如果有帮助到您，希望您能给动个小手点一下右下方的推荐，谢谢。
