【配置化】C# dapper是怎么实现的?精短ORM

  • 【配置化】C# dapper是怎么实现的?精短ORM已关闭评论
  • 34 次浏览
  • A+
所属分类:.NET技术
摘要

一、什么是dapper二、实现问题与思路 & 源码参考三、小结 dapper是个组件,一个dll文件,可以通过NuGet下载。


目录

一、什么是dapper

二、实现问题与思路 & 源码参考

三、小结

 

一、什么是dapper

dapper是个组件,一个dll文件,可以通过NuGet下载。

作用:快速访问数据库并自动完成数据库表对象到C#类对象的字段映射。

应用:对于要完成简单的查询、报表之类的应用是非常快捷、方便的。

比较:

旧有读取数据的方式:

【配置化】C# dapper是怎么实现的?精短ORM【配置化】C# dapper是怎么实现的?精短ORM

 1         private static List<JsonData> getData()  2         {  3             List<JsonData> result = new List<JsonData>();  4             var ds = oracleHelper.getResult("select * from test.scheduleSQL where dwd_nm is not null");  5             StringBuilder sb = new StringBuilder();  6             try  7             {  8                 foreach (DataRow dr in ds.Tables[0].Rows)  9                 { 10                     var entity = new JsonData(); 11                     entity.TB_DES = dr["TB_DES"].ToString(); 12  13                     entity.DB_ID = dr["DB_ID"].ToString(); 14                     entity.SOURCE_OWNER = dr["SOURCE_OWNER"].ToString(); 15                     entity.SOURCE_NM = dr["SOURCE_NM"].ToString(); 16                      17                     entity.ODS_NM = dr["ODS_NM"].ToString(); 18                     entity.TMP_NM = dr["TMP_NM"].ToString(); 19                     entity.TMPVIEW_NM = dr["TMPVIEW_NM"].ToString(); 20                     entity.DWD_NM = dr["DWD_NM"].ToString(); 21                      22                     entity.EXTRACT_SQL = dr["EXTRACT_SQL"].ToString(); 23                     entity.TOODS_SQL = dr["TOODS_SQL"].ToString(); 24                     entity.TODWD_SQL = dr["TODWD_SQL"].ToString(); 25  26                     result.Add(entity); 27                 } 28             } 29             catch (Exception ex) 30             { } 31             return result; 32         }

View Code

需要一行行写对应字段,就算可以通过一些手段自动生成,也是不耐其烦。

使用dapper,它封装了字段映射:

 1         public static void MySQLConn()  2         {  3             try  4             {  5                 var result = DapperHelper.Query<project>(DB.NS_DS, "select * from ds.project ");  6                 Console.WriteLine($"COUNT : {result.Count.ToString()}");  7             }  8             catch (Exception ex)  9             { 10                 Console.WriteLine(ex.Message); 11             } 12         } 13  14         public static List<T> Query<T>(string connectionString, string sql) 15         { 16             List<T> result = new List<T>(); 17             try 18             { 19                 using (var conn = new MySqlConnection(connectionString)) 20                 { 21                     result = conn.Query<T>(sql).ToList(); 22                 } 23             } 24             catch (Exception ex) 25             { 26                 Console.WriteLine($"Query failed : {ex.Message} ."); 27             } 28             return result; 29         } 30  31         private class project 32         { 33             public string id { get; set; } 34             public string name { get; set; } 35         }

简单的一句 Query<T>(sql),传入要自动映射到的类型T,以及查询的sql就可以了。

 

二、实现思路

因为数据库表列名 = 类字段名称,很容易得出,映射是这样的:

entity.字段 = dataReader[字段]

 1 public static IList<T> Query<T>(string sql)  2 {  3      var result = new List<T>();  4      var properties = typeof(T).GetProperties();  5      while (dataReader.Read())  6           foreach (PropertyInfo field in properties)  7           {  8                object val = Convert.ChangeType(dataReader.GetString(index), field.PropertyType);  9                typeof(T).GetProperty(field.Name).SetValue(obj, val); 10           } 11           result.Add((T)obj); 12      } 13 }

这就结束战斗了?其实还有2个问题。

1、  获取数据库连接

2、  如果T传入的是int、string或者是结构体类型,如何处理?

 

