首页 > 移动平台 > 详细

Dapper记录

时间:2021-09-15 23:15:06      阅读:43      评论:0      收藏:0      [点我收藏+]

日常记录,Dapper(ORM框架)的封装

public static class DBContext
    {
        private static IDbConnection _dbConnection = new MySqlConnection();
        public static string ConnectionString { get; set; }

        public static IDbConnection dbConnection
        {
            get
            {
                if (!string.IsNullOrEmpty(ConnectionString) && string.IsNullOrEmpty(_dbConnection.ConnectionString))
                {
                    _dbConnection.ConnectionString = ConnectionString;
                }
                return _dbConnection;
            }
        }

        public static void AddDbContext(this IServiceCollection service, string connectionString)
        {
            ConnectionString = connectionString;
            service.AddScoped<IDapperHelper,DapperHelper> ();
        }

    }
public interface IDapperHelper
    {
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        T QueryFirst<T>
            (
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class;
         

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓存查询结果</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        IEnumerable<T> Query<T>(
                string sql,
                object param,
                IDbTransaction transaction = null,
                bool buffered = true,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class;


        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        int Execute(
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            );
        
    }
public class DapperHelper : IDapperHelper
    {
        //private IDbConnection _dbConnection = new MySqlConnection();

        //private string ConnectionString => DBContext.ConnectionString;
        //public DapperHelper()
        //{
        //    _dbConnection.ConnectionString = ConnectionString;
        //}
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public T QueryFirst<T>
            (
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class
        {
            if (DBContext.dbConnection.State != ConnectionState.Open)
            {
                DBContext.dbConnection.Open();
            }
            using (transaction = DBContext.dbConnection.BeginTransaction())
            { 
                var res = DBContext.dbConnection.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
                transaction.Commit();
                //DBContext.dbConnection.Close();
                return res;
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓存查询结果</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(
                string sql,
                object param,
                IDbTransaction transaction = null,
                bool buffered = true,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class
        { 
            return DBContext.dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public int Execute(
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            )
        { 
            return DBContext.dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
        }

    }

---dapperExt

public interface IDbExtDapperHelper<T> where T : BaseModel, new()
    {
        IEnumerable<T> GetAll();
        T GetT(T t);
        bool Insert(T t);
        bool Update(T t);
    }
public class DbExtDapperHelper<T> : IDbExtDapperHelper<T> where T : BaseModel, new()
    {
        private IDbConnection dbConnection
        {
            get
            {
                return DBContext.dbConnection;
            }
        }

        public T GetT(T t)
        {
            return dbConnection.Get<T>(t);
        }
        public IEnumerable<T> GetAll()
        {
            var res = dbConnection.GetAll<T>();
            return res;
        }
        public bool Update(T t)
        {
            //var tt = Activator.CreateInstance(typeof(Users));
            return dbConnection.Update<T>(t);
        }
         
        public bool Insert(T t)
        {
            return dbConnection.Insert<T>(t) > 0;
        }
    }

--startup.cs中注入

 services.AddDbContext(Configuration.GetConnectionString("MysqlConnectionString"));
 services.AddScoped<UserDAL>();
 services.AddScoped(typeof(IDbExtDapperHelper<>),typeof(DbExtDapperHelper<>));//泛型注入

--dapperext的特性扩展

    [Table("users")]
    public class Users : BaseModel
    {
        public string Password { get; set; }
        public string UserName { get; set; }
        public int? UserLevel { get; set; }
        public int? IsDelete { get; set; }
    }

自己的一些笔记

dapper的一个源码:

private static T QueryRowImpl<T>(IDbConnection cnn, Row row, ref CommandDefinition command, Type effectiveType)
        {
            object param = command.Parameters;
            var identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, param?.GetType(), null);
            var info = GetCacheInfo(identity, param, command.AddToCache);

            IDbCommand cmd = null;
            IDataReader reader = null;

            bool wasClosed = cnn.State == ConnectionState.Closed;
            try
            {
                cmd = command.SetupCommand(cnn, info.ParamReader);

                if (wasClosed) cnn.Open();//如果使用事务,前面使用时Conn需要打开,dapper没有帮你打开。基础先判断后打开。。
                reader = ExecuteReaderWithFlagsFallback(cmd, wasClosed, (row & Row.Single) != 0
                    ? CommandBehavior.SequentialAccess | CommandBehavior.SingleResult // need to allow multiple rows, to check fail condition
                    : CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow);
                wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader

                T result = default(T);
                if (reader.Read() && reader.FieldCount != 0)
                {
                    // with the CloseConnection flag, so the reader will deal with the connection; we
                    // still need something in the "finally" to ensure that broken SQL still results
                    // in the connection closing itself
                    var tuple = info.Deserializer;
                    int hash = GetColumnHash(reader);
                    if (tuple.Func == null || tuple.Hash != hash)
                    {
                        tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false));
                        if (command.AddToCache) SetQueryCache(identity, info);
                    }

                    var func = tuple.Func;
                    object val = func(reader);
                    if (val == null || val is T)
                    {
                        result = (T)val;
                    }
                    else
                    {
                        var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;
                        result = (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture);
                    }
                    if ((row & Row.Single) != 0 && reader.Read()) ThrowMultipleRows(row);
                    while (reader.Read()) { }
                }
                else if ((row & Row.FirstOrDefault) == 0) // demanding a row, and don‘t have one
                {
                    ThrowZeroRows(row);
                }
                while (reader.NextResult()) { }
                // happy path; close the reader cleanly - no
                // need for "Cancel" etc
                reader.Dispose();
                reader = null;

                command.OnCompleted();
                return result;
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed) try { cmd.Cancel(); }
                        catch { /* don‘t spoil the existing exception */ }
                    reader.Dispose();
                }
                if (wasClosed) cnn.Close();
                cmd?.Dispose();
            }
        }

--dapper.controb的一个扩展源码

        /// <summary>
        /// Returns a single entity by a single id from table "Ts".  
        /// Id must be marked with [Key] attribute.
        /// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
        /// for optimal performance. 
        /// </summary>
        /// <typeparam name="T">Interface or type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <param name="transaction">The transaction to run under, null (the default) if none</param>
        /// <param name="commandTimeout">Number of seconds before command execution timeout</param>
        /// <returns>Entity of T</returns>
        public static T Get<T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);

            string sql;
            if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            {
                var key = GetSingleKey<T>(nameof(Get));
                var name = GetTableName(type);

                sql = $"select * from {name} where {key.Name} = @id";//组装成sql,调用dapper基础使用方法
                GetQueries[type.TypeHandle] = sql;
            }

            var dynParms = new DynamicParameters();
            dynParms.Add("@id", id);

            T obj;

            if (type.IsInterface())
            {
                var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary<string, object>;

                if (res == null)
                    return null;

                obj = ProxyGenerator.GetInterfaceProxy<T>();

                foreach (var property in TypePropertiesCache(type))
                {
                    var val = res[property.Name];
                    property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
                }

                ((IProxy)obj).IsDirty = false;   //reset change tracking and return
            }
            else
            {
                obj = connection.Query<T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
            }
            return obj;
        }

 

