dapperpoco mysql_轻量级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 TABLEPosts

(

IdINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

CategoryIdINT NOT NULL,

SlugVARCHAR(120) NOT NULL,

TitleNVARCHAR(100) NOT NULL,

PublishedDATETIME NOT NULL,

Excerpt LONGTEXTNOT NULL,

Content LONGTEXTNOT NULL);

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

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

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

static voidWarmupEntityFramework()

{//EF暖机操作

using (var db = newMyAppDBContext())

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

}

}

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

static voidModelFirstReadPosts()

{var dbContext = newMyAppDBContext();foreach (var item indbContext.Posts)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}

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

classProgram

{staticProgram()

{

WarmupEntityFramework();

}static void Main(string[] args)

{

Stopwatch watch= newStopwatch();

watch.Start();//EF:4.9s

ModelFirstReadPosts();

watch.Stop();

Console.WriteLine("Time consumed : {0} ms", watch.ElapsedMilliseconds);

Console.ReadKey();

}#region Method01.EntityFramework暖机操作

static voidWarmupEntityFramework()

{//EF暖机操作

using (var db = newMyAppDBContext())

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

}

}#endregion

#region Method02.Model First方式读取数据库表记录

static voidModelFirstReadPosts()

{var dbContext = newMyAppDBContext();foreach (var item indbContext.Posts)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}#endregion}

View Code

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

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

二、Dapper

2.1 关于Dapper

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

2.2 使用Dapper

(1)通过nuget添加Dapper组件

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

#region Method01.读取MSSQL单张表

//2.7s

static voidDapperReadPosts()

{using (SqlConnection connection = newSqlConnection(connStr))

{var postList = connection.Query("select * from Posts");foreach (var item inpostList)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}

}#endregion

#region Method02.读取MSSQL连接查询

//2.6s

static voidDapperReadJoin()

{using (SqlConnection connection = newSqlConnection(connStr))

{//这里查询结果是动态语言类型

var postList = connection.Query("select Id,Title,GETDATE() as PostDate from Posts");foreach (var item inpostList)

{

Console.WriteLine("ID:{0},PostDate:{1}", item.Id, item.PostDate);

}

}

}#endregion

#region Method03.读取MSSQL多个结果集

//2.8s

static voidDapperReadMultiResultSet()

{using (SqlConnection connection = newSqlConnection(connStr))

{using (var reader = connection.QueryMultiple("select * from Posts;select 1000 as Number;"))

{var postList = reader.Read();foreach (var item inpostList)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}

}

}#endregion

#region Method04.插入MSSQL新记录

//0.37s

static voidInsertPostRecord()

{using (SqlConnection connection = newSqlConnection(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 postRecords = new List();

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 inpostList)

{

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();foreach (var item inpostList)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}

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

//一次插入多条记录

IList postRecords = new List();

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

{//依赖于抽象，不依赖于具体

using (IDbConnection connection =DbProviderFactories.GetFactory(connSetting.ProviderName).CreateConnection())

{

connection.ConnectionString=connSetting.ConnectionString;//使用标准SQL语句屏蔽差异

var postList = connection.Query("select * from Posts");foreach (var item inpostList)

{

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组件

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

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

private static voidReadAllPostData()

{using (var context = newMyAppDBContext())

{var postList = context.Query("select * from Posts");foreach (var item inpostList)

{

Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title);

}

}

}private static voidInsertNewPostData()

{var post = newPost

{

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

Reference

作者：周旭龙

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。