Dapper.Contrib拓展及数据库生成实体
1.关于Dapper.Contrib
Dapper.Contrib是Dapper的一个拓展类库,Dapper是一个轻量级ORM框架,这个不了解的请自行收集资料,本文主要讲讲,数据库生成实体,并通过实体进行CURD
2.首先,我们了解下Dapper.Contrib需要实体的格式:
using Dapper.Contrib.Extensions; [Table("StudentSet")] public partial class StudentSet { [Key] public int Id { get; set; } public string StudentName { get; set; } public string LoginName { get; set; } public string Password { get; set; } public int Age { get; set; } public string Sex { get; set; } public string ClassID { get; set; } public string RuleID { get; set; } public string Status { get; set; } public string UpdateTime { get; set; } public string CreateTime { get; set; } }
可以看出,这里,需要注明表名,主键字段,并需要引用Dapper.Contrib.Extensions;的命名空间
3.通过EF来从数据库映射出实体
在第二点中,我们发现,定义一个实体来符合Dapper.Contrib要求是非常麻烦的,那么,我们来看看如何利用EF框架生成
(3.1)想要生成与数据库对应的实体,就要用到EF的DB First,首先右键你的项目,添加新建项,数据,选择ADO.NET 实体数据模型
添加,来自数据库EF设计器,之后就是连接你的数据库,选择表/视图/存储过程/函数等,之后,我们就能生成一个与数据库对应的实体
这是我随意创建的一个,我们看一下生成的实体,发现,没有数据库注释说明,也不符合Dapper.Contrib的需要
那么,我们来改动Model2.tt这个文件,这里,需要下载一个文件,很小,十几k,GetSummery.ttinclude下载完了后,放到与edmx文件同级目录下:
然后,在配置文件添加数据库连接字串:
<add name="MyDbContext" connectionString="Data Source=yourserver;Initial Catalog=yourdatabase;Integrated Security=True;" providerName="System.Data.SqlClient" />
改动这个文件:
包含到我们的Model2.tt文件,这是个T4模板文件
<#@ include file="GetSummery.ttinclude" #>
搜索<#=codeStringGenerator.UsingDirectives(inHeader: false)#>
添加
using System.ComponentModel.DataAnnotations;
/// <summary>
/// <#= getTableSummery(code.Escape(entity)) #>
/// </summary>
在之下
搜索<#=codeStringGenerator.Property(edmProperty)#>
添加
/// <summary>
/// <#= getColumnSummery(code.Escape(entity),code.Escape(edmProperty)) #>
/// </summary>
<#=entity.KeyMembers.Contains(edmProperty.Name)?"[Key]":""#>
在上方
保存
这时候查看,嗯,我们的数据库字段说明已经出来了并且符合我们的Dapper.Contrib的需要,这时候可能会报错,前面说过,你需要using Dapper.Contrib.Extensions;
然后,我们就可以愉快的使用Dapper.Contrib了,这里不多说,贴出我自己写的DapperHelper类
using Dapper; using Dapper.Contrib.Extensions; using FK.Common; using FK.Data.Entities; using System; using System.Collections.Concurrent; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Reflection; namespace FK.Web.Data { public class DapperHelper { public static IDbConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString.DESDecrypt()); private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>(); private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>(); #region 增添记录 /// <summary> /// 添加一个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="TableName"></param> /// <param name="fieldlist"></param> /// <param name="key"></param> /// <param name="o"></param> /// <returns></returns> public static int AddByEntity<T>(string TableName, string fieldlist, string key, object o) { string ColumnsName = string.Empty; //fildlist=="*"时,通过实体获取列名 if (fieldlist.Trim().Equals("*") || string.IsNullOrEmpty(fieldlist)) { PropertyInfo[] plist = typeof(T).GetProperties(); string str = ""; foreach (PropertyInfo p in plist) { if (!p.PropertyType.IsGenericType && !p.Name.Equals(key)) str += p.Name + ","; } str = str.Substring(0, str.Length - 1); fieldlist = str; } //根据列名拼接参数名 string[] arrstr = fieldlist.Split(','); foreach (string s in arrstr) { ColumnsName += "@" + s + ","; } ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1); fieldlist = "(" + fieldlist + ")"; string sql = "Insert into " + TableName + " " + fieldlist + " values (" + ColumnsName + ")"; //执行插入 var result = connection.Execute(sql, o); return result; } /// <summary> /// 添加一个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="criteria"></param> /// <param name="o"></param> /// <returns></returns> public static int AddByEntity<T>(PageCriteria criteria, object o) { string ColumnsName = string.Empty; //fildlist=="*"时,通过实体获取列名 if (criteria.Fields.Trim().Equals("*") || string.IsNullOrEmpty(criteria.Fields)) { PropertyInfo[] plist = typeof(T).GetProperties(); string str = ""; foreach (PropertyInfo p in plist) { if (!p.PropertyType.IsGenericType && !p.Name.Equals(criteria.PrimaryKey)) str += p.Name + ","; } str = str.Substring(0, str.Length - 1); criteria.Fields = str; } //根据列名拼接参数名 string[] arrstr = criteria.Fields.Split(','); foreach (string s in arrstr) { ColumnsName += "@" + s + ","; } ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1); criteria.Fields = "(" + criteria.Fields + ")"; string sql = "Insert into " + criteria.TableName + " " + criteria.Fields + " values (" + ColumnsName + ")"; //执行插入 var result = connection.Execute(sql, o); return result; } /// <summary> /// 事务操作 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sqllist"></param> /// <param name="objlist"></param> /// <returns></returns> public static int TranByEntity<T>(IList<string> sqllist, IList<T> objlist) { if (sqllist.Count != objlist.Count) return -100; //参数数量不一致 connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { for (int i = 0; i < sqllist.Count; i++) { connection.Execute(sqllist[i], objlist[i], transaction); } transaction.Commit(); return 1; } catch { transaction.Rollback(); return -1; } } #endregion #region 更新记录 /// <summary> /// 更新记录 /// </summary> /// <param name="TableName">表名</param> /// <param name="UpdateString">更新的列和值</param> /// <param name="WhereString">条件</param> /// <param name="Parameters">参数对象</param> /// <returns></returns> public static int Update(string TableName, string UpdateString, string WhereString, object Parameters = null) { string sql = "update " + TableName + " set " + UpdateString + " where 1=1 " + WhereString; var result = connection.Execute(sql, Parameters); return result; } /// <summary> /// 更新记录 /// </summary> /// <param name="criteria"></param> /// <param name="UpdateString"></param> /// <param name="Parameters"></param> /// <returns></returns> public static int Update(PageCriteria criteria, object Parameters = null) { string sql = "update " + criteria.TableName + " set " + criteria.Fields + " where 1=1 " + criteria.Condition; var result = connection.Execute(sql, Parameters); return result; } /// <summary> /// 更新实体 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="newt">实体对象,主键不能缺少</param> /// <returns></returns> public static int UpdateEntity<T>(T newt) { string TableName = typeof(T).Name; PropertyInfo[] plist = typeof(T).GetProperties(); var type = typeof(T); var keyProperties = KeyPropertiesCache(type).ToList(); string condition = ""; foreach (var item in keyProperties) { if (!item.PropertyType.IsGenericType) condition = item.Name + "=@" + item.Name + " and"; } string field = ""; foreach (PropertyInfo p in plist) { if (!p.PropertyType.IsGenericType) field += p.Name + ","; } field = field.Substring(0, field.Length - 1); condition = condition.Substring(0, condition.Length - 4); T oldt = GetModel<T>(TableName, field, "where 1=1 " + condition, newt); if (newt == null || oldt == null) return 0; if (newt.Equals(oldt)) return 0; string objVal1 = string.Empty; string objVal2 = string.Empty; string updatestr = ""; foreach (PropertyInfo p in plist) { objVal1 = p.GetValue(newt, null) == null ? string.Empty : p.GetValue(newt, null).ToString(); objVal2 = p.GetValue(oldt, null) == null ? string.Empty : p.GetValue(oldt, null).ToString(); if (objVal1 != objVal2) updatestr += p.Name + "=@" + p.Name + ","; } updatestr = updatestr.Substring(0, updatestr.Length - 1); string sql = "update " + TableName + " set " + updatestr + " where 1=1 " + condition; var result = connection.Execute(sql, newt); return result; } /// <summary> /// 更新实体,需要旧的实体对象 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="oldt">旧的实体对象</param> /// <param name="newt">新的实体对象</param> /// <returns></returns> public static int UpdateEntity<T>(T oldt, T newt) { string TableName = typeof(T).Name; PropertyInfo[] plist = typeof(T).GetProperties(); var type = typeof(T); var keyProperties = KeyPropertiesCache(type).ToList(); if (newt == null || oldt == null) return 0; if (newt.Equals(oldt)) return 0; string objVal1 = string.Empty; string objVal2 = string.Empty; string updatestr = ""; foreach (PropertyInfo p in plist) { objVal1 = p.GetValue(newt, null) == null ? string.Empty : p.GetValue(newt, null).ToString(); objVal2 = p.GetValue(oldt, null) == null ? string.Empty : p.GetValue(oldt, null).ToString(); if (objVal1 != objVal2&& !p.PropertyType.IsGenericType) updatestr += p.Name + "=@" + p.Name + ","; } updatestr = updatestr.Substring(0, updatestr.Length - 1); string condition = ""; foreach (var item in keyProperties) { if (!item.PropertyType.IsGenericType) condition = item.Name + "=@" + item.Name + " and"; } condition = condition.Substring(0, condition.Length - 4); string sql = "update " + TableName + " set " + updatestr + " where 1=1 " + condition; var result = connection.Execute(sql, newt); return result; } #endregion #region 删除记录 /// <summary> /// 删除记录 /// </summary> /// <param name="TableName">表名</param> /// <param name="WhereString">条件</param> /// <param name="Parameters">参数对象</param> /// <returns></returns> public static int Delete(string TableName, string WhereString, object Parameters = null) { string sql = "delete from " + TableName + " where 1=1 " + WhereString; var result = connection.Execute(sql, Parameters); return result; } /// <summary> /// 删除 /// </summary> /// <param name="criteria"></param> /// <param name="Parameters"></param> /// <returns></returns> public static int Delete(PageCriteria criteria, object Parameters = null) { string sql = "delete from " + criteria.TableName + " where 1=1 " + criteria.Condition; var result = connection.Execute(sql, Parameters); return result; } #endregion #region 查询操作 /// <summary> /// 查询返回DataSet /// </summary> /// <param name="TableName"></param> /// <param name="FieldString"></param> /// <param name="WhereString"></param> /// <param name="Parameters"></param> /// <returns></returns> public static DataSet QueryGetDS(string TableName, string FieldString, string WhereString, object Parameters = null) { DataSet ds = new XDataSet(); string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString; var result = connection.ExecuteReader(sql, Parameters); try { ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { }); } finally { result.Close(); } return ds; } /// <summary> /// 查询返回DataSet /// </summary> /// <param name="criteria"></param> /// <param name="Parameters"></param> /// <returns></returns> public static DataSet QueryGetDS(PageCriteria criteria, object Parameters = null) { DataSet ds = new XDataSet(); string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition; var result = connection.ExecuteReader(sql, Parameters); try { ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { }); } finally { result.Close(); } return ds; } /// <summary> /// 查询返回DataTable /// </summary> /// <param name="TableName"></param> /// <param name="FieldString"></param> /// <param name="WhereString"></param> /// <param name="Parameters"></param> /// <returns></returns> public static DataTable QueryGetDT(string TableName, string FieldString, string WhereString, object Parameters = null) { DataTable dt = new DataTable(); string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString; var result = connection.ExecuteReader(sql, Parameters); try { dt.Load(result); } finally { result.Close(); } return dt; } /// <summary> /// 查询返回DataTable /// </summary> /// <param name="criteria"></param> /// <param name="Parameters"></param> /// <returns></returns> public static DataTable QueryGetDT(PageCriteria criteria, object Parameters = null) { DataTable dt = new DataTable(); string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition; var result = connection.ExecuteReader(sql, Parameters); try { dt.Load(result); } finally { result.Close(); } return dt; } /// <summary> /// 查询查询返回list /// </summary> /// <param name="TableName"></param> /// <param name="FieldString"></param> /// <param name="WhereString"></param> /// <param name="Parameters"></param> /// <returns>对象集</returns> public static List<T> Query<T>(string TableName, string FieldString, string WhereString, object Parameters = null) { string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString; var result = connection.Query<T>(sql, Parameters).ToList(); return result; } /// <summary> /// 查询返回list /// </summary> /// <param name="criteria"></param> /// <param name="Parameters"></param> /// <returns></returns> public static List<T> Query<T>(PageCriteria criteria, object Parameters = null) { string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition; var result = connection.Query<T>(sql, Parameters).ToList(); return result; } /// <summary> /// 获取模型 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="TableName"></param> /// <param name="FieldString"></param> /// <param name="WhereString"></param> /// <param name="Parameters"></param> /// <returns></returns> public static T GetModel<T>(string TableName, string FieldString, string WhereString, object Parameters = null) { string sql = "select top 1 " + FieldString + " from " + TableName + " where 1=1 " + WhereString; var result = connection.Query<T>(sql, Parameters).FirstOrDefault(); return result; } /// <summary> /// 获取模型 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="criteria"></param> /// <param name="Parameters"></param> /// <returns></returns> public static T GetModel<T>(PageCriteria criteria, object Parameters = null) { string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition; var result = connection.Query<T>(sql, Parameters).FirstOrDefault(); return result; } /// <summary> /// 获取实体通过实体 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="t">实体对象</param> /// <param name="condition">and field1=@field1 and field2=@field2</param> /// <returns></returns> public static T GetModel<T>(T t, string condition=null) { string TableName = typeof(T).Name; PropertyInfo[] plist = typeof(T).GetProperties(); var type = typeof(T); var keyProperties = KeyPropertiesCache(type).ToList(); if (string.IsNullOrEmpty(condition)){ foreach (var item in keyProperties) { if (!item.PropertyType.IsGenericType) condition =" and " + item.Name + "=@" + item.Name ; } } string field = ""; foreach (PropertyInfo p in plist) { if (!p.PropertyType.IsGenericType) field += p.Name + ","; } field = field.Substring(0, field.Length - 1); string sql = "select top 1 " + field + " from " + TableName + " where 1=1 " + condition; var result = connection.Query<T>(sql, t).FirstOrDefault(); return result; } /// <summary> /// 通用分页获取数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="criteria"></param> /// <param name="param"></param> /// <returns></returns> public static PageDataView<T> GetPageModelList<T>(PageCriteria criteria) { var p = new DynamicParameters(); string proName = "Fgly_Public_Pagination"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Filter", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Order", criteria.Sort); p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Open(); var pageData = new PageDataView<T> { Items = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList() }; connection.Close(); pageData.TotalNum = p.Get<int>("TotalCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return pageData; } /// <summary> /// 分页获取数据 /// </summary> /// <param name="criteria"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable GetPageProc(PageCriteria criteria,ref int TotalCount) { var p = new DynamicParameters(); string proName = "Fgly_Public_Pagination"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Filter", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Order", criteria.Sort); p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output); DataTable dt = new DataTable(); //connection.Open(); var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure); try { dt.Load(result); } finally { result.Close(); } connection.Close(); TotalCount = p.Get<int>("TotalCount"); return dt; } /// <summary> /// 分页获取数据 /// </summary> /// <param name="criteria"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static IList<T> GetPageProc<T>(PageCriteria criteria, ref int TotalCount) { var p = new DynamicParameters(); string proName = "Fgly_Public_Pagination"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Filter", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Order", criteria.Sort); p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Open(); var result = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList(); connection.Close(); TotalCount = p.Get<int>("TotalCount"); return result; } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程并返回List /// </summary> /// <param name="proName"></param> /// <param name="p"></param> /// <returns></returns> public static List<T> ExtProcGetList<T>(string proName, object p = null) { var result = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList(); return result; } /// <summary> /// 执行存储过程并返回DataSet /// </summary> /// <param name="proName"></param> /// <param name="p"></param> /// <returns></returns> public static DataSet ExtProcGetDS(string proName, object p=null) { DataSet ds = new XDataSet(); var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure); try { ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { }); } finally { result.Close(); } return ds; } /// <summary> /// 执行存储过程并返回DataTable /// </summary> /// <param name="proName"></param> /// <param name="p"></param> /// <returns></returns> public static DataTable ExtProcGetDT(string proName, object p = null) { DataTable dt = new DataTable(); var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure); try { dt.Load(result); } finally { result.Close(); } return dt; } /// <summary> /// 执行存储过程返回受影响的行数 /// </summary> /// <param name="proName"></param> /// <param name="p"></param> /// <returns></returns> public static int IntExtProc(string proName,object p = null) { int result = connection.Execute(proName, p, commandType: CommandType.StoredProcedure); return result; } /// <summary> /// 执行存储过程返回一个值 /// </summary> /// <param name="proName"></param> /// <param name="p"></param> /// <returns></returns> public static object ExtProcScale(string proName, object p = null) { object result = connection.ExecuteScalar(proName, p, commandType: CommandType.StoredProcedure); return result; } #endregion #region 执行Sql语句 /// <summary> /// 执行sql返回list /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="p"></param> /// <returns></returns> public static List<T> ExtSqlGetList<T>(string sql,object p = null) { var result = connection.Query<T>(sql, p).ToList(); return result; } /// <summary> /// 执行Sql语句返回模型 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="parameter"></param> /// <returns></returns> public static T ExtSqlGetModel<T>(string sql, object parameter) { var result = connection.Query<T>(sql, parameter).ToList().FirstOrDefault(); return result; } /// <summary> /// 执行sql返回DataSet /// </summary> /// <param name="sql"></param> /// <param name="p"></param> /// <returns></returns> public static DataSet ExtSqlGetDS(string sql,object p = null) { DataSet ds = new XDataSet(); var result = connection.ExecuteReader(sql, p); try { ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { }); } finally { result.Close(); } return ds; } /// <summary> /// 执行SQL返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="p"></param> /// <returns></returns> public static DataTable ExtSqlGetDT(string sql, object p = null) { DataTable dt = new DataTable(); var result = connection.ExecuteReader(sql, p); try { dt.Load(result); } finally { result.Close(); } return dt; } /// <summary> /// 执行SQL返回受影响的行数 /// </summary> /// <param name="sql"></param> /// <param name="p"></param> /// <returns></returns> public static int IntExtSql(string sql, object p = null) { int result = connection.Execute(sql, p); return result; } /// <summary> /// 执行SQL返回一个值 /// </summary> /// <param name="sql"></param> /// <param name="p"></param> /// <returns></returns> public static object ExtSqlScale(string sql,object p = null) { object result = connection.ExecuteScalar(sql, p); return result; } #endregion #region 扩展需要方法 private static List<PropertyInfo> KeyPropertiesCache(Type type) { var allProperties = TypePropertiesCache(type); var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is Dapper.Contrib.Extensions.KeyAttribute)).ToList(); if (keyProperties.Count == 0) { var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase)); if (idProp != null && !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)) { keyProperties.Add(idProp); } } KeyProperties[type.TypeHandle] = keyProperties; return keyProperties; } private static List<PropertyInfo> TypePropertiesCache(Type type) { var properties = type.GetProperties().Where(IsWriteable).ToArray(); TypeProperties[type.TypeHandle] = properties; return properties.ToList(); } private static bool IsWriteable(PropertyInfo pi) { var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList(); if (attributes.Count != 1) return true; var writeAttribute = (WriteAttribute)attributes[0]; return writeAttribute.Write; } #endregion } public class XLoadAdapter : DataAdapter { public XLoadAdapter() { } public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords) { return this.Fill(ds, "Table", dataReader, startRecord, maxRecords); } } public class XDataSet : DataSet { public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables) { XLoadAdapter adapter = new XLoadAdapter { FillLoadOption = loadOption, MissingSchemaAction = MissingSchemaAction.AddWithKey }; if (handler != null) { adapter.FillError += handler; } adapter.FillFromReader(this, reader, 0, 0); if (!reader.IsClosed && !reader.NextResult()) { reader.Close(); } } } }
这个类,包含了对实体操作,执行sql语句,执行存储过程,返回list<T>,DataSet,DataTable,int,object等操作,可能需要你手动添加一些引用,这边在贴出我的条件类,分页类,在这个类中有用到:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FK.Data.Entities { public class PageCriteria { private string _TableName; /// <summary> /// 表名 /// </summary> public string TableName { get { return _TableName; } set { _TableName = value; } } private string _Fileds = "*"; /// <summary> /// 字段(逗号隔开,默认*) /// </summary> public string Fields { get { return _Fileds; } set { _Fileds = value; } } private string _PrimaryKey = "ID"; /// <summary> /// 主键名 /// </summary> public string PrimaryKey { get { return _PrimaryKey; } set { _PrimaryKey = value; } } private int _PageSize = 10; /// <summary> /// 每页记录数,默认10 /// </summary> public int PageSize { get { return _PageSize; } set { _PageSize = value; } } private int _CurrentPage = 1; /// <summary> /// 当前页 /// </summary> public int CurrentPage { get { return _CurrentPage; } set { _CurrentPage = value; } } private string _Sort = string.Empty; /// <summary> /// 排序 /// </summary> public string Sort { get { return _Sort; } set { _Sort = value; } } private string _Condition = string.Empty; /// <summary> /// 条件 /// </summary> public string Condition { get { return _Condition; } set { _Condition = value; } } private int _RecordCount; /// <summary> /// 记录数 /// </summary> public int RecordCount { get { return _RecordCount; } set { _RecordCount = value; } } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FK.Data.Entities { public class PageDataView<T> { private int _TotalNum; public PageDataView() { this._Items = new List<T>(); } /// <summary> /// 记录数 /// </summary> public int TotalNum { get { return _TotalNum; } set { _TotalNum = value; } } private IList<T> _Items; /// <summary> /// 列表 /// </summary> public IList<T> Items { get { return _Items; } set { _Items = value; } } /// <summary> /// 当前页 /// </summary> public int CurrentPage { get; set; } /// <summary> /// 总页数,输出参数 /// </summary> public int TotalPageCount { get; set; } } }
好了,还有什么疑问,或者建议,请在评论中与我讨论,感谢你的阅读,再见
推荐阅读
-
EFCore 通过实体Model生成创建SQL Server数据库表脚本
-
.NET Core实战项目之CMS 第十一章 开发篇-数据库生成及实体代码生成器开发
-
C# T4 模板 数据库实体类生成模板(带注释,娱乐用)
-
NetCore +EFCore+SqlServer根据数据库生成实体类到项目中
-
使用ef6创建oracle数据库的实体模型遇到的问题及解决方案
-
通过T4模板生成数据库实体类,妈妈再也不用担心我用CodeFirst了!!!
-
使用IDEA插件EasyCode实现数据库表反向生成实体类
-
Django生成数据库及添加用户报错解决方案
-
写一个工具生成数据库实体类
-
手把手教你从数据库生成实体类(四)