Dapper.NET使用

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

本文目录

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

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

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

        }

巴特西

Dapper.NET——轻量ORM

Dapper.NET使用

1、为什么选择Dapper

2、以Dapper（4.0）为例。

2.1 在数据库中建立几张表。

2.2实体类。

3.使用方法

3.1 一对一映射

3.2 一对多映射

3.3 插入实体

3.4 执行存储过程

最新文章

热门文章