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