private async Task executeProcReaderAsync(CancellationToken cancel, Action <IDataReader> predicate, SqlDBConnection dbConn, bool sequential)
{
#if AWAITUSING
await
#endif
using (var connection = new SqlConnection(dbConn.ConnectionString))
{
//create a command and prepare it for execution
#if AWAITUSING
await
#endif
using (var command = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout, Connection = connection
})
{
_dbconn.LastSql = string.Empty;
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "executeProcReader", _schema, _procname))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = dbConn.GetParameters(_schema, _procname);
/* foreach (var def in _defaults)
* {
* if (!_params.ContainsKey(def.Key))
* AddInParameter(def.Key, def.Value);
* }*/
//assign the provided values to these parameters based on parameter order
_dbconn.LastSql = dbConn.PrepareCommand(command, CommandType.StoredProcedure, _schema,
_procname, commandParameters, _params.Values.ToArray());
Logger.LogTrace(LoggingBoundaries.Database, "Execute Reader Called:\n{0}", _dbconn.LastSql);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync(cancel);
}
#if AWAITUSING
await
#endif
using (SqlDataReader dr = sequential ? await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancel) : await command.ExecuteReaderAsync(cancel))
{
predicate(dr);
}
}
}
catch (Exception ex)
{
var ex2 = new SqlDBException(ex, command, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
}
}
}
/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
/// </remarks>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public object ExecuteScalar()
{
//create a command and prepare it for execution
using (var cmd = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout
})
{
_dbconn.LastSql = string.Empty;
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "ExecuteScalar", _schema, _procname))
{
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return(null);
}
sqlDBConnection.SetCommandConnection(cmd);
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = sqlDBConnection.GetParameters(_schema, _procname);
/* foreach (var def in _defaults)
* {
* if (!_params.ContainsKey(def.Key))
* AddInParameter(def.Key, def.Value);
* }*/
//assign the provided values to these parameters based on parameter order
_dbconn.LastSql = sqlDBConnection.PrepareCommand(cmd, CommandType.StoredProcedure, _schema, _procname, commandParameters, _params.Values.ToArray());
//execute the command & return the results
object retval = cmd.ExecuteScalar();
Logger.LogTrace(LoggingBoundaries.Database, "ExecuteScalar Called:\n{0}", _dbconn.LastSql);
if (cmd.Transaction == null)
{
cmd.Connection.Close();
cmd.Connection = null;
}
return(retval);
}
}
catch (Exception ex)
{
cmd.Connection = null;
var ex2 = new SqlDBException(ex, cmd, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
return(null);
}
}
private void executeProcReaderTran(Action <IDataReader> predicate, SqlTransaction transaction, bool sequential)
{
//create a command and prepare it for execution
using (var command = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout, Connection = transaction.Connection, Transaction = transaction
})
{
_dbconn.LastSql = string.Empty;
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "executeProcReaderTran", _schema, _procname))
{
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return;
}
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = sqlDBConnection.GetParameters(_schema, _procname);
/* foreach (var def in _defaults)
* {
* if (!_params.ContainsKey(def.Key))
* AddInParameter(def.Key, def.Value);
* }*/
//assign the provided values to these parameters based on parameter order
_dbconn.LastSql = sqlDBConnection.PrepareCommand(command, CommandType.StoredProcedure, _schema,
_procname, commandParameters, _params.Values.ToArray());
Logger.LogTrace(LoggingBoundaries.Database, "Execute Reader Called:\n{0}", _dbconn.LastSql);
using (SqlDataReader dr = sequential ? command.ExecuteReader(CommandBehavior.SequentialAccess) : command.ExecuteReader())
{
predicate(dr);
}
}
}
catch (Exception ex)
{
command.Connection = null;
var ex2 = new SqlDBException(ex, command, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
}
}
/// <summary>
/// Execute a generic query and return IDataReader
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader("SELECT * FROM USER");
/// </remarks>
/// <returns>a dataset containing the resultset generated by the command</returns>
private async Task executeCommandReaderAsync(CancellationToken cancel, Action <IDataReader> predicate)
{
//create a command and prepare it for execution
#if AWAITUSING
await
#endif
using (var command = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout, CommandType = _commandType, CommandText = _procname
})
{
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "ExecuteReader", _procname))
{
foreach (DbParameter param in _params.Values)
{
command.Parameters.AddWithValue(param.ParameterName.Replace("@", ""), param.Value);
}
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return;
}
await sqlDBConnection.SetCommandConnectionAsync(cancel, command);
Logger.LogTrace(LoggingBoundaries.Database, "Execute Reader Called:\n{0}", _procname);
// call ExecuteReader with the appropriate CommandBehavior
#if AWAITUSING
await
#endif
using (SqlDataReader dr = await command.ExecuteReaderAsync(cancel))
{
predicate(dr);
}
}
}
catch (Exception ex)
{
command.Connection = null;
var ex2 = new SqlDBException(ex, command, _procname);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
}
}
private int executeCommandNonQuery()
{
//create a command and prepare it for execution
using (var cmd = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout
})
{
try
{
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return(-1);
}
sqlDBConnection.SetCommandConnection(cmd);
cmd.CommandType = _commandType;
cmd.CommandText = _procname;
_dbconn.LastSql = cmd.CommandText;
foreach (DbParameter param in _params.Values)
{
if (param.Direction == ParameterDirection.Output ||
param.Direction == ParameterDirection.InputOutput)
{
var param2 = new SqlParameter
{
ParameterName = param.ParameterName.Replace("@", ""),
Direction = param.Direction,
Value = param.Value
};
var type = param.Value.GetType();
if (type == typeof(Int32) || type == typeof(int))
{
param2.DbType = DbType.Int32;
}
else if (type == typeof(Int64) || type == typeof(long))
{
param2.DbType = DbType.Int64;
}
else if (type == typeof(double))
{
param2.DbType = DbType.Double;
}
else if (type == typeof(float))
{
param2.DbType = DbType.Decimal;
}
else if (type == typeof(DateTime))
{
param2.DbType = DbType.DateTime;
}
else
{
param2.DbType = DbType.String;
if (param.Value != null)
{
param2.Value = param.Value.ToString();
}
}
cmd.Parameters.Add(param2);
}
else
{
var value = param.Value;
//If the parameter is a null value, sql command doesn't recognize as valid parameter, must use dbnull.
if (value == null)
{
value = DBNull.Value;
}
cmd.Parameters.AddWithValue(param.ParameterName.Replace("@", ""), value);
}
}
Logger.LogTrace(LoggingBoundaries.Database, "ExecuteCommandNonQuery Called:\n{0}", _dbconn.LastSql);
int retval = cmd.ExecuteNonQuery();
foreach (DbParameter param in _params.Values)
{
if (param.Direction != ParameterDirection.Output &&
param.Direction != ParameterDirection.InputOutput)
{
continue;
}
foreach (SqlParameter sparam in cmd.Parameters)
{
if (sparam.ParameterName != param.ParameterName.Replace("@", ""))
{
continue;
}
param.Value = sparam.Value;
break;
}
}
if (cmd.Transaction == null)
{
cmd.Connection.Close();
cmd.Connection = null;
}
return(retval);
}
catch (Exception ex)
{
cmd.Connection = null;
var ex2 = new SqlDBException(ex, cmd, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
}
return(-1);
}
public async Task <int> ExecuteNonQueryAsync(CancellationToken cancel)
{
if (_commandType != CommandType.StoredProcedure)
{
return(executeCommandNonQuery());
}
#if AWAITUSING
await
#endif
using (var cmd = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout
})
{
_dbconn.LastSql = string.Empty;
int retval = -1;
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "ExecuteReader", _schema, _procname))
{
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return(-1);
}
await sqlDBConnection.SetCommandConnectionAsync(cmd);
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
var commandParameters = new List <SqlParameter>(sqlDBConnection.GetParameters(_schema, _procname))
{
new SqlParameter("@RETURN_VALUE", 0)
{
Direction = ParameterDirection.ReturnValue
}
};
//assign the provided values to these parameters based on parameter order
_dbconn.LastSql = sqlDBConnection.PrepareCommand(cmd, CommandType.StoredProcedure, _schema, _procname,
commandParameters.ToArray(), _params.Values.ToArray());
commandParameters.Clear();
Logger.LogTrace(LoggingBoundaries.Database, "ExecuteNonQuery Called:\n{0}", _dbconn.LastSql);
retval = await cmd.ExecuteNonQueryAsync(cancel);
if (cmd.Transaction == null)
{
cmd.Connection.Close();
cmd.Connection = null;
}
return(retval);
}
}
catch (Exception ex)
{
cmd.Connection = null;
var ex2 = new SqlDBException(ex, cmd, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
return(retval);
}
}
/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
/// </remarks>
/// <returns>an int representing the number of rows affected by the command, returns -1 when there is an error</returns>
public int ExecuteNonQuery()
{
if (_commandType != CommandType.StoredProcedure)
{
return(executeCommandNonQuery());
}
using (var cmd = new SqlCommand {
CommandTimeout = _dbconn.CommandTimeout
})
{
_dbconn.LastSql = string.Empty;
int retval = -1;
try
{
using (Logger.CreateTrace(LoggingBoundaries.Database, "ExecuteReader", _schema, _procname))
{
var sqlDBConnection = _dbconn as SqlDBConnection;
if (sqlDBConnection == null)
{
return(-1);
}
sqlDBConnection.SetCommandConnection(cmd);
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
var commandParameters = new List <SqlParameter>(sqlDBConnection.GetParameters(_schema, _procname))
{
new SqlParameter("@RETURN_VALUE", 0)
{
Direction = ParameterDirection.ReturnValue
}
};
/* foreach (var def in _defaults)
* {
* if(!_params.ContainsKey(def.Key))
* AddInParameter(def.Key,def.Value);
* }*/
//assign the provided values to these parameters based on parameter order
_dbconn.LastSql = sqlDBConnection.PrepareCommand(cmd, CommandType.StoredProcedure, _schema, _procname,
commandParameters.ToArray(), _params.Values.ToArray());
commandParameters.Clear();
Logger.LogTrace(LoggingBoundaries.Database, "ExecuteNonQuery Called:\n{0}", _dbconn.LastSql);
retval = cmd.ExecuteNonQuery();
if (cmd.Transaction == null)
{
cmd.Connection.Close();
cmd.Connection = null;
}
return(retval);
}
}
catch (Exception ex)
{
cmd.Connection = null;
var ex2 = new SqlDBException(ex, cmd, _dbconn.LastSql);
if (Logger.HandleException(LoggingBoundaries.Database, ex2))
{
throw ex2;
}
}
return(retval);
}
}
