using Dapper;
|
using Microsoft.AspNetCore.Http;
|
using Newtonsoft.Json;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.DLL.BLL;
|
using VueWebCoreApi.Models;
|
|
namespace VueWebCoreApi.Tools
|
{
|
public class DapperHelper
|
{
|
|
/// <summary>
|
/// 创建数据库连接对象Sqlserver
|
/// </summary>
|
/// <returns></returns>
|
public static IDbConnection sqlConnection()
|
{
|
//string sqlServerConnectString = "server=***;database=***;User=***;password=***;Connect Timeout=10000";
|
string sqlServerConnectString = AppSetting.GetAppSetting("DBServer");
|
//获取数据库连接
|
IDbConnection connection = new SqlConnection(sqlServerConnectString);
|
connection.Open();
|
return connection;
|
}
|
|
|
/// <summary>
|
/// Dapper查询返回List
|
/// </summary>
|
/// <typeparam name="T">需要返回的对象类型</typeparam>
|
/// <param name="sql">Sql语句</param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static List<T> select<T>(string sql, Object parm) where T : new()
|
{
|
|
List<T> list = null;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
//用户操作记录写入数据表
|
//Log4net.LogUtil.SaveMessage("PC", "操作了xxxxx", "api/Login/LoginSave", ConfigurationManager.AppSettings["FileIP"], "9999", 1);
|
list = conn.Query<T>(sql, parm).ToList<T>();
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
|
}
|
return list;
|
}
|
|
/// <summary>
|
/// Dapper查询返回List
|
/// </summary>
|
/// <typeparam name="T">需要返回的对象类型</typeparam>
|
/// <param name="sql">Sql语句</param>
|
/// <returns></returns>
|
public static List<T> selectdatalist<T>(string sql) where T : new()
|
{
|
|
List<T> list = null;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
//用户操作记录写入数据表
|
//Log4net.LogUtil.SaveMessage("PC", "操作了xxxxx", "api/Login/LoginSave", ConfigurationManager.AppSettings["FileIP"], "9999", 1);
|
list = conn.Query<T>(sql).ToList<T>();
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
|
}
|
return list;
|
}
|
|
|
/// <summary>
|
/// Dapper查询返回List字典对象 无需手动Wapper对象了
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static List<Dictionary<string, Object>> selectToDict(string sql, Object parm)
|
{
|
|
List<Dictionary<string, Object>> result = null;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
var menus = conn.Query(sql, parm).ToList();
|
result = JsonConvert.DeserializeObject<List<Dictionary<string, Object>>>(JsonConvert.SerializeObject(menus));
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// Dapper查询返回对象非List集合
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static T selectToObject<T>(string sql, Object parm) where T : new()
|
{
|
T t = new T();
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
t = conn.Query<T>(sql, parm).Single<T>();
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return t;
|
}
|
|
/// <summary>
|
/// dapper通用分页函数
|
/// </summary>
|
/// <typeparam name="T">泛型集合实体类</typeparam>
|
/// <param name="sql">查询语句</param>
|
/// <param name="orderBy">排序（字段 DESC/ASC）</param>
|
/// <param name="pageIndex">当前页</param>
|
/// <param name="pageSize">当前页显示条数</param>
|
/// <param name="total">结果集总数</param>
|
/// <returns></returns>
|
public static IEnumerable<T> GetPageList<T>(string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
|
{
|
//int skip = 1;
|
//if (pageIndex > 0)
|
//{
|
// skip = (pageIndex - 1) * pageSize + 1;
|
//}
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
|
sb.AppendFormat(@"SELECT *
|
FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {1} {2}) AS rowNum
|
FROM ({0}) AS Temp) AS Result
|
WHERE rowNum >= {3} AND rowNum <= {4}
|
ORDER BY {1} {2}", sql, orderBy, sort, pageIndex, pageSize); //skip, pageIndex * pageSize
|
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
using (var reader = conn.QueryMultiple(sb.ToString(), parm))
|
{
|
total = reader.ReadFirst<int>();
|
return reader.Read<T>();
|
}
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
|
}
|
}
|
|
/// <summary>
|
/// dapper通用分页函数
|
/// </summary>
|
/// <typeparam name="T">泛型集合实体类</typeparam>
|
///<param name="withsql">with函数语句</param>
|
/// <param name="sql">查询语句</param>
|
/// <param name="orderBy">排序（字段 DESC/ASC）</param>
|
/// <param name="pageIndex">当前页</param>
|
/// <param name="pageSize">当前页显示条数</param>
|
/// <param name="total">结果集总数</param>
|
/// <returns></returns>
|
public static IEnumerable<T> GetPagedDataWith<T>(string withsql, string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
|
{
|
//int skip = 1;
|
//if (pageIndex > 0)
|
//{
|
// skip = (pageIndex - 1) * pageSize + 1;
|
//}
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat("{0} SELECT COUNT(1) FROM ({1}) AS Result;",withsql, sql);
|
sb.AppendFormat(@"{0} SELECT *
|
FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {2} {3}) AS rowNum
|
FROM ({1}) AS Temp) AS Result
|
WHERE rowNum >= {4} AND rowNum <= {5}
|
ORDER BY {2} {3}",withsql, sql, orderBy, sort, pageIndex, pageSize); //skip, pageIndex * pageSize
|
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
using (var reader = conn.QueryMultiple(sb.ToString(), parm))
|
{
|
total = reader.ReadFirst<int>();
|
return reader.Read<T>();
|
}
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
|
}
|
}
|
|
|
/// <summary>
|
/// WITH分页
|
/// </summary>
|
/// <typeparam name="T">泛型集合实体类</typeparam>
|
/// <param name="tableName">表名</param>
|
/// <param name="torg_code">子级字段</param>
|
/// <param name="parent_id">父级字段</param>
|
/// <param name="condition">查询条件</param>
|
/// <param name="parm">查询参数</param>
|
/// <param name="prop">排序字段</param>
|
/// <param name="order">排序规则</param>
|
/// <param name="pageSize">页码</param>
|
/// <param name="pageNumber">每页显示条数</param>
|
/// <returns></returns>
|
|
public static IEnumerable<T> GetPagedData<T>(string tableName, string torg_code, string parent_id, string condition, Object parm, string prop, string order, int pageSize, int pageNumber, out int total)
|
{
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
// 查询总记录数
|
string countQuery = $@"WITH CTE AS (
|
SELECT *
|
FROM {tableName}
|
WHERE {condition}
|
UNION ALL
|
select o.*
|
from {tableName} o
|
JOIN CTE c ON o.{torg_code} = c.{parent_id}
|
)
|
SELECT DISTINCT * FROM CTE";
|
int totalRecords = conn.ExecuteScalar<int>(countQuery, parm);
|
|
// 计算总页数
|
int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);
|
|
// 计算分页的起始行号
|
int startRow = (pageNumber - 1) * pageSize + 1;
|
// 计算分页的结束行号
|
int endRow = pageNumber * pageSize;
|
|
// 分页查询
|
string query = $@"WITH CTE AS (
|
SELECT *
|
FROM {tableName}
|
WHERE {condition}
|
UNION ALL
|
select o.*
|
from {tableName} o
|
JOIN CTE c ON o.{torg_code} = c.{parent_id}
|
)
|
SELECT * FROM(
|
SELECT ROW_NUMBER() OVER (ORDER BY {prop} {order}) AS rowNum,*
|
FROM (
|
SELECT DISTINCT *
|
from CTE
|
) AS subquery) AS AA WHERE rowNum >= {startRow} AND rowNum <={endRow}";
|
total = totalRecords;
|
return conn.Query<T>(query, parm);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// Dapper查询返回datatable数据(带参数)
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static DataTable selectdata(string sql, Object parm)
|
{
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
var data = conn.ExecuteReader(sql, parm);
|
table.Load(data);
|
return table;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// Dapper查询返回datatable数据(带参数)
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static DataTable selectlist(string sql, Object parm)
|
{
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
var data = conn.ExecuteReader(sql, parm);
|
table.Load(data);
|
return table;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// Dapper查询返回datatable数据(不带参数)
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static DataTable selecttable(string sql)
|
{
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
var data = conn.ExecuteReader(sql);
|
table.Load(data);
|
return table;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// Dapper执行存储过程返回datatable数据(带参数)
|
/// </summary>
|
/// <param name="sql">存储过程名</param>
|
/// <param name="parm">参数</param>
|
/// <returns></returns>
|
public static DataTable selectProcedure(string sql, Object parm)
|
{
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
//var data = conn.ExecuteReader(sql, parm);
|
var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程
|
table.Load(res);
|
return table;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
|
}
|
|
/// <summary>
|
/// Dapper执行存储过程返回datatable数据(带参数)
|
/// </summary>
|
/// <param name="sql">存储过程名</param>
|
/// <param name="parm">参数</param>
|
/// <returns></returns>
|
public static bool selectProcedureTable(string sql, Object parm, out DataTable table)
|
{
|
bool result;
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程
|
table= (DataTable)res;
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
|
}
|
|
/// <summary>
|
/// Dapper执行存储过程返回datatable数据(带参数)
|
/// </summary>
|
/// <param name="sql">存储过程名</param>
|
/// <param name="parm">参数</param>
|
/// <returns></returns>
|
public static bool IsProcedure(string sql, Object parm)
|
{
|
bool result;
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
//DataTable table = new DataTable();
|
//var data = conn.ExecuteReader(sql, parm);
|
var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
result = false;
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// Dapper执行存储过程返回datatable数据(不带参数)
|
/// </summary>
|
/// <param name="sql">存储过程名</param>
|
/// <returns></returns>
|
public static bool IsProcedureNo(string sql)
|
{
|
bool result;
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
//var data = conn.ExecuteReader(sql, parm);
|
var res = conn.ExecuteReader(sql);//sql 存储过程
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
result = false;
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// Dapper查询返回数据条数
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public static int selectcount(string sql, Object parm)
|
{
|
//sql语句
|
int result = 0;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
result = conn.Query(sql, parm).Count();
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// Dapper增删改
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parametere"></param>
|
/// <returns></returns>
|
public static int SQL(string sql, Object parametere)
|
{
|
//sql语句
|
int result = 0;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
result = conn.Execute(sql, parametere);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 增加，删除，修改使用的 事务方法 Sqllist为依次执行
|
/// </summary>
|
/// <param name="sqlList"></param>
|
/// <returns></returns>
|
public static bool DoTransaction(List<object> sqlList)
|
{
|
bool result;
|
IDbTransaction tranction = null;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
tranction = conn.BeginTransaction();
|
for (int i = 0; i < sqlList.Count; i++)
|
{
|
conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
|
}
|
tranction.Commit();
|
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
result = false;
|
tranction.Rollback();
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 增加，删除，修改使用的 事务方法 Sqllist为依次执行
|
/// </summary>
|
/// <param name="sqlList"></param>
|
/// <returns></returns>
|
public static int DoTransactionCont(List<object> sqlList)
|
{
|
int result = 0;
|
IDbTransaction tranction = null;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
tranction = conn.BeginTransaction();
|
for (int i = 0; i < sqlList.Count; i++)
|
{
|
result += conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
|
}
|
tranction.Commit();
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
tranction.Rollback();
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// Dapper插入 返回自增主键Id（备份）
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parameter"></param>
|
/// <param name="tableName">待插入数据的表名</param>
|
/// <returns></returns>
|
public static int insertReturnId0(string sql, Object parameter, String tableName)
|
{
|
|
int result = 0;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
result = conn.Execute(sql, parameter);
|
if (result != 0)
|
{
|
result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
|
}
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return result;
|
}
|
|
|
/// <summary>
|
/// Dapper 返回自增主键当前数据表最大Id
|
/// </summary>
|
/// <param name="tableName">待插入数据的表名</param>
|
/// <returns></returns>
|
public static int insertReturnId(String tableName)
|
{
|
|
int result = 0;
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
if (result == 0)
|
{
|
result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
|
}
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return result;
|
}
|
/// <summary>
|
/// 执行sql执行语句,返回字符串
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static string sqlstr(String sql)
|
{
|
|
string result = "";
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
if (result == "")
|
{
|
result = conn.Query<string>(sql).First();
|
}
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
return result;
|
}
|
|
public static DataTable lissql(string sql)
|
{
|
//sql语句
|
using (IDbConnection conn = sqlConnection())
|
{
|
try
|
{
|
DataTable table = new DataTable();
|
//var data = conn.ExecuteReader(sql, parm);
|
var res = conn.ExecuteReader(sql);//sql 存储过程
|
table.Load(res);
|
return table;
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
conn.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
}
|
}
|
