这是一个.ner框架中经常用到的数据映射类，此类主要承接业务应用做数据的增删查改操作。通过序列化将查询数据映射为需要的类。
由于经常会用到，这里收藏一下。
data用作SQL数据库链接操作；
Repository提供数据常用的增删查改以及分页操作；
Serialization对sql查询出的数据进行序列化；
大致目录如下：
一、Data下代码
BaseKey.cs
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Data { /// <summary> /// 读取web.config数据库连接 /// 需要在appsetting配置DBConnectionString节点 /// </summary> public class BaseKey { /// <summary> /// 主数据库连接 /// </summary> public static string DbConnectionString { get { return ConfigurationManager.AppSettings["DBConnectionString"]; } } /// <summary> /// instrument数据库连接 /// </summary> /// <returns></returns> public static SqlConnection GetConnection() { string strcoun = DbConnectionString; SqlConnection con = new SqlConnection(strcoun); if (con.State != ConnectionState.Open) { con.Open(); } return con; } } }
DataAggregate.cs
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Data { /// <summary> /// 执行存储过程数据操作类 /// </summary> public class DataAggregate { #region DataReader读取数据返回DataSet /// <summary> /// DataReader读取数据返回DataSet /// </summary> /// <param name="datareader">装载数据的DataReader实例</param> /// <returns>返回DateSet</returns> public static DataSet GetDataSet(SqlDataReader datareader) { DataSet dst = new DataSet(); DataTable dtbl = new DataTable(); int readerCount = datareader.FieldCount; for (int i = 0; i < readerCount; i++) { DataColumn dcol = new DataColumn(); dcol.DataType = datareader.GetFieldType(i); dcol.ColumnName = datareader.GetName(i); dtbl.Columns.Add(dcol); } while (datareader.Read()) { DataRow drow = dtbl.NewRow(); for (int j = 0; j < readerCount; j++) { drow[j] = datareader[j]; } dtbl.Rows.Add(drow); } dst.Tables.Add(dtbl); return dst; } #endregion #region DataReader读取数据返回DataTable /// <summary> /// DataReader读取数据返回DataTable /// </summary> /// <param name="datareader">装载数据的DataReader实例</param> /// <returns></returns> public static DataTable DataReaderToTable(SqlDataReader datareader) { DataTable dtbl = new DataTable(); int readerCount = datareader.FieldCount; for (int i = 0; i < readerCount; i++) { DataColumn dcol = new DataColumn(); dcol.DataType = datareader.GetFieldType(i); dcol.ColumnName = datareader.GetName(i); dtbl.Columns.Add(dcol); } while (datareader.Read()) { DataRow drow = dtbl.NewRow(); for (int j = 0; j < readerCount; j++) { drow[j] = datareader[j]; } dtbl.Rows.Add(drow); } return dtbl; } #endregion #region 无参返回表 /// <summary> /// 无参返回表 /// </summary> /// <param name="Procedure">存储过程名</param> /// <returns></returns> public static DataTable GetDataTableN(string Procedure) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; DataTable list; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, Procedure); list = DataReaderToTable(dtr); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } return list; } #endregion #region 读取列表 /// <summary> /// 获取数据列表 /// </summary> /// <param name="procedure">存储过程名称</param> /// <param name="parm">存储过程的参数</param> /// <returns></returns> public static DataTable GetDateTabel(string procedure, SqlParameter[] parm) { SqlDataReader dtr = null; SqlConnection con = BaseKey.GetConnection(); try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); return DataReaderToTable(dtr); } catch (Exception ex) { throw new Exception(ex.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 获取多张表 /// <summary> /// 获取多张表，无参数 /// </summary> /// <param name="procedure">存储过程名称</param> /// <returns></returns> public static DataSet GetDataTables(string procedure) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; DataSet dst = new DataSet(); try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure); dst.Tables.Add(DataReaderToTable(dtr)); while (dtr.NextResult()) { dst.Tables.Add(DataReaderToTable(dtr)); } return dst; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 获取多张表 /// <summary> /// 获取多张表 /// </summary> /// <param name="procedure">存储过程名</param> /// <param name="commandParameters">参数对象</param> /// <returns></returns> public static DataSet GetDataTables(string procedure, params SqlParameter[] commandParameters) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; DataSet dst = new DataSet(); try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, commandParameters); dst.Tables.Add(DataReaderToTable(dtr)); while (dtr.NextResult()) { dst.Tables.Add(DataReaderToTable(dtr)); } return dst; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 读取表的其中一列 /// <summary> /// 读取表的其中一列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columnname">读取的列名</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static string GetColumnInfo(string procedure, string columnname, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); string strInfo = ""; while (dtr.Read()) { strInfo = dtr[columnname].ToString(); } return strInfo; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 读取表的其中一列具体数字列 /// <summary> /// 读取表的其中一列具体数字列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columnname">读取的列名</param> /// <param name="parm">参数对象</param> /// <returns>如果没有数据则是-1</returns> public static int GetColumnInfoToInt(string procedure, string columnname, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); int Number = -1; while (dtr.Read()) { Number = Convert.ToInt32(dtr[columnname]); } return Number; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 读取表的其中一列具体数字列 /// <summary> /// 读取表的其中一列具体数字数字列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columnname">读取的列名</param> /// <returns>如果没有数据则是-1</returns> public static int GetColumnInfoToInt(string procedure, string columnname) { return GetColumnInfoToInt(procedure, columnname, null); } #endregion #region 读取表的多列 /// <summary> /// 读取表的多列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columncount">总列数</param> /// <returns></returns> public static string[] GetColumnsInfo(string procedure, int columncount) { return GetColumnsInfo(procedure, columncount, null); } #endregion #region 读取表的多列 /// <summary> /// 读取表的多列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columncount">总列数</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static string[] GetColumnsInfo(string procedure, int columncount, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); string[] arrInfo = new string[columncount]; while (dtr.Read()) { for (int i = 0; i < columncount; i++) { if (dtr[i] == DBNull.Value) { arrInfo[i] = default(string); } else { arrInfo[i] = dtr[i].ToString(); } } } return arrInfo; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Close(); dtr.Dispose(); } } #endregion #region 读取表的多列 /// <summary> /// 读取表的多列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columncount">总列数</param> /// <returns></returns> public static int[] GetColumnsInfoToInt(string procedure, int columncount) { return GetColumnsInfoToInt(procedure, columncount, null); } #endregion #region 读取表的多列 /// <summary> /// 读取表的多列 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columnindex">总列数</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static int[] GetColumnsInfoToInt(string procedure, int columncount, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); int[] arrInfo = new int[columncount]; while (dtr.Read()) { for (int i = 0; i < columncount; i++) { if (dtr[i] == DBNull.Value) { arrInfo[i] = default(int); } else { arrInfo[i] = Convert.ToInt32(dtr[i]); } } } return arrInfo; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Close(); dtr.Dispose(); } } #endregion #region 读取表的多列 /// <summary> /// 读取表的多 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columnindex">总列数</param> /// <returns></returns> public static T[] GetColumnsInfo<T>(string procedure, int columncount) { return GetColumnsInfo<T>(procedure, columncount, null); } #endregion #region 读取表的多列 /// <summary> /// 读取表的多 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="columncount">总列数</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static T[] GetColumnsInfo<T>(string procedure, int columncount, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); T[] list = new T[columncount]; while (dtr.Read()) { for (int i = 0; i < columncount; i++) { if (dtr[i] == DBNull.Value) { list[i] = default(T); } else { list[i] = (T)dtr[i]; } } } return list; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Close(); dtr.Dispose(); } } #endregion #region 执行曾删改 /// <summary> /// 执行曾删改 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="parm">参数对象</param> public static void EXECprocedure(string procedure, params SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); try { int temp = DbHelperSQL.ExecuteNonQuery(con, CommandType.StoredProcedure, procedure, parm); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 执行曾删改返回受影响行数 /// <summary> /// 执行曾删改返回受影响行数 /// </summary> /// <param name="procedure">存过过程名</param> /// <param name="parm"></param> public static int EXECprocedureCount(string procedure, params SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); try { return DbHelperSQL.ExecuteNonQuery(con, CommandType.StoredProcedure, procedure, parm); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region sqldatereader读取数据返回表，有释放功能，无参 /// <summary> /// sqldatereader读取数据返回表，有释放功能，无参 /// </summary> /// <param name="procedure">存储过程名</param> /// <returns></returns> public static DataTable GetReaderTable(string procedure) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = null; DataTable dblt = null; try { dtr = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure); dblt = DataReaderToTable(dtr); return dblt; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 执行存成过程返回id /// <summary> /// 执行存成过程返回id /// </summary> /// <param name="procedure">存储过程名字</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static int GetSCOPE_IDENTITY(string procedure, params SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); try { return Convert.ToInt32(DbHelperSQL.ExecuteScalar(con, CommandType.StoredProcedure, procedure, parm)); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 返回一行数据 /// <summary> /// 返回一行数据 /// </summary> /// <param name="procedure">存储过程名字</param> /// <param name="count">要返回的维数</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static object[] GetRowDate(string procedure, int count, params SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); object[] arr = new object[count]; try { SqlDataReader reader = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader[i] != DBNull.Value) { arr[i] = reader[i]; } else { arr[i] = default(object); } } } return arr; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 返回符合条件的所有数据（返回单列） /// <summary> /// 返回符合条件的所有数据（返回单列） /// </summary> /// <param name="procedure">存储过程名字</param> /// <param name="parm">参数对象</param> /// <returns></returns> public static ArrayList GetRowDate(string procedure, params SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); ArrayList arr = new ArrayList(); try { SqlDataReader reader = DbHelperSQL.ExecuteReader(CommandType.StoredProcedure, con, procedure, parm); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader[i] != DBNull.Value) { arr.Add(reader[i]); } else { arr.Add(default(object)); } } } return arr; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 获取分页数据 /// <summary> /// 获取分页数据 /// </summary> /// <param name="tbname">要分页显示的表名</param> /// <param name="FieldKey">用于定位记录的主键(惟一键)字段,只能是单个字段</param> /// <param name="PageCurrent">要显示的页码</param> /// <param name="PageSize">每页的大小(记录数)</param> /// <param name="FieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param> /// <param name="FieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序</param> /// <param name="Where">查询条件</param> /// <param name="RecordCount">总页数</param> /// <returns></returns> public static DataSet DataSelect(string tbname, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int RecordCount) { SqlConnection con = BaseKey.GetConnection(); SqlDataAdapter dad = new SqlDataAdapter(); dad.SelectCommand = new SqlCommand(); dad.SelectCommand.Connection = con; dad.SelectCommand.CommandText = "sp_PageView"; dad.SelectCommand.CommandType = CommandType.StoredProcedure; dad.SelectCommand.Parameters.Add("@tbname", SqlDbType.NVarChar, 128).Value = tbname; dad.SelectCommand.Parameters.Add("@FieldKey", SqlDbType.NVarChar, 128).Value = FieldKey; dad.SelectCommand.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = PageCurrent; dad.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize; dad.SelectCommand.Parameters.Add("@FieldShow", SqlDbType.NVarChar, 1000).Value = FieldShow; dad.SelectCommand.Parameters.Add("@FieldOrder", SqlDbType.NVarChar, 1000).Value = FieldOrder; dad.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 1000).Value = Where; dad.SelectCommand.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output; DataSet dst = new DataSet(); try { dad.Fill(dst); RecordCount = (Int32)dad.SelectCommand.Parameters["@PageCount"].Value; //求出总记录数，该值是output出来的值 return dst; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dad.Dispose(); } } #endregion #region 获取分页数据 /// <summary> /// 获取分页数据 /// </summary> /// <param name="tbname">要分页显示的表名</param> /// <param name="fieldKey">用于定位记录的主键(惟一键)字段,只能是单个字段</param> /// <param name="pageCurrent">要显示的页码</param> /// <param name="pageSize">每页的大小(记录数)</param> /// <param name="fieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param> /// <param name="fieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序</param> /// <param name="sWhere">查询条件</param> /// <param name="recordCount">总页数</param> /// <returns></returns> public static DataSet FindByPage(string tbname, string fieldKey, int pageCurrent, int pageSize, string fieldShow, string fieldOrder, string sWhere, out int recordCount) { var procedure = "sp_PageView"; return FindByPage(procedure, tbname, fieldKey, pageCurrent, pageSize, fieldShow, fieldOrder, sWhere, out recordCount); } #endregion #region 获取分页数据 /// <summary> /// 获取分页数据 /// </summary> /// <param name="procedure">分页的存储过程，sp_PageView 返回总页数 ，sp_PageViewRecordCount 返回总记录数</param> /// <param name="tbname">要分页显示的表名</param> /// <param name="fieldKey">用于定位记录的主键(惟一键)字段,只能是单个字段</param> /// <param name="pageCurrent">要显示的页码</param> /// <param name="pageSize">每页的大小(记录数)</param> /// <param name="fieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param> /// <param name="fieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序</param> /// <param name="sWhere">查询条件</param> /// <param name="recordCount">总页数</param> /// <returns></returns> public static DataSet FindByPage(string procedure, string tbname, string fieldKey, int pageCurrent, int pageSize, string fieldShow, string fieldOrder, string sWhere, out int recordCount) { var defauleprocedure = "sp_PageView"; if (!string.IsNullOrEmpty(procedure)) { defauleprocedure = procedure; } SqlConnection con = BaseKey.GetConnection(); SqlDataAdapter dad = new SqlDataAdapter(); dad.SelectCommand = new SqlCommand(); dad.SelectCommand.Connection = con; dad.SelectCommand.CommandText = defauleprocedure; dad.SelectCommand.CommandType = CommandType.StoredProcedure; dad.SelectCommand.Parameters.Add("@tbname", SqlDbType.NVarChar, 128).Value = tbname; dad.SelectCommand.Parameters.Add("@FieldKey", SqlDbType.NVarChar, 128).Value = fieldKey; dad.SelectCommand.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = pageCurrent; dad.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize; dad.SelectCommand.Parameters.Add("@FieldShow", SqlDbType.NVarChar, 1000).Value = fieldShow; dad.SelectCommand.Parameters.Add("@FieldOrder", SqlDbType.NVarChar, 1000).Value = fieldOrder; dad.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 1000).Value = sWhere; dad.SelectCommand.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output; DataSet dst = new DataSet(); try { dad.Fill(dst); recordCount = (Int32)dad.SelectCommand.Parameters["@PageCount"].Value; //求出总记录数，该值是output出来的值 return dst; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dad.Dispose(); } } #endregion } }
DbHelperSQL.cs
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Data { /// <summary> /// 数据访问抽象基础类 /// </summary> public abstract class DbHelperSQL { #region 返回影响的行数 执行存储过程或sql语句 /// <summary> /// 执行存储过程或sql语句 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="cmdType">执行的类型（sql语句或者存储过程）</param> /// <param name="cmdText">存储过程名字或sql语句</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } #endregion #region 返回影响的行数 执行存储过程或sql语句（事物处理） /// <summary> /// 执行存储过程或sql语句（事物处理） /// </summary> /// <param name="trans">事物对象</param> /// <param name="cmdType">执行的类型（sql语句或者存储过程）</param> /// <param name="cmdText">存储过程名字或sql语句</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } #endregion #region 返回影响的行数 执行存储过程或sql语句（事物处理） /// <summary> /// 执行存储过程或sql语句（事物处理） /// </summary> /// <param name="trans">事物对象</param> /// <param name="connection">连接对象</param> /// <param name="cmdType">执行类型</param> /// <param name="cmdText">sql语句或存储过程</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static int ExecuteNonQuery(SqlTransaction trans, SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } #endregion #region 执行存储过程或sql语句返回SqlDataReader对象 /// <summary> /// 执行存储过程或sql语句返回SqlDataReader对象 /// </summary> /// <param name="cmdType">执行的类型（sql语句或者存储过程）</param> /// <param name="connection">连接对象</param> /// <param name="cmdText">存储过程名字或sql语句</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static SqlDataReader ExecuteReader(CommandType cmdType, SqlConnection connection, string cmdText, params SqlParameter[] commandParameters) { SqlCommand Comm = new SqlCommand(); try { PrepareCommand(Comm, connection, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = Comm.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } catch (Exception e) { throw new Exception(e.Message); } finally { Comm.Dispose(); } } #endregion #region 执行存储过程或sql语句返回SqlDataReader对象 /// <summary> /// 执行存储过程或sql语句返回SqlDataReader对象 /// </summary> /// <param name="trans">事务对象</param> /// <param name="cmdType">执行的类型（sql语句或者存储过程）</param> /// <param name="connection">连接对象</param> /// <param name="cmdText">存储过程名字或sql语句</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, SqlConnection connection, string cmdText, params SqlParameter[] commandParameters) { SqlCommand Comm = new SqlCommand(); try { PrepareCommand(Comm, connection, trans, cmdType, cmdText, commandParameters); SqlDataReader rdr = Comm.ExecuteReader(CommandBehavior.CloseConnection); Comm.Parameters.Clear(); return rdr; } catch (Exception e) { throw new Exception(e.Message); } finally { Comm.Dispose(); } } #endregion #region 执行sql语句SqlDataReader对象 /// <summary> /// 执行sql语句SqlDataReader对象 /// </summary> /// <param name="cmdType">执行类型（只能是sql语句）</param> /// <param name="connection">连接对象</param> /// <param name="cmdText">sql语句</param> /// <returns></returns> public static SqlDataReader ExecuteReader(CommandType cmdType, SqlConnection connection, string cmdText) { SqlCommand Comm = new SqlCommand(); try { PrepareCommand(Comm, connection, null, cmdType, cmdText, null); SqlDataReader rdr = Comm.ExecuteReader(CommandBehavior.CloseConnection); Comm.Parameters.Clear(); return rdr; } catch (Exception e) { throw new Exception(e.Message); } finally { Comm.Dispose(); } } #endregion #region 执行存储过程或sql语句并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// <summary> /// 执行存储过程或sql语句并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// </summary> /// <param name="connection">连接对象</param> /// <param name="cmdType">执行的类型（sql语句或者存储过程）</param> /// <param name="cmdText">存储过程名字或sql语句</param> /// <param name="commandParameters">SqlParameter数组</param> /// <returns></returns> public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } #endregion #region 匹配ADO对象 /// <summary> /// 匹配ADO对象 /// </summary> /// <param name="Comm"></param> /// <param name="connection"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> private static void PrepareCommand(SqlCommand Comm, SqlConnection connection, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { Comm.Connection = connection; Comm.CommandText = cmdText; if (trans != null) { Comm.Transaction = trans; } Comm.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) { Comm.Parameters.Add(parm); } } } #endregion } }
SqlHelper.cs
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Data { /// <summary> /// 执行sql语句 /// </summary> public class SqlHelper { #region 执行sql语句的 INSERT语句返回新增的ID /// <summary> /// 执行sql语句的 INSERT语句返回新增的ID /// </summary> /// <param name="sql"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static Int64 ExecuteInsert(string sql) { SqlConnection con = BaseKey.GetConnection(); try { return Convert.ToInt64(DbHelperSQL.ExecuteScalar(con, CommandType.Text, sql, null)); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } /// <summary> /// 执行sql语句的 INSERT语句返回新增的ID /// </summary> /// <param name="sql"></param> /// <param name="parm"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static Int64 ExecuteInsert(string sql, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); try { return Convert.ToInt64(DbHelperSQL.ExecuteScalar(con, CommandType.Text, sql, parm)); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 执行sql语句 返回第一行第一列 /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql"></param> /// <param name="parm"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static object ExecuteScalar(string sql) { SqlConnection con = BaseKey.GetConnection(); try { return DbHelperSQL.ExecuteScalar(con, CommandType.Text, sql, null); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql"></param> /// <param name="parm"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static object ExecuteScalar(string sql, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); try { return DbHelperSQL.ExecuteScalar(con, CommandType.Text, sql, parm); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } } #endregion #region 执行sql语句的 UPDATE、INSERT 或 DELETE语句返回受影响行数 /// <summary> /// 执行sql语句的 UPDATE、INSERT 或 DELETE语句返回受影响行数 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public static int ExecuteSqlCount(string sql) { int ExecuteCount = 0; SqlConnection conn = BaseKey.GetConnection(); SqlCommand cmd = new SqlCommand(sql, conn); try { ExecuteCount = cmd.ExecuteNonQuery(); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); } return ExecuteCount; } /// <summary> /// 执行sql语句的 UPDATE、INSERT 或 DELETE语句返回受影响行数 /// </summary> /// <param name="sql"></param> /// <param name="parm"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static int ExecuteSqlCount(string sql, SqlParameter[] parm) { int executeCount = 0; SqlConnection conn = BaseKey.GetConnection(); try { executeCount = DbHelperSQL.ExecuteNonQuery(conn, CommandType.Text, sql, parm); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); } return executeCount; } #endregion #region 执行sql语句的 UPDATE、INSERT 或 DELETE语句 /// <summary> /// 执行sql语句的 UPDATE、INSERT 或 DELETE语句 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public static void ExecuteSqlVoid(string sql) { SqlConnection conn = BaseKey.GetConnection(); SqlCommand cmd = new SqlCommand(sql, conn); try { if (conn.State == ConnectionState.Closed) { conn.Open(); } cmd.ExecuteNonQuery(); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); } } /// <summary> /// 执行sql语句的 UPDATE、INSERT 或 DELETE语句 /// </summary> /// <param name="sql"></param> /// <param name="parm"></param> /// <exception cref="Exception"></exception> public static void ExecuteSqlVoid(string sql, SqlParameter[] parm) { SqlConnection conn = BaseKey.GetConnection(); try { DbHelperSQL.ExecuteNonQuery(conn, CommandType.Text, sql, parm); } catch (Exception e) { throw new Exception(e.Message); } finally { conn.Close(); conn.Dispose(); } } #endregion #region 执行多条SQL语句，打开一次连接，实现数据库事务 /// <summary> /// 执行多条SQL语句，打开一次连接，实现数据库事务 /// </summary> /// <param name="sqllist">sqllist</param> public static void ExecuteSqlsTran(ArrayList sqllist) { SqlConnection con = BaseKey.GetConnection(); SqlCommand cmd = new SqlCommand(); con.Open(); cmd.Connection = con; SqlTransaction transaction = con.BeginTransaction(); cmd.Transaction = transaction; try { for (int i = 0; i < sqllist.Count; i++) { string strsql = sqllist[i].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception e) { transaction.Rollback(); throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); cmd.Dispose(); } } #endregion #region 执行sql语句返回一个DataTable（集合） /// <summary> /// 执行sql语句返回一个DataTable（集合） /// </summary> /// <param name="sql">sql</param> /// <returns></returns> public static DataTable GetDataTable(string sql) { //SqlDataReader dtr = ExecuteReader(CommandType.Text, con, sql); SqlConnection con = BaseKey.GetConnection(); DataSet ds = new DataSet(); try { SqlDataAdapter command = new SqlDataAdapter(sql, con); command.Fill(ds); } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); } return ds.Tables.Count > 0 ? ds.Tables[0] : null; } /// <summary> /// 获取数据列表 /// </summary> /// <param name="sql">sql</param> /// <param name="parm">参数</param> /// <returns></returns> public static DataTable GetDataTable(string sql, SqlParameter[] parm) { SqlDataReader dtr = null; SqlConnection con = BaseKey.GetConnection(); try { dtr = DbHelperSQL.ExecuteReader(CommandType.Text, con, sql, parm); return DataAggregate.DataReaderToTable(dtr); } catch (Exception ex) { throw new Exception(ex.Message); } finally { con.Close(); con.Dispose(); dtr.Dispose(); } } #endregion #region 执行sql语句返回某一个字段 /// <summary> /// 执行sql语句返回某一个字段 /// </summary> /// <param name="sql">sql</param> /// <param name="fieldname">要读取的列名</param> /// <returns></returns> public static string GetDataInfo(string sql, string fieldname) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = DbHelperSQL.ExecuteReader(CommandType.Text, con, sql); try { string strCountInfo = ""; while (dtr.Read()) { strCountInfo = dtr[fieldname].ToString(); } return strCountInfo; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Close(); dtr.Dispose(); } } /// <summary> /// 执行sql语句返回某一个字段 /// </summary> /// <param name="sql">sql</param> /// <param name="fieldname">要读取的列名</param> /// <param name="parm">参数</param> /// <returns></returns> public static string GetDataInfo(string sql, string fieldname, SqlParameter[] parm) { SqlConnection con = BaseKey.GetConnection(); SqlDataReader dtr = DbHelperSQL.ExecuteReader(CommandType.Text, con, sql, parm); try { string strCountInfo = ""; while (dtr.Read()) { strCountInfo = dtr[fieldname].ToString(); } return strCountInfo; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); con.Dispose(); dtr.Close(); dtr.Dispose(); } } #endregion } }
二、数据仓储Repository
IRepositoryBase.cs
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Repository { /// <summary> /// 基础仓储接口 /// </summary> public interface IRepositoryBase { #region Readonly /// <summary> /// 获取全部数据 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <returns></returns> IList<TEntity> All<TEntity>() where TEntity : class; /// <summary> /// 获取实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="id">Id</param> /// <returns>实体</returns> TEntity Find<TEntity>(object id) where TEntity : class; /// <summary> /// 获取实体 部分字段 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="filed"></param> /// <param name="id"></param> /// <returns></returns> TEntity Get<TEntity>(string filed, long id, string pKey) where TEntity : class, new(); /// <summary> /// 查找数据 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <returns>集合</returns> IQueryable<TEntity> Query<TEntity>() where TEntity : class; /// <summary> /// 根据查询字符串查询数据 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="query">查询字符串</param> /// <returns>集合</returns> IQueryable<TEntity> Query<TEntity>(string query) where TEntity : class; /// <summary> /// 执行原生sql查询语句 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TEntity"></typeparam> /// <param name="querySql"></param> /// <returns></returns> IList<TEntity> QuerySqlWhere<TEntity>(string querySql) where TEntity : class; /// <summary> /// 使用count函数查询记录总数 /// 如：("select count(*) from ....") /// </summary> /// <param name="queryHql">string</param> /// <returns>int</returns> object UniqueResult<TEntity>(string queryHql) where TEntity : class; /// <summary> /// 使用count函数查询记录总数 /// 如：("select count(*) from ....") /// </summary> /// <param name="queryHql">string</param> /// <returns>int</returns> object UniqueResultBySql<TEntity>(string queryHql) where TEntity : class; #endregion #region 写入 /// <summary> /// 添加实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="entity">实体</param> /// <returns>Id</returns> object Insert<TEntity>(TEntity entity) where TEntity : class; /// <summary> /// 批量添加实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="obj"></param> /// <returns>Id</returns> bool Insert<TEntity>(IList<TEntity> obj) where TEntity : class; #endregion #region 更新 /// <summary> /// 更新实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="entity">实体</param> void Update<TEntity>(TEntity entity) where TEntity : class; /// <summary> /// 自定义修改 /// </summary> /// <param name="id"></param> /// <param name="ht"></param> /// <param name="where"></param> int Update<TEntity>(Hashtable ht, string where) where TEntity : class; /// <summary> /// 自定义修改 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="id"></param> /// <param name="ht"></param> /// <param name="pKey"></param> /// <returns></returns> int Update<TEntity>(long id, Hashtable ht, string pKey) where TEntity : class; /// <summary> /// 批量修改实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="obj"></param> /// <returns>Id</returns> bool Update<TEntity>(IList<TEntity> obj) where TEntity : class; #endregion #region 删除 /// <summary> /// 删除实体 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="entity">实体</param> void Delete<TEntity>(TEntity entity) where TEntity : class; /// <summary> /// /// </summary> /// <param name="id"></param> /// <typeparam name="TEntity"></typeparam> void Delete<TEntity>(object id) where TEntity : class; /// <summary> /// 根据query条件Sql字符串删除实体 /// </summary> /// <param name="where">query条件字符串,如果是ORM则是对象，ADO.net是表字段</param> int Delete<TEntity>(string where) where TEntity : class; #endregion } }
RepositoryBase.cs
using IM.Job.WebApi.Common.Data; using IM.Job.WebApi.Common.Serialization; using System; using System.Collections; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Repository { /// <summary> /// 基础仓储实现（ADO.Net） /// </summary> public class RepositoryBase : IRepositoryBase { #region 查询 public virtual IList<TEntity> All<TEntity>() where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); var filed = ToSqlSerialize.ObjectToSqlFiled(typeof(TEntity)); var sql = "select " + filed + " from " + tableName; var dt = Data.SqlHelper.GetDataTable(sql); return DataHelper.FillList<TEntity>(dt); } public virtual TEntity Find<TEntity>(object id) where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); var filed = ToSqlSerialize.ObjectToSqlFiled(typeof(TEntity)); var primarykeyName = ToSqlSerialize.GetPrimarykey(typeof(TEntity)); var sb = new StringBuilder(); sb.Append("select "); sb.Append(filed); sb.Append(" From "); sb.Append(tableName); sb.Append(" Where "); sb.Append(primarykeyName); sb.Append(" = "); sb.Append("@" + primarykeyName); var parm = new[] { new SqlParameter("@" + primarykeyName, id) }; var dt = Data.SqlHelper.GetDataTable(sb.ToString(), parm); if (dt != null && dt.Rows.Count > 0 && dt.Columns.Count > 0) { return DataHelper.Fill<TEntity>(dt); } return null; } public TEntity Get<TEntity>(string filed, long id, string pKey) where TEntity : class, new() { if (string.IsNullOrEmpty(filed)) { return Find<TEntity>(id); } var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); var filedlist = ToSqlSerialize.ObjectToSqlFiled(typeof(TEntity), filed); var primarykeyName = ToSqlSerialize.GetPrimarykey(typeof(TEntity)); var sb = new StringBuilder(); sb.Append("select "); sb.Append(filedlist); sb.Append(" From "); sb.Append(tableName); sb.Append(" Where "); sb.Append(primarykeyName); sb.Append(" = "); sb.Append("@" + primarykeyName); var parm = new[] { new SqlParameter("@" + primarykeyName, id) }; var dt = Data.SqlHelper.GetDataTable(sb.ToString(), parm); return DataHelper.Fill<TEntity>(dt); } public virtual IList<TEntity> QuerySqlWhere<TEntity>(string querySql) where TEntity : class { var dt = SqlHelper.GetDataTable(querySql); return Serialization.DataHelper.FillList<TEntity>(dt); } public virtual object UniqueResult<TEntity>(string queryHql) where TEntity : class { return SqlHelper.ExecuteScalar(queryHql); } public virtual object UniqueResultBySql<TEntity>(string queryHql) where TEntity : class { return SqlHelper.ExecuteScalar(queryHql); } public virtual IQueryable<TEntity> Query<TEntity>() where TEntity : class { throw new NotImplementedException(); } public virtual IQueryable<TEntity> Query<TEntity>(string query) where TEntity : class { throw new NotImplementedException(); } #endregion #region 添加 public virtual object Insert<TEntity>(TEntity entity) where TEntity : class { var ps = entity.GetType().GetProperties(); var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); StringBuilder sb = new StringBuilder(); SqlParameter[] parm; List<SqlParameter> p = new List<SqlParameter>(); foreach (var pi in ps) { var attr = pi.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var a = (TableFieldAttribute)attr[0]; if (a.IsPrimarykey) //如果是标识字段，跳过 continue; var val = pi.GetValue(entity, null); if (val == null) { continue; } p.Add(new SqlParameter("@" + a.Name, val)); sb.Append(a.Name); sb.Append(","); } StringBuilder SqlString = new StringBuilder(); SqlString.Append("Insert Into "); SqlString.Append(tableName); SqlString.Append(" ("); string[] fields = sb.ToString().TrimEnd(',').Split(','); SqlString.Append(string.Join(",", fields)); SqlString.Append(") Values ("); SqlString.Append("@"); SqlString.Append(string.Join(",@", fields)); SqlString.Append(");select @@IDENTITY"); parm = new SqlParameter[fields.Length]; int index = 0; foreach (var s in p) { parm[index] = s; index++; } var robjet = Data.SqlHelper.ExecuteInsert(SqlString.ToString(), parm); return robjet; } public virtual bool Insert<TEntity>(IList<TEntity> obj) where TEntity : class { foreach (var model in obj) { Insert(model); } return true; } #endregion #region 更新 public bool Update<TEntity>(IList<TEntity> obj) where TEntity : class { foreach (var model in obj) { Update<TEntity>(model); } return true; } public virtual void Update<TEntity>(TEntity entity) where TEntity : class { var ps = entity.GetType().GetProperties(); var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); StringBuilder sb = new StringBuilder(); SqlParameter[] parm; List<SqlParameter> p = new List<SqlParameter>(); string strwhere = ""; var primarykeyValue = ""; var primarykey = ""; foreach (var pi in ps) { var attr = pi.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var a = (TableFieldAttribute)attr[0]; var val = pi.GetValue(entity, null); if (val == null) { continue; } if (a.IsPrimarykey) { strwhere = a.Name + "=@" + a.Name; primarykeyValue = val.ToString(); primarykey = a.Name; continue; } p.Add(new SqlParameter("@" + a.Name, val)); sb.Append(a.Name); sb.Append("="); sb.Append("@"); sb.Append(a.Name); sb.Append(","); } StringBuilder SqlString = new StringBuilder(); SqlString.Append("update "); SqlString.Append(tableName); SqlString.Append(" set "); string[] fields = sb.ToString().TrimEnd(',').Split(','); SqlString.Append(string.Join(",", fields)); SqlString.Append(" where "); SqlString.Append(strwhere); parm = new SqlParameter[fields.Length + 1]; int index = 0; foreach (var s in p) { parm[index] = s; index++; } parm[index] = new SqlParameter("@" + primarykey, primarykeyValue); Data.SqlHelper.ExecuteSqlVoid(SqlString.ToString(), parm); } public virtual int Update<TEntity>(Hashtable ht, string where) where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); var list = ToSqlSerialize.HashtableToUpdateSql(ht, typeof(TEntity)); if (list.Count <= 0) return 0; var sql = " update " + tableName + " set " + string.Join(",", list.ToArray()) + " where " + where; return Data.SqlHelper.ExecuteSqlCount(sql); } public int Update<TEntity>(long id, Hashtable ht, string pKey) where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); var list = ToSqlSerialize.HashtableToUpdateSql(ht, typeof(TEntity)); if (list.Count <= 0) return 0; var sql = " update " + tableName + " set " + string.Join(",", list.ToArray()) + " where " + pKey + "=" + id; return Data.SqlHelper.ExecuteSqlCount(sql); } #endregion #region 删除 public virtual void Delete<TEntity>(TEntity entity) where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); if (string.IsNullOrEmpty(tableName)) return; var primarykeyName = ""; var primarykeyValues = ""; var ps = entity.GetType().GetProperties(); foreach (var p in ps) { var attr = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var a = (TableFieldAttribute)attr[0]; if (a.IsPrimarykey) { var val = p.GetValue(entity, null); primarykeyName = a.Name; if (string.IsNullOrEmpty(primarykeyName)) { primarykeyName = "ID"; } primarykeyValues = val.ToString(); break; } } if (string.IsNullOrEmpty(primarykeyName)) return; var sb = new StringBuilder(); sb.Append("Delete From "); sb.Append(tableName); sb.Append(" Where "); sb.Append(primarykeyName); sb.Append(" = "); sb.Append("@" + primarykeyName); var parm = new[] { new SqlParameter("@" + primarykeyName, primarykeyValues) }; Data.SqlHelper.ExecuteSqlCount(sb.ToString(), parm); } public virtual void Delete<TEntity>(object id) where TEntity : class { var tableName = ToSqlSerialize.GetTableName(typeof(TEntity)); if (string.IsNullOrEmpty(tableName)) return; var primarykeyName = ToSqlSerialize.GetPrimarykey(typeof(TEntity)); var sb = new StringBuilder(); sb.Append("Delete From "); sb.Append(tableName); sb.Append(" Where "); sb.Append(primarykeyName); sb.Append(" = "); sb.Append("@" + primarykeyName); var parm = new[] { new SqlParameter("@" + primarykeyName, id) }; Data.SqlHelper.ExecuteSqlCount(sb.ToString(), parm); } public virtual int Delete<TEntity>(string where) where TEntity : class { var tableName = Serialization.ToSqlSerialize.GetTableName(typeof(TEntity)); if (!string.IsNullOrEmpty(tableName)) { var sb = new StringBuilder(); sb.Append("Delete From "); sb.Append(tableName); sb.Append(" Where "); sb.Append(where); return Data.SqlHelper.ExecuteSqlCount(sb.ToString()); } return 0; } #endregion #region 分页 /// <summary> /// 获取全部信息 /// </summary> /// <param name="tableName">表名</param> /// <param name="strFieldShow">查询的字段</param> /// <param name="strWhere">查询条件</param> /// <param name="strFieldOrder">排序字段</param> /// <param name="recordcount">总记录数</param> /// <returns></returns> public virtual IList<TEntity> FindByPageAll<TEntity>(string tableName, string strFieldShow, string strWhere, string strFieldOrder, out int recordcount) where TEntity : class { var sql = "select " + strFieldShow + " from " + tableName + " where " + strWhere + " order by " + strFieldOrder; var listall = QuerySqlWhere<TEntity>(sql); if (listall != null && listall.Count > 0) { recordcount = listall.Count; } else { recordcount = 0; } return listall; } /// <summary> /// 统一的分页ADO.net处理 /// </summary> /// <param name="tableName">表名</param> /// <param name="strFieldKey">主键</param> /// <param name="pageIndex">当前页</param> /// <param name="pageCount">每页显示的数量</param> /// <param name="strFieldShow">要查询的字段</param> /// <param name="strWhere">查询条件</param> /// <param name="strFieldOrder">排序字段，如 id desc</param> /// <param name="recordcount">总记录数</param> /// <typeparam name="TEntity"></typeparam> /// <returns></returns> public virtual IList<TEntity> FindByPageSql<TEntity>(string tableName, string strFieldKey, int pageIndex, int pageCount, string strFieldShow, string strWhere, string strFieldOrder, out int recordcount) where TEntity : class { if (pageCount <= 0) { return FindByPageAll<TEntity>(tableName, strFieldShow, strWhere, strFieldOrder, out recordcount); } //sp_PageViewRecordCount 返回总记录数 var procedure = "sp_PageViewRecordCount"; var ds =DataAggregate.FindByPage(procedure, tableName, strFieldKey, pageIndex, pageCount, strFieldShow, strFieldOrder, strWhere, out recordcount); if (ds != null && ds.Tables.Count > 0) { var dt = ds.Tables[0]; var list =Serialization.DataHelper.FillList<TEntity>(dt); return list; } return null; } #endregion } }
RepositoryServices.cs
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Repository { public abstract class RepositoryServices : IRepositoryBase { public IRepositoryBase Dao; protected RepositoryServices(IRepositoryBase dao) { Dao = dao; } #region IRepositoryBase 成员 public virtual IList<TEntity> All<TEntity>() where TEntity : class { return Dao.All<TEntity>(); } public virtual TEntity Find<TEntity>(object id) where TEntity : class { return Dao.Find<TEntity>(id); } public TEntity Get<TEntity>(string filed, long id, string pKey) where TEntity : class, new() { return Dao.Get<TEntity>(filed, id, pKey); } public virtual IQueryable<TEntity> Query<TEntity>() where TEntity : class { return Dao.Query<TEntity>(); } public virtual IQueryable<TEntity> Query<TEntity>(string query) where TEntity : class { return Dao.Query<TEntity>(query); } public virtual IList<TEntity> QuerySqlWhere<TEntity>(string querySql) where TEntity : class { return Dao.QuerySqlWhere<TEntity>(querySql); } public virtual object UniqueResult<TEntity>(string queryHql) where TEntity : class { return Dao.UniqueResult<TEntity>(queryHql); } public virtual object UniqueResultBySql<TEntity>(string queryHql) where TEntity : class { return Dao.UniqueResultBySql<TEntity>(queryHql); } public virtual object Insert<TEntity>(TEntity entity) where TEntity : class { return Dao.Insert(entity); } public virtual bool Insert<TEntity>(IList<TEntity> obj) where TEntity : class { return Dao.Insert(obj); } public virtual void Update<TEntity>(TEntity entity) where TEntity : class { Dao.Update(entity); } public virtual int Update<TEntity>(Hashtable ht, string where) where TEntity : class { return Dao.Update<TEntity>(ht, where); } public virtual void Delete<TEntity>(TEntity entity) where TEntity : class { Dao.Delete(entity); } public virtual void Delete<TEntity>(object id) where TEntity : class { Dao.Delete<TEntity>(id); } public virtual int Delete<TEntity>(string where) where TEntity : class { return Dao.Delete<TEntity>(where); } #endregion public int Update<TEntity>(long id, Hashtable ht, string pKey) where TEntity : class { return Dao.Update<TEntity>(id, ht, pKey); } public bool Update<TEntity>(IList<TEntity> obj) where TEntity : class { return Dao.Update(obj); } } }
三、Serialization序列化
DataHelper.cs
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Linq; using System.Reflection; using System.Text; namespace IM.Job.WebApi.Common.Serialization { /// <summary> /// 数据操作 /// </summary> public class DataHelper { /// <summary> /// 将泛型集合填充为数据表 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="list">泛型集合</param> /// <param name="tableName">表名</param> /// <returns>数据表</returns> public static DataTable Fill<T>(IList<T> list, string tableName) { DataTable dt = new DataTable(tableName); T t = Activator.CreateInstance<T>(); Type type = t.GetType(); PropertyInfo[] properties = type.GetProperties().Where(p => p.MemberType == MemberTypes.Property && p.CanRead).ToArray(); foreach (PropertyInfo p in properties) { dt.Columns.Add(p.Name); } foreach (T t1 in list) { PropertyInfo[] properties1 = t1.GetType().GetProperties().Where(p => p.MemberType == MemberTypes.Property && p.CanRead).ToArray(); DataRow dr = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { PropertyInfo propertyInfo = properties1.SingleOrDefault(p => p.Name == Convert.ToString(dt.Columns[i].ColumnName)); if (propertyInfo != null) { dr[dt.Columns[i].ColumnName] = propertyInfo.GetValue(t1, null); } } dt.Rows.Add(dr); } return dt; } /// <summary> /// 将泛型填充为数据表 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="t">泛型</param> /// <param name="tableName">表名</param> /// <returns>数据表</returns> public static DataTable Fill<T>(T t, string tableName) { IList<T> list = new List<T>(); if (t != null) { list.Add(t); } return Fill<T>(list, tableName); } /// <summary> /// 将对象集合填充为数据表 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="list">对象集合</param> /// <param name="tableName">表名</param> /// <returns>数据表</returns> public static DataTable Fill(IList list, string tableName) { DataTable dt = new DataTable(tableName); if (list.Count > 0) { PropertyInfo[] properties = list[0].GetType().GetProperties().Where(p => p.MemberType == MemberTypes.Property && p.CanRead).ToArray(); foreach (PropertyInfo p in properties) { dt.Columns.Add(p.Name); } foreach (var t in list) { PropertyInfo[] properties1 = t.GetType().GetProperties().Where(p => p.MemberType == MemberTypes.Property && p.CanRead).ToArray(); DataRow dr = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { PropertyInfo propertyInfo = properties1.SingleOrDefault(p => p.Name == Convert.ToString(dt.Columns[i].ColumnName)); if (propertyInfo != null) { dr[dt.Columns[i].ColumnName] = propertyInfo.GetValue(t, null); } } dt.Rows.Add(dr); } } return dt; } /// <summary> /// 将对象填充为数据表 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="obj">对象</param> /// <param name="tableName">表名</param> /// <returns>数据表</returns> public static DataTable Fill(Object obj, string tableName) { IList list = null; if (typeof(IEnumerable).IsAssignableFrom(obj.GetType())) { list = (IList)obj; } else { list = new ArrayList(); if (obj != null) { list.Add(obj); } } return Fill(list, tableName); } /// <summary> /// 将定IDictionary数据转换为DataSet数据 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="dictionary">键值对数据:key表名,value实体(或实体集合)</param> /// <returns>DataSet数据集</returns> public static DataSet Fill(IDictionary dictionary) { DataSet ds = new DataSet(); foreach (DictionaryEntry de in dictionary) { DataTable dt = Fill(de.Value, de.Key.ToString()); ds.Tables.Add(dt); } return ds; } /// <summary> /// 将数据表填充为泛型集合 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="dataTable">数据表</param> /// <returns>泛型集合</returns> public static IList<T> FillList<T>(DataTable dataTable) { IList<T> list = list = new List<T>(); if (dataTable != null && dataTable.Rows.Count > 0 && dataTable.Columns.Count > 0) { List<string> columnNameList = new List<string>(); for (int i = 0; i < dataTable.Columns.Count; i++) { columnNameList.Add(dataTable.Columns[i].ColumnName); } for (int i = 0; i < dataTable.Rows.Count; i++) { DataRow dr = dataTable.Rows[i]; T t = Activator.CreateInstance<T>(); Type type = t.GetType(); PropertyInfo[] properties = type.GetProperties().Where(p => p.MemberType == MemberTypes.Property && p.CanWrite).ToArray(); foreach (PropertyInfo p in properties) { if (columnNameList.Contains(p.Name) && dr[p.Name] != DBNull.Value) { if (p.PropertyType.IsGenericType) { p.SetValue(t, Convert.ChangeType(dr[p.Name], p.PropertyType.GetGenericArguments()[0]), null); } else { p.SetValue(t, Convert.ChangeType(dr[p.Name], p.PropertyType), null); } } } list.Add(t); } } return list; } /// <summary> /// 将数据表填充为泛型集合 /// <para>创建作者：changjin</para> /// <para>创建日期：2011/07/26</para> /// </summary> /// <param name="dataTable">数据表</param> /// <returns>泛型集合</returns> public static T Fill<T>(DataTable dataTable) { return FillList<T>(dataTable)[0]; } } }
TableAttribute.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Serialization { [AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = true)] public class TableAttribute:Attribute { private string _name; public string Name { get { return _name; } set { _name = value; } } /// <summary> /// 表名 /// </summary> /// <param name="tablename"></param> public TableAttribute(string tablename) { this._name = tablename; } } }
TableFieldAttribute.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Serialization { [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = true, Inherited = true)] public class TableFieldAttribute:Attribute { private string _name; public string Name { get { return _name; } set { _name = value; } } private bool _IsPrimarykey = false; public bool IsPrimarykey { get { return _IsPrimarykey; } set { _IsPrimarykey = value; } } /// <summary> /// 字段属性 /// </summary> /// <param name="fieldname"></param> public TableFieldAttribute(string fieldname) { this._name = fieldname; } } }
ToSqlSerialize.cs
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.Common.Serialization { public class ToSqlSerialize { /// <summary> /// 获得对象对应的表名 /// </summary> /// <param name="t"></param> /// <returns></returns> public static string GetTableName(System.Type t) { var tablename = ""; var attrs = Attribute.GetCustomAttributes(t); // reflection foreach (var a in attrs.OfType<TableAttribute>()) { tablename = a.Name; } return tablename; } /// <summary> /// 获得对象的主键 /// </summary> /// <param name="obj"></param> /// <returns></returns> public static string GetPrimarykey(System.Type obj) { var ps = Activator.CreateInstance(obj).GetType().GetProperties(); var primarykeyname = ""; foreach (var p in ps) { var attr = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var a = (TableFieldAttribute)attr[0]; if (!a.IsPrimarykey) continue; primarykeyname = a.Name; if (string.IsNullOrEmpty(primarykeyname)) { primarykeyname = "ID"; } break; } return primarykeyname; } /// <summary> /// HashtableToUrl，适用于ADO.Net /// </summary> /// <param name="ht">查询哈希表（KEY 为URL 参数名）</param> /// <param name="obj"></param> /// <returns>更新的sql语句</returns> public static List<string> HashtableToUpdateSql(Hashtable ht, System.Type obj) { var list = new List<string>(); var ps = Activator.CreateInstance(obj).GetType().GetProperties(); foreach (var p in ps) { var attr = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var a = (TableFieldAttribute)attr[0]; if (a.IsPrimarykey) continue; foreach (var s in ht.Keys) { if (s.ToString() != p.Name) continue; var sqlColumnName = a.Name; if (string.IsNullOrEmpty(sqlColumnName)) continue; switch (s.GetType().Name) { case "String": case "string": list.Add(sqlColumnName + "='" + ht[s] + "'"); break; case "DateTime": list.Add(sqlColumnName + "='" + ht[s] + "'"); break; case "Int64": case "Int32": list.Add(sqlColumnName + "=" + ht[s]); break; case "Boolean": case "bool": if ((bool)ht[s]) { list.Add(sqlColumnName + "=1"); } else { list.Add(sqlColumnName + "=0"); } break; } } } return list; } /// <summary> /// 扩展方法 获取实体类的SQL 列名 /// <para>创建作者：wupan</para> /// <para>创建日期：2013/7/16</para> /// </summary> /// <param name="obj"></param> /// <param name="flag"></param> /// <returns></returns> public static string ObjectToSqlFiled(System.Type obj) { var properties = Activator.CreateInstance(obj).GetType().GetProperties(); var list = new List<string>(); foreach (var p in properties) { var attrbutes = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attrbutes.Length > 0) { var att = (TableFieldAttribute)attrbutes[0]; if (att.Name.ToLower() == p.Name.ToLower()) { list.Add(p.Name); } else { list.Add(att.Name + " as " + p.Name); } } } return String.Join(",", list.ToArray()); } /// <summary> /// 扩展方法 获取实体类的SQL 列名 /// </summary> /// <param name="obj"></param> /// <param name="field"></param> /// <returns></returns> public static string ObjectToSqlFiled(System.Type obj, string field) { var properties = Activator.CreateInstance(obj).GetType().GetProperties(); var list = new List<string>(); var strList = field.Split(',').ToList(); foreach (var p in properties) { var attr = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var att = (TableFieldAttribute)attr[0]; foreach (var s in strList) { if (s != p.Name) continue; var sqlColumnName = att.Name; if (string.IsNullOrEmpty(sqlColumnName)) continue; if (att.Name.ToLower() == p.Name.ToLower()) { list.Add(p.Name); } else { list.Add(att.Name + " as " + p.Name); } } } return String.Join(",", list.ToArray()); } /// <summary> /// Query sort /// </summary> /// <param name="ht">查询排序</param> /// <param name="defaultField"></param> /// <returns></returns> public static string QuerySort(Hashtable ht, string defaultField) { var orderbylist = ""; if (ht["sortby"] != null) { if (ht["sortby"].ToString().ToLower() == "asc") { if (ht["order"] != null) { orderbylist = ht["order"] + " asc"; } else { orderbylist = defaultField + " asc"; } } else { if (ht["order"] != null) { orderbylist = ht["order"].ToString() + " desc"; } else { orderbylist = ht["order"].ToString() + " desc"; } } } else { if (ht["order"] != null) { orderbylist = ht["order"].ToString() + " desc"; } else { orderbylist = defaultField + " desc"; } } return orderbylist; } /// <summary> /// Query sort /// </summary> /// <param name="obj"></param> /// <param name="ht">查询排序</param> /// <param name="defaultField"></param> /// <returns></returns> public static string QuerySort(System.Type obj, Hashtable ht, string defaultField = "ID") { var orderstr = ""; //排序字段 var sortbystr = "desc";//排序方式 if (ht["order"] != null) { orderstr = ht["order"].ToString(); } else { orderstr = defaultField; } if (ht["sortby"] != null) { sortbystr = ht["sortby"].ToString(); } var queryorder = ""; var ps = Activator.CreateInstance(obj).GetType().GetProperties(); foreach (var p in ps) { var attr = p.GetCustomAttributes(typeof(TableFieldAttribute), false); if (attr.Length <= 0) continue; var att = (TableFieldAttribute)attr[0]; //如果排序字段没有值，则读取主键作为排序字段 if (string.IsNullOrEmpty(orderstr)) { if (!att.IsPrimarykey) continue; var primarykeyname = att.Name; if (string.IsNullOrEmpty(primarykeyname)) { queryorder = "ID"; } break; } //读取对应属性的表字段 var sqlColumnName = att.Name; if (string.IsNullOrEmpty(sqlColumnName)) continue; if (orderstr.ToLower() != p.Name.ToLower()) continue; queryorder = sqlColumnName; break; } var orderbylist = queryorder + sortbystr; return orderbylist; } } }
以上三个可以直接封装为一个动态的数据读取映射类
可以在BLL中实现自己的业务逻辑，比如：
using IM.Job.WebApi.Common.Repository; using IM.Job.WebApi.Common.Serialization; using IM.Job.WebApi.Core.IMCom; using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; namespace IM.Job.WebApi.DAL.IMCom { public partial class IMListDAL : RepositoryBase { public IMListInfo GetImComsInfo(string IMListID) { var listim = QuerySqlWhere<IMListInfo>(string.Format(" select ID,IMListID,IMAddress,IMCOM,IMCOM_EN,IMTel,IMemployees,IMNote from instrument.dbo.IM_List where IMListID='{0}' ", IMListID)); if (listim.Count > 0) { return listim.FirstOrDefault(); } return null; } #region 分页查询 /// <summary> /// /// </summary> /// <param name="ht"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="recordcount"></param> /// <returns></returns> public IList<IMListInfo> FindByPage(Hashtable ht, int pageIndex, int pageCount, out int recordcount) { var tobj = typeof(IMListInfo); var tableName = ToSqlSerialize.GetTableName(tobj); var strFieldKey = ToSqlSerialize.GetPrimarykey(tobj); var strFieldShow = "*"; var strFieldOrder = ToSqlSerialize.QuerySort(ht, "id"); var strWhere = "1=1"; if (ht["strWhere"] != null && !string.IsNullOrWhiteSpace(ht["strWhere"].ToString())) { strWhere += " and " + ht["strWhere"].ToString(); } if (ht["keyword"] != null && !string.IsNullOrWhiteSpace(ht["keyword"].ToString())) { var keyword = ht["keyword"].ToString(); strWhere += " and ( IMCOM like '%" + keyword + "%' or IMCOM_EN like '%" + keyword + "%')"; } if (ht["fieldshow"] != null && !string.IsNullOrWhiteSpace(ht["fieldshow"].ToString())) { strFieldShow = ht["fieldshow"].ToString(); } return FindByPageSql<IMListInfo>(tableName, strFieldKey, pageIndex, pageCount, strFieldShow, strWhere, strFieldOrder, out recordcount); } #endregion } }
