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(,) NOT NULL,

    [LoginNo] [nvarchar]() NOT NULL,

    [PassWord] [nvarchar]() NOT NULL,

    [PassPort] [nvarchar]() NULL,

    [Type] [tinyint] NULL,

    [FactoryId] [int] NULL,

    [RoleId] [int] NULL,

    [TrueName] [nvarchar]() NULL,

    [NickName] [nvarchar]() NULL,

    [Gender] [tinyint] NULL,

    [Company] [nvarchar]() NULL,

    [Dept] [nvarchar]() NULL,

    [SelfCellphone] [nvarchar]() NULL,

    [SelfTellphone] [nvarchar]() NULL,

    [Duty] [nvarchar]() NULL,

    [Address] [nvarchar]() NULL,

    [Legal] [nvarchar]() NULL,

    [LinkMan] [nvarchar]() NULL,

    [Cellphone] [nvarchar]() NULL,

    [Tellphone] [nvarchar]() NULL,

    [Fax] [nvarchar]() NULL,

    [PostCode] [nvarchar]() NULL,

    [Email] [nvarchar]() NULL,

    [QQ] [nvarchar]() NULL,

    [BusinessImgPath] [nvarchar]() NULL,

    [TaxRegImgPath] [nvarchar]() NULL,

    [OrgImgPath] [nvarchar]() NULL,

    [AuthNo] [nvarchar]() NULL,

    [ifAuth] [bit] NULL,

    [CreateTime] [datetime] NULL,

    [EditeTime] [datetime] NULL,

    [EditeMan] [nvarchar]() 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](),

@ToLoginNo [nvarchar](),

@Type [tinyint],

@Message [text],

@SendTime [datetime],

@Status [tinyint]

as

declare @nowYear nvarchar()

declare @sqlstr nvarchar()

set @nowYear=CAST(DATEPART(year,GETDATE()) as nvarchar())

begin

  if (not exists(select object_id from sys.tables where name='ChatRecord_'+@nowYear))

  begin

   set @sqlstr= 'CREATE TABLE ChatRecord_'+@nowYear+'(

    [Id] [int] IDENTITY(,) NOT NULL,

    [FromLoginNo] [nchar]() NOT NULL,

    [ToLoginNo] [nvarchar]() 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)>=;

        }

        private bool Delete(int id)

        {

            string sql = "delete from [user] where id=@id";

            return conn.Execute(sql, new { id = id })>=;

        }

        //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对多方面处理起来比麻烦。有什么好的实现方式，还请各位大虾指点....

巴特西

Dapper 学习笔记

最新文章

热门文章