Dapper 学习笔记
一、基础
1.Dapper代码就一个SqlMapper.cs文件， 前人测试Dapper速度较快 的Orm,读取速度接近IDataReader,超过DataTable。
2.a.下载地址 https://github.com/StackExchange/dapper-dot-net ,包含在线示例 （test project）。b.net 下可以通过 Nuget下载。
3.实体类用NHibernateMappingGenerator生成。
二、示例代码
1.sql脚本
USE [FactoryDeal] GO /****** Object: Table [dbo].[User] Script Date: 06/12/2015 10:57:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[User]( [Id] [int] IDENTITY(1,1) NOT NULL, [LoginNo] [nvarchar](16) NOT NULL, [PassWord] [nvarchar](32) NOT NULL, [PassPort] [nvarchar](32) NULL, [Type] [tinyint] NULL, [FactoryId] [int] NULL, [RoleId] [int] NULL, [TrueName] [nvarchar](8) NULL, [NickName] [nvarchar](32) NULL, [Gender] [tinyint] NULL, [Company] [nvarchar](32) NULL, [Dept] [nvarchar](32) NULL, [SelfCellphone] [nvarchar](32) NULL, [SelfTellphone] [nvarchar](32) NULL, [Duty] [nvarchar](32) NULL, [Address] [nvarchar](64) NULL, [Legal] [nvarchar](32) NULL, [LinkMan] [nvarchar](16) NULL, [Cellphone] [nvarchar](32) NULL, [Tellphone] [nvarchar](32) NULL, [Fax] [nvarchar](32) NULL, [PostCode] [nvarchar](6) NULL, [Email] [nvarchar](16) NULL, [QQ] [nvarchar](16) NULL, [BusinessImgPath] [nvarchar](128) NULL, [TaxRegImgPath] [nvarchar](128) NULL, [OrgImgPath] [nvarchar](128) NULL, [AuthNo] [nvarchar](20) NULL, [ifAuth] [bit] NULL, [CreateTime] [datetime] NULL, [EditeTime] [datetime] NULL, [EditeMan] [nvarchar](8) NULL, [Status] [tinyint] NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO USE [FactoryDeal] GO /****** Object: Table [dbo].[User_Module] Script Date: 06/12/2015 11:00:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[User_Module]( [UserId] [int] NOT NULL, [ModuleId] [int] NOT NULL, CONSTRAINT [PK_User_Module] PRIMARY KEY CLUSTERED ( [UserId] ASC, [ModuleId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO USE [FactoryDeal] GO /****** Object: StoredProcedure [dbo].[usp_insertChatRecord] Script Date: 06/12/2015 11:01:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create proc [dbo].[usp_insertChatRecord] @FromLoginNo [nchar](32), @ToLoginNo [nvarchar](32), @Type [tinyint], @Message [text], @SendTime [datetime], @Status [tinyint] as declare @nowYear nvarchar(4) declare @sqlstr nvarchar(1024) set @nowYear=CAST(DATEPART(year,GETDATE()) as nvarchar(4)) begin if (not exists(select object_id from sys.tables where name='ChatRecord_'+@nowYear)) begin set @sqlstr= 'CREATE TABLE ChatRecord_'+@nowYear+'( [Id] [int] IDENTITY(1,1) NOT NULL, [FromLoginNo] [nchar](32) NOT NULL, [ToLoginNo] [nvarchar](32) NOT NULL, [Type] [tinyint] NULL), [Message] [text] NULL, [SendTime] [datetime] NULL, [Status] [tinyint] NULL) go' exec sp_executesql @sqlstr end ---动态插入数据 set @sqlstr='INSERT INTO ChatRecord_'+@nowYear+' ([FromLoginNo] ,[ToLoginNo] ,[Type] ,[Message] ,[SendTime] ,[Status]) VALUES(@FromLoginNo,@ToLoginNo,@Type,@Message ,@SendTime,@Status)' exec sp_executesql @sqlstr ,N'@FromLoginNo nchar(32),@ToLoginNo [nvarchar](32),@Type [tinyint],@Message [text],@SendTime [datetime],@Status [tinyint]', @FromLoginNo,@ToLoginNo,@Type,@Message,@SendTime,@Status end
2.实体类
public class User { public virtual int Id { get; set; } public virtual string Loginno { get; set; } public virtual string Password { get; set; } public virtual string Passport { get; set; } public virtual byte? Type { get; set; } public virtual int? Factoryid { get; set; } public virtual int? Roleid { get; set; } public virtual string Truename { get; set; } public virtual string Nickname { get; set; } public virtual byte? Gender { get; set; } public virtual string Company { get; set; } public virtual string Dept { get; set; } public virtual string Selfcellphone { get; set; } public virtual string Selftellphone { get; set; } public virtual string Duty { get; set; } public virtual string Address { get; set; } public virtual string Legal { get; set; } public virtual string Linkman { get; set; } public virtual string Cellphone { get; set; } public virtual string Tellphone { get; set; } public virtual string Fax { get; set; } public virtual string Postcode { get; set; } public virtual string Email { get; set; } public virtual string Qq { get; set; } public virtual string Businessimgpath { get; set; } public virtual string Taxregimgpath { get; set; } public virtual string Orgimgpath { get; set; } public virtual string Authno { get; set; } public virtual bool? Ifauth { get; set; } public virtual DateTime? Createtime { get; set; } public virtual DateTime? Editetime { get; set; } public virtual string Editeman { get; set; } // public virtual byte? Status { get; set; } public int MyProperty { get; set; } public IList<UserModule> Modules { get; set; } } public class UserModule { public virtual int Userid { get; set; } public virtual int Moduleid { get; set; } public User User { get; set; } } public class ChatRecord { public virtual string Fromloginno { get; set; } public virtual string Tologinno { get; set; } public virtual byte? Type { get; set; } public virtual string Message { get; set; } public virtual DateTime? Sendtime { get; set; } public virtual byte? Status { get; set; } }
3.Dapper方法
private IList<User> GetAll() { string sql = "select * from [user]"; return conn.Query<User>(sql).ToList(); } private User GetSingleById(int id) { string sql = "select * from [user] where id=@id"; return conn.Query<User>(sql, param: new {id=id}).FirstOrDefault(); //param 必须是对象项属性 param:id 不正确 } private User Add(User user) { string sql = "insert into [user](loginNo,password) OUTPUT inserted.Id values(@loginNo,@password)"; user.Id=(int)conn.ExecuteScalar(sql, user); return user; } private bool Update(User user) { string sql = "update [user] set loginNo=@loginNo where id=@id"; return conn.Execute(sql, user)>=1; } private bool Delete(int id) { string sql = "delete from [user] where id=@id"; return conn.Execute(sql, new { id = id })>=1; } //1-N private IList<User> GetAllUserWithModules() { string sql = "select * from [user] as a left join User_Module as b on a.Id=b.UserId"; //保证join UserModule条数 User user = null; //异常 When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id //必须添加 splitOn 同时查询字段必须包括外键 b.UserId return conn.Query<User,UserModule, User>(sql, (u, m) => { if (user==null||user.Id!=u.Id) { user = u; } if (m != null) { if (user.Modules==null) { user.Modules = new List<UserModule>(); } user.Modules.Add(m); } return user; },splitOn:"Id,UserId").Distinct().ToList(); } //1*1 private IList<UserModule> GetAllModuleWithUser() { //inner join string sql = "select * from User_Module a left join [User] b on a.UserId=b.Id "; return conn.Query<UserModule, User, UserModule>(sql,(m, u) => { m.User = u; return m; },"UserId,Id").ToList(); } //事务 private bool DeleteUserWithModule(int id) { string sql1 = "delete from [User] where id=@id"; string sql2 = "delete from User_Module where userId=@userId"; using (conn) { conn.Open(); using (var transaction = conn.BeginTransaction()) { try { //Query必须添加 transaction:transaction conn.Execute(sql1, new { id = id},transaction:transaction); conn.Execute(sql2, new { userId = id },transaction:transaction); transaction.Commit(); return true; } catch (Exception) { transaction.Rollback(); return false; } } } } private bool AdddChatRecord(ChatRecord chatRecord) { conn.Execute("usp_insertChatRecord", param: chatRecord,commandType:System.Data.CommandType.StoredProcedure); return true; }
三、总结
个人感觉dapper 在处理join实体转换，尤其是1对多方面处理起来比麻烦。有什么好的实现方式，还请各位大虾指点....
