/// <summary>
/// 执行增（INSERT）语句【执行单条SQL语句，返回自增列】【同步】
/// </summary>
/// <param name="sql">SQL语句（本方法会自动在SQL的结尾增加分号，并增加查询ID的语句）</param>
/// <param name="param">参数</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns>自增ID</returns>
public static int Insert(string sql, object param = null, int?commandTimeout = null)
{
var indentitySql = GetInsertSqlWithIndentity();
using (var tran = OpenTransaction(DBWriteConnection))
{
try
{
DBWriteConnection.Execute(sql, param, transaction: tran, commandTimeout: commandTimeout);
var id = DBWriteConnection.ExecuteScalar <int>(indentitySql, transaction: tran, commandTimeout: commandTimeout);
tran.Commit();
return(id);
}
catch (Exception ex)
{
tran.Rollback();
ex.WriteExceptionLog("插入数据库失败");
return(-1);
}
}
}
/// <summary>
/// 执行增（INSERT）删（DELETE）改（UPDATE）语句或存储过程【单条SQL语句，可以获得输出参数】【同步】
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <param name="outParam">输出参数[参数名(@name)] </param>
/// <param name="commandType">如何解释命令字符串</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns>Tuple(受影响的行数, Dictionary(输出参数名(@name), 输出参数))</returns>
public static Tuple <int, Dictionary <string, string> > Execute(string sql, DynamicParameters param, List <string> outParam, CommandType?commandType = null, int?commandTimeout = null)
{
var rows = DBWriteConnection.Execute(sql, param, commandTimeout: commandTimeout, commandType: commandType);
var outDic = new Dictionary <string, string>();
foreach (var item in outParam)
{
outDic.Add(item, param.Get <string>(item));
}
return(new Tuple <int, Dictionary <string, string> >(rows, outDic));
}
/// <summary>
/// 执行增（INSERT）删（DELETE）改（UPDATE）语句【带事务，可以同时执行多条SQL】【异步】【待测试】
/// </summary>
/// <param name="sqlDic">SQL + 参数【key：sql语句 value：参数】</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns></returns>
public static async Task ExecuteToTransactionAsync(Dictionary <string, object> sqlDic, int?commandTimeout = null)
{
var trans = DBWriteConnection.BeginTransaction();
try
{
foreach (var item in sqlDic)
{
await DBWriteConnection.ExecuteAsync(item.Key, item.Value, transaction : trans, commandTimeout : commandTimeout);
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
/// <summary>
/// 执行存储过程（Procdeure）【异步】
/// </summary>
/// <typeparam name="T">返回结果的类型</typeparam>
/// <param name="porcdeureName">存储过程名称</param>
/// <param name="param">参数</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns></returns>
public static Task <IEnumerable <T> > ExecuteToProcdeureAsync <T>(string porcdeureName, object param = null, int?commandTimeout = null)
{
return(DBWriteConnection.QueryAsync <T>(porcdeureName, commandTimeout: commandTimeout, commandType: CommandType.StoredProcedure, param: param));
}
/// <summary>
/// 执行增（INSERT）删（DELETE）改（UPDATE）语句【执行单条SQL语句】【异步】
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns>受影响的行数</returns>
public static Task <int> ExecuteAsync(string sql, object param = null, int?commandTimeout = null)
{
return(DBWriteConnection.ExecuteAsync(sql, param, commandTimeout: commandTimeout));
}
