农村四月闲人少,勤学苦攻把名扬。这篇文章主要讲述dapper.net 转载相关的知识,希望能为你提供帮助。
Dapper.NET使用
本文目录
- Dapper.NET使用
- 1、为什么选择Dapper
- 2、以Dapper(4.0)为例。
- 2.1 在数据库中建立几张表。
- 2.2实体类。
- 3.使用方法
- 3.1 一对一映射
- 3.2 一对多映射
- 3.3 插入实体
- 3.4 执行存储过程
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
文章图片
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.UsernameAS UserName, c.PasswordHashAS [Password], c.Email, c.PhoneNumber, c.IsFirstTimeLogin, c.AccessFailedCount, c.CreationDate, c.IsActive, r.RoleId, r.RoleName FROMdbo.CICUser c WITH(NOLOCK) LEFT JOIN CICUserRole cr ONcr.UserId = c.UserId LEFT JOIN CICRole r ONr.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(); }
文章图片
推荐阅读
- Android开发之旅-Fragment和Activity之间onCreateOptionsMenu的联系
- 2017.1-TOP5 Android开源库
- NineOldAndroids 基本用法
- Android实践 -- 对apk进行系统签名
- 用用匿名函数和闭包加apply强制待定函数调用时使用特定上下文
- android判断网络是否可用
- Android ViewPager使用方法小结
- Linux系统/dev/mapper目录浅谈
- callee(),call(),apply()方法的使用