petapoco mysql_[原创].netcore通过PetaPoco操作mysql、sqlserver等

一、引入PetaPoco.NetCore，PetaPoco.NetCore 是基于PetaPoco的轻量ORM，支持.netframework和.netcore，支持单个实体对象映射，也支持多实体对象映射，NetCore未需指定驱动连接，其它API一致。

github地址：https://github.com/qingask/PetaPoco.NetCore

nugut地址：https://www.nuget.org/packages/PetaPoco.NetCore

通过nuget安装 PM>Install-Package PetaPoco.NetCore

二、.netcore配置

在project.json增加相应.netcore版本的数据库驱动引用，这里mysql的驱动使用Pomelo.Data.MySql，mysql官方的netcore版本驱动兼容性太差，坑太多，等完善后可替换为官方的myssql core驱动

"dependencies": {

"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0-rc2-final",

"Microsoft.Extensions.Configuration.Json": "1.0.0-rc2-final",

"Microsoft.Extensions.Configuration.UserSecrets": "1.0.0-rc2-final",

"Microsoft.NETCore.App": {

"version": "1.0.0-rc2-3002702",

"type": "platform"

},

"Pomelo.Data.MySql": "1.0.0",

"System.Text.Encoding.CodePages": "4.0.1"

}

三、使用PetaPoco.NetCore

1.测试sql

测试sql

CREATE TABLE blogs (

BlogId int(11) NOT NULL PRIMARY KEY,

Url varchar(1000) DEFAULT NULL

);

create table post(

id int,

title varchar(32),

author int

);

drop table author;

create table author(

id int,

name varchar(32)

);

INSERT into blogs values(1,'test1'); INSERT into blogs values(2,'test2');

INSERT into author value(1,'作者1'); INSERT into author value(2,'作者2');

INSERT into post values(1,'book1',1); INSERT into post values(2,'book2',1); INSERT into post values(3,'book3',2); INSERT into post values(4,'book4',2);

2.使用PetaPoco.NetCore

MySqlConnection connection = new MySqlConnection(""server=localhost;database=test;uid=root;password=123456;charset=utf8;SslMode=None"");

var db = new Database(connection);

//实体测试

Blog blog = new Blog() { BlogId = 3, Url = "test3" };

//保存

var result = db.Insert(blog);

//编辑

blog.Url = "test333";

result = db.Update(blog);

//删除

result = db.Delete(blog);

//sql测试

var sql1 = Sql.Builder.Append("insert into blogs values(4,'test4')");

result = db.Execute(sql1);

var sql2 = Sql.Builder.Append("update blogs set Url='test444' where BlogId=4");

result = db.Execute(sql2);

//查询

var model2 = db.SingleOrDefault(1);

//列表

var list = db.Query(Sql.Builder.Append("select * from blogs")).ToList();

//分页

var list2 = db.Page(1, 2, Sql.Builder.Append("select * from blogs"));

//查询

var sql3 = Sql.Builder.Append("select * from blogs where BlogId=4");

var model1 = db.Query(sql3).FirstOrDefault();

var model3 = db.FirstOrDefault(sql3);

//返回多个结果测试

result = db.Fetch(

(p, a) =>

{

p.author_obj = a;

return p;

},

@"SELECT * FROM post LEFT JOIN author ON post.author = author.id ORDER BY post.id");

using (var multi = db.QueryMultiple("select * from post"))

{

result = multi.Read().ToList();

}

using (var multi = db.QueryMultiple(@"SELECT * FROM post LEFT JOIN author ON post.author = author.id ORDER BY post.id"))

{

result = multi.Read((p, a) => { p.author_obj = a; return p; }).ToList();

}

using (var multi = db.QueryMultiple("select * from post;select * from author;"))

{

var p = multi.Read().First();

var a = multi.Read().First();

}