Dapper.NET——轻量ORM

迷南。 2022-04-10 12:51 234阅读 0赞

# [Dapper.NET——轻量ORM][Dapper.NET_ORM] #

# Dapper.NET使用 #

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

## 1、为什么选择Dapper ##

1.  轻量。只有一个文件（[SqlMapper.cs][]），编译完成之后只有120k(好象是变胖了)
2.  速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
3.  支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
4.  可以映射一对一，一对多，多对多等多种关系。
5.  性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。
6.  支持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实体类。 ###

在创建实体类时，属性名称一定要与数据库字段一一对应。

![ExpandedBlockStart.gif][]

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]: https://www.cnblogs.com/yankliu-vip/p/4182892.html
[Github]: https://github.com/StackExchange/dapper-dot-net.git
[SqlMapper.cs]: https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs
[ExpandedBlockStart.gif]: /images/20220330/6d871e16b7aa471b98277d9093c257f6.png