/// <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> /// 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 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); }