Dapper.NET使用

http://www.cnblogs.com/yankliu-vip/p/4182892.html

Dapper是一款轻量级ORM工具(Github)。如果你在小的项目中,使用Entity Framework、NHibernate 来处理大数据访问及关系映射,未免有点杀鸡用牛刀。你又觉得ORM省时省力,这时Dapper 将是你不二的选择。

1、为什么选择Dapper

  1. 轻量。只有一个文件(SqlMapper.cs),编译完成之后只有120k(好象是变胖了)
  2. 速度快。Dapper的速度接近与IDataReader,取列表的数据超过了DataTable。
  3. 支持多种数据库。Dapper可以在所有Ado.net Providers下工作,包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
  4. 可以映射一对一,一对多,多对多等多种关系。
  5. 性能高。通过Emit反射IDataReader的序列队列,来快速的得到和产生对象,性能不错。
  6. 支持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实体类。

在创建实体类时,属性名称一定要与数据库字段一一对应。

public class User
{
public User()
{
Role = new List<Role>();
}
public int UserId { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string PhoneNumber { get; set; }
public bool IsFirstTimeLogin { get; set; }
public int AccessFailedCount { get; set; }
public DateTime CreationDate { get; set; }
public bool IsActive { get; set; }
public List<Role> Role { get; set; }
}
public class Role
{
public int RoleId { get; set; }
public string RoleName { get; set; }
}
public class Customer
{
public int UserId { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string PhoneNumber { get; set; }
public bool IsFirstTimeLogin { get; set; }
public int AccessFailedCount { get; set; }
public DateTime CreationDate { get; set; }
public bool IsActive { get; set; }
public Role Role { get; set; }
}

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();
}

最新文章

  1. JS网站右下角悬浮视窗可关闭广告
  2. hibernate-cascade级联关系
  3. CORS(跨域资源共享)
  4. Cocos2d-x 3.2 项目源代码从Mac打包到安卓教程【转自:http://www.2cto.com/kf/201410/342649.html】
  5. 在备份和导入mysql数据库遇到的几个问题
  6. AndroidSDK无法下载API包的解决方法
  7. centos5 vim升级到7.4
  8. dorado抽取js
  9. 为啥NSString的属性要用copy而不用retain
  10. 《割绳子》《蜡笔物理学》《Contre Jour》《顽皮鳄鱼爱洗澡》等游戏用Box2D引擎实现物理部分的方法(转)
  11. linux命令和awk
  12. Spring Aop底层原理详解
  13. 我对DFS的理解
  14. 水晶报表使用IEnumerable&lt;T&gt;数据源
  15. Ubuntu-18.04安装Docker
  16. BP神经网络与Python实现
  17. WordPress数据库及各表结构分析
  18. 深入浅出的webpack构建工具--webpack4+vue搭建环境 (十三)
  19. 小学四则运算APP 第三阶段冲刺
  20. python初级 1 数据类型和变量

热门文章

  1. SpringMVC类型转换器、属性编辑器
  2. UML类图分析
  3. 第六百一十八天 how can I 坚持
  4. 详细介绍ASP.NET页面间数据传递的使用方法
  5. 添加SSH密钥到GitHub
  6. python练习程序(显示图像)
  7. text-align:justify实现文本两端对齐布局,兼容IE
  8. KVM背靠Linux好乘凉
  9. href 做导航 特效
  10. DocNan博文目录
  11. iOS 模式详解—「runtime面试、工作」看我就 &#128018; 了 ^_^.
  12. MSDTC启用——分布式事务
  13. NLP入门(七)中文预处理之繁简体转换及获取拼音
  14. Java开发笔记(六十七)清单:ArrayList和LinkedList
  15. TCP/IP学习
  16. 【git】如何去解决fatal: refusing to merge unrelated histories
  17. Lambda 动态表达式(排序)
  18. 树状数组入门 hdu1541 Stars
  19. C++ LocalAlloc() &amp; LocalSize() &amp; LocalFree ()
  20. mysql之零碎知识