---其他记录

Dapper---
轻量级==?简单。


1.Dapper
  Dapper.Contrib(扩展T)
  MySqlClient(对mySql的支持)
2.C# 连接Mysql 字符串
Database=bbs;Data Source=127.0.0.1;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306


3.设施层进化思路:
 1)在哪里,什么时候获取ConnectionString,
 2)静态实例,属性获取这个静态实例(get时给这个静态实例加属性,如连接字符串)。单例模式。
 3)IServiceCollection扩展下,注入到startup.cs中。
4.配置连接字符串mysql
  "ConnectionStrings": {
    "MysqlConnectionString": "Database=bbs;Data Source=localhost;User Id=root;Password=root;port=3306;SslMode=none;"
  },
5.写dbhelper
public static class DBContext
    {
        private static IDbConnection _dbConnection = new MySqlConnection();
        public static string ConnectionString { get; set; }

        public static IDbConnection dbConnection
        {
            get
            {
               if (!string.IsNullOrEmpty(ConnectionString) && string.IsNullOrEmpty(_dbConnection.ConnectionString))
                {
                    _dbConnection.ConnectionString = ConnectionString;
                }
                return _dbConnection;
            }
        }

        public static void AddDbContext(this IServiceCollection service, string connectionString)
        {
            ConnectionString = connectionString;
            service.AddScoped<IDapperHelper,DapperHelper> ();
        }

    }
