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); } }