针对问题1:获取数据库连接配置

对于数据库的连接上,一开始我是这么写的,只要轻轻地 DbSetting.Get(db) 就可以得到连接串

【配置化】C# dapper是怎么实现的?精短ORM【配置化】C# dapper是怎么实现的?精短ORM

 1     public enum Dbs  2     {  3         M_CD = 0,  4         M_DS = 1,  5         NS_CD = 2,  6         NS_DS = 3  7     }  8   9     public class DbSetting 10     { 11         private static IDictionary<Enum, string> _dic; 12  13         private static string M_DS = "Database='ds';Data Source='10.168.1.1';User Id='test';Password='123456'"; 14         private static string NS_DS = "Database='ds';Data Source='10.24.1.1';User Id='test';Password='123456'"; 15         private static string M_CD = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.168.1.2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=otest)));User Id=test;Password=123456"; 16         private static string NS_CD = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.24.1.2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test)));User Id=test;Password=123456"; 17  18         public static string Get(Enum db) 19         { 20             if (_dic == null) init(); 21             return _dic[db]; 22         } 23  24         private static void init() 25         { 26             _dic = new Dictionary<Enum, string>(); 27             _dic.Add(Dbs.M_DS, M_DS); 28             _dic.Add(Dbs.NS_DS, NS_DS); 29             _dic.Add(Dbs.M_CD, M_CD); 30             _dic.Add(Dbs.NS_CD, NS_CD); 31         } 32     }

View Code

但很快遇到问题,我写了4个连接串,分别是两种类型的数据库,而不同数据库的连接需要引用对应的组件,

像是连接oracle,需要Oracle.ManagedDataAccess;连接MySQL,需引用MySql.Data,

不同数据库类型,DBConnection对象会不一样;我要如何自动匹配到需要使用哪个DBConnection类呢?

可以比较一下二者是怎么完成数据库连接的:

using (MySqlConnection conn = getConn(db))

{

    MySqlCommand cmd = new MySqlCommand(sql, conn);

    MySqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())

    {

        string id = reader.GetString("id");

        string name = reader.GetString("name");

        //。。

     }

        reader.Close();

        conn.Close();

 }

using (OracleConnection conn = connectDB())

{

    OracleCommand cmd = new OracleCommand(sql, conn);

    OracleDataReader od = cmd.ExecuteReader();

    //。。

}

注意 xxConnection

 

注意 xxCommand

注意 xxDataReader

可以推断出,数据库连接并且读取数据,都需要实现 IDbConnection、IDbCommand、IDataReader这三个接口。

归纳一下,完成“连接数据库”,需要知道连接串、数据库类型、DbConnection类、DbCommand类、DataReader类

可以设计一个对象,携带好这些配置。

维护数据库的信息就变得简单,只需要配置连接串 + 数据库类型,即一句 public static DB M_DS = new DB("连接串", "MYSQL");

 1     internal class DB  2     {  3         public static DB M_DS = new DB("连接串", "MYSQL");  4         public static DB NS_DS = new DB("连接串", "MYSQL");  5         public static DB M_CD = new DB("连接串", "ORACLE");  6         public static DB NS_CD = new DB("连接串", "ORACLE");  7   8         public string ConnStr { get; private set; }  9         public string DbType { get; private set; } 10  11         internal DB(string conn, string dbType) 12         { 13             ConnStr = conn; 14             DbType = dbType; 15         } 16  17         public bool IsOracle { get => DbType == "ORACLE"; } 18         public bool IsMySQL { get => DbType == "MYSQL"; } 19     } 20  21     public class DbRT : IDisposable 22     { 23         private string _connStr; 24          25  26         private connType _dbType; 27         internal DbRT(DB db, string sql = null) 28         { 29             _connStr = db.ConnStr; 30             if (db.IsOracle) 31                 _dbType = new connType(typeof(OracleConnection), typeof(OracleCommand)); 32             else if (db.IsMySQL) 33                 _dbType = new connType(typeof(MySqlConnection), typeof(MySqlCommand)); 34  35             init(sql); 36         } 37  38         private void init(string sql = null) 39         { 40             _conn = Activator.CreateInstance(_dbType.conn, _connStr) as DbConnection; 41             _conn.Open(); 42  43             if (sql != null) 44                 SetCommand(sql); 45         } 46  47         private IDbConnection _conn; 48         private IDbCommand _cmd; 49         private IDataReader _dr; 50         public IDataReader DataReader { get => _dr; } 51         public void SetCommand(string sql) 52         { 53             if (_dr != null) _dr.Dispose(); 54             if (_cmd != null) _cmd.Dispose(); 55  56             _cmd = Activator.CreateInstance(_dbType.cmd, sql, _conn) as DbCommand; 57             _dr = _cmd.ExecuteReader(); 58         } 59  60         private class connType 61         { 62             public Type conn { get; set; } 63  64             public Type cmd { get; set; } 65             public connType(Type conn, Type cmd) 66             { 67                 this.conn = conn; 68                 this.cmd = cmd; 69             } 70         } 71         public void Dispose() 72         { 73             if (_dr != null) 74                 _dr.Dispose(); 75             if (_cmd != null) 76                 _cmd.Dispose(); 77             if (_conn != null) 78                 _conn.Dispose(); 79         } 80     }

