日常记录，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(); } }