.NET Core WebAPI 配置Dapper连接SQL server数据库对数据表进行增删改查

前言

.NET Core WebAPI 配置Dapper连接SQL server数据库对数据表进行增删改查。

之前已经配置好了文件上传基本项目、JWT验证配置，本文代码在次项目上继续扩展
文件上传基本项目文章地址：跳转地址
JWT验证配置文章地址：跳转地址
源码下载地址：下载地址

---

配置步骤

1 新增TS.DB类库步骤如下

2 在新建的类库中使用NutGet管理工具安装Dapper和Microsoft.Data.SqlClient

3 在项目中新建DapperContext.cs文件编写如下代码

using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using System.Collections;


namespace TS.DB.Database
{
    public class DapperContext
    {
        private readonly static string sqlConne = "Data Source=192.139.127.20;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=Rtestpassword123456_;Connect Timeout=500;Encrypt=false;";

        private static IDbConnection CreateConnection() => new SqlConnection(sqlConne);


        /// <summary>
        /// 执行SQL 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteSql(string sql, object param = null)
        {
            try
            {
                return Execute(sql, param);
            }
            catch (Exception ex)
            {
            }

            return 0;
        }


        /// <summary>
        /// 执行SQL 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static int Execute(string sql, object param = null)
        {
            int ret = 0;
            using (var con = DapperContext.CreateConnection())
            {
                ret = con.Execute(sql, param);
            }

            return ret;
        }


        /// <summary>
        /// 执行SQL，返回第一行第一个元素的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static dynamic? ExecuteSqlByDynamic(string sql, object param = null)
        {
            return ExecuteSqlToEnumerable(sql, param).FirstOrDefault();
        }

        /// <summary>
        /// 执行SQL，返回数据实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static IEnumerable<T> ExecuteEnumList<T>(string sql, object param = null)
        {
            try
            {
                using (var con = DapperContext.CreateConnection())
                {
                    return con.Query<T>(sql, param);
                }
            }
            catch (Exception ex)
            {
            }

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> ExecuteSqlToEnumerable(string sql, object param = null)
        {
            try
            {
                using (var con = DapperContext.CreateConnection())
                {
                    return con.Query(sql, param);
                }
            }
            catch (Exception ex)
            {
            }

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static ArrayList ExecuteSqlToMultiEnumerable(string sql, object param = null)//IEnumerable<dynamic> 
        {
            ArrayList arr = new ArrayList();

            try
            {
                using (var con = DapperContext.CreateConnection())
                {
                    SqlMapper.GridReader reader = con.QueryMultiple(sql, param);
                    for (int i = 0; i < 10; i++)
                    {
                        if (reader.IsConsumed) break;

                        arr.Add(reader.Read());
                    }
                }
            }
            catch (Exception ex)
            {
            }

            return arr;
        }


    }
}

4 引入新建的项目到WebAPI项目中

5 在控制器文件中新增TestDb接口测试

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [HttpGet("TestDb")]
        public ActionResult TestDb()
        {
            string sql = "select top(1) * from test";

            var ret = TS.DB.Database.DapperContext.ExecuteSqlByDynamic(sql);

            return Ok(ret);

        }

6 调用接口得到数据

---

总结

.NET Core WebAPI 配置好Dapper后 就能愉快的对数据表进行增删改查拉