纵有疾风起
人生不言弃

轻量级ORM框架初探-Dapper与PetaPoco的基本使用


一、EntityFramework

  EF是传统的ORM框架,也是一个比较重量级的ORM框架。这里仍然使用EF的原因在于为了突出轻量级ORM框架的性能,所谓有对比才有更优的选择。

1.1 准备一张数据库表

  (1)For MSSQL

CREATE TABLE [dbo].[Posts](    [Id] INT NOT NULL PRIMARY KEY IDENTITY,     [CategoryId] INT NOT NULL,     [Slug] VARCHAR(120) NOT NULL,      [Title] NVARCHAR(100) NOT NULL,     [Published] DATETIME NOT NULL,    [Excerpt] NVARCHAR(MAX) NOT NULL,     [Content] NVARCHAR(MAX) NOT NULL);

  (2)For MySQL

CREATE TABLE Posts(    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     CategoryId INT NOT NULL,     Slug VARCHAR(120) NOT NULL,      Title NVARCHAR(100) NOT NULL,     Published DATETIME NOT NULL,    Excerpt LONGTEXT NOT NULL,     Content LONGTEXT NOT NULL);

1.2 使用Model First方式创建数据模型

  (1)通过nuget添加EF组件引用,然后创建edmx数据模型

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图

  (2)由于EF首次使用存在效率问题,因此采用园子里推荐的EF暖机操作作为测试首次执行的代码

    static void WarmupEntityFramework()    {        // EF暖机操作        using (var db = new MyAppDBContext())        {            var objectContext = ((IObjectContextAdapter)db).ObjectContext;            var mappingCollection = (System.Data.Entity.Core.Mapping.StorageMappingItemCollection)objectContext.MetadataWorkspace.GetItemCollection(System.Data.Entity.Core.Metadata.Edm.DataSpace.CSSpace);            mappingCollection.GenerateViews(new System.Collections.Generic.List<System.Data.Entity.Core.Metadata.Edm.EdmSchemaError>());        }    } 

  (3)写一个读取数据的方法,遍历读取Posts表记录(该表有1万行记录)

    static void ModelFirstReadPosts()    {        var dbContext = new MyAppDBContext();        foreach (var item in dbContext.Posts)        {            Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);        }    } 

  (4)编写入口方法,通过Stopwatch记录测试耗时

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(1)

    class Program    {        static Program()        {            WarmupEntityFramework();        }        static void Main(string[] args)        {            Stopwatch watch = new Stopwatch();            watch.Start();            // EF:4.9s            ModelFirstReadPosts();            watch.Stop();            Console.WriteLine("Time consumed : {0} ms", watch.ElapsedMilliseconds);            Console.ReadKey();        }        #region Method01.EntityFramework暖机操作        static void WarmupEntityFramework()        {            // EF暖机操作            using (var db = new MyAppDBContext())            {                var objectContext = ((IObjectContextAdapter)db).ObjectContext;                var mappingCollection = (System.Data.Entity.Core.Mapping.StorageMappingItemCollection)objectContext.MetadataWorkspace.GetItemCollection(System.Data.Entity.Core.Metadata.Edm.DataSpace.CSSpace);                mappingCollection.GenerateViews(new System.Collections.Generic.List<System.Data.Entity.Core.Metadata.Edm.EdmSchemaError>());            }        }         #endregion        #region Method02.Model First方式读取数据库表记录        static void ModelFirstReadPosts()        {            var dbContext = new MyAppDBContext();            foreach (var item in dbContext.Posts)            {                Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);            }        }         #endregion    }

View Code

  F5调试运行,首次查询结果如下图所示:

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(3)

  五次查询之后平均耗时:4.9s

二、Dapper

2.1 关于Dapper

  Dapper是一个开源轻的量级的ORM,只有一个代码文件,完全开源,你可以放在项目里的任何位置,来实现数据到对象的ORM操作,体积小速度快。 

2.2 使用Dapper

  (1)通过nuget添加Dapper组件

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(4)

  (2)针对MSSQL的查询和新增操作

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(5)

    #region Method01.读取MSSQL单张表    // 2.7s    static void DapperReadPosts()    {        using (SqlConnection connection = new SqlConnection(connStr))        {            var postList = connection.Query<Post>("select * from Posts");            foreach (var item in postList)            {                Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);            }        }    }    #endregion    #region Method02.读取MSSQL连接查询    // 2.6s    static void DapperReadJoin()    {        using (SqlConnection connection = new SqlConnection(connStr))        {            // 这里查询结果是动态语言类型            var postList = connection.Query("select Id,Title,GETDATE() as PostDate from Posts");            foreach (var item in postList)            {                Console.WriteLine("ID:{0},PostDate:{1}", item.Id, item.PostDate);            }        }    }    #endregion    #region Method03.读取MSSQL多个结果集    // 2.8s    static void DapperReadMultiResultSet()    {        using (SqlConnection connection = new SqlConnection(connStr))        {            using (var reader = connection.QueryMultiple("select * from Posts;select 1000 as Number;"))            {                var postList = reader.Read<Post>();                foreach (var item in postList)                {                    Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);                }            }        }    }    #endregion    #region Method04.插入MSSQL新记录    // 0.37s    static void InsertPostRecord()    {        using (SqlConnection connection = new SqlConnection(connStr))        {            // 多次插入单条记录            int count = connection.Execute("insert into Posts values(@CategoryId, @Slug, @Title, @Published, @Excerpt, @Content);", new { CategoryId = 10, Slug = "BOOK", Title = "大话设计模式", Published = DateTime.Now.AddDays(1), Excerpt = "ChengJie", Content = "Design Patterns" });            Console.WriteLine("受影响行数:{0}", count);            count = connection.Execute("insert into Posts values(@CategoryId, @Slug, @Title, @Published, @Excerpt, @Content);", new Post() { CategoryId = 10, Slug = "BOOK", Title = "大话数据结构", Published = DateTime.Now.AddDays(1), Excerpt = "ChengJie", Content = "Data Structure" });            Console.WriteLine("受影响行数:{0}", count);            // 一次插入多条记录            IList<Post> postRecords = new List<Post>();            postRecords.Add(new Post() { CategoryId = 10, Slug = "BOOK", Title = "构建之法-现代软件工程", Published = DateTime.Now.AddDays(1), Excerpt = "ZouXin", Content = "Software Engineering" });            postRecords.Add(new Post() { CategoryId = 10, Slug = "BOOK", Title = "编程之美", Published = DateTime.Now.AddDays(1), Excerpt = "ZouXin", Content = "I Love Coding" });            count = connection.Execute("insert into Posts values(@CategoryId, @Slug, @Title, @Published, @Excerpt, @Content);", postRecords);            Console.WriteLine("受影响行数:{0}", count);        }    }    #endregion

View Code

  ① 棒棒哒的地方1:读取多表连接查询

        // 这里查询结果是动态语言类型        var postList = connection.Query("select Id,Title,GETDATE() as PostDate from Posts");        foreach (var item in postList)        {            Console.WriteLine("ID:{0},PostDate:{1}", item.Id, item.PostDate);        }

  ② 棒棒哒的地方2:读取多个查询结果集

        using (var reader = connection.QueryMultiple("select * from Posts;select 1000 as Number;"))        {            var postList = reader.Read<Post>();            foreach (var item in postList)            {                Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);            }        }

  ③ 棒棒哒的地方3:一次插入多条数据记录

    // 一次插入多条记录    IList<Post> postRecords = new List<Post>();    postRecords.Add(new Post() { CategoryId = 10, Slug = "BOOK", Title = "构建之法-现代软件工程", Published = DateTime.Now.AddDays(1), Excerpt = "ZouXin", Content = "Software Engineering" });    postRecords.Add(new Post() { CategoryId = 10, Slug = "BOOK", Title = "编程之美", Published = DateTime.Now.AddDays(1), Excerpt = "ZouXin", Content = "I Love Coding" });    count = connection.Execute("insert into Posts values(@CategoryId, @Slug, @Title, @Published, @Excerpt, @Content);", postRecords);

  ④ 如何跨数据库读取数据记录:依赖于抽象,不依赖于具体

    static void GetPostsCrossMultiDB()    {        // 依赖于抽象,不依赖于具体        using (IDbConnection connection = DbProviderFactories.GetFactory(connSetting.ProviderName).CreateConnection())        {            connection.ConnectionString = connSetting.ConnectionString;            // 使用标准SQL语句屏蔽差异            var postList = connection.Query<Post>("select * from Posts");            foreach (var item in postList)            {                Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);            }        }    } 

  (3)测试结果:

方法 耗时
读取MSSQL单张表 2.7s
读取MSSQL连接查询 2.6s
读取MSSQL多个结果集 2.8s
多次插入MSSQL新记录 148ms

三、PetaPoco

3.1 关于PetaPoco

  PetaPoco是一款适用于.NET应用程序的轻型对象关系映射器。与那些功能完备的ORM(如NHibernate或Entity Framework)不同的是,PetaPoco更注重易用性和性能,而非丰富的功能。使用PetaPoco只需要引入一个C#文件,可以使用强类型的 POCO(Plain Old CLR Object),并支持使用T4模板生成的类等等。

3.2 使用PetaPoco

  (1)通过nuget添加PetaPoco组件

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(7)

  (2)编辑Database.tt模板文件,前提是首先将连接字符串配置正确

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(8)

  (3)针对MSSQL的读取和插入操作

轻量级ORM框架初探-Dapper与PetaPoco的基本使用插图(9)

    private static void ReadAllPostData()    {        using (var context = new MyAppDBContext())        {            var postList = context.Query<Post>("select * from Posts");            foreach (var item in postList)            {                Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);            }        }    }    private static void InsertNewPostData()    {        var post = new Post        {            CategoryId = 1,            Slug = "BOOK",            Title = "Microsoft SQL Server 2008技术内幕",            Content = Guid.NewGuid().ToString(),            Excerpt = Guid.NewGuid().ToString(),            Published = DateTime.Now        };        var count = post.Insert();        Console.WriteLine("受影响行数:{0}", count);    }

View Code

  (4)测试结果:

方法 耗时
读取MSSQL单张表 2.7s
插入MSSQL新纪录 30ms

SourceCode

(1)ORMDemo:http://pan.baidu.com/s/1pJAEf0n

Reference

(1)Dapper.NET:https://github.com/StackExchange/dapper-dot-net

 

文章转载于:https://www.cnblogs.com/edisonchou/p/4854036.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » 轻量级ORM框架初探-Dapper与PetaPoco的基本使用

分享到: 生成海报
avatar

评论 抢沙发

评论前必须登录!

立即登录   注册

切换注册

登录

忘记密码 ?

切换登录

注册

我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活