/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aConnection">A SQL Connection object</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <param name="aTransaction">The SqlTransaction object that this connection is part of</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
/// <seealso cref="System.Data.SqlClient.SqlTransaction"/>
public Boolean TryOpen(Statement aStatement,
SqlConnection aConnection,
Boolean aCloseConnection,
SqlTransaction aTransaction,
out SqlResultWithDataSet aResult)
{
// Assign default values
aResult = new SqlResultWithDataSet();
bool bCompleted;
try
{
// Open and execute
aResult = Open(aStatement, aConnection, aCloseConnection, aTransaction);
bCompleted = true;
}
catch (Exception e)
{
bCompleted = false;
aResult.Exception = e;
}
// Done
return(bCompleted);
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
public Boolean TryOpen(Statement aStatement,
out SqlResultWithDataSet aResult)
{
// Original:
// return TryOpen(aStatement, CreateConnection(), out aResult);
SqlConnection oConnection = null;
Boolean bReturnValue = true;
aResult = null;
try
{
oConnection = CreateConnection();
}
catch (Exception aException)
{
bReturnValue = false;
aResult = new SqlResultWithDataSet {
Exception = aException, Statistics = null, Results = null
};
}
finally
{
if (bReturnValue)
{
bReturnValue = TryOpen(aStatement, oConnection, out aResult);
}
}
return(bReturnValue);
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aConnection">A SQL Connection object</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
public Boolean TryOpen(Statement aStatement,
SqlConnection aConnection,
Boolean aCloseConnection,
out SqlResultWithDataSet aResult)
{
return(TryOpen(aStatement, aConnection, aCloseConnection, null, out aResult));
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aParameters">A list of parameters that will be used by the statement</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="SqlResultWithDataSet"/>
/// <seealso cref="System.Data.SqlClient.SqlParameter"/>
/// <seealso cref="System.Collections.Generic.IEnumerable{T}"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
out SqlResultWithDataSet aResult,
params SqlParameter[] aParameters)
{
return(TryOpen
(
aSqlStatement,
aCommandType,
aParameters == null ? null : aParameters.AsEnumerable(),
out aResult
));
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aParameters">A list of parameters that will be used by the statement</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="SqlResultWithDataSet"/>
/// <seealso cref="System.Data.SqlClient.SqlParameter"/>
/// <seealso cref="System.Collections.Generic.IEnumerable{T}"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
IEnumerable <SqlParameter> aParameters,
out SqlResultWithDataSet aResult)
{
return(TryOpen
(
new Statement
{
Sql = aSqlStatement,
Type = aCommandType,
Parameters = aParameters
},
out aResult
));
}
/// <summary>
/// Executes a SQL statement and returns the value of the statement
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute</param>
/// <param name="aConnection">The connection to use for this SQL statement</param>
/// <param name="aCloseConnection">If true, the connection will be closed after execution</param>
/// <param name="aTransaction">The SqlTransaction object to use during execution</param>
/// <returns>An object of type <typeparam name="T">T</typeparam></returns>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
public SqlResultWithValue <T> Execute <T>(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
{
// Call .Open() and return the first row of the first column.
SqlResultWithDataSet oResults = Open(aStatement, aConnection, aCloseConnection, aTransaction);
DataTable oTable = oResults.Results.Tables[0]; // Open(aStatement).Tables[0];
// Create our T variable
T oRetval = default(T);
// Now that we have the table, grab the first row and first value
if (oTable.Rows.Count > 0 && oTable.Rows[0].ItemArray.Any())
{
oRetval = (T)Convert.ChangeType(oTable.Rows[0][0], typeof(T));
}
// Return!
return(new SqlResultWithValue <T>()
{
Exception = null, Value = oRetval, Statistics = oResults.Statistics
});
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="SqlResultWithDataSet"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
out SqlResultWithDataSet aResult)
{
return(TryOpen(aSqlStatement, aCommandType, null, out aResult));
}
/// <summary>
/// Creates a DataSet that contains zero or more DataTables based on the SQL Statement passed
/// </summary>
/// <param name="aStatement">The SQL statement structure to open</param>
/// <param name="aConnection">The connection to use for this SQL statement</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aTransaction">The SqlTransaction object that this connection is part of</param>
/// <returns>A DataSet that contains a set of DataTables</returns>
/// <seealso cref="System.Data.DataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
/// <seealso cref="System.Data.SqlClient.SqlTransaction"/>
public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
{
// Create the connection
SqlDataReader oReader = null;
DataSet oDataSet = null;
SqlResultWithDataSet oResult = new SqlResultWithDataSet();
// Create a command
SqlCommand oCommand = new SqlCommand
{
CommandText = aStatement.Sql,
CommandType = aStatement.Type,
CommandTimeout = iTimeout,
Connection = aConnection,
Transaction = aTransaction
};
// Add the parameters
if (aStatement.Parameters != null)
{
oCommand.Parameters.AddRange(aStatement.Parameters.ToArray());
}
// Do a log of the command being executed
if (iLogging)
{
}
// Set back
oCommand.CommandText = aStatement.Sql;
try
{
// Store the reader
oReader = oCommand.ExecuteReader(aCloseConnection ? CommandBehavior.CloseConnection : CommandBehavior.Default);
// Set the statistics
oResult.Statistics = aConnection.RetrieveStatistics();
// Post-Execution
if (iLogging)
{
}
// Convert
oDataSet = oReader.ToDataSet();
// Set
oResult.Results = oDataSet;
// Close!
oReader.Close();
// Give back the result set
return(oResult);
}
catch (Exception)
{
// ToDo: Log the error here but rethrow the error!
throw;
}
finally
{
// Close the connection if necessary:
if (aCloseConnection && aConnection.State == ConnectionState.Open && aConnection != null)
{
aConnection.Close();
aConnection.Dispose();
}
}
}