using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
|
namespace WebAPI.Dapper
|
{
|
public class SqlHelper
|
{
|
private readonly string _connStr;
|
public SqlHelper(string connStr)
|
{
|
_connStr = connStr;
|
}
|
IDbConnection GetConnection()
|
{
|
IDbConnection db = new SqlConnection(_connStr);
|
db.Open();
|
return db;
|
}
|
/// <summary>
|
/// 获取一个值，param可以是SQL参数也可以是匿名对象
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <param name="transaction"></param>
|
/// <param name="commandTimeout"></param>
|
/// <param name="commandType"></param>
|
/// <returns></returns>
|
public T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
|
{
|
using (var connection = GetConnection())
|
{
|
return connection.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
|
}
|
}
|
/// <summary>
|
/// 事务执行
|
/// </summary>
|
/// <param name="sql1"></param>
|
/// <param name="sql2"></param>
|
/// <param name="obj1"></param>
|
/// <param name="obj2"></param>
|
/// <returns></returns>
|
public int Trans(string sql1, string sql2, object obj1, object obj2)
|
{
|
using (var connection = GetConnection())
|
{
|
IDbTransaction transaction = connection.BeginTransaction();
|
try
|
{
|
connection.Execute(sql1, obj1);
|
connection.Execute(sql2, obj2);
|
transaction.Commit();
|
return 1;
|
}
|
catch (Exception)
|
{
|
transaction.Rollback();
|
return -1;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取一个数据模型实体类，param可以是SQL参数也可以是匿名对象
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <param name="transaction"></param>
|
/// <param name="commandTimeout"></param>
|
/// <param name="commandType"></param>
|
/// <returns></returns>
|
public T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
|
{
|
using (var connection = GetConnection())
|
{
|
return connection.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
|
}
|
}
|
public int GetExecuteScalar(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
|
{
|
using (var connection = GetConnection())
|
{
|
var result = connection.ExecuteScalar<int>(sql, param, null, commandTimeout, commandType);
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 获取符合条件的所有数据模型实体类列表，param可以是SQL参数也可以是匿名对象
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <param name="transaction"></param>
|
/// <param name="buffered"></param>
|
/// <param name="commandTimeout"></param>
|
/// <param name="commandType"></param>
|
/// <returns></returns>
|
public List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
|
{
|
using (var connection = GetConnection())
|
{
|
return connection.Query<T>(sql, param, null, buffered, commandTimeout, commandType).AsList();
|
}
|
}
|
|
|
/// <summary>
|
/// 获取符合条件的所有指定返回结果对象的列表(复合对象【如：1对多，1对1】)，param可以是SQL参数也可以是匿名对象
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="types"></param>
|
/// <param name="map"></param>
|
/// <param name="param"></param>
|
/// <param name="transaction"></param>
|
/// <param name="buffered"></param>
|
/// <param name="splitOn"></param>
|
/// <param name="commandTimeout"></param>
|
/// <param name="commandType"></param>
|
/// <returns></returns>
|
|
public List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
|
{
|
using (var connection = GetConnection())
|
{
|
return connection.Query<T>(sql, types, map, param, null, buffered, splitOn, commandTimeout, commandType).AsList();
|
}
|
}
|
|
/// <summary>
|
/// 执行SQL命令（CRUD），param可以是SQL参数也可以是要添加的实体类
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <param name="transaction"></param>
|
/// <param name="commandTimeout"></param>
|
/// <param name="commandType"></param>
|
/// <returns></returns>
|
public int ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
|
{
|
using (var connection = GetConnection())
|
{
|
|
int result = connection.Execute(sql, param, null, commandTimeout, commandType);
|
return result;
|
}
|
}
|
|
|
public bool ExecuteCommandByTrans(Action<IDbCommand> action)
|
{
|
using (var connection = GetConnection())
|
{
|
IDbTransaction transaction = connection.BeginTransaction();
|
IDbCommand cmd = connection.CreateCommand();
|
cmd.Transaction = transaction;
|
try
|
{
|
action.Invoke(cmd);
|
transaction.Commit();
|
return true;
|
}
|
catch
|
{
|
transaction.Rollback();
|
}
|
}
|
return false;
|
}
|
}
|
}
|