针对问题2:如何处理返回List<string>、List<int>、List<projectStruct>这种类型的数据?

如果能知道这个对象是“类”还是“值对象”,会好处理。注意,string看似值对象,但其实是特殊的 “类”。

bool isValueType = (typeof(T).IsValueType && !properties.Any()) || typeof(T) == typeof(string);

完整Query<T>代码:

var list = Query<project>(DB.NS_DS, "select * from ds.project");

 1         public static IList<T> Query<T>(DB db, string sql)  2         {  3             var result = new List<T>();  4             try  5             {  6                 var properties = typeof(T).GetProperties();  7                 bool isValueType = (typeof(T).IsValueType && !properties.Any()) || typeof(T) == typeof(string);  8                   9                 using (var conn = new DbRT(db, sql)) 10                 { 11                     while (conn.DataReader.Read()) 12                     { 13                         if (isValueType) 14                         { 15                             var val = Convert.ChangeType(conn.DataReader.GetString(0), typeof(T)); 16                             result.Add((T)val); 17                         } 18                         else 19                         { 20                             var obj = Activator.CreateInstance(typeof(T)); 21                             foreach (PropertyInfo field in properties) 22                             { 23                                 //Console.WriteLine($"{field.Name} {field.PropertyType}"); 24                                 int index = -1; 25                                 try 26                                 { 27                                     index = conn.DataReader.GetOrdinal(field.Name); 28                                 } 29                                 catch (IndexOutOfRangeException ex) { } 30  31                                 if (index > -1) 32                                 { 33                                     var val = Convert.ChangeType(conn.DataReader.GetString(index), field.PropertyType); 34                                     typeof(T).GetProperty(field.Name).SetValue(obj, val); 35                                 } 36                             } 37                             result.Add((T)obj); 38                         } 39                     } 40                 } 41             } 42             catch (Exception ex) 43             { 44                 Console.WriteLine($"query failed : {ex.Message}"); 45             } 46              47             return result; 48         }

写完之后,比较了一下dapper的实现,可以校验我的思考上是不是有遗漏。

visual studio2022反编译dapper.dll,version=2.0.0.0代码参考:

        public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)

        {

            CommandDefinition command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, buffered ? CommandFlags.Buffered : CommandFlags.None);

            IEnumerable<T> enumerable = cnn.QueryImpl<T>(command, typeof(T));

            if (!command.Buffered)

            {

                return enumerable;

            }

 

            return enumerable.ToList();

        }

 

 

 

初始化配置,暂且无视

 

关键语句,进去看看

 

 

 

        private static IEnumerable<T> QueryImpl<T>(this IDbConnection cnn, CommandDefinition command, Type effectiveType)

        {

            object parameters = command.Parameters;

            Identity identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, parameters?.GetType());

            CacheInfo cacheInfo = GetCacheInfo(identity, parameters, command.AddToCache);

            IDbCommand cmd = null;

            IDataReader reader = null;

            bool wasClosed = cnn.State == ConnectionState.Closed;

            try

            {

                cmd = command.SetupCommand(cnn, cacheInfo.ParamReader);

                if (wasClosed)

                {

                    cnn.Open();

                }

 

                reader = ExecuteReaderWithFlagsFallback(cmd, wasClosed, CommandBehavior.SingleResult | CommandBehavior.SequentialAccess);

                wasClosed = false;

                DeserializerState deserializerState = cacheInfo.Deserializer;

                int columnHash = GetColumnHash(reader);

                if (deserializerState.Func != null && deserializerState.Hash == columnHash)

                {

                    goto IL_016c;

                }

 

                if (reader.FieldCount != 0)

                {

                    DeserializerState deserializerState3 = cacheInfo.Deserializer = new DeserializerState(columnHash, GetDeserializer(effectiveType, reader, 0, -1, returnNullIfFirstMissing: false));

                    deserializerState = deserializerState3;

                    if (command.AddToCache)

                    {

                        SetQueryCache(identity, cacheInfo);

                    }

 

                    goto IL_016c;

                }

 

                goto end_IL_0098;

            IL_016c:

                Func<IDataReader, object> func = deserializerState.Func;

                _ = (Nullable.GetUnderlyingType(effectiveType) ?? effectiveType);

                while (reader.Read())

                {

                    object val = func(reader);

                    yield return GetValue<T>(reader, effectiveType, val);

                }

 

                while (reader.NextResult())

                {

                }

 

                reader.Dispose();

                reader = null;

                command.OnCompleted();

            end_IL_0098:;

            }

            finally

            {

                if (reader != null)

                {

                    if (!reader.IsClosed)

                    {

                        try

                        {

                            cmd.Cancel();

                        }

                        catch

                        {

                        }

                    }

 

                    reader.Dispose();

                }

 

                if (wasClosed)

                {

                    cnn.Close();

                }

 

                cmd?.Parameters.Clear();

                cmd?.Dispose();

            }

        }

 

已知,

effectiveType就是typeof(T)

 

 数据库访问里面,最需要关注的对象就是:

DbConnection

DbCommand

DataReader

 

 

 

 

 

 

 

 

 

reader的实现在这里;

 

 

 

 

 

 

 

 

 

reader结果是有列的;

 

 

 

 

 

 

 

反编译出来的IL语言,

代码跳转到 IL_016c 处;

 

 

IL_016c 在这里;

 

 

 

 

val 是一行数据;

关键代码,进去看看

 

 

 

        [MethodImpl(MethodImplOptions.AggressiveInlining)]

        private static T GetValue<T>(IDataReader reader, Type effectiveType, object val)

        {

            if (val is T)

            {

                return (T)val;

            }

 

            if (val == null && (!effectiveType.IsValueType || Nullable.GetUnderlyingType(effectiveType) != null))

            {

                return default(T);

            }

 

            Array array = val as Array;

            if (array != null && typeof(T).IsArray)

            {

                Type elementType = typeof(T).GetElementType();

                Array array2 = Array.CreateInstance(elementType, array.Length);

                for (int i = 0; i < array.Length; i++)

                {

                    array2.SetValue(Convert.ChangeType(array.GetValue(i), elementType, CultureInfo.InvariantCulture), i);

                }

 

                return (T)(object)array2;

            }

 

            try

            {

                Type conversionType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;

                return (T)Convert.ChangeType(val, conversionType, CultureInfo.InvariantCulture);

            }

            catch (Exception ex)

            {

                ThrowDataException(ex, 0, reader, val);

                return default(T);

            }

        }

 

 

 

如果一行里面的数据类型就是T,代表就是一个列的值对象

 

 

 

 

 

 

 

 

如果一行数据里面有很多数据

 

 

三、小结

写完代码的时候很兴奋发给同事看,但他只是说“不就数据库连接吗,运行sql拿结果”。

他这么说是没错,但也挺泼冷水;让我不得不思考一下我代码的优胜之处。

1、  数据库配置化

在查询中,要切换数据库,只要修改第一个参数即可

var list = Query<project>(DB.NS_DS, "select * from ds.project");

维护数据库的语句,写入连接串 + 数据库类型

public static DB M_DS = new DB("连接串", "MYSQL");

 

2、  解决了不同数据库需要不同DbConnection类的问题

connection对象使用IDbConnection的接口类型

 

3、不需要逐个写数据库表对象到代码类的字段映射