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 + "[email protected]" + 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 + "[email protected]" + 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 + "[email protected]" + p.Name + ",";
    }
    updatestr = updatestr.Substring(0, updatestr.Length - 1);
    string condition = "";
    foreach (var item in keyProperties)
    {
    if (!item.PropertyType.IsGenericType)
    condition = item.Name + "[email protected]" + 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 [email protected] and [email protected]</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 + "[email protected]" + 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; }
    }
    }

      好了,还有什么疑问,或者建议,请在评论中与我讨论,感谢你的阅读,再见

    以上就是:Dapper.Contrib拓展及数据库生成实体 的全部内容。

    本站部分内容来源于互联网和用户投稿,如有侵权请联系我们删除,谢谢。
    Email:[email protected]


    0 条回复 A 作者 M 管理员
      所有的伟大,都源于一个勇敢的开始!
    欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论