-----------
public interface IDapperHelper
    {
 
        T QueryFirst<T>
            (
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class;
         
        IEnumerable<T> Query<T>(
                string sql,
                object param,
                IDbTransaction transaction = null,
                bool buffered = true,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class;
 
        int Execute(
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            );
        
    }
 ------
 public class DapperHelper : IDapperHelper
    {
        //private IDbConnection _dbConnection = new MySqlConnection();
        //
        //private string ConnectionString => DBContext.ConnectionString;
        //public DapperHelper()
        //{
        //    _dbConnection.ConnectionString = ConnectionString;
        //}
        
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public T QueryFirst<T>
            (
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class
        {
            if (DBContext.dbConnection.State != ConnectionState.Open)//这里我也忘了。。
            {
                DBContext.dbConnection.Open();
            }
            using (transaction = DBContext.dbConnection.BeginTransaction())
            { 
                var res = DBContext.dbConnection.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
                transaction.Commit();
                DBContext.dbConnection.Close();
                return res;
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓存查询结果</param>
        /// <param name="commandTimeout">查询超时时间</param>
        /// <param name="commandType">sql语句类型</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(
                string sql,
                object param,
                IDbTransaction transaction = null,
                bool buffered = true,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            ) where T : class
        { 
            return DBContext.dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public int Execute(
                string sql,
                object param,
                IDbTransaction transaction = null,
                int? commandTimeout = null,
                CommandType commandType = CommandType.Text
            )
        { 
            return DBContext.dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
        }
    }
6,startup.cs中:
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext(Configuration.GetConnectionString("MysqlConnectionString"));
    services.AddScoped<UserDAL>();
    
}
7,controller中使用:
 public class UsersController : ControllerBase
{
    private readonly UserDAL _userDAL = null;
    public UsersController(UserDAL userDAL)
    {
        _userDAL = userDAL;
    }

    [HttpGet("UserName/Password")]
    public Users Get(string UserName,string Password)
    {
        return _userDAL.GetUserByLogin(UserName,Password);
    }
}
 
----注入模式记录。。
AddTransient瞬时模式:每次请求,都获取一个新的实例。即使同一个请求获取多次也会是不同的实例
AddScoped:每次请求,都获取一个新的实例。同一个请求获取多次会得到相同的实例
AddSingleton单例模式:每次都获取同一个实例


--Dapper源码解读:
        /// <summary>
        /// Executes a single-row query, returning the data typed as per T
        /// </summary>
        /// <returns>A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
        /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
        /// </returns>
        public static T QuerySingle<T>(
            this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
        )
        {
            var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.None);
            return QueryRowImpl<T>(cnn, Row.Single, ref command, typeof(T));
        }
--
 /// <summary>
        /// Initialize the command definition--给私有化变量赋值
        /// </summary>
        public CommandDefinition(string commandText, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null,
                                 CommandType? commandType = null, CommandFlags flags = CommandFlags.Buffered
#if ASYNC
                                 , CancellationToken cancellationToken = default(CancellationToken)
#endif
            )
        {
            CommandText = commandText;
            Parameters = parameters;
            Transaction = transaction;
            CommandTimeout = commandTimeout;
            CommandType = commandType;
            Flags = flags;
#if ASYNC
            CancellationToken = cancellationToken;
#endif
        }
-----
      private static T QueryRowImpl<T>(IDbConnection cnn, Row row, ref CommandDefinition command, Type effectiveType)
        {
            object param = command.Parameters;
            var identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, param?.GetType(), null);
            var info = GetCacheInfo(identity, param, command.AddToCache);

            IDbCommand cmd = null;
            IDataReader reader = null;

            bool wasClosed = cnn.State == ConnectionState.Closed;
            try
            {
                cmd = command.SetupCommand(cnn, info.ParamReader);

                if (wasClosed) cnn.Open();
                reader = ExecuteReaderWithFlagsFallback(cmd, wasClosed, (row & Row.Single) != 0
                    ? CommandBehavior.SequentialAccess | CommandBehavior.SingleResult // need to allow multiple rows, to check fail condition
                    : CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow);
                wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader

                T result = default(T);
                if (reader.Read() && reader.FieldCount != 0)
                {
                    // with the CloseConnection flag, so the reader will deal with the connection; we
                    // still need something in the "finally" to ensure that broken SQL still results
                    // in the connection closing itself
                    var tuple = info.Deserializer;
                    int hash = GetColumnHash(reader);
                    if (tuple.Func == null || tuple.Hash != hash)
                    {
                        tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false));
                        if (command.AddToCache) SetQueryCache(identity, info);
                    }

                    var func = tuple.Func;
                    object val = func(reader);
                    if (val == null || val is T)
                    {
                        result = (T)val;
                    }
                    else
                    {
                        var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;
                        result = (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture);
                    }
                    if ((row & Row.Single) != 0 && reader.Read()) ThrowMultipleRows(row);
                    while (reader.Read()) { }
                }
                else if ((row & Row.FirstOrDefault) == 0) // demanding a row, and don‘t have one
                {
                    ThrowZeroRows(row);
                }
                while (reader.NextResult()) { }
                // happy path; close the reader cleanly - no
                // need for "Cancel" etc
                reader.Dispose();
                reader = null;

                command.OnCompleted();
                return result;
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed) try { cmd.Cancel(); }
                        catch { /* don‘t spoil the existing exception */ }
                    reader.Dispose();
                }
                if (wasClosed) cnn.Close();
                cmd?.Dispose();
            }
        }
        
        
        
        

 

Dapper记录

原文:https://www.cnblogs.com/Insist-Y/p/15268061.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!