/// <summary>
/// Fills a dataset
/// </summary>
/// <param name="executableQuery">SELECT ExecutionQuery based on which data is selected</param>
/// <returns>Resulting DataSet</returns>
public DataSet ExecuteDataSet(ExecutionQuery executableQuery)
{
DataSet dset = null;
IDbDataAdapter idap = null;
DataFactory factory = new DataFactory();
factory.InitializeDataAdapter(database, ref idap, command);
dset = new DataSet();
command.CommandType = CommandType.Text;
command.CommandText = executableQuery.Query;
command.Parameters.Clear();
if (executableQuery.Parameters != null)
{
foreach (IDataParameter var in executableQuery.Parameters)
{
command.Parameters.Add(var);
}
}
(new ExecutionEngineLogger()).LogContext(executableQuery);
idap.Fill(dset);
return(dset);
}
/// <summary>
/// Returns a dataset which contains data specified in the criteria query
/// </summary>
/// <param name="criteria">QueryCriteria based upon which the data is selected</param>
/// <returns>DataSet containing the selected data</returns>
public DataSet GetDataSet(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
DataSet dsTemp = null;
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
//check for session
if (contextSession != null)
{
dsTemp = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
dsTemp = e.ExecuteDataSet(database, connectionString, selectQuery);
}
}
return(dsTemp);
}
/// <summary>
/// Logs the execution context.
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="dataParameters">The data parameters.</param>
public void LogContext(string storedProcedureName, params IDataParameter[] dataParameters)
{
ExecutionQuery query = new ExecutionQuery("exec " + storedProcedureName, dataParameters);
Log.LogMessage(query);
Configuration.RaiseQueryLoggingEvent(query);
}
/// <summary>
/// Gets a DataTable by running the specified criteria
/// </summary>
/// <param name="criteria">Specified criteria</param>
/// <returns>Resulting DataTable</returns>
public DataTable GetDataTable(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
DataTable table = null;
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
//check for session
if (contextSession != null)
{
table = execEngine.ExecuteDataTable(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
//no session
table = e.ExecuteDataTable(selectQuery);
}
}
return(table);
}
/// <summary>
/// Returns a dataset with the data from the specified fields.
/// </summary>
/// <param name="fields">DatabaseFields which will be included in the select</param>
/// <returns>DataSet with results</returns>
public DataSet GetDataSet(params DatabaseField[] fields)
{
DataSet ds = null;
SqlGenerator generator = new SqlGenerator();
if (fields.Length == 0)
{
throw new ArgumentException("Invalid fields number");
}
ds = new DataSet();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, fields, null);
if (contextSession != null)
{
ds = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
ds = e.ExecuteDataSet(selectQuery);
}
}
return(ds);
}
/// <summary>
/// Executes the non query with primary key constraints.
/// </summary>
/// <param name="database">The database.</param>
/// <param name="connection">The connection.</param>
/// <param name="command">The command.</param>
/// <param name="listQueries">The list queries.</param>
/// <param name="listPrimaryKeyValues">The list primary key values.</param>
/// <returns></returns>
public int ExecuteNonQueryWithPrimaryKeyConstraints(DatabaseServer database, ref DbConnection connection, ref DbCommand command,
List <ExecutionQuery> listQueries, out List <object> listPrimaryKeyValues)
{
List <object> listValues = new List <object>();
int modifiedRows = 0;
foreach (ExecutionQuery executionQuery in listQueries)
{
if (executionQuery.Query.StartsWith(PRIMARY_KEY_CONSTRAINT))
{
PrimaryKeyConstraint keyConstraint = ConstraintManager.ParsePrimaryKeyConstraint(executionQuery);
ExecutionQuery currentQuery = ConstraintManager.StripPrimaryKeyConstraint(executionQuery);
object pkValue = null;
modifiedRows += ExecuteNonQueryWithPrimaryKey(database, ref connection, ref command, currentQuery, keyConstraint.PrimaryKeyFieldName,
keyConstraint.TableName, ref pkValue);
listValues.Add(pkValue);
}
}
listPrimaryKeyValues = listValues;
return(modifiedRows);
}
/// <summary>
/// Executes a DataReader
/// </summary>
/// <param name="executableQuery">ExecutionQuery</param>
/// <param name="cmd">Command Behaviour for DataReader</param>
/// <returns>IDataReader instance</returns>
public IDataReader ExecuteReader(ExecutionQuery executableQuery, CommandBehavior cmd)
{
IDataReader iread = null;
if (cmd == CommandBehavior.CloseConnection)
{
throw new ArgumentException("CloseConnection is not a valid command behaviour");
}
command.Parameters.Clear();
if (executableQuery.Parameters != null)
{
foreach (IDataParameter var in executableQuery.Parameters)
{
command.Parameters.Add(var);
}
}
command.CommandType = CommandType.Text;
command.CommandText = executableQuery.Query;
//log the execution query
(new ExecutionEngineLogger()).LogContext(executableQuery);
iread = command.ExecuteReader(cmd);
return(iread);
}
/// <summary>
/// Get data from a child table based on the relation name and the primary key's fieldValue from the parent table.
/// </summary>
/// <param name="primaryKeyValue">The fieldValue of the primary key</param>
/// <returns>The selected TableMetadata </returns>
public TableMetadata GetTableMetadata(object primaryKeyValue)
{
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
//generate select statement
if (primaryKeyValue == null)
{
throw new ArgumentException("Invalid fieldValue for primary key");
}
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
DatabaseField pkField = mappedObject.GetPrimaryKeyField();
pkField.fieldValue = primaryKeyValue;
//generate select query
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, mappedObject.TableFields, pkField);
TableMetadata table = (TableMetadata)Activator.CreateInstance(mappedObject.GetType());
ArrayList alList = MapDataReaderToTableMetadata(selectQuery, table);
table = (TableMetadata)alList[0];
return(table);
}
/// <summary>
/// Returns a dataset with all the data from our business object
/// </summary>
/// <returns>DataSet containing all the data</returns>
public DataSet GetDataSet()
{
DataSet dsTemp = null;
SqlGenerator generator = new SqlGenerator();
try
{
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject, false);
dsTemp = new DataSet();
//check for session
if (contextSession != null)
{
dsTemp = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
dsTemp = e.ExecuteDataSet(database, connectionString, selectQuery);
}
}
return(dsTemp);
}
catch (Exception ex)
{
Log.LogMessage(ex.Message + ex.StackTrace);
throw;
}
}
/// <summary>
/// Returns a DataSet which contains data from the related table
/// </summary>
/// <param name="relatedTableName">Name of the related table</param>
/// <param name="foreignKeyValue">Value of the foreign key</param>
/// <returns>DataSet containing data from the related table</returns>
public DataSet GetDataSet(string relatedTableName, object foreignKeyValue)
{
SqlGenerator generator = new SqlGenerator();
DataSet ds = new DataSet();
ExecutionQuery selectQuery = new ExecutionQuery();
TableRelation[] relations = mappedObject.Relations;
for (int i = 0; i < relations.Length; i++)
{
if (relations[i].RelatedTableName == relatedTableName.Trim())
{
DatabaseField keyField;
//check if we have a ParentRelation or a ChildRelation
if (relations[i] is ParentTableRelation)
{
DatabaseField primaryKeyField = mappedObject.GetPrimaryKeyField();
//this is the parent so we select from the child table.
keyField = new DatabaseField(primaryKeyField.fieldType, ((ParentTableRelation)relations[i]).ForeignKeyName, false, false, foreignKeyValue);
}
else
{
//child relation
ChildTableRelation childRelation = (ChildTableRelation)relations[i];
//this is the child so get data from the parent
keyField = new DatabaseField(mappedObject.GetPrimaryKeyField().fieldType, childRelation.RelatedTableKeyName, true, false, foreignKeyValue);
}
selectQuery = generator.GenerateSelectQuery(database, relations[i].RelatedTableName, keyField);
break;
}
}
if (selectQuery.Query == string.Empty)
{
throw new ArgumentException("Invalid relation name");
}
//run the query in the associated context
if (contextSession != null)
{
ds = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
ds = e.ExecuteDataSet(selectQuery);
}
}
return(ds);
}
public static ExecutionQuery StripPrimaryKeyConstraint(ExecutionQuery query)
{
//parse the constraint
string[] values = Regex.Split(query.Query, CONSTRAINT_DATA_SEPARATOR);
query.Query = values[3];
return(query);
}
/// <summary>
/// Generates a DELETE query for a single table
/// </summary>
/// <param name="database">Database server type </param>
/// <param name="tableName">Name of the table</param>
/// <param name="conditionalField">DatabaseFields array from which we generate the statement</param>
/// <returns>Returns the DELETE query</returns>
public ExecutionQuery GenerateDeleteQuery(DatabaseServer database, string tableName, params DatabaseField[] conditionalField)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
isql = factory.InitializeSqlGenerator(database);
sbuild.Append(" DELETE ");
if (database == DatabaseServer.Access)
{
sbuild.Append(" * ");
}
sbuild.Append(" FROM " + GetTableName(database, tableName)); // + " WHERE ");
bool isFirst = true;
if (conditionalField.Length > 0)
{
sbuild.Append(" WHERE ");
for (int i = 0; i < conditionalField.Length; i++)
{
IDataParameter[] parameter = converter.ConvertToDataParameter(database, conditionalField[i]);
listParameters.Add(parameter[0]);
if (isFirst)
{
sbuild.Append(conditionalField[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
isFirst = false;
}
else
{
sbuild.Append(" AND " + conditionalField[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
}
}
}
execQuery = new ExecutionQuery();
IDataParameter[] par = new IDataParameter[listParameters.Count];
listParameters.CopyTo(par);
execQuery.Parameters = par;
execQuery.Query = sbuild.ToString();
return(execQuery);
}
/// <summary>
/// Maps a DataReader to a TableMetadata implementation.
/// </summary>
/// <param name="selectQuery">The ExecutionQuery</param>
/// <param name="table">TableMetadata on which the query results will be mapped </param>
/// <returns>ArrayList with TableMetadata results</returns>
internal ArrayList MapDataReaderToTableMetadata(ExecutionQuery selectQuery, TableMetadata table)
{
IDataReader iread = null;
ArrayList alTables = null;
ExecutionEngine exec = null;
try
{
alTables = new ArrayList();
//check if we run it in the context session
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine();
iread = exec.ExecuteReader(database, connectionString, selectQuery);
}
int columnCount = iread.FieldCount;
while (iread.Read())
{
//create a instance of the table metadata
TableMetadata tempTable = (TableMetadata)Activator.CreateInstance(table.GetType());
//set the field's fieldValue
for (int i = 0; i < columnCount; i++)
{
//tempTable.SetFieldValue(tempTable.TableFields[i].fieldName, iread.GetValue(i));
tempTable.SetFieldValue(i, iread.GetValue(i));
}
alTables.Add(tempTable);
}
iread.Close();
return(alTables);
}
finally
{
if (iread != null && iread.IsClosed == false)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Returns a ArraList with the data from the specified field using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>ArrayList which contains the selected result</returns>
public virtual ArrayList GetFieldList(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
IDataReader iread = null;
ArrayList scData = null;
ExecutionEngine exec = null;
try
{
if (criteria.Fields.Length > 1)
{
throw new ArgumentException("Invalid field length. Must have only one field ");
}
scData = new ArrayList();
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine(database, connectionString);
iread = exec.ExecuteReader(selectQuery);
}
while (iread.Read())
{
scData.Add(iread.GetValue(0));
}
iread.Close();
return(scData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
internal static void RaiseQueryLoggingEvent(ExecutionQuery query)
{
if (OnQueryLogging != null)
{
List <ExecutionQuery> listQueries = new List <ExecutionQuery>();
listQueries.Add(query);
RaiseQueryLoggingEvent(listQueries);
}
}
/// <summary>
/// Generates a SELECT(*) query
/// </summary>
/// <param name="database">Database server dataType</param>
/// <param name="tableName">Name of the table</param>
/// <param name="conditionalFields">DatabaseFields from which the condition is generated</param>
/// <returns>Returns the SELECT query</returns>
public ExecutionQuery GenerateSelectQuery(DatabaseServer database, string tableName, params DatabaseField[] conditionalFields)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
sbuild.Append(" SELECT * FROM " + GetTableName(database, tableName));
if (conditionalFields.Length > 0)
{
sbuild.Append(" WHERE ");
}
else
{
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
return(execQuery);
}
isql = factory.InitializeSqlGenerator(database);
if (conditionalFields != null)
{
for (int i = 0; i < conditionalFields.Length; i++)
{
IDataParameter param = converter.ConvertToDataParameter(database, tableName, conditionalFields[i]);
if (i == conditionalFields.Length - 1)
{
sbuild.Append(GetTableName(database, tableName) + "." + conditionalFields[i].fieldName + isql.GetValueWithAttributionOperator(param));
}
else
{
sbuild.Append(GetTableName(database, tableName) + "." + conditionalFields[i].fieldName + isql.GetValueWithAttributionOperator(param) + ",");
}
listParameters.Add(param);
}
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] pmca = new IDataParameter[listParameters.Count];
listParameters.CopyTo(pmca);
execQuery.Parameters = pmca;
return(execQuery);
}
/// <summary>
/// Checks if the specified fieldValue exists in the database. Returns true if the
/// fieldValue doesn't exists in the database and false if it exists
/// </summary>
/// <param name="field">The field to which the specified fieldValue belongs</param>
/// <param name="value">Value to search for</param>
/// <returns>Returns true if a field with the specified fieldValue is found</returns>
public bool IsUnique(DatabaseField field, object value)
{
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
//TODO: implement this with count
object oldValue = field.fieldValue; //save the initial fieldValue of the field
field.fieldValue = value; //set the new fieldValue to the field
//get the execution query
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, field);
object resultValue = null;
//check execution context
if (contextSession != null)
{
resultValue = execEngine.ExecuteScalar(selectQuery);
field.fieldValue = oldValue;
if (resultValue == null)
{
return(true);
}
return(false);
}
exec = new ExecutionEngine();
resultValue = exec.ExecuteScalar(database, connectionString, selectQuery);
//set the original fieldValue back
field.fieldValue = oldValue;
if (resultValue == null)
{
return(true);
}
return(false);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Maps a select query on a TableMetadata[]
/// </summary>
/// <param name="selectQuery">Select query</param>
/// <returns>TableMetadata[]</returns>
internal Array GetTableMetadata(ExecutionQuery selectQuery)
{
ArrayList alData = MapDataReaderToTableMetadata(selectQuery, mappedObject);
Array tempArray = Array.CreateInstance(mappedObject.GetType(), alData.Count);
for (int i = 0; i < tempArray.Length; i++)
{
tempArray.SetValue(alData[i], i);
}
return(tempArray);
}
/// <summary>
/// Return a sorted list with all the data from the specified 2 fields
/// </summary>
/// <param name="idField">First field</param>
/// <param name="descriptionField">Second field.</param>
/// <returns>StringDictionary which contains the selected data</returns>
public virtual SortedList GetFieldList(DatabaseField idField, DatabaseField descriptionField)
{
IDataReader iread = null;
SortedList scData = null;
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
DatabaseField[] fields = new DatabaseField[2];
fields[0] = idField;
fields[1] = descriptionField;
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, fields, null);
scData = new SortedList();
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine(database, connectionString);
iread = exec.ExecuteReader(database, connectionString, selectQuery);
}
while (iread.Read())
{
scData.Add(iread.GetValue(0), iread.GetValue(1));
}
iread.Close();
return(scData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Deletes the row by the specified primary key value.
/// </summary>
/// <param name="primaryKeyValue">The primary key value.</param>
/// <returns>Number of affected rows</returns>
public int Delete(object primaryKeyValue)
{
IQueryCriteriaGenerator queryCriteriaGenerator = null;
DataFactory factory = new DataFactory();
int affectedRows = 0;
ExecutionEngine exec = null;
try
{
queryCriteriaGenerator = factory.InitializeQueryCriteriaGenerator(database);
DatabaseField field = mappedObject.GetPrimaryKeyField();
field.fieldValue = primaryKeyValue;
QueryCriteria criteria = new QueryCriteria(mappedObject.TableName);
criteria.Add(CriteriaOperator.Equality, field, primaryKeyValue);
ExecutionQuery query = queryCriteriaGenerator.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Returns a list with all the data from the specified field
/// </summary>
/// <param name="field">DatabaseField based upon which data is selected</param>
/// <returns>ArrayList which contains the selected data</returns>
public virtual ArrayList GetFieldList(DatabaseField field)
{
IDataReader iread = null;
ArrayList alData = null;
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
alData = new ArrayList();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, new DatabaseField[1] {
field
}, null);
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine();
iread = exec.ExecuteReader(database, connectionString, selectQuery);
}
while (iread.Read())
{
alData.Add(iread.GetValue(0));
}
iread.Close();
return(alData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Get data as a TableMetadata array
/// </summary>
/// <returns>TableMetadata Array</returns>
public Array GetTableMetadata()
{
try
{
SqlGenerator generator = new SqlGenerator();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject, false);
return(GetTableMetadata(selectQuery));
}
catch (Exception ex)
{
Log.LogMessage(ex.Message + ex.StackTrace);
throw new DataBlockException(ex.Message, ex);
}
}
/// <summary>
/// ExecuteXmlReader. Supported only by SQL Server 2000(5).
/// </summary>
/// <param name="connectionString">Connection string</param>
/// <param name="executableQuery">Query to be executed</param>
public XmlReader ExecuteXmlReader(string connectionString, ExecutionQuery executableQuery)
{
SqlConnection icon = new SqlConnection(connectionString);
SqlCommand icmd = new SqlCommand(executableQuery.Query, icon);
XmlReader xreader = null;
//log queries
(new ExecutionEngineLogger()).LogContext(executableQuery);
icon.Open();
//must also clse the underlying connectino when closing the reader
return(xreader = icmd.ExecuteXmlReader());
}
/// <summary>
/// Generates INSERT queries for the parent table and her related tables.
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="mainTable">TableMetadata based on which we generate the query</param>
/// <returns>List containing the queries</returns>
public List <ExecutionQuery> GenerateMultipleInsertQueries(DatabaseServer database, TableMetadata mainTable)
{
GraphBuilder builder = null;
List <ExecutionQuery> listQueries = null;
List <object> listGraph = null;
List <string> listTableNames = null;
try
{
//no deep recursion implementation yet.
listQueries = new List <ExecutionQuery>();
builder = new GraphBuilder();
listGraph = new List <object>();
listTableNames = new List <string>();
builder.BuildGraphList(mainTable, ref listGraph, ref listTableNames);
foreach (object var in listGraph)
{
if (var is TableMetadata)
{
ExecutionQuery execQuery = GenerateInsertQuery(database, (TableMetadata)var);
listQueries.Add(execQuery);
}
else
{
ExecutionQuery execQuery = new ExecutionQuery(var.ToString(), null);
listQueries.Add(execQuery);
}
}
return(listQueries);
}
finally
{
if (listGraph != null)
{
listGraph.Clear();
}
if (listTableNames != null)
{
listTableNames.Clear();
}
}
}
/// <summary>
/// Get data as a TableMetadata array
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>A TableMetadata array containing all the data</returns>
public Array GetTableMetadata(QueryCriteria criteria)
{
if (criteria.TableName != mappedObject.TableName)
{
throw new ArgumentException("Invalid criteria query. Must be the same as current table metadata");
}
DataFactory factory = new DataFactory();
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
return(GetTableMetadata(selectQuery));
}
/// <summary>
/// Executes a DataSet
/// </summary>
/// <param name="edt">Database provider dataType</param>
/// <param name="connectionString">Connection string</param>
/// <param name="executableQuery">Query to be executed</param>
/// <returns>Resulting DataSet</returns>
public DataSet ExecuteDataSet(DatabaseServer edt, string connectionString, ExecutionQuery executableQuery)
{
DbConnection icon = null;
DbCommand icmd = null;
DataSet dset = null;
DbDataAdapter idap = null;
DataFactory factory = new DataFactory();
try
{
factory.InitializeDbConnection(edt, ref icon);
factory.InitializeDbCommand(edt, ref icmd);
factory.InitializeDbDataAdapter(edt, ref idap, icmd);
icmd.Connection = icon;
dset = new DataSet();
icon.ConnectionString = connectionString;
icmd.Parameters.Clear();
if (executableQuery.Parameters != null)
{
foreach (IDataParameter var in executableQuery.Parameters)
{
icmd.Parameters.Add(var);
}
}
icmd.CommandText = executableQuery.Query;
idap.SelectCommand = icmd;
//log the queries
(new ExecutionEngineLogger()).LogContext(executableQuery);
icon.Open();
idap.Fill(dset);
return(dset);
}
finally
{
DisposeObjects(ref icon, ref icmd);
}
}
/// <summary>
/// Delete multiple rows from the table using the specified criteria from a QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based on which data is deleted</param>
public int Delete(QueryCriteria criteria)
{
int affectedRows = 0;
DataFactory factory = new DataFactory();
ExecutionEngine exec = null;
try
{
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Returns a single value from the database using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>The selected fieldValue</returns>
public object GetValue(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
object result = null;
ExecutionEngine exec = null;
try
{
if (criteria.Fields.Length > 1)
{
throw new ArgumentException("Invalid fields length. Must have only one field");
}
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
if (contextSession != null)
{
result = execEngine.ExecuteScalar(selectQuery);
}
else
{
exec = new ExecutionEngine();
result = exec.ExecuteScalar(selectQuery);
}
return(result);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the resultset returned by the query.
/// Extra columns or rows are ignored.
/// </summary>
/// <param name="executableQuery">Query to be executed</param>
/// <returns>Returns the selected object</returns>
public object ExecuteScalar(ExecutionQuery executableQuery)
{
command.Parameters.Clear();
if (executableQuery.Parameters != null)
{
foreach (IDataParameter var in executableQuery.Parameters)
{
command.Parameters.Add(var);
}
}
command.CommandType = CommandType.Text;
command.CommandText = executableQuery.Query;
//log data
(new ExecutionEngineLogger()).LogContext(executableQuery);
return(command.ExecuteScalar());
}
/// <summary>
/// Gets the data table paginated
/// </summary>
/// <param name="numberOfItems">The number of items.</param>
/// <param name="pageNumber">The page number.</param>
/// <returns></returns>
public DataTable GetDataTablePaginated(int numberOfItems, int pageNumber)
{
SqlGenerator generator = new SqlGenerator();
ExecutionQuery query = generator.GenerateSelectPaginatedQuery(database, mappedObject, numberOfItems, pageNumber);
DataTable table = null;
if (contextSession != null)
{
table = execEngine.ExecuteDataTable(query);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
table = e.ExecuteDataTable(query);
}
}
return(table);
}