public static async Task <T> ExecuteNonQueryAsync <T>(string storedProcedure,
DynamicParameters parameters = null,
SqlTransaction transaction = null,
int commandTimeout = 60)
{
var con = GetConnection();
var command = con.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedure;
command.CommandTimeout = commandTimeout;
if (transaction != null)
{
command.Transaction = transaction;
}
if (parameters != null)
{
foreach (var sqlParameter in parameters.Parameters)
{
command.Parameters.Add(sqlParameter);
}
}
con.Open();
try {
object retval = await command.ExecuteNonQueryAsync();
if (parameters != null)
{
var outputParameters = parameters.GetOutputParameters();
foreach (var outputParameter in outputParameters)
{
parameters.Dictionary[outputParameter.ParameterName].Value =
command.Parameters[outputParameter.ParameterName].Value;
}
}
command.Dispose();
command = null;
return((T)retval);
} catch (Exception ex) {
con.Close();
con = null;
throw ex;
} finally {
if (transaction == null)
{
con.Close();
con.Dispose();
}
}
}
public static async Task <DataSet> ExecuteDataSetAsync(string storedProcedure,
DynamicParameters parameters,
SqlTransaction transaction = null,
int commandTimeout = 60)
{
var con = GetConnection();
try {
using (var command = new SqlCommand(storedProcedure, con)) {
if (transaction != null)
{
command.Transaction = transaction;
}
foreach (var sqlParameter in parameters.Parameters)
{
command.Parameters.Add(sqlParameter);
}
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = commandTimeout;
var da = new SqlDataAdapter(command);
con.Open();
var ds = new DataSet();
//da.Fill(ds);
await Task.Run(() => da.Fill(ds));
//await Task.FromResult(da.Fill(ds));
if (parameters != null)
{
var outputParameters = parameters.GetOutputParameters();
foreach (var outputParameter in outputParameters)
{
parameters.Dictionary[outputParameter.ParameterName].Value =
command.Parameters[outputParameter.ParameterName].Value;
}
}
return(ds);
}
} catch (Exception ex) {
throw ex;
} finally {
if (transaction == null)
{
con.Close();
con.Dispose();
}
}
}
public static async Task <IDataReader> ExecuteDataReaderAsync(string storedProcedure,
DynamicParameters parameters = null,
int commandTimeout = 60)
{
var connection = GetConnection();
var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedure;
if (parameters != null)
{
foreach (var sqlParameter in parameters.Parameters)
{
command.Parameters.Add(sqlParameter);
}
}
connection.Open();
try {
command.CommandTimeout = commandTimeout;
IDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
if (parameters != null)
{
var outputParameters = parameters.GetOutputParameters();
foreach (var outputParameter in outputParameters)
{
parameters.Dictionary[outputParameter.ParameterName].Value =
command.Parameters[outputParameter.ParameterName].Value;
}
}
command.Dispose();
command = null;
return(reader);
} catch (Exception ex) {
connection.Close();
connection = null;
throw ex;
}
}
