ORM系列之三:Dapper

目录

1.Dapper 简介

2.Dapper 安装

3.Dapper 使用

 

 

 

 

 

 

Dapper简介  

         Dapper是一个轻量级的ORM框架,短小精悍,正如其名。对于小项目,使用EF,NHibernate这样的重量级ORM框架,有点杀鸡用牛刀的感觉,此时Dapper会是个很不错的选择。 

Dapper优势  

  1. 轻量,Dapper.dll大小不到105KB。
  2. 快,读取数据接近IDataReader。
  3. 支持大部分主流的数据库,如:MS SQL,Oracle,MySQL,Sqlite等等。
  4. 支持映射一对一,一对多,多对多关系。 

Dapper原理

  Dapper实际上是扩展了IDbConnection,提供了一些的查询和执行方法,对ADO.NET的访问对象进行了最底层的封装,所以性能接近ADO.NET。 

Dapper安装  

  通过NuGet包管理器,直接搜索Dapper,选择搜索结果中的第一个,点击安装到项目中即可。

    

Dapper 使用

  Dapper查询结果支持强类型,同时也支持动态类型,在实际开发中我们建议使用强类型,方便以后维护。 

实体类  

  示例中需要用到两个实体类型,分别为Post.cs和User.cs。  

public class Post
 {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public User User { get; set; }
 }

 public class User
{
        public int Id { get; set; }
        public string Name { get; set; }
}

  创建表脚本:  

CREATE TABLE [dbo].[Post](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) NULL,
    [Content] [nvarchar](2000) NULL,
    [UserId] [int] NULL,
 CONSTRAINT [PK_Post] 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]

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](20) NULL,
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]

 

辅助类

  由于Dapper是IDbConnection.cs进行了扩展,所以我们自己要管理数据库连接,本示例我封装了一个简单的DBHelper.cs用于管理数据库连接的打开和关闭。

  

  public class DBHelper
    {
        private static string strConn = "Data Source=2012-20130731KI;Initial Catalog=QYHDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

        /// <summary>
        /// 打开链接
        /// </summary>
        public static SqlConnection OpenConnection()
        {
            SqlConnection conn = new SqlConnection(strConn);
            conn.Open();

            return conn;
        }

        public static void CloseConnection()
        {
            SqlConnection conn = new SqlConnection(strConn);
            if (conn.State != System.Data.ConnectionState.Closed)
            {
                conn.Close();
            }
        }

    }

 

方法测试

  /// <summary>
    /// Dapper示例
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = DBHelper.OpenConnection();

            //示例1:简单查询,返回强类型列表
            Console.WriteLine("示例1:简单查询,返回强类型列表:");
            var result1 = conn.Query<Post>("select Id,Title,Content from Post");
            foreach (var item in result1)
            {
                Console.WriteLine(item.Id + " " + item.Title + " " + item.Content);
            }

            //示例2:简单查询,返回动态列表
            Console.WriteLine("示例2:简单查询,返回动态列表");
            var result2 = conn.Query("select Id,Title,Content from Post");
            foreach (var item in result1)
            {
                Console.WriteLine(item.Id + " " + item.Title + " " + item.Content);
            }

            //示例3:带参数的查询
            Console.WriteLine("示例3:带参数的查询");
            var result3 = conn.Query<Post>("select Id,Title,Content from Post where UserId=@UserId", new { UserId = 1 });
            foreach (var item in result3)
            {
                Console.WriteLine(item.Id + " " + item.Title + " " + item.Content);
            }

            //示例4:执行没有返回结果的命名
            //Test04(conn);

            //示例5:执行多次脚本
            //Console.WriteLine("示例5:执行多次脚本");
            //conn.Execute("insert into [User](Name) values(@Name)", new[] { new { Name = "wangwu" }, new { Name = "zhouliu" }, new { Name = "chenqi" } }); 

            ////示例6:返回多个结果
            Console.WriteLine("示例6:返回多个结果");
            string sql = @"select * from Post where Id=@Id
                           select * from [User] where Id=@Id";

            using (var multi = conn.QueryMultiple(sql, new { Id = 1 }))
            {
                Post post = multi.Read<Post>().Single();
                User user = multi.Read<User>().Single();
            }

            //示例7:执行存储过程
            Console.WriteLine("示例7:执行存储过程");
            var result7 = conn.Query<Post>("sp_GetPost", new { Id = 3 }, commandType: CommandType.StoredProcedure);
            foreach (var item in result7)
            {
                Console.WriteLine(item.Id + " " + item.Title + " " + item.Content);
            }

            // 示例8:一对多映射,返回的一行数据映射到数据库多张表
            Console.WriteLine("示例8:一对多映射");
            string sqlOneToMulti = "select * from Post p inner join [User] u on p.UserId=u.Id";

            //Query<TFirst, TSecond, TReturn>
            var data = conn.Query<Post, User, Post>(sqlOneToMulti, (post, user) => { post.User = user; return post; });

            DBHelper.CloseConnection();


            Console.Read();
        }
    }
View Code

 

posted @ 2016-12-09 18:21  齐_大圣  阅读(1985)  评论(2编辑  收藏  举报