代码改变世界

Stackoverflow/dapper的Dapper-Extensions用法(一)

2015-07-02 09:17  Fururur  阅读(24767)  评论(58编辑  收藏  举报

Dapper-Extensions

Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.

github:https://github.com/tmsmith/Dapper-Extensions

  • Dapper是一个开源轻的量级的orm,他的优点和用法在之前写的博客中有提到。可是它只支持带sql语句的CRUD。
  • Dapper-Extensions也是一个开源库,他在Dapper的基础上封装了基本的CRUD操作,使得一些简单的数据库操作可以不用自己写sql语句。使用起来更方面。

若不熟悉Dapper的,请移步:

后续文档翻译

下面是对他的用法的描述,也就是对项目文档的翻译。如果读者英文不错可以直接看原版文档,见github。

Introduction

Dapper Extensions是github上的一个开源库是对StackOVerflow开发的Dapper ORM的一个扩展。它增加了基础的CRUD操作((Get, Insert, Update, Delete)),对更高级的查询场景,该类库还提供了一套谓词系统。它的目标是保持POCOs的纯净,不需要额外的attributes和类的继承。

自定义映射请参见 ClassMapper

Features

  • 零配置
  • 自动映射POCOs的CRUD操作
  • GetList, Count等方法可以用于更高级的场景。
  • GetPage for returning paged result sets.支持分页
  • 自动支持Guid和Integer类型的主键,(也可以手动指定其他键类型)
  • 通过使用ClassMapper可以使保证POCOs纯净。 (Attribute Free!)
  • 可以通过使用ClassMapper来自定义entity-table映射
  • 支持联合主键
  • POCO类名默认与数据表名相匹配,也可以自定义
  • 易于使用的 Predicate System适用于高级场合
  • 在生成SQL语句时正确转义表名和类名 (Ex: SELECT FirstName FROM Users WHERE Users.UserId = @ UserId_0)
  • 覆盖单元测试(覆盖了150+个单元测试)

Naming Conventions(命名约定)

  • POCO类名与数据库中表名匹配,多元化(Pluralized)的表名(暂时理解为别名吧)可以通过PlurizedAutoClassMapper来自定义。
  • POCO类中属性名和数据库表中的类名匹配。
  • 暂约定主键被命名为Id.使用其他主键需要自定义映射(ClassMapper)。

Installation

Nuget:
http://nuget.org/List/Packages/DapperExtensions

PM> Install-Package DapperExtensions

Examples

pernson POCO的定义

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool Active { get; set; }
    public DateTime DateCreated { get; set; }
}

Get Operation

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    int personId = 1;
    Person person = cn.Get<Person>(personId);
    cn.Close();
}

Simple Insert Operation

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    Person person = new Person { FirstName = "Foo", LastName = "Bar" };
    int id = cn.Insert(person);
    cn.Close();
}

Advanced Insert Operation (Composite Key)复合主键

//返回dynamic类型,若主键为单,返回主键值,若主键为复合的,返回IDictionary<string,object>
 public static dynamic Insert<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
public class Car
{
    public int ModelId { get; set; }
    public int Year { get; set; }
    public string Color { get; set; }
}

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    Car car = new Car { Color = "Red" };
   //返回o
    var multiKey = cn.Insert(car);
    cn.Close();

    int modelId = multiKey.ModelId;
    int year = multiKey.Year;
}

Simple Update Operation

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    int personId = 1;
    Person person = _connection.Get<Person>(personId);
    person.LastName = "Baz";
    cn.Update(person);
    cn.Close();
}

Simple Delete Operation

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    Person person = _connection.Get<Person>(1);
    cn.Delete(person);
    cn.Close();
}

GetList Operation (with Predicates)

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
    IEnumerable<Person> list = cn.GetList<Person>(predicate);
    cn.Close();
}

Generated SQL

SELECT 
   [Person].[Id]
 , [Person].[FirstName]
 , [Person].[LastName]
 , [Person].[Active]
 , [Person].[DateCreated] 
FROM [Person] 
WHERE ([Person].[Active] = @Active_0)

Count Operation (with Predicates)

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<Person>(f => f.DateCreated, Operator.Lt, DateTime.UtcNow.AddDays(-5));
    int count = cn.Count<Person>(predicate);
    cn.Close();
}      

Generated SQL

SELECT 
   COUNT(*) Total 
FROM [Person] 
WHERE ([Person].[DateCreated] < @DateCreated_0)