【配置化】C# dapper是怎么實現(xiàn)的？精短ORM
目錄
一、什么是dapper
二、實現(xiàn)問題與思路 & 源碼參考
三、小結(jié)
一、什么是dapper
dapper是個組件，一個dll文件，可以通過NuGet下載。
作用：快速訪問數(shù)據(jù)庫并自動完成數(shù)據(jù)庫表對象到C#類對象的字段映射。
應用：對于要完成簡單的查詢、報表之類的應用是非常快捷、方便的。
比較：
舊有讀取數(shù)據(jù)的方式：
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 }
需要一行行寫對應字段，就算可以通過一些手段自動生成，也是不耐其煩。
使用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就可以了。
二、實現(xiàn)思路
因為數(shù)據(jù)庫表列名 = 類字段名稱，很容易得出，映射是這樣的：
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 }
這就結(jié)束戰(zhàn)斗了？其實還有2個問題。
1、 獲取數(shù)據(jù)庫連接
2、 如果T傳入的是int、string或者是結(jié)構(gòu)體類型，如何處理？
針對問題1：獲取數(shù)據(jù)庫連接配置
對于數(shù)據(jù)庫的連接上，一開始我是這么寫的，只要輕輕地 DbSetting.Get(db) 就可以得到連接串
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 }
但很快遇到問題，我寫了4個連接串，分別是兩種類型的數(shù)據(jù)庫，而不同數(shù)據(jù)庫的連接需要引用對應的組件，
像是連接oracle，需要Oracle.ManagedDataAccess；連接MySQL，需引用MySql.Data，
不同數(shù)據(jù)庫類型，DBConnection對象會不一樣；我要如何自動匹配到需要使用哪個DBConnection類呢？
可以比較一下二者是怎么完成數(shù)據(jù)庫連接的：
|
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 |
可以推斷出，數(shù)據(jù)庫連接并且讀取數(shù)據(jù)，都需要實現(xiàn) IDbConnection、IDbCommand、IDataReader這三個接口。
歸納一下，完成“連接數(shù)據(jù)庫”，需要知道連接串、數(shù)據(jù)庫類型、DbConnection類、DbCommand類、DataReader類
可以設計一個對象，攜帶好這些配置。
維護數(shù)據(jù)庫的信息就變得簡單，只需要配置連接串 + 數(shù)據(jù)庫類型，即一句 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>這種類型的數(shù)據(jù)？
如果能知道這個對象是“類”還是“值對象”，會好處理。注意，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 var dr = conn.Command.ExecuteReader(); 12 while (dr.Read()) 13 { 14 if (isValueType) 15 { 16 var val = Convert.ChangeType(dr.GetString(0), typeof(T)); 17 result.Add((T)val); 18 } 19 else 20 { 21 var obj = Activator.CreateInstance(typeof(T)); 22 foreach (PropertyInfo field in properties) 23 { 24 //Console.WriteLine($"{field.Name} {field.PropertyType}"); 25 int index = -1; 26 try 27 { 28 index = dr.GetOrdinal(field.Name); 29 } 30 catch (IndexOutOfRangeException ex) { } 31 32 if (index > -1) 33 { 34 var val = dr.GetValue(index); 35 if (!(val is DBNull)) 36 typeof(T).GetProperty(field.Name).SetValue(obj, val); 37 } 38 } 39 result.Add((T)obj); 40 } 41 } 42 dr.Close(); 43 44 } 45 } 46 catch (Exception ex) 47 { 48 Console.WriteLine($"query failed : {ex.Message}"); 49 } 50 51 return result; 52 }
寫完之后，比較了一下dapper的實現(xiàn)，可以校驗我的思考上是不是有遺漏。
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)；
數(shù)據(jù)庫訪問里面，最需要關注的對象就是： DbConnection， DbCommand， DataReader
reader的實現(xiàn)在這里；
reader結(jié)果是有列的；
反編譯出來的IL語言， 代碼跳轉(zhuǎn)到 IL_016c 處；
IL_016c 在這里；
val 是一行數(shù)據(jù)； 關鍵代碼，進去看看
|
|
[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); } } |
如果一行里面的數(shù)據(jù)類型就是T，代表就是一個列的值對象
如果一行數(shù)據(jù)里面有很多數(shù)據(jù)
|
三、小結(jié)
寫完代碼的時候很興奮發(fā)給同事看，但他只是說“不就數(shù)據(jù)庫連接嗎，運行sql拿結(jié)果”。
他這么說是沒錯，但也挺潑冷水；讓我不得不思考一下我代碼的優(yōu)勝之處。
1、 數(shù)據(jù)庫配置化
在查詢中，要切換數(shù)據(jù)庫，只要修改第一個參數(shù)即可
var list = Query<project>(DB.NS_DS, "select * from ds.project");
維護數(shù)據(jù)庫的語句，寫入連接串 + 數(shù)據(jù)庫類型
public static DB M_DS = new DB("連接串", "MYSQL");
2、 解決了不同數(shù)據(jù)庫需要不同DbConnection類的問題
connection對象使用IDbConnection的接口類型
3、不需要逐個寫數(shù)據(jù)庫表對象到代碼類的字段映射
浙公網(wǎng)安備 33010602011771號