.NET技术-2.0. 操作数据库-Dapper

.NET技术-2.0. 操作数据库-Dapper

 

项目参见：

 

 

1. 为什么选择Dapper

1) 性能优越：

    其实在各大网站上，我们大概都会看到这样的一个对比效果图，在超过500次poco serialization的过程中所表现的性能，我们发现dapper是第二名，

当然第一名谁也无法超越，越底层的当然久越快，同时也就越麻烦。就好像谁能超过“01代码”呢？？？

 

2) 支持多数据库

      支持多数据库的本质是因为Dapper是对IDBConnection接口进行了方法扩展，比如你看到的SqlMapper.cs，一旦你这样做了，我们也知道，

SqlConnection,MysqlConnection,OracleConnection都是继承于DBConnection，而DBConnection又是实现了IDBConnection的接口，对吧。。。

 

 

2. 安装 Dapper

Install-Package Dapper

Install-Package Pomelo.EntityFrameworkCore.MySql

 

 

注意 Pomelo.EntityFrameworkCore.MySql 的版本   应与项目框架版本一致

 

 

 3. 维护数据库连接字符串，appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "MysqlConnection": "server=127.0.0.1;userid=root;password=root;database=larger"
  },
  "AllowedHosts": "*"
}

 

 

3. 注册IDbConnection服务

在startup.cs文件中， ConfigureServices方法中

            services.AddScoped<IDbConnection>(_ => new MySqlConnection(Configuration.GetConnectionString("MysqlConnection")));

 

 

3. 测试

增加API控制器

using Dapper;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace NETCORE.Dapper.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BookController : ControllerBase
    {
        private readonly IDbConnection DB;

        public BookController(IDbConnection _db)
        {
            DB = _db;
        }

        [Route("GetBookAsync")]
        [HttpGet]
        public async Task<object> GetBookAsync()
        {
            var res= await DB.QueryAsync<object>("select * from te_book where id='0007e11c-f67e-4797-9b5e-d81cd1b0b081'");

            return res;
        }
    }
}

 

 

3. 无事务的操作 

            IDbConnection connection = DB.Database.GetDbConnection();

            //查询
            var queryRes = connection.Query<TblOrder>("select * from tbl_order where amount=@amount", new { amount = "10" }).ToList();



            //新增
            List<Models.TblOrder> list = new List<TblOrder>() {new TblOrder() {  Amount = 10, Uptime= DateTime.Now, Order_Code = "119", User_Id = 2  ,Text="aaaa"},
                                                               new TblOrder() {  Amount = 10, Uptime= DateTime.Now, Order_Code = "118", User_Id = 2  ,Text="bbbbb"},
                                                               new TblOrder() {  Amount = 10, Uptime= DateTime.Now, Order_Code = "117", User_Id = 2  ,Text="ccccc"}};

            string str = "INSERT INTO tbl_order (order_code,user_id,amount,uptime,text) VALUES(@order_code,@user_id,@amount,@uptime,@text)";

            connection.Execute(str, list);


            //修改
            list = connection.Query<TblOrder>("select * from tbl_order where order_code=@order_code", new { order_code = "117" }).ToList();

            str = "UPDATE tbl_order SET Text='abcabc' where order_code=@order_code";

            connection.Execute(str, list);


            //删除
            list = connection.Query<TblOrder>("select * from tbl_order order by id").ToList();

            connection.Execute("delete from tbl_order where id =@id", list.Take(2).ToList());

 

 

 

4. 带事务的操作

     using (IDbConnection connection = DB.Database.GetDbConnection())
            {
                connection.Open();

                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    //修改
                    var list = connection.Query<TblOrder>("select * from tbl_order where order_code=@order_code", new { order_code = "119" }).ToList();

                    string str = "UPDATE tbl_order SET Text='开心开心' where order_code=@order_code";

                    connection.Execute(str, list,transaction);


                    //删除
                    list = connection.Query<TblOrder>("select * from tbl_order order by id").ToList();

                    connection.Execute("delete from tbl_order where id =@id", list.Take(2).ToList(), transaction);


                    transaction.Commit();

                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                }

            }

 

 

Dapper 调用 存储过程 

 

创建存储过程：https://www.cnblogs.com/1285026182YUAN/p/17490283.html

创建返回类：

    public class DataDictionaryFlat
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Guid ParentId { get; set; }
        public int Order { get; set; }
    }

 

调用方式

var res = await DapperCde.QueryAsync<DataDictionaryFlat>("GetDataDictionaryTree", new { tkey = "SelectOrganization" },commandType: CommandType.StoredProcedure);

 

 

 

 

 

 

 

 

引用：https://www.cnblogs.com/huangxincheng/p/5828470.html