Based on Dapper, we encapsulate an easy-to-use ORM tool class: SqlDapperUtil, which simplifies and encapsulates all kinds of CRUDs that can be used everyday, so that ordinary programmers only need to pay attention to business, because it is very simple, so we can paste the source code directly, if we need to use it, we can directly encapsulate it.Copied to the project, the SqlDapperUtil has been widely used in company projects.
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>
/// Tool classes for data manipulation class encapsulation based on Dapper/ / / Author: Zuo Wenjun/ / / 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 methodPrivate IDbConnecTion GetDbConnection (){Bool needCreateNew = false;If (dbConnecTion = = null string.IsNullOrWhiteSpace (dbConnection.ConnectionString){NeedCreateNew = true;}Else if (! MemoryCacheUtil.Contains (dbConnect)IonName)){NeedCreateNew = true;}If (NEEdCreateNew){DbConnectionString = GetDbConnectionString (dbConnection)Name, out dbProviderName);Var dbProviderFactory = DbProviderFactories.GetFactory (d)BProviderName);DbConnection = dbProviderFactory.CreateConnection ();DbConnection.ConnectionString = dbConnectionString;}If (dbConnectio)N.State = = ConnectionState.Closed){DbConnection.Open ();}Return dbConnection;}Private string GetDbConnectionString (st)Ring dbConnName, out string dbProviderName){/ / if the specified connection string configuration file path is created, cache dependency is created, once configured.The change is invalid, and then read again.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> read connection string configuration node [{0}]: {1})ProviderName:{2}, "dbConnName, dbConnStr, dbProdName", "SqlDapperUtil.GetDbConnectionString".Return new[] {EncryptUtil.Decrypt (dbConnStr), dbProdName};}, SqlDapperUTil.DbConnectionStringConfigPath);DbProviderName = connInfos[1];ReturnConnInfos[0];}Private T UseDbConnection< T> (Func< IDbConnection, T&g)T; queryOrExecSqlFunc){IDbConnection dbConn = null;Try{Type modelType = typeof (T);Var typeMap = Dapper.SqlMappEr.GetTypeMap (modelType);If (typeMap = = null!!!! (typeMap is ColumnAttributeTypeMa)Pper< T>){Dapper.SqlMapper.SetTypeMap (modelType, new C)OlumnAttributeTypeMapper< T> ());}DbConn = GetDbConnection() ();If (useDbTransaction & & dbTransaction = null){DbTransaction = GetDbTransaction ();}Return queRyOrExecSqlFunc (dbConn);}Catch{Throw;}Finally{If (dbTransaction = = null & &am)P; 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>/ / / get a transaction object (e.g.Fruit needs to ensure consistency of multiple execution statements, and transactions must be used./ / / < /summary>/ / / < param name= "IL" > < /param>/ / / < returns> < /returns>Private IDbTransaction GetDbTransaction (I)SolationLevel IL = IsolationLevel.Unspecified){Return GetDbConnection ().BegiNTransaction (IL);}Private DynamicParameters ToDynamicParameters (Dictionary<String, object> paramDic){Return new DynamicParameters (paramDic);}#endregionPublic static string DbConnectionStringConfigPath{Get{If (string.IsNullOrEmpty (dbConnectionStringConfigPath)/ / if the configuration file is not specified, the default configuration file path is used as the cache dependent path.{DbConnectionStringConfigPATH = BaseUtil.GetConfigPath ();}Return dbConnectionStringConfiGPath;}Set{If (! String.IsNullOrWhiteSpac)E (value) & &! File.Exists (value){Throw new FileNotFoUndException ("the specified DB connection string configuration file does not exist:" + value);}/ / if the configuration file changes, it may lead to a connection.String changes, so all cached strings must be cleared for subsequent reloading of strings.If (! String.Equals (dbConnectionStringConfigPath, V)Alue, StringComparison.OrdinalIgnoreCase){Foreach (VaR item)In dbConnNamesCacheDic){MemoryCacheUtil.RemoveCacheItem (item.Key);}}DbConnectionStringConfiGPath = value;}}Public SqlDapperUtil (string connName){DbConnectionName = connName;If (dbConnNamesCacheDic.ContainsKey connNam)E) / / if static caching does not exist, add it to the static cache.{DbConnNamesCacheDic[connName] = true;}}/ / / < summary>/ / use transactions/ / / < /summary>Public void UseDbTransaction (){UseDbTransaction = true;}/ / / < summary>/ / / get a value. Param can be a SQL parameter or an anonymous object./ / / < /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,MmandType? CommandType = null){Return UseDbConnection ((dbConn) =>{Return dbConn.ExecuteScalar< T> (SQL, param, dbTransaction, commandTi)Meout, commandType);});}/ / / < summary>/ / / get the first line.For all values, param can be SQL parameter or anonymous object./ / / < /summary>/ / / < param name= "SQL" > < /paRam>/ / / < param name= "param" > < /param>/ / / < param name= "transactiOn "> < /param>";/ / / < param name= "commandTimeout" > < /param>/ / / &lT; param name= "commandType" > < /param>/ / / < returns> < /returns>PUblic Dictionary< string, dynamic> GetFirstValues (string SQL, object param = null, int? NullTimeout = null, CommandType? CommandType = null){Return UseDbConnection ((dbC)Onn) =>{Dictionary< string, dynamic> firstValues = new DictiOnary< string, dynamic> ();List< string> indexColNameMappings = new List≪ string> ();Int rowIndex = 0;Using (VaR reader = dbConn.Exec)UteReader (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>/ / / get a data model entity class, param can be SQL parameter or anonymous object./ / / </summary>/ / / < typeparam name= "T" > < /typeparam>/ / / < param name= ".SQL "> < /param>";/ / / < param name= "param" > < /param>/ / / < paramName= "transaction" > < /param>/ / / < param name= "commandTimeout" > < /param>/ / / < param name= "commandType" > < /param>/ / / < returns> < /returns>Public T GetModel< T> (string SQL, object param = null, int? CommandTimeout = nullCommandType? CommandType = null) where T: Class{Return UseDbConnection ((d)BConn) =>{Return dbConn.QueryFirstOrDefault< T> (SQL, param,DbTransaction, commandTimeout, commandType);});}/ / / < summary≫/// Gets a list of all the data model entity classes that meet the criteria, and param can be either an SQL parameter or an anonymous object/ / / < /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>/ / / get qualified places.Data is available and a Model class delegate is dynamically constructed to create the appropriate return result (for temporary results without corresponding model entity classes)/ / / < /summary>/ / / < tYpeparam name= "T" > < /typeparam>/ / / < param name= "buildModelFunc" > < /param&gT;/ / / < 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, buffered?NdTimeout = null, CommandType? CommandType = null){Var dynamicResult = UseDbConnection ((dbConn) =>{Return dbConn.Query (SQL, param, dbTransactio)N, buffered, commandTimeout, commandType);});Return buildModelFunc (dyna)MicResult);}/ / / < summary>/ / / get the list of all specified return result objects (compound object [1: many, 1, >For 1], param can be SQL parameter or anonymous object./ / / < /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 = =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>/ / / execute SQL command (CRUD), param can be SQL parameter or entity class to add./ / / < /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, commandTimeo)UT, commandType);Return (result > 0);});}/ / // < summary>/// batch transfer data (batch deletion of SqlBulkCopy for fast mass insertion into specified destination tables)/ / // < /summary>Public bool BatchMoveData (string srcSelectSql, string srcTableName, List< SqlParameter> srcPrimarykeyParams, string destConnName, string destTableName){Using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter (srcSelectSql, GetDbConnectio)NString (dbConnectionName, out dbProviderName)){DataTable srcTable =New DataTable ();SqlCommand deleteCommand = null;Try{SrcSqlDataAdapter.AcceptChangesDuringFill = true;SrcSqlDataAdapter.AcceptChangesDuringUpdate = false;SrcSqlDataAdapter.Fill (SRC)Table);If (srcTable = = null 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}", keyColNa)Me);}String dbProviderName2 = null;Using (VaR destConn = new SqlConnection (GetDbConnectionString (destConnName, out dbProviderName2))){DestConn.Open ();StringTempDestTableName = "#temp_" + destTableName;DestConn.Execute (string.Form)At ("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} WHE")RE {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 (Excepti)On Ex){//LogUtil.Error ("SqlDapperUtil.BatchMoveData:" + ex.T)OString (), "SqlDapperUtil.BatchMoveData";Return false;}Finally{If (deleteCommand! = null){DeleteCommand.Parameters.Clear ();}}}}/ / / < summary>/ / / batch copy data (source DB in S)The result of the QL statement is inserted into the destination table of the destination DB in batches of COPY./ / / < /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 (dbC)OnnectionName, out dbProviderName){DataTable srcTable = new DataTaBle ();TResult copyResult = default (TResult);Try{SrcSqlDataAdapter.AcceptChangesDuringFill = true;SrcSqLDataAdapter.AcceptChangesDuringUpdate = false;SrcSqlDataAdapter.Fill (srcTable));If (srcTable = = null 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} fr")Om {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.BatchMoveDat"A ");}}If (bcpResUlt){CopyResult = afterCoppyFunc (destConn);}DestConn.Close ();}Return copyResult;}Catch (Exception Ex){//LogUtil.Error ("SqlDapperUtil.BatchCopyData:" + ex.ToString (), "S"QlDapperUtil.BatchCopyData "";Return copyResult;}}}/ / / < summary>/ / / when the transaction is used, the method is finally required to submit all operations./ / / / / / /< /summary>/ / / < param name= "dbTransaction" > < /param>Public voidCommit (){Try{If (dbTransaction.Connection!= null & & dbTransaction.Connection.State! = ConnectionState.Closed){DbTransaction.Commit ();}}Catch{Throw;}Finally{If (dbTransaction.Connection! = null){CloseDbConnection (dbTr)Ansaction.Connection);}DbTransaction.Dispose ();DbTransaction = null;UseDbTransaction = false;If (dbConnectio)N! = null){CloseDbConnection (dbConnection);}}}/ / / < summary>/ / / when a transaction is used, if it is wrong or needs to be interrupted, it needs to be invoked.Perform rollback operations/ / / < /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{}}}}
ColumnAttributeTypeMapperThe code for the auxiliary class is as follows: (If you don’t consider the inconsistency between the attributes of the entity class and the table fields, the following mapping classes can be added without the need to remove the dependent ColumnAttributeTypeMapper logic from SqlDapperUtil)
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);
}
}
}
Examples are as follows:
1.To simulate a variety of query data (since it is directly written to simulate SQL output, so no conditions, but also easy for everyone to COPY directly after the test results)
//Instantiate the SqlDapperUtil object, the constructor is the Name name of the connectionStrings in the config file.Var dapper = new SqlDapperUtiL ("LmsConnectionString");/ / query 1 valuesDateTime nowTime = dapper.GetValue<DateTime> ("select getdate () as nowtime");//Query 1 line value and convert it to a dictionary (useful for temporarily querying multiple fields without defining entity classes)Dictionary< string, dynamic> rowValues = dapper.GetFirstValues ("select 0 as col0,1")As col1,2 as col2 "";/ / query 1 rows and return to entity classesPerson person = dapper.GetModel<Person & gt ("select'Zhang San'as Name, 22 as Age,'2018-1-1' as Birth Day,'Shenzhen, Guangdong, China'as Home Addr");/ / query the 1 row table field is inconsistent with entity class attribute mapping.Person person2 = dapper.GetModel< Person> ("select 'Zhang three").As Name, 22 as Age,'2018-1-1'as BirthDay,' Shenzhen, Guangdong, China 'as HomeAddress';/ / query multiple rows return entity collectionVar persons = dapper.GetModelList< Person> (@ "select" Zhang three "as Name, 22 as Age,'2018-1-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddr union allSelect 'Lee four' as Name, 25 as Age,'2018-10-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddressUnion allSelect 'king five' aS Name, 35 as Age,'1982-10-1'as BirthDay,' China Guangdong Guangzhou 'as HomeAddress");/ / query multiple rows to return 1 pairs of 1 associated entity result sets.Var persoNWithCarResult = dapper.GetMultModelList< Person> (@ @ select t1.*, t2.* from(select 'Zhang three' as Name, 22 as Age,'2018-1-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddr union allSelect 'Lee four' as Name, 25 as Age,'2018-10-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddress union allSelect 'king five' as Name, 35 as Age,'1982-10-1'asBirthDay, 'Guangzhou, Guangdong, China' as HomeAddress) as T1 inner join(Select 'Zhang three' as DriverName, 'Volkswagen' as Brand,'2018-8-8'as ManufactureDate union allSelect 'Lee four' as DriverName, 'Mercedes' as Brand,'2018-1-8' as ManufactureDate union allSelect 'king five' as DriverName, 'Audi' as Brand,'2017-8-8'as ManufactureDateAs T2On 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");/ / query multiple rows to return 1 pairs of multiple associated entity nodesGuo =personWithManyCarsList< Person> personWithManyCars = new List< Person> ();Dapper.GetMultModelList< Person> (@ @ select t1.*, t2.* from(select 'Zhang three' as Name, 22As Age,'2018-1-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddr union allSelect 'Lee four' as Name, 25 as Age,'2018-10-1'as BirthDay,' China Guangdong Shenzhen 'as HomeAddress union allSelect 'king five' as Name, 35 as Age,'1982-10-1'as BirtHDay, 'Guangzhou, Guangdong, China' as HomeAddress) as T1 inner join(Select 'Zhang three' as DriverName, 'Volkswagen' as Brand,'2018-8-8'As ManufactureDate union allSelect 'Zhang three' as DriverName, 'Mercedes' as Brand,'2018-1-8' as ManufactureDateUnion allSelect 'Zhang three' as DriverName, 'Audi' as Brand,'2017-8-8'as ManufactureDateAs T2On 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.The following is a demonstration of how to add, delete, modify, and dynamic queries.
//Create multiple tables using transactions, and write multiple SQL statements together.Try{Dapper.UseDbTransaction ();Dapper.ExecuteCommand (@ @ create table T_Person (Name nvarchar (20) primary key, Age int, B)IrthDay 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 ();/ / log}/ / transaction volumeMultiple records are inserted into multiple tables, and multiple SQLs are executed multiple times (parameters support batch collection object entry, without looping)Try{Dapper.UseDbTransaction ();Dapper.ExecuteCommand (@ @ insert into T_Person)Select N'Zhang three' as Name, 22 as Age,'2018-1-1'as BirthDay, N' China Guangdong Shenzhen 'as HomeAddress union allSelect N'Lee four' as Name, 25 as Age'2018-10-1'as BirthDay, N' China Guangdong Shenzhen 'as HomeAddress union allSelect N'Wang five' as Name, 35 as Age,'1982-10-1'as BirthDay, N' China Guangdong Guangzhou 'as HomeAddress';Var carInfos = dapper.GetModelList≪ CarInfo> (@ "Select N'Zhang three' as DriverName, N'public' as Brand,'2018-8-8'as ManufactureDate Union UnionSelect N'Lee four' as DriverName, N'Benz' as Brand,'2018-1-8'as ManufactureDate Union UnionSelect N'king five' asDriverName, N'Audi' as Brand,'2017-8-8'as ManufactureDate';Dapper.ExecuteCommand(@ @ insert into T_CarInfo (DriverName, Brand, ManufactureDate) Values (@DriverName, @Brand, @ManufactureDat)E) ", carInfos";Dapper.Commit ();}Catch (Exception Ex){Dapper.Rollback ();/ / log}// Perform deletion with parameters that can be entity classes, anonymous objects, dictionaries (collections, if necessary, to support batch operations)Bool deleteResult = dapper.ExecuteCommand ("delete from T_CarInfo where DriverName=@DriverName", new {DriverName = "Lee four"});/ / build dynamic execution of SQL statement (below is update, query similar)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" = = "Tiananmen of Beijing, China";}String updateSql = string.Concat ("update T_Person set", updateSqlBuilder.To)String ().TrimStart (','), "where Name=@Name";UpdateParams["Name" = "Zhang San";Bool updateResult = dapper.ExecuteCommand (updateSql, updateParams);/ / query return dynamic customizationAs a result, it's better not to go directly back to Dynamic because of poor readability, so it turns to the specified type as soon as possible after executionTuple< string, int> hasCarInfo = DaPper.GetDynamicModel< Tuple< string; int> > ((RS) =>{VarResult = rs.First ();Return Tuple.Create< string, int> (result.Name, result.Car)Count);} @ @ select a.Name, count (b.DriverName) as CarCount from T_Person a left join leftCarInfo B on a.Name=b.DriverName where a.Name=@Name group by a.Name, new {Name = "Zhang three"};
3.There are two more ways: BatchCopyData, BatchMoveData, which are specially encapsulated, not based on Dapper, but on native Ado. net and BCP, to quickly and massively transfer COPY data across DB tables or across tables, without using itComplicated, the netizens who want to know can check my past articles.
The above example method uses two classes, as follows:
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; }
}
SqlDapperUtilThe class relies on the classes I encapsulated earlier: MemoryCacheUtil, ConfigUtil, EncryptUtil, if you don’t want to refer to these classes in your project.It can be removed or changed to other methods.
In addition, in order to prevent and reduce the connection pool shortage caused by the DB connection not released in time, so the default execution of all CRUD methods are exhausted and released, but there is a case will not be released is the use of transactions, if the use of transactions, it is necessary to match the use of:UseDbTransAction, Commit, or failure to perform Rollback may result in a failure to release objects in time, although ultimately when the SqlDapperUtil instance is recovered, transactions that are not committed or rolled back will be forced to rollback and release transactions and connection objects to preventPossible waste of resources.
I would like to summarize this article for a long time, but I have been too busy with work to have time, and I use the spare time of overtime research. NET CORE to finish today. Please support me, there are good things I will share, although not necessarily tall, but must be practical and the project has been combat.