public static GetParameters ( System.Guid dataID, |
||
dataID | System.Guid | |
providerMetadata | ||
columns | ||
return | SqlParameter>.Dictionary |
static public void PopulateStoredProcParameters(SqlCommand cmd, tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlParameter p; foreach (tgColumnMetadata col in request.Columns) { p = types[col.Name]; p = CloneParameter(p); if (packet.CurrentValues.ContainsKey(col.Name)) { p.Value = packet.CurrentValues[col.Name]; } if (p.SqlDbType == SqlDbType.Timestamp) { p.Direction = ParameterDirection.InputOutput; } if (col.IsComputed && col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } cmd.Parameters.Add(p); } }
static public SqlCommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string sql = "SET NOCOUNT OFF; "; sql += "DELETE FROM " + CreateFullName(request) + " "; string comma = String.Empty; string concur = String.Empty; comma = String.Empty; sql += " WHERE "; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { SqlParameter p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); sql += comma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; comma = " AND "; } else if (col.IsConcurrency || col.IsTiraggoConcurrency) { SqlParameter p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008" || col.IsTiraggoConcurrency) { concur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; } else { concur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")"; } } } if (concur.Length > 0) { sql += " AND " + concur; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
static public SqlCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate); PopulateStoredProcParameters(cmd, request, packet); tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { if (col.IsComputed || col.IsTiraggoConcurrency) { SqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.InputOutput; } } if (cols.DateModified != null && cols.DateModified.IsServerSide && cols.FindByColumnName(cols.DateModified.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName]; p = cmd.Parameters[p.ParameterName]; p.Value = null; p.Direction = ParameterDirection.Output; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide && cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName]; p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; p = cmd.Parameters[p.ParameterName]; p.Value = null; p.Direction = ParameterDirection.Output; } return(cmd); }
static public SqlCommand BuildStoredProcDeleteCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spDelete); SqlParameter p; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { p = types[col.Name]; p = CloneParameter(p); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); } else if (col.IsConcurrency || col.IsTiraggoConcurrency) { p = types[col.Name]; p = CloneParameter(p); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); } } return(cmd); }
static public SqlCommand BuildStoredProcInsertCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spInsert); PopulateStoredProcParameters(cmd, request, packet); tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { if (col.HasDefault && (col.Default.ToLower().Contains("newid") || col.Default.ToLower().Contains("newsequentialid"))) { // They could pre-assign this even though it has a default SqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; if (packet.ModifiedColumns.Contains(col.Name)) { p.Direction = ParameterDirection.InputOutput; } else { p.Direction = ParameterDirection.Output; } } else if (col.IsComputed || col.IsAutoIncrement || col.IsTiraggoConcurrency) { SqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } } if (cols.DateAdded != null && cols.DateAdded.IsServerSide && cols.FindByColumnName(cols.DateAdded.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.DateAdded.ColumnName].ParameterName]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } if (cols.DateModified != null && cols.DateModified.IsServerSide && cols.FindByColumnName(cols.DateModified.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } if (cols.AddedBy != null && cols.AddedBy.IsServerSide && cols.FindByColumnName(cols.AddedBy.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.AddedBy.ColumnName].ParameterName]; p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide && cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null) { SqlParameter p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName]; p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } return(cmd); }
static public SqlCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet) { string into = String.Empty; string values = String.Empty; string computed = String.Empty; string computedComma = String.Empty; string autoInc = String.Empty; string comma = String.Empty; string where = String.Empty; // newsequentialid variables int seqCount = 0; string seqDeclare = " DECLARE @table_ids TABLE ("; string seqOutput = " OUTPUT "; string seqSelect = " SELECT "; List <string> modifiedColumns = packet.ModifiedColumns; Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); SqlParameter p = null; if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string sql = "SET NOCOUNT OFF"; foreach (tgColumnMetadata col in request.Columns) { string colName = col.Name; if (request.SelectedColumns != null && !request.SelectedColumns.ContainsKey(colName)) { continue; } bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name); if (isModified && !col.IsComputed && !col.IsConcurrency && !col.IsAutoIncrement) { p = types[colName]; p = cmd.Parameters.Add(CloneParameter(p)); object value = packet.CurrentValues[colName]; p.Value = value != null ? value : DBNull.Value; CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma); } else { bool needOutputParam = false; bool needsFetchedAfterSave = false; SqlParameter clone = null; if (col.HasDefault) { p = types[colName]; if (col.esType == tgSystemType.Guid) { if (col.Default.ToLower().Contains("newid")) { // Special logic for newid()'s that weren't supplied with a value, they // go into the SELECT INTO as well sql += " SET " + p.ParameterName + " = NEWID(); "; CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma); needOutputParam = true; } else if (col.Default.ToLower().Contains("newsequentialid")) { if (seqCount > 0) { seqDeclare += ", "; seqOutput += ", "; seqSelect += ", "; } seqCount++; seqDeclare += col.Name + " uniqueidentifier"; seqOutput += "INSERTED." + col.Name; seqSelect += Delimiters.Param + col.PropertyName + "=" + col.Name; needOutputParam = true; } } else { // 11/15/2009 Let's return all default values needOutputParam = true; needsFetchedAfterSave = true; } } else if (col.IsTiraggoConcurrency) { p = types[colName]; sql += " SET " + p.ParameterName + " = 1; "; into += comma; into += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose; values += comma; values += "1"; comma = ", "; needOutputParam = true; } else if (col.IsAutoIncrement) { p = types[colName]; autoInc += " SELECT " + p.ParameterName + " = SCOPE_IDENTITY() "; needOutputParam = true; } else if (col.IsComputed || col.IsConcurrency) { p = types[colName]; needOutputParam = true; needsFetchedAfterSave = true; } if (needOutputParam) { clone = CloneParameter(p); clone.Direction = ParameterDirection.Output; cmd.Parameters.Add(clone); } if (needsFetchedAfterSave) { computed += computedComma; computed += p.ParameterName + " = " + Delimiters.ColumnOpen + colName + Delimiters.ColumnClose; computedComma = ", "; if (col.CharacterMaxLength > 0) { clone.Size = (int)col.CharacterMaxLength; } } } } tgColumnMetadataCollection cols = request.Columns; #region Special Column Logic if (cols.DateAdded != null && cols.DateAdded.IsServerSide && cols.FindByColumnName(cols.DateAdded.ColumnName) != null) { p = CloneParameter(types[cols.DateAdded.ColumnName]); sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateAdded.ServerSideText"] + ";"; CreateInsertSQLSnippet(cols.DateAdded.ColumnName, p, ref into, ref values, ref comma); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } if (cols.DateModified != null && cols.DateModified.IsServerSide && cols.FindByColumnName(cols.DateModified.ColumnName) != null) { p = CloneParameter(types[cols.DateModified.ColumnName]); sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";"; CreateInsertSQLSnippet(cols.DateModified.ColumnName, p, ref into, ref values, ref comma); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } if (cols.AddedBy != null && cols.AddedBy.IsServerSide && cols.FindByColumnName(cols.AddedBy.ColumnName) != null) { p = CloneParameter(types[cols.AddedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength; sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["AddedBy.ServerSideText"] + ";"; CreateInsertSQLSnippet(cols.AddedBy.ColumnName, p, ref into, ref values, ref comma); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide && cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";"; CreateInsertSQLSnippet(cols.ModifiedBy.ColumnName, p, ref into, ref values, ref comma); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } #endregion seqDeclare += ")"; seqOutput += " INTO @table_ids"; seqSelect += " FROM @table_ids"; if (computed.Length > 0) { foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { // We need the were clause if there are defaults to bring back p = types[col.Name]; if (where.Length > 0) { where += " AND "; } where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; if (!cmd.Parameters.Contains(p.ParameterName)) { p = CloneParameter(p); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } } } } string fullName = CreateFullName(request); if (seqCount > 0) { sql += seqDeclare; } sql += " INSERT INTO " + fullName; if (into.Length != 0 && seqCount > 0) { sql += "(" + into + ") " + seqOutput + " VALUES (" + values + ")"; } else if (into.Length != 0) { sql += "(" + into + ") VALUES (" + values + ")"; } else { sql += "DEFAULT VALUES"; } sql += autoInc; if (seqCount > 0) { sql += seqSelect; } if (computed.Length > 0) { sql += " SELECT " + computed + " FROM " + fullName + " WHERE (" + where + ")"; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
static public SqlCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary <string, SqlParameter> types = Cache.GetParameters(request); SqlCommand cmd = new SqlCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string set = string.Empty; string sql = "SET NOCOUNT OFF "; sql += "UPDATE " + CreateFullName(request) + " SET "; string where = String.Empty; string conncur = String.Empty; string computed = String.Empty; string comma = String.Empty; string and = String.Empty; string prolog = String.Empty; SqlParameter p = null; List <string> modifiedColumns = packet.ModifiedColumns; foreach (string colName in modifiedColumns) { tgColumnMetadata col = request.Columns[colName]; if (col == null) { continue; } if (!col.IsInPrimaryKey && !col.IsComputed) { p = CloneParameter(types[colName]); p = cmd.Parameters.Add(p); object value = packet.CurrentValues[colName]; p.Value = value != null ? value : DBNull.Value; sql += comma; sql += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose + " = " + p.ParameterName; comma = ", "; } } foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); where += and; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; and = " AND "; } else if (col.IsConcurrency) { p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008") { conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; } else { conncur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")"; } if (computed.Length > 0) { computed += ", "; } computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; } else if (col.IsComputed && !col.IsAutoIncrement) { if (request.SelectedColumns != null && request.SelectedColumns.ContainsKey(col.Name)) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } cmd.Parameters.Add(p); if (computed.Length > 0) { computed += ", "; } computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; } } else if (col.IsTiraggoConcurrency) { if (packet.OriginalValues != null && packet.OriginalValues.ContainsKey(col.Name)) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.InputOutput; p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); sql += comma.Length > 0 ? ", " : string.Empty; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1"; conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; prolog += " SET " + p.ParameterName + " = " + p.ParameterName + " + 1"; } } } tgColumnMetadataCollection cols = request.Columns; if (cols.DateModified != null && cols.DateModified.IsServerSide && cols.FindByColumnName(cols.DateModified.ColumnName) != null) { p = CloneParameter(types[cols.DateModified.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += comma; sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName; comma = ", "; set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";"; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide && cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += comma; sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName; comma = ", "; set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";"; } sql = set + sql + " WHERE (" + where + ")"; if (conncur.Length > 0) { sql += " AND " + conncur; } if (computed.Length > 0) { sql += " SELECT " + computed + " FROM " + CreateFullName(request) + " WHERE (" + where + ")"; } if (prolog.Length > 0) { sql += prolog; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List <tgComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; DbType paramType = DbType.String; string compareTo = String.Empty; foreach (tgComparison comparisonItem in items) { paramType = DbType.String; tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem; tgDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; std.needsIntegerParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == tgParenthesis.Open) { sql += "("; } else { sql += ")"; } continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case tgConjunction.And: sql += " AND "; break; case tgConjunction.Or: sql += " OR "; break; case tgConjunction.AndNot: sql += " AND NOT "; break; case tgConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary <string, SqlParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { if (comparisonData.Column.Name[0] == '<') { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); } else { sql += comparisonData.Column.Name; } continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as tgDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case tgComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case tgComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case tgComparisonOperand.Equal: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case tgComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case tgComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case tgComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; paramType = DbType.AnsiStringFixedLength; needsStringParameter = true; break; case tgComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case tgComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case tgComparisonOperand.In: case tgComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == tgComparisonOperand.In) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) { sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose; } else { sql += comma + Convert.ToString(o); } comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ")"; requiresParam = false; } } break; case tgComparisonOperand.Between: SqlCommand sqlCommand = std.cmd as SqlCommand; sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); sql += " AND " + compareTo; sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { SqlParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (needsStringParameter) { p.DbType = paramType; } else if (std.needsIntegerParameter) { p.DbType = DbType.Int32; } } else { p = new SqlParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return(sql); }