假如你喜欢原生的Sql语句,又喜欢ORM的简单,那你一定会喜欢上Dapper这款ROM. 点击下载 Dapper的优势:
1,Dapper是一个轻型的ORM类。代码就一个SqlMapper.cs文件,编译后就40K的一个很小的Dll.
2,Dapper很快。Dapper的速度接近与IDataReader,取列表的数据超过了DataTable。
3,Dapper支持什么数据库。Dapper支持Mysql,SqlLite,Mssql2000,Mssql2005,Oracle等一系列的数据库,当然如果你知道原理也可以让它支持Mongo db
4,Dapper的r支持多表并联的对象。支持一对多 多对多的关系。并且没侵入性,想用就用,不想用就不用。无XML无属性。代码以前怎么写现在还怎么写。 5,Dapper原理通过Emit反射IDataReader的序列队列,来快速的得到和产生对象。性能实在高高高。
下载地址 https://github.com/StackExchange/dapper-dot-net
测试的数据结构为public class ReportInfo{
public string UserName { get; set; }
public string DataString { get; set; }
public int ProjectCode { get; set; }
public int Hour { get; set; }
}
Dapper以扩展IDbConnection类的方式 提供三个扩展方法重载
第一种public static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
调用示例using (IDbConnection connection = new SqlConnection(ConnectionString)) {
var list = connection.Query("select * from Report where UserName=@UserName ",
new { UserName = "Ocean" });
}
第二种public static IEnumerable Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
调用示例using (IDbConnection connection = new SqlConnection(ConnectionString)) {
var list = connection.Query("select * from Report where UserName=@UserName ",
new { UserName = "Ocean" });
foreach (var item in list)
{
Console.WriteLine(item.UserName);
}
}
第三种public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)
调用示例using (IDbConnection connection = new SqlConnection(ConnectionString))
{
var result = connection.Execute(@"insert Report(UserName, DataString,ProjectCode,Hour) values (@UserName, @DataString,@ProjectCode,@Hour)",
new { UserName = "OceanTest", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 });
}
entity的话可以更简单public static void Insert(BookInfo bookInfo)
{
using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))
{
var result = connection.Execute(@"insert tb_book(Title,Content,Volume) values (?Title,?Content,?Volume)",
bookInfo);
}
}
另外还可以批量添加using (IDbConnection connection = new SqlConnection(ConnectionString))
{
var result = connection.Execute(@"insert Report(UserName, DataString,ProjectCode,Hour) values (@UserName, @DataString,@ProjectCode,@Hour)",
new []{
new { UserName = "OceanTest2", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 },
new { UserName = "OceanTest3", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 },
new { UserName = "OceanTest4", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 }
});
}
多结果集,需要注意结果集大于1条数据则Single()会报错using (IDbConnection connection = new SqlConnection(ConnectionString))
{
var sql = @"
select * from Report where UserName=@UserName;
select * from Report where ProjectCode=@ProjectCode;
select * from [User] where UserName=@UserName ;
select * from Report where Hour>@Hour";
using (var multi = connection.QueryMultiple(sql,
new { UserName = "Ocean", ProjectCode = 3, Hour=100 }))
{
var recordSet1 = multi.Read().ToList();
var recordSet2 = multi.Read().Single();
var recordSet3 = multi.Read().ToList();
var recordSet4 = multi.Read().ToList();
}
}
支持事务(未验证)using (IDbConnection connection = new SqlConnection(ConnectionString))
{
const string deleteColumn = "delete from [Column] where ColumnCatid=@catid";
const string deleteColumnCat = "delete from ColumnCat where id=@Id";
IDbTransaction transaction = connection.BeginTransaction();
int row = connection.Execute(deleteColumn, new { catid = 1}, transaction, null, null);
row = connection.Execute(deleteColumnCat, new { id = 1 }, transaction, null, null);
transaction.Commit();
}
另外一种批量插入数据的方法(未测试)//批量插入数据
List schools = new List()
{
new School() {Address="China·BeiJing",Title="清华大学" },
new School() {Address="杭州",Title="浙江大学" },
new School() {Address="不知道,US?",Title="哈弗大学" }
};
//在执行参数化的SQL时,SQL中的参数(如@title可以和数据表中的字段不一致,但要和实体类型的属性Title相对应)
dbConnection.Execute("insert into t_schools(Address,Name) values(@address,@title);", schools);
总结
Dapper通过Emit的方式组装语句,因此执行效率接近原生语句.
并且很轻,无配置,无属性.SQL语句完全可控,简直就是我最喜欢的东西啊.
相见恨晚,以后在项目中需要多用此ROM类库