/// <summary>
/// 批量执行sql语句
/// </summary>
/// <param name="context">上下文</param>
/// <param name="sqlList">sql语句集合</param>
/// <param name="paramList">参数数组集合</param>
public static bool ExecuteBatch(this IRepositoryContext context, IEnumerable <string> sqlList,
IEnumerable <DbParameter[]> paramList)
{
var command = context.Connection().CreateCommand();
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
DbTransaction trans = null;
try {
trans = context.Connection().BeginTransaction();
command.Transaction = trans;
var length = sqlList.Count();
IEnumerable <DbParameter> parameters = null;
for (var i = 0; i < length; i++)
{
command.CommandText = sqlList.ElementAt(i);
command.Parameters.Clear();
parameters = paramList.ElementAt(i);
foreach (var pm in parameters)
{
command.Parameters.Add(pm);
}
command.ExecuteNonQuery();
}
trans.Commit();
return(true);
} catch (System.Exception ex) {
Trace.WriteLine(ex.Message);
if (trans != null)
{
trans.Rollback();
}
throw;
} finally {
if (trans != null)
{
trans.Dispose();
}
command.Dispose();
}
}
/// <summary>
/// Creates the parameter.
/// </summary>
/// <param name="context">上下文</param>
/// <param name="name">The name.</param>
/// <param name="value">The value.</param>
public static DbParameter CreateParameter(this IRepositoryContext context, string name, object value)
{
var command = context.Connection().CreateCommand();
var result = command.CreateParameter();
result.ParameterName = name;
result.Value = value;
return(result);
}
/// <summary>
/// Begins the native database transaction.
/// </summary>
/// <param name="context">上下文</param>
public static DbTransaction BeginNativeDbTransaction(this IRepositoryContext context)
{
var connection = context.Connection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return(connection.BeginTransaction());
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="context">上下文</param>
/// <param name="tableName">Name of the table.</param>
public static bool CreateDataTable(this IRepositoryContext context, string tableName)
{
try {
var createDbStr = $"Create database {tableName}";
var command = context.Connection().CreateCommand();
command.ExecuteNonQuery();
return(false);
} catch (System.Exception ex) {
Trace.WriteLine(ex.Message);
return(true);
}
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="context">上下文</param>
/// <param name="transaction">The transaction.</param>
/// <param name="sql">The SQL.</param>
public static object ExecuteScalar(this IRepositoryContext context, DbTransaction transaction, string sql)
{
var command = context.Connection().CreateCommand();
command.Transaction = transaction;
command.CommandText = sql;
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return(command.ExecuteScalar());
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="context">上下文</param>
/// <param name="sqlStringList">多条SQL语句</param>
public static int ExecuteSqlList(this IRepositoryContext context, List <string> sqlStringList)
{
var command = context.Connection().CreateCommand();
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
DbTransaction transaction = null;
var count = 0;
try {
transaction = context.Connection().BeginTransaction();
command.Transaction = transaction;
foreach (var item in sqlStringList)
{
command.CommandText = item;
count += command.ExecuteNonQuery();
}
transaction.Commit();
} catch (System.Exception ex) {
Trace.WriteLine(ex.Message);
transaction.Rollback();
return(0);
} finally {
if (transaction != null)
{
transaction.Dispose();
}
command.Dispose();
}
return(count);
}
public static TReturn DapperGet <TFirst, TSecond, TReturn>(this IRepositoryContext context, string sql,
Func <TFirst, TSecond, TReturn> map)
{
var connection = context.Connection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = connection.Query(sql, map);
return(result.FirstOrDefault());
}
public static IEnumerable <TReturn> DapperGetList <TFirst, TSecond, TReturn>(this IRepositoryContext context,
string sql, Func <TFirst, TSecond, TReturn> map)
{
var connection = context.Connection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = connection.Query(sql, map);
return(result);
}
public static IEnumerable <T> DapperGetList <T>(this IRepositoryContext context, string sql, object param = null)
where T : class
{
var connection = context.Connection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = connection.Query <T>(sql, param);
return(result);
}
/// <summary>
/// Executes the data reader.
/// </summary>
/// <param name="context">上下文</param>
/// <param name="sql">The SQL.</param>
public static DbDataReader ExecuteDataReader(this IRepositoryContext context, string sql)
{
var command = context.Connection().CreateCommand();
if (context.IsInTransaction)
{
command.Transaction = context.GetDbTransaction();
}
command.CommandText = sql;
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return(command.ExecuteReader());
}
/// <summary>
/// Executes the data reader.
/// </summary>
/// <param name="context">上下文</param>
/// <param name="transaction">The transaction.</param>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
public static DbDataReader ExecuteDataReader(this IRepositoryContext context, DbTransaction transaction,
string sql, params DbParameter[] parameters)
{
var command = context.Connection().CreateCommand();
command.Transaction = transaction;
command.CommandText = sql;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return(command.ExecuteReader());
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="context">上下文</param>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
public static object ExecuteScalar(this IRepositoryContext context, string sql, params DbParameter[] parameters)
{
var command = context.Connection().CreateCommand();
if (context.IsInTransaction)
{
command.Transaction = context.GetDbTransaction();
}
command.CommandText = sql;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return(command.ExecuteScalar());
}
/// <summary>
/// Determines whether [is has data] [the specified SQL].
/// </summary>
/// <param name="context">上下文</param>
/// <param name="sql">The SQL.</param>
public static bool IsHasData(this IRepositoryContext context, string sql)
{
var result = false;
var command = context.Connection().CreateCommand();
if (context.IsInTransaction)
{
command.Transaction = context.GetDbTransaction();
}
command.CommandText = sql;
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
var reader = command.ExecuteReader();
result = reader.HasRows;
reader.Dispose();
return(result);
}