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