/// <summary> /// Generates a query using paging /// </summary> /// <param name="metadata">The metadata.</param> /// <param name="numberOfItems">The number of items.</param> /// <param name="pageNumber">The page number.</param> /// <returns></returns> public string GeneratePaginatedQuery(TableMetadata metadata, int numberOfItems, int pageNumber) { StringBuilder builder = new StringBuilder(); SqlGenerator generator = new SqlGenerator(); //SELECT TOP 5 * FROM items WHERE intitemid NOT IN (SELECT TOP 10 intitemid FROM items ORDER BY intitemid) ORDER BY intitemid asc DatabaseField field = metadata.GetPrimaryKeyField(); string tableName = generator.GetTableName(DatabaseServer.SqlServer, metadata.TableName); builder.Append("SELECT TOP "); builder.Append(numberOfItems.ToString()); builder.Append(" FROM "); builder.Append(tableName); builder.Append(" WHERE "); builder.Append(field.fieldName + " NOT IN ("); builder.Append(" SELECT TOP " + (pageNumber * numberOfItems).ToString()); builder.Append(" " + field.fieldName + " "); builder.Append(" FROM "); builder.Append(tableName); builder.Append(" ORDER BY " + field.fieldName + ")"); builder.Append(" ORDER BY " + field.fieldName + " asc"); return(builder.ToString()); }
/// <summary> /// Converts the specified DataField into a IDataParameter. This ensures that the /// name of the parameter is unique by comparing with a list of specified used names /// </summary> /// <param name="database">Database server</param> /// <param name="tableName">Name of the table to which the field belongs</param> /// <param name="field">DatabaseField which will be converted</param> /// <param name="listUsedParameterNames">List with used names of a parameter. When the name of the parameter is given it is checked against the values in this list. If the name if found then it is changed to be unique</param> /// <returns>The IDataParameter</returns> public IDataParameter ConvertToDataParameter(DatabaseServer database, string tableName, DatabaseField field, ref List <string> listUsedParameterNames) { IDataParameter parameter = null; SqlGenerator generator = new SqlGenerator(); DataFactory factory = new DataFactory(); factory.InitializeDataParameter(database, ref parameter); string parameterName = factory.GetParameterChar(database) + generator.GetTableName(tableName) + field.fieldName; while (listUsedParameterNames.Contains(parameterName)) { parameterName = parameterName + PARAMETER_NAME_ENDING; } parameter.ParameterName = parameterName; //add it to the external list listUsedParameterNames.Add(parameterName); if (field.fieldValue == null) { parameter.Value = DBNull.Value; } else { parameter.Value = field.fieldValue; } parameter.DbType = field.fieldType; parameter.SourceColumn = field.fieldName; return(parameter); }
/// <summary> /// </summary> /// <param name="criteria"></param> /// <remarks>Generste</remarks> /// <returns></returns> internal ExecutionQuery GenerateWithJoin(QueryCriteria criteria) { StringBuilder sbuild = new StringBuilder(); bool appendWhere = false; ExecutionQuery execQuery; List <IDataParameter> listParameters = null; SqlGenerator generator = new SqlGenerator(); try { listParameters = new List <IDataParameter>(); //generate the head of the SELECT query. //we'll used this temporary structure (tempQuery) and pass it around //to the generator functions. execQuery = generator.GenerateSelectQuery(DatabaseServer.MySQL, criteria); //add from the head query to the temporary objects. sbuild.Append(execQuery.Query); if (execQuery.Parameters != null) { foreach (IDataParameter var in execQuery.Parameters) { listParameters.Add(var); } } //add joins now for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++) { switch (criteria.JoinCriteriaConditions[i].Join) { case JoinType.Inner: sbuild.Append(" Inner Join "); break; case JoinType.Left: sbuild.Append(" Left Join "); break; case JoinType.Right: sbuild.Append(" Right Join "); break; } sbuild.Append(generator.GetTableName(DatabaseServer.MySQL, criteria.JoinCriteriaConditions[i].ForeignKeyFieldTableName) + " ON " + generator.GetTableName(DatabaseServer.MySQL, criteria.JoinCriteriaConditions[i].PrimaryKeyFieldTableName) + "." + criteria.JoinCriteriaConditions[i].PrimaryKey.fieldName + "=" + generator.GetTableName(DatabaseServer.MySQL, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." + criteria.JoinCriteriaConditions[i].ForeignKey.fieldName); } //add conditions //add "WHERE" condition from the first criteria if (criteria.CriteriaConditions.Length > 0) { sbuild.Append(" WHERE "); appendWhere = true; sbuild.Append(GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters)); } //add the join criterias for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++) { if ((i == 0) && (appendWhere == false)) { sbuild.Append(" WHERE "); } if (criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions.Length > 0) { if (sbuild.ToString().EndsWith("WHERE ")) { sbuild.Append(GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions, ref sbuild, ref listParameters)); } else { sbuild.Append(" AND " + GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions, ref sbuild, ref listParameters)); } } } //checks for where and and if (sbuild.ToString().EndsWith(" WHERE ")) { sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH); } execQuery.Query = sbuild.ToString(); IDataParameter[] pmc = new IDataParameter[listParameters.Count]; listParameters.CopyTo(pmc); execQuery.Parameters = pmc; return(execQuery); } catch (Exception ex) { throw ex; } finally { if (listParameters != null) { listParameters.Clear(); listParameters = null; } } }
/// <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> /// Converts the fields to IDataParameter. The name of the parameter will also include /// the name of the table. /// </summary> /// <param name="database">Database server type</param> /// <param name="tableName">Name of the database table</param> /// <param name="field">Field to be converted</param> /// <returns>The IDataParameter</returns> public IDataParameter ConvertToDataParameter(DatabaseServer database, string tableName, DatabaseField field) { IDataParameter parameter = null; DataFactory factory = new DataFactory(); factory.InitializeDataParameter(database, ref parameter); SqlGenerator generator = new SqlGenerator(); parameter.ParameterName = factory.GetParameterChar(database).ToString() + generator.GetTableName(tableName) + field.fieldName; parameter.SourceColumn = field.fieldName; if (field.fieldValue == null) { parameter.Value = DBNull.Value; } else { parameter.Value = field.fieldValue; } parameter.DbType = field.fieldType; return(parameter); }
/// <summary> /// Generates the query without a join /// </summary> /// <param name="criteria">The criteria.</param> /// <returns></returns> internal ExecutionQuery GenerateWithoutJoin(QueryCriteria criteria) { List <IDataParameter> listParameters = null; ExecutionQuery execQuery = new ExecutionQuery(); SqlGenerator generator = new SqlGenerator(); listParameters = new List <IDataParameter>(); StringBuilder sbuild = new StringBuilder(); DataFactory factory = new DataFactory(); if (generatorType == QueryCriteriaGeneratorType.Select) { execQuery = generator.GenerateSelectQuery(DatabaseServer.SqlServer, criteria); } else if (generatorType == QueryCriteriaGeneratorType.Update) { execQuery = generator.GenerateUpdateQuery(DatabaseServer.SqlServer, criteria.TableName, criteria.Fields, false); } else if (generatorType == QueryCriteriaGeneratorType.Delete) { execQuery = generator.GenerateDeleteQuery(DatabaseServer.SqlServer, 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 ISqlGenerator isql = factory.InitializeSqlGenerator(DatabaseServer.SqlServer); //append where clause sbuild.Append(WHERE_KEYWORD); //generate the condition based on criteria string condition = GenerateCondition(generator.GetTableName(DatabaseServer.SqlServer, criteria.TableName), criteria.CriteriaConditions, ref sbuild, ref listParameters); //more checks if (sbuild.ToString().EndsWith(WHERE_KEYWORD) && 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_KEYWORD)) { 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> /// GENERATE a SELECT query with joins /// </summary> /// <param name="criteria"></param> /// <remarks>Generste</remarks> /// <returns></returns> internal ExecutionQuery GenerateWithJoin(QueryCriteria criteria) { StringBuilder sbuild = new StringBuilder(); bool appendWhere = false; ExecutionQuery execQuery; List <IDataParameter> listParameters = null; SqlGenerator generator = new SqlGenerator(); try { listParameters = new List <IDataParameter>(); //generate the head of the SELECT query. //we'll used this temporary structure (tempQuery) and pass it around //to the generator functions. execQuery = generator.GenerateSelectQuery(DatabaseServer.Access, criteria); //add from the head query to the temporary objects. sbuild.Append(execQuery.Query); if (execQuery.Parameters != null) { foreach (IDataParameter var in execQuery.Parameters) { listParameters.Add(var); } } int joinsCount = criteria.JoinCriteriaConditions.Length; StringBuilder parantheses = null; if (joinsCount > 1) { parantheses = new StringBuilder(joinsCount); for (int i = 0; i < parantheses.Capacity; i++) { parantheses.Append("("); } //HACK : here we need to add the colons for the joins. //Because the name of the first table is already added we must //add the parantheses before. string generatedQuery = sbuild.ToString(); int lastIndex = generatedQuery.LastIndexOf(" "); generatedQuery = generatedQuery.Insert(lastIndex, parantheses.ToString()); //clear the generated string so far sbuild.Remove(0, sbuild.Length); //add the new string sbuild.Append(generatedQuery); } //add the JOINS for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++) { switch (criteria.JoinCriteriaConditions[i].Join) { case JoinType.Inner: sbuild.Append(" Inner Join "); break; case JoinType.Left: sbuild.Append(" Left Join "); break; case JoinType.Right: sbuild.Append(" Right Join "); break; } sbuild.Append(generator.GetTableName(DatabaseServer.Access, criteria.JoinCriteriaConditions[i].ForeignKeyFieldTableName) + " ON " + generator.GetTableName(DatabaseServer.Access, criteria.JoinCriteriaConditions[i].PrimaryKeyFieldTableName) + "." + criteria.JoinCriteriaConditions[i].PrimaryKey.fieldName + "=" + generator.GetTableName(DatabaseServer.Access, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." + criteria.JoinCriteriaConditions[i].ForeignKey.fieldName); if (parantheses != null) { sbuild.Append(")"); } } //add conditions if (criteria.CriteriaConditions.Length > 0) { sbuild.Append(" WHERE "); appendWhere = true; sbuild.Append(GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters)); } for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++) { if ((i == 0) && (appendWhere == false)) { sbuild.Append(" WHERE "); } if (criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions.Length > 0) { if (sbuild.ToString().EndsWith("WHERE ")) { sbuild.Append(GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions, ref sbuild, ref listParameters)); } else { sbuild.Append(" AND " + GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions, ref sbuild, ref listParameters)); } } } //checks for where and and if (sbuild.ToString().EndsWith(" WHERE ")) { sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH); } execQuery.Query = sbuild.ToString(); IDataParameter[] pmc = new IDataParameter[listParameters.Count]; listParameters.CopyTo(pmc); execQuery.Parameters = pmc; return(execQuery); } catch (Exception ex) { throw ex; } finally { if (listParameters != null) { listParameters.Clear(); listParameters = null; } } }