Dapper的基本使用
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 Name=@name WHERE id =@id";
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 FORM 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);
}
}
Dapper.NET——轻量ORM
Dapper.NET使用
本文目录
Dapper是一款轻量级ORM工具（Github）。如果你在小的项目中，使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀。你又觉得ORM省时省力，这时Dapper 将是你不二的选择。
1、为什么选择Dapper
- 轻量。只有一个文件（SqlMapper.cs），编译完成之后只有120k(好象是变胖了)
- 速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
- 支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
- 可以映射一对一，一对多，多对多等多种关系。
- 性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。
- 支持FrameWork2.0，3.0，3.5，4.0，4.5
2、以Dapper（4.0）为例。
2.1 在数据库中建立几张表。
CREATE TABLE [dbo].[CICUser]
(
[UserId] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[Username] [nvarchar](256) NOT NULL,
[PasswordHash] [nvarchar](500) NULL,
[Email] [nvarchar](256) NULL,
[PhoneNumber] [nvarchar](30) NULL,
[IsFirstTimeLogin] [bit] DEFAULT(1) NOT NULL,
[AccessFailedCount] [int] DEFAULT(0) NOT NULL,
[CreationDate] [datetime] DEFAULT(GETDATE()) NOT NULL,
[IsActive] [bit] DEFAULT(1) NOT NULL
)
CREATE TABLE [dbo].[CICRole]
(
[RoleId] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
)
CREATE TABLE [dbo].[CICUserRole]
(
[Id] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[UserId] [int] FOREIGN KEY REFERENCES [dbo].[CICUser] ([UserId]) NOT NULL,
[RoleId] [int] FOREIGN KEY REFERENCES [dbo].[CICRole] ([RoleId]) NOT NULL
)
2.2实体类。
在创建实体类时，属性名称一定要与数据库字段一一对应。
实体类3.使用方法
3.1 一对一映射
private static void OneToOne(string sqlConnectionString)
{
List<Customer> userList = new List<Customer>();
using (IDbConnection conn = GetSqlConnection(sqlConnectionString))
{
string sqlCommandText = @"SELECT c.UserId,c.Username AS UserName,
c.PasswordHash AS [Password],c.Email,c.PhoneNumber,c.IsFirstTimeLogin,c.AccessFailedCount,
c.CreationDate,c.IsActive,r.RoleId,r.RoleName
FROM dbo.CICUser c WITH(NOLOCK)
INNER JOIN CICUserRole cr ON cr.UserId = c.UserId
INNER JOIN CICRole r ON r.RoleId = cr.RoleId";
userList = conn.Query<Customer, Role, Customer>(sqlCommandText,
(user, role) => { user.Role = role; return user; },
null,
null,
true,
"RoleId",
null,
null).ToList();
}
if (userList.Count > 0)
{
userList.ForEach((item) => Console.WriteLine("UserName:" + item.UserName +
"----Password:" + item.Password +
"-----Role:" + item.Role.RoleName +
"\n"));
Console.ReadLine();
}
}
3.2 一对多映射
private static void OneToMany(string sqlConnectionString)
{
Console.WriteLine("One To Many");
List<User> userList = new List<User>();
using (IDbConnection connection = GetSqlConnection(sqlConnectionString))
{
string sqlCommandText3 = @"SELECT c.UserId,
c.Username AS UserName,
c.PasswordHash AS [Password],
c.Email,
c.PhoneNumber,
c.IsFirstTimeLogin,
c.AccessFailedCount,
c.CreationDate,
c.IsActive,
r.RoleId,
r.RoleName
FROM dbo.CICUser c WITH(NOLOCK)
LEFT JOIN CICUserRole cr
ON cr.UserId = c.UserId
LEFT JOIN CICRole r
ON r.RoleId = cr.RoleId";
var lookUp = new Dictionary<int, User>();
userList = connection.Query<User, Role, User>(sqlCommandText3,
(user, role) =>
{
User u;
if (!lookUp.TryGetValue(user.UserId, out u))
{
lookUp.Add(user.UserId, u = user);
}
u.Role.Add(role);
return user;
}, null, null, true, "RoleId", null, null).ToList();
var result = lookUp.Values;
}
if (userList.Count > 0)
{
userList.ForEach((item) => Console.WriteLine("UserName:" + item.UserName +
"----Password:" + item.Password +
"-----Role:" + item.Role.First().RoleName +
"\n"));
Console.ReadLine();
}
else
{
Console.WriteLine("No Data In UserList!");
}
}
3.3 插入实体
public static void InsertObject(string sqlConnectionString)
{
string sqlCommandText = @"INSERT INTO CICUser(Username,PasswordHash,Email,PhoneNumber)VALUES(
@UserName,
@Password,
@Email,
@PhoneNumber
)";
using (IDbConnection conn = GetSqlConnection(sqlConnectionString))
{
User user = new User();
user.UserName = "Dapper";
user.Password = "654321";
user.Email = "Dapper@infosys.com";
user.PhoneNumber = "13795666243";
int result = conn.Execute(sqlCommandText, user);
if (result > 0)
{
Console.WriteLine("Data have already inserted into DB!");
}
else
{
Console.WriteLine("Insert Failed!");
}
Console.ReadLine();
}
}
3.4 执行存储过程
/// <summary>
/// Execute StoredProcedure and map result to POCO
/// </summary>
/// <param name="sqlConnnectionString"></param>
public static void ExecuteStoredProcedure(string sqlConnnectionString)
{
List<User> users = new List<User>();
using (IDbConnection cnn = GetSqlConnection(sqlConnnectionString))
{
users = cnn.Query<User>("dbo.p_getUsers",
new { UserId = 2 },
null,
true,
null,
CommandType.StoredProcedure).ToList();
}
if (users.Count > 0)
{
users.ForEach((user) => Console.WriteLine(user.UserName + "\n"));
}
Console.ReadLine();
}
/// <summary>
/// Execute StroedProcedure and get result from return value
/// </summary>
/// <param name="sqlConnnectionString"></param>
public static void ExecuteStoredProcedureWithParms(string sqlConnnectionString)
{
DynamicParameters p = new DynamicParameters();
p.Add("@UserName", "cooper");
p.Add("@Password", "123456");
p.Add("@LoginActionType", null, DbType.Int32, ParameterDirection.ReturnValue);
using (IDbConnection cnn = GetSqlConnection(sqlConnnectionString))
{
cnn.Execute("dbo.p_validateUser", p, null, null, CommandType.StoredProcedure);
int result = p.Get<int>("@LoginActionType");
Console.WriteLine(result);
}
Console.ReadLine();
}
浙公网安备 33010602011771号