Dapper.NET——輕量ORM

來源:互聯網
上載者:User

標籤:bool   映射   parameter   關係   方法   code   add   data   acl   

  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實體類。

在建立實體類時,屬性名稱一定要與資料庫欄位一一對應。

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.RoleNameFROM   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 = "[email protected]";                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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.