基於Dapper二次封裝了一個易用的ORM工具類：SqlDapperUtil,把日常能用到的各種CRUD都進行了簡化封裝，讓普通程式員只需關註業務即可，因為非常簡單，故直接貼源代碼，大家若需使用可以直接複製到項目中，該SqlDapperUtil已廣泛用於公司項目中。 ColumnAttributeT ...
基於Dapper二次封裝了一個易用的ORM工具類：SqlDapperUtil,把日常能用到的各種CRUD都進行了簡化封裝，讓普通程式員只需關註業務即可，因為非常簡單，故直接貼源代碼，大家若需使用可以直接複製到項目中，該SqlDapperUtil已廣泛用於公司項目中。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.IO;
using System.Collections.Concurrent;
using System.Data.SqlClient;
namespace Zuowj.Common
{
/// <summary>
/// 基於Dapper的數據操作類封裝的工具類
/// Author：左文俊
/// Date：2017/12/11
/// </summary>
public class SqlDapperUtil
{
private static string dbConnectionStringConfigPath = null;
private readonly static ConcurrentDictionary<string, bool> dbConnNamesCacheDic = new ConcurrentDictionary<string, bool>();
private string dbConnectionName = null;
private string dbConnectionString = null;
private string dbProviderName = null;
private IDbConnection dbConnection = null;
private bool useDbTransaction = false;
private IDbTransaction dbTransaction = null;
#region 私有方法
private IDbConnection GetDbConnection()
{
bool needCreateNew = false;
if (dbConnection == null || string.IsNullOrWhiteSpace(dbConnection.ConnectionString))
{
needCreateNew = true;
}
else if (!MemoryCacheUtil.Contains(dbConnectionName))
{
needCreateNew = true;
}
if (needCreateNew)
{
dbConnectionString = GetDbConnectionString(dbConnectionName, out dbProviderName);
var dbProviderFactory = DbProviderFactories.GetFactory(dbProviderName);
dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = dbConnectionString;
}
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
return dbConnection;
}
private string GetDbConnectionString(string dbConnName, out string dbProviderName)
{
//如果指定的連接字元串配置文件路徑，則創建緩存依賴，一旦配置文件更改就失效，再重新讀取
string[] connInfos = MemoryCacheUtil.GetOrAddCacheItem(dbConnName, () =>
{
var connStrSettings = ConfigUtil.GetConnectionStringForConfigPath(dbConnName, SqlDapperUtil.DbConnectionStringConfigPath);
string dbProdName = connStrSettings.ProviderName;
string dbConnStr = connStrSettings.ConnectionString;
//LogUtil.Info(string.Format("SqlDapperUtil.GetDbConnectionString>讀取連接字元串配置節點[{0}]:{1},ProviderName:{2}", dbConnName, dbConnStr, dbProdName), "SqlDapperUtil.GetDbConnectionString");
return new[] { EncryptUtil.Decrypt(dbConnStr), dbProdName };
}, SqlDapperUtil.DbConnectionStringConfigPath);
dbProviderName = connInfos[1];
return connInfos[0];
}
private T UseDbConnection<T>(Func<IDbConnection, T> queryOrExecSqlFunc)
{
IDbConnection dbConn = null;
try
{
Type modelType = typeof(T);
var typeMap = Dapper.SqlMapper.GetTypeMap(modelType);
if (typeMap == null || !(typeMap is ColumnAttributeTypeMapper<T>))
{
Dapper.SqlMapper.SetTypeMap(modelType, new ColumnAttributeTypeMapper<T>());
}
dbConn = GetDbConnection();
if (useDbTransaction && dbTransaction == null)
{
dbTransaction = GetDbTransaction();
}
return queryOrExecSqlFunc(dbConn);
}
catch
{
throw;
}
finally
{
if (dbTransaction == null && dbConn != null)
{
CloseDbConnection(dbConn);
}
}
}
private void CloseDbConnection(IDbConnection dbConn, bool disposed = false)
{
if (dbConn != null)
{
if (disposed && dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction.Dispose();
dbTransaction = null;
}
if (dbConn.State != ConnectionState.Closed)
{
dbConn.Close();
}
dbConn.Dispose();
dbConn = null;
}
}
/// <summary>
/// 獲取一個事務對象（如果需要確保多條執行語句的一致性，必需使用事務）
/// </summary>
/// <param name="il"></param>
/// <returns></returns>
private IDbTransaction GetDbTransaction(IsolationLevel il = IsolationLevel.Unspecified)
{
return GetDbConnection().BeginTransaction(il);
}
private DynamicParameters ToDynamicParameters(Dictionary<string, object> paramDic)
{
return new DynamicParameters(paramDic);
}
#endregion
public static string DbConnectionStringConfigPath
{
get
{
if (string.IsNullOrEmpty(dbConnectionStringConfigPath))//如果沒有指定配置文件，則取預設的配置文件路徑作為緩存依賴路徑
{
dbConnectionStringConfigPath = BaseUtil.GetConfigPath();
}
return dbConnectionStringConfigPath;
}
set
{
if (!string.IsNullOrWhiteSpace(value) && !File.Exists(value))
{
throw new FileNotFoundException("指定的DB連接字元串配置文件不存在：" + value);
}
//如果配置文件改變，則可能導致連接字元串改變，故必需清除所有連接字元串的緩存以便後續重新載入字元串
if (!string.Equals(dbConnectionStringConfigPath, value, StringComparison.OrdinalIgnoreCase))
{
foreach (var item in dbConnNamesCacheDic)
{
MemoryCacheUtil.RemoveCacheItem(item.Key);
}
}
dbConnectionStringConfigPath = value;
}
}
public SqlDapperUtil(string connName)
{
dbConnectionName = connName;
if (!dbConnNamesCacheDic.ContainsKey(connName)) //如果靜態緩存中沒有，則加入到靜態緩存中
{
dbConnNamesCacheDic[connName] = true;
}
}
/// <summary>
/// 使用事務
/// </summary>
public void UseDbTransaction()
{
useDbTransaction = true;
}
/// <summary>
/// 獲取一個值，param可以是SQL參數也可以是匿名對象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
{
return UseDbConnection((dbConn) =>
{
return dbConn.ExecuteScalar<T>(sql, param, dbTransaction, commandTimeout, commandType);
});
}
/// <summary>
/// 獲取第一行的所有值，param可以是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 Dictionary<string, dynamic> GetFirstValues(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
{
return UseDbConnection((dbConn) =>
{
Dictionary<string, dynamic> firstValues = new Dictionary<string, dynamic>();
List<string> indexColNameMappings = new List<string>();
int rowIndex = 0;
using (var reader = dbConn.ExecuteReader(sql, param, dbTransaction, commandTimeout, commandType))
{
while (reader.Read())
{
if ((++rowIndex) > 1) break;
if (indexColNameMappings.Count == 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
indexColNameMappings.Add(reader.GetName(i));
}
}
for (int i = 0; i < reader.FieldCount; i++)
{
firstValues[indexColNameMappings[i]] = reader.GetValue(i);
}
}
reader.Close();
}
return firstValues;
});
}
/// <summary>
/// 獲取一個數據模型實體類，param可以是SQL參數也可以是匿名對象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) where T : class
{
return UseDbConnection((dbConn) =>
{
return dbConn.QueryFirstOrDefault<T>(sql, param, dbTransaction, commandTimeout, commandType);
});
}
/// <summary>
/// 獲取符合條件的所有數據模型實體類列表，param可以是SQL參數也可以是匿名對象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
{
return UseDbConnection((dbConn) =>
{
return dbConn.Query<T>(sql, param, dbTransaction, buffered, commandTimeout, commandType).ToList();
});
}
/// <summary>
/// 獲取符合條件的所有數據並根據動態構建Model類委托來創建合適的返回結果（適用於臨時性結果且無對應的模型實體類的情況）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="buildModelFunc"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="buffered"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public T GetDynamicModel<T>(Func<IEnumerable<dynamic>, T> buildModelFunc, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
var dynamicResult = UseDbConnection((dbConn) =>
{
return dbConn.Query(sql, param, dbTransaction, buffered, commandTimeout, commandType);
});
return buildModelFunc(dynamicResult);
}
/// <summary>
/// 獲取符合條件的所有指定返回結果對象的列表(複合對象【如：1對多，1對1】)，param可以是SQL參數也可以是匿名對象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="types"></param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <param name="splitOn"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return UseDbConnection((dbConn) =>
{
return dbConn.Query<T>(sql, types, map, param, dbTransaction, buffered, splitOn, commandTimeout, commandType).ToList();
});
}
/// <summary>
/// 執行SQL命令（CRUD），param可以是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 bool ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
{
return UseDbConnection((dbConn) =>
{
int result = dbConn.Execute(sql, param, dbTransaction, commandTimeout, commandType);
return (result > 0);
});
}
/// <summary>
/// 批量轉移數據(利用SqlBulkCopy實現快速大批量插入到指定的目的表及SqlDataAdapter的批量刪除)
/// </summary>
public bool BatchMoveData(string srcSelectSql, string srcTableName, List<SqlParameter> srcPrimarykeyParams, string destConnName, string destTableName)
{
using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetDbConnectionString(dbConnectionName, out dbProviderName)))
{
DataTable srcTable = new DataTable();
SqlCommand deleteCommand = null;
try
{
srcSqlDataAdapter.AcceptChangesDuringFill = true;
srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
srcSqlDataAdapter.Fill(srcTable);
if (srcTable == null || srcTable.Rows.Count <= 0) return true;
string notExistsDestSqlWhere = null;
string deleteSrcSqlWhere = null;
for (int i = 0; i < srcPrimarykeyParams.Count; i++)
{
string keyColName = srcPrimarykeyParams[i].ParameterName.Replace("@", "");
notExistsDestSqlWhere += string.Format(" AND told.{0}=tnew.{0}", keyColName);
deleteSrcSqlWhere += string.Format(" AND {0}=@{0}", keyColName);
}
string dbProviderName2 = null;
using (var destConn = new SqlConnection(GetDbConnectionString(destConnName, out dbProviderName2)))
{
destConn.Open();
string tempDestTableName = "#temp_" + destTableName;
destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
string destInsertCols = null;
using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
{
try
{
destSqlBulkCopy.BulkCopyTimeout = 120;
destSqlBulkCopy.DestinationTableName = tempDestTableName;
foreach (DataColumn col in srcTable.Columns)
{
destSqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
destInsertCols += "," + col.ColumnName;
}
destSqlBulkCopy.BatchSize = 1000;
destSqlBulkCopy.WriteToServer(srcTable);
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
}
destInsertCols = destInsertCols.Substring(1);
destConn.Execute(string.Format("insert into {1}({0}) select {0} from {2} tnew where not exists(select 1 from {1} told where {3})",
destInsertCols, destTableName, tempDestTableName, notExistsDestSqlWhere.Trim().Substring(3)), null, null, 100);
}
destConn.Close();
}
deleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1}", srcTableName, deleteSrcSqlWhere.Trim().Substring(3)), srcSqlDataAdapter.SelectCommand.Connection);
deleteCommand.Parameters.AddRange(srcPrimarykeyParams.ToArray());
deleteCommand.UpdatedRowSource = UpdateRowSource.None;
deleteCommand.CommandTimeout = 200;
srcSqlDataAdapter.DeleteCommand = deleteCommand;
foreach (DataRow row in srcTable.Rows)
{
row.Delete();
}
srcSqlDataAdapter.UpdateBatchSize = 1000;
srcSqlDataAdapter.Update(srcTable);
srcTable.AcceptChanges();
return true;
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
return false;
}
finally
{
if (deleteCommand != null)
{
deleteCommand.Parameters.Clear();
}
}
}
}
/// <summary>
/// 批量複製數據（把源DB中根據SQL語句查出的結果批量COPY插入到目的DB的目的表中）
/// </summary>
public TResult BatchCopyData<TResult>(string srcSelectSql, string destConnName, string destTableName, IDictionary<string, string> colMappings, Func<IDbConnection, TResult> afterCoppyFunc)
{
using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetDbConnectionString(dbConnectionName, out dbProviderName)))
{
DataTable srcTable = new DataTable();
TResult copyResult = default(TResult);
try
{
srcSqlDataAdapter.AcceptChangesDuringFill = true;
srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
srcSqlDataAdapter.Fill(srcTable);
if (srcTable == null || srcTable.Rows.Count <= 0) return copyResult;
string dbProviderName2 = null;
using (var destConn = new SqlConnection(GetDbConnectionString(destConnName, out dbProviderName2)))
{
destConn.Open();
string tempDestTableName = "#temp_" + destTableName;
destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
bool bcpResult = false;
using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
{
try
{
destSqlBulkCopy.BulkCopyTimeout = 120;
destSqlBulkCopy.DestinationTableName = tempDestTableName;
foreach (var col in colMappings)
{
destSqlBulkCopy.ColumnMappings.Add(col.Key, col.Value);
}
destSqlBulkCopy.BatchSize = 1000;
destSqlBulkCopy.WriteToServer(srcTable);
bcpResult = true;
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
}
}
if (bcpResult)
{
copyResult = afterCoppyFunc(destConn);
}
destConn.Close();
}
return copyResult;
}
catch (Exception ex)
{
//LogUtil.Error("SqlDapperUtil.BatchCopyData:" + ex.ToString(), "SqlDapperUtil.BatchCopyData");
return copyResult;
}
}
}
/// <summary>
/// 當使用了事務，則最後需要調用該方法以提交所有操作
/// </summary>
/// <param name="dbTransaction"></param>
public void Commit()
{
try
{
if (dbTransaction.Connection != null && dbTransaction.Connection.State != ConnectionState.Closed)
{
dbTransaction.Commit();
}
}
catch
{
throw;
}
finally
{
if (dbTransaction.Connection != null)
{
CloseDbConnection(dbTransaction.Connection);
}
dbTransaction.Dispose();
dbTransaction = null;
useDbTransaction = false;
if (dbConnection != null)
{
CloseDbConnection(dbConnection);
}
}
}
/// <summary>
/// 當使用了事務，如果報錯或需要中斷執行，則需要調用該方法執行回滾操作
/// </summary>
/// <param name="dbTransaction"></param>
public void Rollback()
{
try
{
if (dbTransaction.Connection != null && dbTransaction.Connection.State != ConnectionState.Closed)
{
dbTransaction.Rollback();
}
}
catch
{
throw;
}
finally
{
if (dbTransaction.Connection != null)
{
CloseDbConnection(dbTransaction.Connection);
}
dbTransaction.Dispose();
dbTransaction = null;
useDbTransaction = false;
}
}
~SqlDapperUtil()
{
try
{
CloseDbConnection(dbConnection, true);
}
catch
{ }
}
}
}
ColumnAttributeTypeMapper輔助類相關代碼如下：(如果不考慮實體類的屬性與表欄位不一致的情況，如下映射類可以不需要添加，同時SqlDapperUtil中移除相關依賴ColumnAttributeTypeMapper邏輯即可)
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace KYExpress.Common
{
public class ColumnAttributeTypeMapper<T> : FallbackTypeMapper
{
public ColumnAttributeTypeMapper()
: base(new SqlMapper.ITypeMap[]
{
new CustomPropertyTypeMap(
typeof(T),
(type, columnName) =>
type.GetProperties().FirstOrDefault(prop =>
prop.GetCustomAttributes(false)
.OfType<ColumnAttribute>()
.Any(attr => attr.Name == columnName)
)
),
new DefaultTypeMap(typeof(T))
})
{
}
}
[AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
public class ColumnAttribute : Attribute
{
public string Name { get; set; }
}
public class FallbackTypeMapper : SqlMapper.ITypeMap
{
private readonly IEnumerable<SqlMapper.ITypeMap> _mappers;
public FallbackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers)
{
_mappers = mappers;
}
public ConstructorInfo FindConstructor(string[] names, Type[] types)
{
foreach (var mapper in _mappers)
{
try
{
ConstructorInfo result = mapper.FindConstructor(names, types);
if (result != null)
{
return result;
}
}
catch (NotImplementedException)
{
}
}
return null;
}
public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName)
{
foreach (var mapper in _mappers)
{
try
{
var result = mapper.GetConstructorParameter(constructor, columnName);
if (result != null)
{
return result;
}
}
catch (NotImplementedException)
{
}
}
return null;
}
public SqlMapper.IMemberMap GetMember(string columnName)
{
foreach (var mapper in _mappers)
{
try
{
var result = mapper.GetMember(columnName);
if (result != null)
{
return result;
}
}
catch (NotImplementedException)
{
}
}
return null;
}
public ConstructorInfo FindExplicitConstructor()
{
return _mappers
.Select(mapper => mapper.FindExplicitConstructor())
.FirstOrDefault(result => result != null);
}
}
}
使用示例方法如下：
1.先來模擬各種查詢數據（由於是直接寫模擬SQL輸出，故沒有條件，也便於大家COPY後直接可以測試結果）
//實例化SqlDapperUtil對象，構造函數是config文件中的connectionStrings的Name名
var dapper = new SqlDapperUtil("LmsConnectionString");
//查詢1個值
DateTime nowTime = dapper.GetValue<DateTime>("select getdate() as nowtime");
//查詢1行值，並轉換成字典（這對於臨時查詢多個欄位而無需定義實體類有用）
Dictionary<string, dynamic> rowValues = dapper.GetFirstValues("select 0 as col0,1 as col1,2 as col2");
//查詢1行並返回實體類
Person person = dapper.GetModel<Person>("select '張三' as Name,22 as Age,'2018-1-1' as BirthDay,'中國廣東深圳' as HomeAddr");
//查詢1行表欄位與實體類屬性不一致映射
Person person2 = dapper.GetModel<Person>("select '張三' as Name,22 as Age,'2018-1-1' as BirthDay,'中國廣東深圳' as HomeAddress");
//查詢多行返回實體集合
var persons = dapper.GetModelList<Person>(@"select '張三' as Name,22 as Age,'2018-1-1' as BirthDay,'中國廣東深圳' as HomeAddr union all
select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中國廣東深圳' as HomeAddress union all
select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中國廣東廣州' as HomeAddress
");
//查詢多行返回1對1關聯實體結果集
var personWithCarResult = dapper.GetMultModelList<Person>(@"select t1.*,t2.* from
(select '張三' as Name,22 as Age,'2018-1-1' as BirthDay,'中國廣東深圳' as HomeAddr union all
select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中國廣東深圳' as HomeAddress union all
select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中國廣東廣州' as HomeAddress)as t1 inner join
(
select '張三' as DriverName,'大眾' as Brand,'2018-8-8' as ManufactureDate union all
select '李四' as DriverName,'賓士' as Brand,'2018-1-8' as ManufactureDate union all
select '王五' as DriverName,'奧迪' as Brand,'2017-8-8' as ManufactureDate
)as t2
on t1.Name=t2.DriverName
", new[] { typeof(Person), typeof(CarInfo) }, (objs) =>
{
Person personItem = objs[0] as Person;
CarInfo carItem = objs[1] as CarInfo;
personItem.Car = carItem;
return personItem;
}, splitOn: "DriverName");
//查詢多行返回1對多關聯實體結果=personWithManyCars
List<Person> personWithManyCars = new List<Person>();
dapper.GetMultModelList<Person>(@"select t1.*,t2.* from
(select '張三' as Name,22 as Age,'2018-1-1' as BirthDay,'中國廣東深圳' as HomeAddr union all
select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中國廣東深圳' as HomeAddress union all
select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中國廣東廣州' as HomeAddress)as t1 inner join
(
select '張三' as DriverName,'大眾' as Brand,'2018-8-8' as ManufactureDate union all
select '張三' as DriverName,'賓士' as Brand,'2018-1-8' as ManufactureDate union all
select '張三' as DriverName,'奧迪' as Brand,'2017-8-8' as ManufactureDate
)as t2
on t1.Name=t2.DriverName
", new[] { typeof(Person), typeof(CarInfo) }, (objs) =>
{
Person personItem = objs[0] as Person;
CarInfo carItem = objs[1] as CarInfo;
Person personItemMain = personWithManyCars.FirstOrDefault(p => p.Name == personItem.Name);
if (personItemMain == null)
{
personItem.Cars = new List<CarInfo>();
personItemMain = personItem;
personWithManyCars.Add(personItemMain);
}
personItemMain.Cars.Add(carItem);
return personItemMain;
}, splitOn: "DriverName");
2.下麵是演示如何進行增、刪、改以及動態查詢的情況：
//使用事務創建多張表，多條SQL語句寫在一起
try
{
dapper.UseDbTransaction();
dapper.ExecuteCommand(@"create table T_Person(Name nvarchar(20) primary key,Age int,BirthDay datetime,HomeAddress nvarchar(200));
create table T_CarInfo(DriverName nvarchar(20) primary key,Brand nvarchar(50),ManufactureDate datetime)");
dapper.Commit();
}
catch (Exception ex)
{
dapper.Rollback();
//記日誌
}
//使用事務批量插入多張表的多個記錄，多條SQL分多次執行（參數支持批量集合對象傳入，無需迴圈）
try
{
dapper.UseDbTransaction();
dapper.ExecuteCommand(@"insert into T_Person
select N'張三' as Name,22 as Age,'2018-1-1' as BirthDay,N'中國廣東深圳' as HomeAddress union all
select N'李四' as Name,25 as Age,'2018-10-1' as BirthDay,N'中國廣東深圳' as HomeAddress union all
select N'王五' as Name,35 as Age,'1982-10-1' as BirthDay,N'中國廣東廣州' as HomeAddress");
var carInfos = dapper.GetModelList<CarInfo>(@"
select N'張三' as DriverName,N'大眾' as Brand,'2018-8-8' as ManufactureDate union all
select N'李四' as DriverName,N'賓士' as Brand,'2018-1-8' as ManufactureDate union all
select N'王五' as DriverName,N'奧迪' as Brand,'2017-8-8' as ManufactureDate");
dapper.ExecuteCommand(@"insert into T_CarInfo(DriverName,Brand,ManufactureDate) Values(@DriverName,@Brand,@ManufactureDate)", carInfos);
dapper.Commit();
}
catch (Exception ex)
{
dapper.Rollback();
//記日誌
}
//執行刪除，有參數，參數可以是實體類、匿名對象、字典（如有需要，可以是集合，以支持批量操作）
bool deleteResult = dapper.ExecuteCommand("delete from T_CarInfo where DriverName=@DriverName", new { DriverName = "李四" });
//構建動態執行SQL語句（以下是更新，查詢類似）
StringBuilder updateSqlBuilder = new StringBuilder();
var updateParams = new Dictionary<string, object>();
if (1 == 1)
{
updateSqlBuilder.Append(",Age=@Age");
updateParams["Age"] = 20;
}
if (2 == 2)
{
updateSqlBuilder.Append(",BirthDay=@BirthDay");
updateParams["BirthDay"] = Convert.ToDateTime("2010-1-1");
}
if (3 == 3)
{
updateSqlBuilder.Append(",HomeAddress=@HomeAddress");
updateParams["HomeAddress"] = "中國北京天安門";
}
string updateSql = string.Concat("update T_Person set ", updateSqlBuilder.ToString().TrimStart(','), " where Name=@Name");
updateParams["Name"] = "張三";
bool updateResult = dapper.ExecuteCommand(updateSql, updateParams);
//查詢返回動態自定義結果，之所以不直接返回Dynamic就好，是因為可讀性差，故儘可能的在執行後就轉成指定的類型
Tuple<string, int> hasCarInfo = dapper.GetDynamicModel<Tuple<string, int>>((rs) =>
{
var result = rs.First();
return Tuple.Create<string, int>(result.Name, result.CarCount);
}, @"select a.Name,count(b.DriverName) as CarCount from T_Person a left join T_CarInfo b on a.Name=b.DriverName where a.Name=@Name group by a.Name", new { Name = "張三" });
3.還有兩個方法：BatchCopyData、BatchMoveData，這是特殊封裝的，不是基於Dapper而是基於原生的Ado.net及BCP，目的是快速大量跨DB跨表COPY數據或轉移數據，使用也不複雜，建議想瞭解的網友可以查看我以往的文章
以上示例方法用到了兩個類，如下：
class Person
{
public string Name { get; set; }
public int Age { get; set; }
public DateTime BirthDay { get; set; }
[Column(Name = "HomeAddress")]
public string HomeAddr { get; set; }
public CarInfo Car { get; set; }
public List<CarInfo> Cars { get; set; }
}
class CarInfo
{
public string Brand { get; set; }
public DateTime ManufactureDate { get; set; }
public string DriverName { get; set; }
}
SqlDapperUtil類中依賴了之前我封裝的類：如：MemoryCacheUtil(本地記憶體依賴緩存實用工具類)、ConfigUtil（配置文件管理工具類）、EncryptUtil（加密工具類），如果項目中不想引用這些類，可以移除或改成其它方法即可。
另外說明一下，為了防止和減少因DB連接未及時釋放導致的連接池不足等原因，故預設執行所有的CRUD方法都是用完即釋放，但有一種情況不會釋放就是使用了事務，若使用事務，則必需配套使用：UseDbTransaction、Commit、或失敗執行Rollback，否則可能導致未能及時釋放對象，當然最終當SqlDapperUtil實例被回收後事務若沒有提交或回滾，會強制執行回滾操作並釋放事務及連接對象，防止可能的資源浪費情況。
本來早就想總結一下這篇文章，但一直由於工作太忙沒有時間，今天利用加班研究.NET CORE的空隙時間完成，請大家支持，有好東西我一定會分享的，雖然不一定高大上，但一定實用且項目中有實戰過的。
