前言
.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后 就能愉快的对数据表进行增删改查拉