Dapper是.NET下一个micro的ORM,它和Entity Framework或Nhibnate不同,属于轻量级的,并且是半自动的。也就是说实体类都要自己写。它没有复杂的配置文件,一个单文件就可以了。给出官方地址。
http://code.google.com/p/dapper-dot-net/
个人觉得他非常好用,现在已经取代了原来的SqlHelper。优点:
- 使用Dapper可以自动进行对象映射!
- 轻量级,单文件。
- 支持多数据库。
- Dapper原理通过Emit反射IDataReader的序列队列,来快速的得到和产生对象。
网上还有对Dapper的扩展类,这里就不赘述了。下面只讲下简单的增删改查、数据库表间的对应关系和事务的应用。
先给出实体类的关系:
书和书评是1---n的关系。(沿用Entity Framework的实体类,virtual表示延迟加载,此处忽略)
//书 public class Book { public Book() { Reviews = new List<BookReview>(); } public int Id { get; set; } public string Name { get; set; } public virtual List<BookReview> Reviews { get; set; } public override string ToString() { return string.Format("[{0}]------《{1}》", Id, Name); } } //书评 public class BookReview { public int Id { get; set; } public int BookId { get; set; } public virtual string Content { get; set; } public virtual Book AssoicationWithBook { get; set; } public override string ToString() { return string.Format("{0})--[{1}]t"{3}"", Id, BookId, Content); } }
- 基本的增删改查操作
由于Dapper ORM的操作实际上是对IDbConnection类的扩展,所有的方法都是该类的扩展方法。所以在使用前先实例化一个IDBConnection对象。
IDbConnection conn = new SqlConnection(connString);
Insert
Book book = new Book(); book.Name="C#本质论"; string query = "INSERT INTO Book(Name)VALUES(@name)"; //对对象进行操作 conn.Execute(query, book); //直接赋值操作 conn.Execute(query, new {name = "C#本质论"});
update
string query = "UPDATE Book SET [email protected] WHERE id [email protected]"; conn.Execute(query, book);
delete
string query = "DELETE FROM Book WHERE id = @id"; conn.Execute(query, book); conn.Execute(query, new { id = id });
query
string query = "SELECT * FROM Book"; //无参数查询,返回列表,带参数查询和之前的参数赋值法相同。 conn.Query<Book>(query).ToList(); //返回单条信息 string query = "SELECT * FROM Book WHERE id = @id"; book = conn.Query<Book>(query, new { id = id }).SingleOrDefault();
- 数据库表对应关系操作
//查询图书时,同时查找对应的书评,并存在List中。实现1--n的查询操作 string query = "SELECT * FROM Book b LEFT JOIN BookReview br ON br.BookId = b.Id WHERE b.id = @id"; Book lookup = null; //Query<TFirst, TSecond, TReturn> var b = conn.Query<Book, BookReview, Book>(query, (book, bookReview) => { //扫描第一条记录,判断非空和非重复 if (lookup == null || lookup.Id != book.Id) lookup = book; //书对应的书评非空,加入当前书的书评List中,最后把重复的书去掉。 if (bookReview != null) lookup.Reviews.Add(bookReview); return lookup; }, new { id = id }).Distinct().SingleOrDefault(); return b;
//1--1操作 BookReview br; string query = "SELECT * FROM BookReview WHERE id = @id"; using (conn) { br = conn.Query<BookReview, Book, BookReview>(query, (bookReview, book) => { bookReview.AssoicationWithBook = book; return bookReview; }, new { id = id }).SingleOrDefault(); return br; }
- 事务操作
using (conn) { //开始事务 IDbTransaction transaction = conn.BeginTransaction(); try { string query = "DELETE FROM Book WHERE id = @id"; string query2 = "DELETE FROM BookReview WHERE BookId = @BookId"; conn.Execute(query2, new { BookId = id }, transaction, null, null); conn.Execute(query, new { id = id }, transaction, null, null); //提交事务 transaction.Commit(); } catch (Exception ex) { //出现异常,事务Rollback transaction.Rollback(); throw new Exception(ex.Message); } }
后续文章
由于自己的毕设也是用它来写的,所以放出来,可以作为一个实例来参考一下。
sql server2012 dapper asp.net mvc4 easyUI的药库管理系统,有兴趣的可以star一下哦~
评论( 0)