using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.Quartz
|
{
|
public static class DapperHelper
|
{
|
// 配置文件读取，建议放到appsettings.json
|
private static readonly string _connectionString = AppSetting.GetAppSetting("DBServer");
|
/// <summary>
|
/// 获取数据库连接
|
/// </summary>
|
public static IDbConnection GetConnection()
|
{
|
var conn = new SqlConnection(_connectionString);
|
if (conn.State != ConnectionState.Open)
|
conn.Open();
|
return conn;
|
}
|
|
/// <summary>
|
/// 执行查询返回DataTable
|
/// </summary>
|
public static DataTable SelectTable(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
var reader = conn.ExecuteReader(sql, param);
|
var dt = new DataTable();
|
dt.Load(reader);
|
return dt;
|
}
|
}
|
|
/// <summary>
|
/// 执行增删改
|
/// </summary>
|
public static int Execute(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
return conn.Execute(sql, param);
|
}
|
}
|
|
/// <summary>
|
/// 异步执行增删改
|
/// </summary>
|
public static async Task<int> ExecuteAsync(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
return await conn.ExecuteAsync(sql, param);
|
}
|
}
|
|
/// <summary>
|
/// 执行事务
|
/// </summary>
|
public static bool DoTransaction(List<(string Sql, object Param)> list)
|
{
|
using (var conn = GetConnection())
|
{
|
using (var tran = conn.BeginTransaction())
|
{
|
try
|
{
|
foreach (var item in list)
|
{
|
conn.Execute(item.Sql, item.Param, tran);
|
}
|
tran.Commit();
|
return true;
|
}
|
catch
|
{
|
tran.Rollback();
|
return false;
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 查询列表
|
/// </summary>
|
public static List<T> Query<T>(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
return conn.Query<T>(sql, param).ToList();
|
}
|
}
|
|
/// <summary>
|
/// 异步查询列表
|
/// </summary>
|
public static async Task<List<T>> QueryAsync<T>(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
var result = await conn.QueryAsync<T>(sql, param);
|
return result.ToList();
|
}
|
}
|
|
/// <summary>
|
/// 查询单个对象
|
/// </summary>
|
public static T QueryFirstOrDefault<T>(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
return conn.QueryFirstOrDefault<T>(sql, param);
|
}
|
}
|
|
/// <summary>
|
/// 异步查询单个对象
|
/// </summary>
|
public static async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null)
|
{
|
using (var conn = GetConnection())
|
{
|
return await conn.QueryFirstOrDefaultAsync<T>(sql, param);
|
}
|
}
|
}
|
}
|
