public DataSet ExecuteDataSet(QueryBuilder queryBuilder, string dataSetName)
{
QueryBuilders queryBuilders = new QueryBuilders(dataSetName);
queryBuilders.AddSQL(queryBuilder);
return(ExecuteDataSet(queryBuilders));
}
public QueryContainer Build()
{
if (QueryBuilders?.Any() != true)
{
throw new InvalidOperationException("QueryBuilders is null or empty");
}
QueryContainer queryContainer = null;
foreach (var queryBuilder in QueryBuilders)
{
var query = queryBuilder.Build();
if (queryContainer == null)
{
queryContainer = query;
}
else
{
if (Operator == Operator.And)
{
queryContainer = queryContainer && query;
}
else
{
queryContainer = queryContainer || query;
}
}
}
return(queryContainer);
}
/// <summary>
/// ExecuteDataSet
/// </summary>
/// <param name="queryBuilder"></param>
/// <returns>ExecuteDataSet</returns>
public DataSet ExecuteDataSet(QueryBuilder queryBuilder)
{
QueryBuilders queryBuilders = new QueryBuilders(DataConstants.NewDataSet);
queryBuilders.AddSQL(queryBuilder);
return(ExecuteDataSet(queryBuilders));
}
/// <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);
}
public IEnumerable <WeatherForecast> Get()
{
var fieldDefinition = new TextboxField("test", "test");
fieldDefinition.FieldName = "test";
var filter = QueryBuilders.Contains(fieldDefinition, "test");
var sort = QueryBuilders.SortBy(fieldDefinition, SortDirection.Desc);
var searchCriteria = new SearchCondition(1, 10, filter, sort);
var rng = new Random();
return(Enumerable.Range(1, 5).Select(index => new WeatherForecast
{
Date = DateTime.Now.AddDays(index),
TemperatureC = rng.Next(-20, 55),
Summary = Summaries[rng.Next(Summaries.Length)]
})
.ToArray());
}
/// <summary>
/// QueryCollection Constructor of QueryCollection Class.
/// create new instance of QueryBuilder
/// </summary>
/// <param name="name">string</param>
public QueryCollection(string name)
{
queryBuilders = new QueryBuilders(name);
}
/// <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);
}
