基于Dapper二次封装了一个易用的ORM工具类：SqlDapperUtil
程序员文章站
2022-05-13 22:53:59
基于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的空隙时间完成，请大家支持，有好东西我一定会分享的，虽然不一定高大上，但一定实用且项目中有实战过的。