/// <summary>
/// ExecuteDataSet method
/// </summary>
/// <param name="queryBuilders">QueryBuilders</param>
/// <returns>null</returns>
public DataSet ExecuteDataSet(QueryBuilders queryBuilders)
{
Logger.Info(LogMessageResID.DBServiceExecuteDataSet);
try
{
DateTime BatchStartTime = DateTime.Now;
DataSet dsCollection = new DataSet(queryBuilders.Name);
dsCollection.Locale = CultureInfo.InvariantCulture;
DbCommand dbCommand = null;
string sqlQuery = string.Empty;
foreach (QueryBuilder queryBuilder in queryBuilders.GetQueryBuilders())
{
sqlQuery = queryBuilder.ToSQL();
if (queryBuilder.IsStoredProcedure)
{
dbCommand = GetStoredProcCommand(sqlQuery);
}
else
{
dbCommand = GetSqlStringCommand(sqlQuery);
}
dbCommand.CommandTimeout = GlobalConstants.DBCommandTimeout;
AssignParameters(queryBuilder, dbCommand);
LoadDataSet(dbCommand, dsCollection, queryBuilder.Name);
LogSQL(queryBuilder.Name, sqlQuery);
//loop to get values of out parameters
foreach (KeyValuePair <string, ParameterItem> parameter in queryBuilder.Parameters)
{
if (parameter.Value.IsOutParameter)
{
queryBuilder.SetOutParameterValue(parameter.Value.Name, GetParameterValue(dbCommand, parameter.Value.Name));
}
}
}
LogDataSetXML(queryBuilders.Name, dsCollection);
TimeSpan BatchTime = DateTime.Now - BatchStartTime;
UpdatePerformanceCounter(queryBuilders.Count, BatchTime, BatchStartTime);
return(dsCollection);
}
catch (Exception exp)
{
Logger.Error(ErrorMessageResID.DBServiceExecuteDataSetFailed, exp);
bool rethrow = ExceptionHandler.HandleDataExcception(exp);
if (rethrow)
{
throw;
}
}
return(null);
}
/// <summary>
/// Use to Execute Multiple Database operations with Transactions
/// </summary>
/// <param name="queryBuilders"></param>
/// <param name="Name"></param>
/// <returns></returns>
public int ExecuteNonQuery(QueryBuilders queryBuilders)
{
Logger.Info(LogMessageResID.DBServiceExecuteNonQuery);
DbConnection dbConnection = null;
DbTransaction dbTransaction = null;
try
{
//open the connection
using (dbConnection = this.CreateConnection())
{
dbConnection.Open();
//Create the database transaction
dbTransaction = dbConnection.BeginTransaction();
foreach (QueryBuilder queryBuilder in queryBuilders.GetQueryBuilders())
{
string sqlQuery = queryBuilder.ToSQL();
LogSQL(queryBuilder.Name, sqlQuery);
DateTime BatchStartTime = DateTime.Now;
DbCommand dbCommand;
if (queryBuilder.IsStoredProcedure)
{
dbCommand = GetStoredProcCommand(sqlQuery);
}
else
{
dbCommand = GetSqlStringCommand(sqlQuery);
}
dbCommand.CommandTimeout = GlobalConstants.DBCommandTimeout;
AssignParameters(queryBuilder, dbCommand);
//Execute the query
int status = ExecuteNonQuery(dbCommand, dbTransaction);
//loop to get values of out parameters
foreach (KeyValuePair <string, ParameterItem> parameter in queryBuilder.Parameters)
{
if (parameter.Value.IsOutParameter)
{
queryBuilder.SetOutParameterValue(parameter.Value.Name, GetParameterValue(dbCommand, parameter.Value.Name));
}
}
TimeSpan BatchTime = DateTime.Now - BatchStartTime;
UpdatePerformanceCounter(1, BatchTime, BatchStartTime);
}
//commit the transaction started
dbTransaction.Commit();
}
}
catch (Exception exp)
{
//Roll back the transaction
if (dbConnection.State == ConnectionState.Open)
{
dbTransaction.Rollback();
}
Logger.Error(ErrorMessageResID.DBServiceExecuteNonQueryFailed, exp);
bool rethrow = ExceptionHandler.HandleDataExcception(exp);
if (rethrow)
{
throw;
}
}
return(0);
}