.ner core API +dapper +sql

一、查询
生成实体类键我以前博客

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text.Json;
using System.Threading.Tasks;
using TP.Domain;
using TP.Infrastructure;
using TPAPI.ModelsVue;

namespace TPAPI.ControllerTest
{
    [Route("api/[controller]")]
    [ApiController]
    [ApiExplorerSettings(GroupName = "yw")]
    public class SqlController : Controller
    {
        [HttpPost("/[controller]/testSelselect")]
        public string testSelselect(string person)
        {
            try
            {
                IDbConnection connection = new SqlConnection(TPConfig.conn_Main);
                using (connection)
                {
                    connection.Open();
                    string sql = "SELECT * FROM testTable where person=@person";
                    IList<TestTable> resClassSet = connection.Query<TestTable>(sql, new { person = person }).ToList();

                    var kkk = JsonConvert.SerializeObject(resClassSet);
                    return kkk;
                }
            }
            catch (Exception)
            {
                throw;
            }

        }
    }
}

多表联合查询

IEnumerable<dynamic> res = connection.Query<dynamic>(selectProjectName);

二、复杂

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text.Json;
using System.Threading.Tasks;
using TP.Domain;
using TP.Infrastructure;
using TPAPI.ModelsVue;

namespace TPAPI.ControllerTest
{
    [Route("api/[controller]")]
    [ApiController]
    [ApiExplorerSettings(GroupName = "yw")]
    public class SqlController : Controller
    {
        [HttpPost("/[controller]/testSelselect")]
        public string testSelselect(string person)
        {
            try
            {
                IDbConnection connection = new SqlConnection(TPConfig.conn_Main);
                using (connection)
                {
                    connection.Open();
                    string sql = "SELECT * FROM testTable where person=@person";
                    IList<TestTable> resClassSet = connection.Query<TestTable>(sql, new { person = person }).ToList();

                    var kkk = JsonConvert.SerializeObject(resClassSet);
                    return kkk;
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        [HttpPost("/[controller]/testInsert")]
        public string testInsert([FromBody] object data)
        {
            try
            {
                IDbConnection connection = new SqlConnection(TPConfig.conn_Main);
                JObject result = (JObject)JsonConvert.DeserializeObject(data.ToString());
                using (connection)
                {
                    connection.Open();
                    string sql = "insert into testTable (code,name,person) values (@code,@name,@person)";
                    connection.Execute(sql,new { code= result["code"].ToString(), name = result["name"].ToString(), person = result["person"].ToString() });
                    return "OK!";
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        [HttpPost("/[controller]/testDelete")]
        public string testDelete([FromBody] object data)
        {
            try
            {
                IDbConnection connection = new SqlConnection(TPConfig.conn_Main);
                JObject result = (JObject)JsonConvert.DeserializeObject(data.ToString());
                using (connection)
                {
                    connection.Open();
                    string sql = "delete from testTable where Id=@id";
                    connection.Execute(sql, new { id= result["id"].ToString() });
                    return "OK!";
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        [HttpPost("/[controller]/testUpdate")]
        public string testUpdate([FromBody] object data)
        {
            try
            {
                IDbConnection connection = new SqlConnection(TPConfig.conn_Main);
                JObject result = (JObject)JsonConvert.DeserializeObject(data.ToString());
                using (connection)
                {
                    connection.Open();
                    string sql = "update testTable set name=@name where ID=@Id";
                    connection.Execute(sql, new { name = result["name"].ToString(), Id = result["Id"].ToString() });
                    return "OK!";
                }
            }
            catch (Exception)
            {
                throw;
            }

        }
    }
}