/// <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>
/// Generates the select paginated query.
/// </summary>
/// <param name="database">The database.</param>
/// <param name="metadata">The metadata.</param>
/// <param name="numberOfItems">The number of items.</param>
/// <param name="pageNumber">The page number.</param>
/// <returns></returns>
public ExecutionQuery GenerateSelectPaginatedQuery(DatabaseServer database, TableMetadata metadata, int numberOfItems, int pageNumber)
{
DataFactory factory = new DataFactory();
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
string result = isql.GeneratePaginatedQuery(metadata, numberOfItems, pageNumber);
return(new ExecutionQuery(result, new IDataParameter[0]));
}
/// <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>
/// 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>
/// Generates the sql query condition
/// </summary>
/// <param name="tableName">Name of the datbase table</param>
/// <param name="conditions">Criteria conditions </param>
/// <param name="sbSqlHeader">StringBuilder which contains the SELECT part of the sql query build so far</param>
/// <param name="listParameters">List with used parameters</param>
/// <returns></returns>
internal string GenerateCondition(string tableName, CriteriaCondition[] conditions, ref StringBuilder sbSqlHeader, ref List <IDataParameter> listParameters)
{
//keeps the order by part of the query
StringBuilder sbOrderByCriteria = new StringBuilder();
//holds the generated query
StringBuilder sbuild = new StringBuilder();
ISqlGenerator isql = null;
List <string> listParameterNames = null;
SqlGenerator generator = new SqlGenerator();
DataConvertor converter = new DataConvertor();
//temporary vars
string fieldName = string.Empty;
int index = -1;
string tempString = string.Empty;
DataFactory factory = new DataFactory();
try
{
listParameterNames = new List <string>();
//initialize generator
isql = factory.InitializeSqlGenerator(DatabaseServer.MySQL);
//generate conditions
for (int i = 0; i < conditions.Length; i++)
{
//check if we generate "AND" operator
if (i > 0)
{
if ((conditions[i].CriteriaOperator != CriteriaOperator.OrderBy) && (conditions[i].CriteriaOperator != CriteriaOperator.Or) &&
(conditions[i - 1].CriteriaOperator != CriteriaOperator.Or) && (conditions[i - 1].CriteriaOperator != CriteriaOperator.Not))
{
sbuild.Append(" AND ");
}
}
DatabaseField field = conditions[i].Field;
switch (conditions[i].CriteriaOperator)
{
case CriteriaOperator.Between:
//here we must have 2 parameters with two diffferent values and name. These
//parameters must be generated based on a single name.
IDataParameter paramBetweenFirst = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
IDataParameter paramBetweenSecond = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
paramBetweenFirst.ParameterName = paramBetweenFirst.ParameterName + "First";
paramBetweenSecond.ParameterName = paramBetweenSecond.ParameterName + "Second";
//set the parameter's value and add it to the list
paramBetweenFirst.Value = conditions[i].Values[0];
listParameters.Add(paramBetweenFirst);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName + " BETWEEN " +
isql.GetValue(paramBetweenFirst));
sbuild.Append(" AND ");
//set the value of the second parameter
paramBetweenSecond.Value = conditions[i].Values[1];
listParameters.Add(paramBetweenSecond);
sbuild.Append(isql.GetValue(paramBetweenSecond));
break;
case CriteriaOperator.Not:
sbuild.Append(" NOT");
break;
case CriteriaOperator.Different:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramDifferent = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramDifferent);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + "<>" + isql.GetValue(paramDifferent));
break;
case CriteriaOperator.Like:
field.fieldValue = "%" + conditions[i].Values[0] + "%";
IDataParameter paramLike = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramLike);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLike));
break;
case CriteriaOperator.LikeEnd:
field.fieldValue = "%" + conditions[i].Values[0];
IDataParameter paramLikeEnd = converter.ConvertToDataParameter(DatabaseServer.Access, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeEnd);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Access, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeEnd));
break;
case CriteriaOperator.LikeStart:
field.fieldValue = conditions[i].Values[0] + "%";
IDataParameter paramLikeStart = converter.ConvertToDataParameter(DatabaseServer.Access, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeStart);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Access, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeStart));
break;
case CriteriaOperator.Equality:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramEquality = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramEquality);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + "=" + isql.GetValue(paramEquality));
break;
case CriteriaOperator.IsNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " is null");
break;
case CriteriaOperator.IsNotNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " is not null");
break;
case CriteriaOperator.Or:
sbuild.Append(" OR");
break;
case CriteriaOperator.Smaller:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramSmaller = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramSmaller);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " < " + isql.GetValue(paramSmaller));
break;
case CriteriaOperator.SmallerOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramSmallerOrEqual = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramSmallerOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " <= " + isql.GetValue(paramSmallerOrEqual));
break;
case CriteriaOperator.Higher:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigher = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramHigher);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " > " + isql.GetValue(paramHigher));
break;
case CriteriaOperator.HigherOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigherOrEqual = converter.ConvertToDataParameter(DatabaseServer.MySQL, tableName, field, ref listParameterNames);
listParameters.Add(paramHigherOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " >= " + isql.GetValue(paramHigherOrEqual));
break;
case CriteriaOperator.OrderBy:
if (sbOrderByCriteria.Length == 0)
{
//add the operator for the first criteria
sbOrderByCriteria.Append("ORDER BY " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " " + conditions[i].Values[0]);
}
else
{
//add "," for the subsequent criterias
sbOrderByCriteria.Append(", " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + field.fieldName + " " + conditions[i].Values[0]);
}
break;
//NOTE : DISTICT requires modification of the sql header. Also
// DISTINCT clause requires that the distinct field should be
// the first one in the list.
case CriteriaOperator.Distinct:
//get the field
fieldName = generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid Distinct clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " distinct " + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName);
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: MAX fields must be after SELECT statement
case CriteriaOperator.Max:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Access, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid MAX clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " max(" + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: MIN fields must be after SELECT statement
case CriteriaOperator.Min:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Access, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid MIN clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " min(" + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: COUNT fields must be after SELECT statement
case CriteriaOperator.Count:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Access, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid count clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " count(" + generator.GetTableName(DatabaseServer.MySQL, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
}
}
//last check to prevent invalid sql queries
//conditions remove the "WHERE".
if (sbuild.ToString().EndsWith(" WHERE "))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
//check if we have conditions which don't require a "WHERE" clause
if (sbuild.Length == 0 && sbOrderByCriteria.Length > 0)
{
//remove from query header
sbSqlHeader.Remove(sbSqlHeader.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
if (sbOrderByCriteria.Length > 0)
{
sbuild.Append(" " + sbOrderByCriteria);
}
return(sbuild.ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (listParameterNames != null)
{
listParameterNames.Clear();
listParameterNames = null;
}
}
}
/// <summary>
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
internal ExecutionQuery GenerateWithoutJoin(QueryCriteria criteria)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
execQuery = new ExecutionQuery();
if (generatorType == QueryCriteriaGeneratorType.Select)
{
execQuery = generator.GenerateSelectQuery(DatabaseServer.MySQL, criteria);
}
else if (generatorType == QueryCriteriaGeneratorType.Update)
{
execQuery = generator.GenerateUpdateQuery(DatabaseServer.MySQL, criteria.TableName, criteria.Fields, false);
}
else if (generatorType == QueryCriteriaGeneratorType.Delete)
{
execQuery = generator.GenerateDeleteQuery(DatabaseServer.MySQL, criteria.TableName);
}
//add to the intermediary objects
if (execQuery.Parameters != null)
{
foreach (IDataParameter var in execQuery.Parameters)
{
listParameters.Add(var);
}
}
sbuild.Append(execQuery.Query);
//initialize generator
isql = factory.InitializeSqlGenerator(DatabaseServer.MySQL);
//append where clause
sbuild.Append(" WHERE ");
//generate the condition based on criteria
string condition = GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters);
//more checks
if (sbuild.ToString().EndsWith(" WHERE ") && condition.StartsWith(" ORDER BY "))
{
if (condition.StartsWith(" ORDER BY"))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
}
sbuild.Append(condition);
//last check to prevent invalid sql queries
if (sbuild.ToString().EndsWith(" WHERE "))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] pmc = new IDataParameter[listParameters.Count];
listParameters.CopyTo(pmc);
execQuery.Parameters = pmc;
return(execQuery);
}
/// <summary>
/// Generates a UPDATE query for a single table
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="tableName">Name of the table</param>
/// <param name="skipConditionalFieldsFromUpdateList">Flag used to determine if the conditional fields are included in the list of the updated fields</param>
/// <param name="conditionalFields">Database fields from which the "WHERE" clause is generated</param>
/// <param name="updatedFields">DatabaseFields which will be updated.</param>
/// <returns>Returns the UPDATE query</returns>
public ExecutionQuery GenerateUpdateQuery(DatabaseServer database, string tableName, bool skipConditionalFieldsFromUpdateList, DatabaseField[] conditionalFields, params DatabaseField[] updatedFields)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
//check params
if (updatedFields.Length == 0)
{
throw new ArgumentException("Invalid fields to update");
}
List <IDataParameter> listParameters = new List <IDataParameter>();
isql = factory.InitializeSqlGenerator(database);
StringBuilder sbuild = new StringBuilder();
sbuild.Append("UPDATE " + GetTableName(database, tableName) + " SET ");
bool isConditionalField = false;
isql = factory.InitializeSqlGenerator(database);
for (int i = 0; i < updatedFields.Length; i++)
{
//skip conditional fields.
if (skipConditionalFieldsFromUpdateList)
{
isConditionalField = false;
//check if this is a conditional field. If it is skip it...
for (int j = 0; j < conditionalFields.Length; j++)
{
//check if it's the same field
if (updatedFields[i].Equals(conditionalFields[j]))
{
isConditionalField = true;
break;
}
}
if (isConditionalField)
{
continue;
}
}
IDataParameter[] parameter = converter.ConvertToDataParameter(database, updatedFields[i]);
sbuild.Append(" " + updatedFields[i].fieldName + isql.GetValueWithAttributionOperator(parameter[0]) + " , ");
listParameters.Add(parameter[0]);
}
//remove trailing
sbuild.Remove(sbuild.Length - 2, 1);
if (conditionalFields != null)
{
//generate conditon
sbuild.Append(" WHERE ");
for (int i = 0; i < conditionalFields.Length; i++)
{
IDataParameter[] parameter = converter.ConvertToDataParameter(database, conditionalFields[i]);
listParameters.Add(parameter[0]);
if (i == conditionalFields.Length - 1)
{
sbuild.Append(conditionalFields[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
}
else
{
sbuild.Append(conditionalFields[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]) + " AND ");
}
}
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] prm = new IDataParameter[listParameters.Count];
listParameters.CopyTo(prm);
execQuery.Parameters = prm;
return(execQuery);
}
/// <summary>
/// Generates a UPDATE query for a single table
/// </summary>
/// <param name="database">Database server</param>
/// <param name="tableName">Name of the table</param>
/// <param name="fields">DatabaseField array from which the query is generated </param>
/// <param name="generateConditionByPrimaryKey">Boolean flag which signals if a "WHERE" condition is added based on the primary key</param>
/// <returns>The generated execution query</returns>
public ExecutionQuery GenerateUpdateQuery(DatabaseServer database, string tableName, DatabaseField[] fields, bool generateConditionByPrimaryKey)
{
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(" UPDATE " + GetTableName(database, tableName) + " SET ");
//generate update fields
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].isPrimaryKey)
{
continue;
}
if (i == fields.Length - 1)
{
IDataParameter[] parameters = converter.ConvertToDataParameter(database, fields[i]);
sbuild.Append(" " + fields[i].fieldName + isql.GetValueWithAttributionOperator(parameters[0]));
listParameters.Add(parameters[0]);
}
else
{
IDataParameter[] parameter = converter.ConvertToDataParameter(database, fields[i]);
sbuild.Append(" " + fields[i].fieldName + isql.GetValueWithAttributionOperator(parameter[0]) + ",");
listParameters.Add(parameter[0]);
}
}
//flag used if we have more than a primary key field
bool isFirst = true;
//check if we generate condition by the primary key
if (generateConditionByPrimaryKey)
{
//generate condition
sbuild.Append(" WHERE ");
for (int i = 0; i < fields.Length; i++)
{
//check if this is the first primary key
if (fields[i].isPrimaryKey)
{
IDataParameter[] parameter = converter.ConvertToDataParameter(database, fields[i]);
listParameters.Add(parameter[0]);
if (isFirst)
{
sbuild.Append(fields[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
isFirst = false;
}
else
{
sbuild.Append("," + fields[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
}
}
}
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] prm = new IDataParameter[listParameters.Count];
listParameters.CopyTo(prm);
execQuery.Parameters = prm;
return(execQuery);
}
/// <summary>
/// Generates a DELETE query for a single table
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="table">TableMetadata from which we generate the statement</param>
/// <param name="generateConditionOnlyByPrimaryKey">Boolean flag to know if we generate the "WHERE" clause by the primary key or by all the fields </param>
/// <returns>Returns the DELETE query</returns>
public ExecutionQuery GenerateDeleteQuery(DatabaseServer database, TableMetadata table, bool generateConditionOnlyByPrimaryKey)
{
ISqlGenerator isql = null;
StringBuilder sbuild = null;
ExecutionQuery execQuery;
List <IDataParameter> listParameters = null;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
//generate condition by all the DatabaseFields.
if (!generateConditionOnlyByPrimaryKey)
{
return(GenerateDeleteQuery(database, table.TableName, table.TableFields));
}
//generate conditions by all the fields.
listParameters = new List <IDataParameter>();
sbuild = new StringBuilder();
isql = factory.InitializeSqlGenerator(database);
sbuild.Append(" DELETE ");
if (database == DatabaseServer.Access)
{
sbuild.Append(" * ");
}
sbuild.Append(" FROM " + GetTableName(database, table.TableName) + " WHERE ");
//flag used if we have multiple primary keys
bool isFirst = true;
for (int i = 0; i < table.TableFields.Length; i++)
{
if (table.TableFields[i].isPrimaryKey)
{
IDataParameter[] parameter = converter.ConvertToDataParameter(database, table.TableFields[i]);
listParameters.Add(parameter[0]);
if (isFirst)
{
sbuild.Append(table.TableFields[i].fieldName + isql.GetValueWithComparationOperator(parameter[0]));
isFirst = false;
}
else
{
sbuild.Append(" AND " + table.TableFields[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>
/// Generates a INSERT query for a single table
/// </summary>
/// <param name="database">Database server</param>
/// <param name="fields">Array of DatabaseFields from which the query is generated</param>
/// <param name="tableName">Name of the table</param>
/// <returns>The generated query</returns>
public ExecutionQuery GenerateInsertQuery(DatabaseServer database, DatabaseField[] fields, string tableName)
{
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("INSERT INTO " + GetTableName(database, tableName) + "(");
for (int i = 0; i < fields.Length; i++)
{
//skip the PK field if is autogenerated.
if (fields[i].isPrimaryKey && fields[i].isValueAutogenerated)
{
continue;
}
//check if this is the last field
if (i == fields.Length - 1)
{
sbuild.Append(fields[i].fieldName);
}
else
{
sbuild.Append(fields[i].fieldName + ",");
}
}
sbuild.Append(") VALUES(");
//generate the execution query
for (int i = 0; i < fields.Length; i++)
{
//skip the PK field if is autogenerated.
if (fields[i].isPrimaryKey && fields[i].isValueAutogenerated)
{
continue;
}
//check if this is the last field
if (i == fields.Length - 1)
{
//check for PK placeholder
if (fields[i].fieldValue != null && fields[i].fieldValue.ToString() == FOREIGN_KEY_PLACEHOLDER_VALUE)
{
sbuild.Append(isql.GetValue(fields[i].fieldType, fields[i].fieldValue) + ")");
}
else
{
//add the name of the field
sbuild.Append(factory.GetParameterChar(database) + fields[i].fieldName + ")");
//add the corresponding parameter.
IDataParameter[] iparams = converter.ConvertToDataParameter(database, fields[i]);
listParameters.Add(iparams[0]);
}
}
else
{
//check for PK placeholder
if (fields[i].fieldValue != null && fields[i].fieldValue.ToString() == FOREIGN_KEY_PLACEHOLDER_VALUE)
{
sbuild.Append(isql.GetValue(fields[i].fieldType, fields[i].fieldValue) + ",");
}
else
{
//add the name of the field
sbuild.Append(factory.GetParameterChar(database) + fields[i].fieldName + ",");
//add the coresponding parameter.
IDataParameter[] iparams = converter.ConvertToDataParameter(database, fields[i]);
listParameters.Add(iparams[0]);
}
}
}
execQuery = new ExecutionQuery();
IDataParameter[] par = new IDataParameter[listParameters.Count];
listParameters.CopyTo(par);
execQuery.Parameters = par;
execQuery.Query = sbuild.ToString();
return(execQuery);
}
/// <summary>
/// Generates a query based on a QueryCriteria. This function generates only the
/// query header without the citeria
/// </summary>
/// <param name="database">Database type for which we generate the query</param>
/// <param name="criteria">QueryCriteria based on which the query is generated</param>
/// <returns>Generated ExecutionQuery</returns>
public ExecutionQuery GenerateSelectQuery(DatabaseServer database, QueryCriteria criteria)
{
StringBuilder sqlBuilder = new StringBuilder();
DataFactory factory = new DataFactory();
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
sqlBuilder.Append(" SELECT ");
//add field's name for the criteria mai9n table
for (int i = 0; i < criteria.Fields.Length; i++)
{
sqlBuilder.Append(" " + GetTableName(database, criteria.TableName) + "." + criteria.Fields[i].fieldName);
//check for alias
for (int j = 0; j < criteria.Aliases.Length; j++)
{
if (criteria.Fields[i].fieldName == criteria.Aliases[j].fieldName && criteria.TableName == criteria.Aliases[j].tableName)
{
sqlBuilder.Append(" AS " + criteria.Aliases[j].aliasName);
break;
}
}
if (i != criteria.Fields.Length - 1)
{
sqlBuilder.Append(" ,");
}
}
//check if the criteria has join criteria conditions. If it has then
//add comma.
if (criteria.JoinCriteriaConditions.Length > 0)
{
sqlBuilder.Append(" , ");
}
//now add the joins fields
for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++)
{
for (int j = 0; j < criteria.JoinCriteriaConditions[i].Criteria.Fields.Length; j++)
{
sqlBuilder.Append(GetTableName(DatabaseServer.PostgreSql, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." + criteria.JoinCriteriaConditions[i].Criteria.Fields[j].fieldName);
if (criteria.JoinCriteriaConditions[i].Criteria.Aliases.Length > 0)
{
for (int x = 0; x < criteria.JoinCriteriaConditions[i].Criteria.Aliases.Length; x++)
{
if (criteria.JoinCriteriaConditions[i].Criteria.Fields[j].fieldName == criteria.JoinCriteriaConditions[i].Criteria.Aliases[x].fieldName)
{
sqlBuilder.Append(" AS " + criteria.JoinCriteriaConditions[i].Criteria.Aliases[x].aliasName);
break;
}
}
}
sqlBuilder.Append(COMMA);
}
}
string temp = sqlBuilder.ToString().TrimEnd();
//remove the last comma if it exists
if (temp.EndsWith(COMMA))
{
temp = temp.Remove(temp.Length - COMMA.Length, COMMA.Length);
sqlBuilder.Remove(0, sqlBuilder.Length);
sqlBuilder.Append(temp);
}
sqlBuilder.Append(" FROM " + GetTableName(database, criteria.TableName));
ExecutionQuery execQuery = new ExecutionQuery();
execQuery.Query = sqlBuilder.ToString();
return(execQuery);
}
/// <summary>
/// Generates a SELECT query
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="tableName">Name of the table</param>
/// <param name="fields">List of DatabaseFields based on which we generate the condition</param>
/// <param name="generateConditionByPrimaryKey">Flag used to know if we generate a condition by the primary key field</param>
/// <returns>Returns the SELECT query</returns>
public ExecutionQuery GenerateSelectQuery(DatabaseServer database, string tableName, DatabaseField[] fields, bool generateConditionByPrimaryKey)
{
ExecutionQuery execQuery;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameter = new List <IDataParameter>();
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
StringBuilder sbuild = new StringBuilder();
sbuild.Append("SELECT ");
//separate code paths for code generation depending on the "WHERE" condition
//generate condition by primary key
if (generateConditionByPrimaryKey)
{
for (int i = 0; i < fields.Length; i++)
{
if (!fields[i].isPrimaryKey)
{
if (i == fields.Length - 1)
{
sbuild.Append(GetTableName(database, tableName) + "." + fields[i].fieldName);
}
else
{
sbuild.Append(GetTableName(database, tableName) + "." + fields[i].fieldName + ",");
}
}
}
sbuild.Append(" FROM " + GetTableName(database, tableName));
//generate "where" condition
bool isFirst = true;
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].isPrimaryKey)
{
IDataParameter param = converter.ConvertToDataParameter(database, GetTableName(database, tableName), fields[i]);
if (isFirst)
{
sbuild.Append(" WHERE " + GetTableName(database, tableName) + "." + fields[i].fieldName + isql.GetValueWithComparationOperator(param));
isFirst = false;
listParameter.Add(param);
}
else
{
sbuild.Append(" AND " + GetTableName(database, tableName) + "." + fields[i].fieldName + isql.GetValueWithComparationOperator(param));
listParameter.Add(param);
}
}
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] pmc = new IDataParameter[listParameter.Count];
listParameter.CopyTo(pmc);
return(execQuery);
}
for (int i = 0; i < fields.Length; i++)
{
if (i == fields.Length - 1)
{
sbuild.Append(" " + GetTableName(database, tableName) + "." + fields[i].fieldName);
}
else
{
sbuild.Append(" " + GetTableName(database, tableName) + "." + fields[i].fieldName + ",");
}
}
sbuild.Append(" FROM " + tableName);
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] pmca = new IDataParameter[listParameter.Count];
listParameter.CopyTo(pmca);
return(execQuery);
}
