/// <summary> /// This method prepares a sql command object for execution. /// </summary> /// <param name="databaseConnection">The DatabaseConnection object used to get the connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> /// <param name="parameters">The sql parameters to be used in the statement.</param> /// <returns>A sql command object.</returns> private SqlCommand PrepareCommand(SqlDataConnection databaseConnection, string statement, CommandType commandType, SqlParameter[] parameters) { // Create the connectionstring object if it doesn't already exist or the connection string changes. if (this.sqlConnection == null || this.sqlConnection.ConnectionString.ToLower() != databaseConnection.ConnectionString.ToLower()) { this.sqlConnection = new SqlConnection(databaseConnection.ConnectionString); this.sqlConnection.FireInfoMessageEventOnUserErrors = true; this.sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.SqlConnection_InfoMessage); } SqlCommand command = this.sqlConnection.CreateCommand(); command.CommandText = statement; command.CommandType = commandType; if (parameters != null && parameters.Length > 0) { foreach (SqlParameter parameter in parameters) { command.Parameters.Add(parameter); } } return command; }
/// <summary> /// Runs a selection against the database and returns a single result set. /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> /// <returns>A datatable containing the results.</returns> public DataTable ExecuteSelection(SqlDataConnection databaseConnection, string statement, CommandType commandType) { return this.ExecuteSelection(databaseConnection, statement, commandType, null); }
/// <summary> /// Runs a selection against the database and returns a single result set. /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> /// <param name="parameters">The sql parameters to be used in the statement.</param> /// <returns>A datatable containing the results.</returns> public DataTable ExecuteSelection(SqlDataConnection databaseConnection, string statement, CommandType commandType, SqlParameter[] parameters) { List<DataTable> results = this.ExecuteMultiSelection(databaseConnection, statement, commandType, parameters); if (results != null && results.Count > 0) { // Only return the first result set. // It will be the programmer's responsibility to call the correct method if they needed any other results from the script. return results[0]; } return new DataTable(); }
/// <summary> /// Executes a statement which will contain multiple batches (Go statement). /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> /// <param name="parameters">The sql parameters to be used in the statement.</param> public void ExecuteNonQuery(SqlDataConnection databaseConnection, string statement, CommandType commandType, SqlParameter[] parameters) { try { // Parse any GO keywords from the statement. List<string> statements = this.ParseStatements(statement); this.ExecutionError = String.Empty; this.ExecutionInfoMessages = String.Empty; if (statements.Count > 0) { foreach (string currentStatement in statements) { using (SqlCommand command = this.PrepareCommand(databaseConnection, currentStatement, commandType, parameters)) { if (!String.IsNullOrEmpty(command.CommandText)) { if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } command.ExecuteNonQuery(); } } } } } catch (Exception ex) { throw ex; } finally { if (!this.KeepConnectionOpenAfterExecution) { this.CloseConnection(); } } }
/// <summary> /// Executes a statement which will contain multiple batches (Go statement). /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> public void ExecuteNonQuery(SqlDataConnection databaseConnection, string statement, CommandType commandType) { this.ExecuteNonQuery(databaseConnection, statement, commandType, null); }
/// <summary> /// Executes a statement which will contain multiple batches (Go statement). /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> public void ExecuteNonQuery(SqlDataConnection databaseConnection, string statement) { this.ExecuteNonQuery(databaseConnection, statement, CommandType.Text); }
/// <summary> /// Executes a statement which will contain multiple result sets. /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <param name="commandType">The command type to be executed.</param> /// <param name="parameters">The sql parameters to be used in the statement.</param> /// <returns>A list containing the result sets from the statement.</returns> public List<DataTable> ExecuteMultiSelection(SqlDataConnection databaseConnection, string statement, CommandType commandType, SqlParameter[] parameters) { try { List<DataTable> results = new List<DataTable>(); // Parse any GO keywords from the statement. List<string> statements = this.ParseStatements(statement); if (statements.Count > 0) { this.ExecutionError = String.Empty; this.ExecutionInfoMessages = String.Empty; foreach (string currentStatement in statements) { using (SqlCommand command = this.PrepareCommand(databaseConnection, currentStatement, commandType, parameters)) { if (!String.IsNullOrEmpty(command.CommandText)) { if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } DataTable result = new DataTable(); result.Load(command.ExecuteReader()); if (result.Rows.Count > 0) { results.Add(result); } } } } } return results; } catch (Exception ex) { throw ex; } finally { if (this.KeepConnectionOpenAfterExecution) { this.CloseConnection(); } } }
/// <summary> /// Executes a statement which will contain multiple result sets. /// </summary> /// <param name="databaseConnection">The database connection string.</param> /// <param name="statement">The statement to be executed.</param> /// <returns>A list containing the result sets from the statement.</returns> public List<DataTable> ExecuteMultiSelection(SqlDataConnection databaseConnection, string statement) { return this.ExecuteMultiSelection(databaseConnection, statement, CommandType.Text); }