首页 > 代码库 > [原创].netcore通过PetaPoco操作mysql、sqlserver等

[原创].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

测试sqlCREATE 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<Blog>(1);              //列表            var list = db.Query<Blog>(Sql.Builder.Append("select * from blogs")).ToList();              //分页            var list2 = db.Page<Blog>(1, 2, Sql.Builder.Append("select * from blogs"));              //查询            var sql3 = Sql.Builder.Append("select * from blogs where BlogId=4");            var model1 = db.Query<Blog>(sql3).FirstOrDefault();            var model3 = db.FirstOrDefault<Blog>(sql3);            //返回多个结果测试            result = db.Fetch<post, author, post>(            (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<post>().ToList();            }            using (var multi = db.QueryMultiple(@"SELECT * FROM post LEFT JOIN author ON post.author = author.id ORDER BY post.id"))            {                result = multi.Read<post, author, post>((p, a) => { p.author_obj = a; return p; }).ToList();            }            using (var multi = db.QueryMultiple("select * from post;select * from author;"))            {                var p = multi.Read<post>().First();                var a = multi.Read<author>().First();            }

  

 

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