Dapper.NET使用
- 1、為什麼選擇Dapper
- 2、以Dapper（4.0）為例。
- 2.1 在資料庫中建立幾張表。
- 2.2實體類。
- 3.使用方法
- 3.1 一對一映射
- 3.2 一對多映射
- 3.3 插入實體
- 3.4 執行存儲過程
Dapper是一款輕量級ORM工具（Github）。如果你在小的項目中，使用Entity Framework、NHibernate 來處理大資料通路及關系映射，未免有點殺雞用牛刀。你又覺得ORM省時省力，這時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
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
) 在建立實體類時，屬性名稱一定要與資料庫字段一一對應。
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; }
} private static SqlConnection GetSqlConnection(string sqlConnectionString)
{
SqlConnection conn = new SqlConnection(sqlConnectionString);
conn.Open();
return conn;
}
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();
}
} 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!");
}
} 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 = "[email protected]";
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();
}
} /// <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();
} --//儲存過程分頁sql腳本
USE [Xindongai_App]
GO
/****** Object: StoredProcedure [dbo].[sp_DapperPager] Script Date: 2017/02/10 12:23:40 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Create PROCEDURE [dbo].[sp_DapperPager]
( @TableName VARCHAR(1000), --表名,多表是請使用 tA a inner join tB b On a.AID = b.AID
@PrimaryKey NVARCHAR(100), --主鍵，可以帶表頭 a.AID
@Fields NVARCHAR(2000) = '*',--讀取字段
@Condition NVARCHAR(3000) = '',--Where條件
@PageIndex INT = 1, --開始頁碼
@PageSize INT = 10, --頁大小
@Order NVARCHAR(200) = '', --排序字段
@TotalItemCount INT = 0 OUT
)
AS
DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0
BEGIN
SET @strWhere = ' WHERE ' + @Condition + ' '
END
ELSE
SET @strWhere = ''
IF (charindex(ltrim(rtrim(@PrimaryKey)),@Order)=0)
BEGIN
IF(@Order='')
SET @Order = @PrimaryKey + ' DESC '
ELSE
SET @Order = @Order+ ' , '+@PrimaryKey + ' DESC '
END
SET @strsql = 'SELECT @TotalItemCount = Count(1) FROM ' + @TableName + @strWhere
EXECUTE sp_executesql @strsql ,N'@TotalItemCount INT output',@TotalItemCount OUTPUT
IF @PageIndex = 1 --第一頁提高性能
BEGIN
SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Fields
+ ' FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Order
END
/* Execute dynamic query */
DECLARE @START_ID NVARCHAR(50)
DECLARE @END_ID NVARCHAR(50)
SET @START_ID = CONVERT(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
SET @END_ID = CONVERT(NVARCHAR(50),@PageIndex * @PageSize)
SET @strsql = ' SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Order+') AS rownum,
'+@Fields+ '
FROM '+@TableName + @strWhere +') AS XX
WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC'
EXEC(@strsql)
RETURN