static public EfzCommand BuildStoredProcUpdateCommand(esDataRequest request) { EfzCommand cmd = new EfzCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request); foreach (esColumnMetadata col in request.Columns) { if (col.IsComputed || col.IsEntitySpacesConcurrency) { EfzParameter p = cmd.Parameters["?p" + (col.Name).Replace(" ", String.Empty)]; p.SourceVersion = DataRowVersion.Original; p.Direction = ParameterDirection.InputOutput; } } return(cmd); }
static public EfzCommand BuildDynamicDeleteCommand(esDataRequest request, List <string> modifiedColumns) { Dictionary <string, EfzParameter> types = Cache.GetParameters(request); EfzCommand cmd = new EfzCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string sql = "DELETE FROM " + CreateFullName(request) + " "; string comma = String.Empty; comma = String.Empty; sql += " WHERE "; foreach (esColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency || col.IsConcurrency) { EfzParameter p = types[col.Name]; cmd.Parameters.Add(CloneParameter(p)); sql += comma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; comma = " AND "; } } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
static public void GatherReturnParameters(EfzCommand cmd, esDataRequest request, esDataResponse response) { if (cmd.Parameters.Count > 0) { if (request.Parameters != null && request.Parameters.Count > 0) { string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param; response.Parameters = new esParameters(); foreach (esParameter esParam in request.Parameters) { if (esParam.Direction != esParameterDirection.Input) { response.Parameters.Add(esParam); EfzParameter p = cmd.Parameters[paramPrefix + esParam.Name]; esParam.Value = p.Value; } } } } }
static public Dictionary <string, EfzParameter> GetParameters(Guid dataID, esProviderSpecificMetadata providerMetadata, esColumnMetadataCollection columns) { lock (parameterCache) { if (!parameterCache.ContainsKey(dataID)) { // The Parameters for this Table haven't been cached yet, this is a one time operation Dictionary <string, EfzParameter> types = new Dictionary <string, EfzParameter>(); EfzParameter param1; foreach (esColumnMetadata col in columns) { esTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName); if (typeMap != null) { string nativeType = typeMap.NativeType; EfzType dbType = Cache.NativeTypeToDbType(nativeType); param1 = new EfzParameter(Delimiters.Param + col.PropertyName, dbType, col.PropertyName); param1.SourceColumn = col.Name; switch (dbType) { // TODO: //case SqlDbType.BigInt: //case SqlDbType.Decimal: //case SqlDbType.Float: //case SqlDbType.Int: //case SqlDbType.Money: //case SqlDbType.Real: //case SqlDbType.SmallMoney: //case SqlDbType.TinyInt: //case SqlDbType.SmallInt: // param1.Size = (int)col.CharacterMaxLength; // param1.Precision = (byte)col.NumericPrecision; // param1.Scale = (byte)col.NumericScale; // break; //case SqlDbType.DateTime: // param1.Precision = 23; // param1.Scale = 3; // break; //case SqlDbType.SmallDateTime: // param1.Precision = 16; // break; //case SqlDbType.Udt: // SetUdtTypeNameToAvoidMonoError(param1, typeMap); // break; } types[col.Name] = param1; } } parameterCache[dataID] = types; } } return(parameterCache[dataID]); }
static public EfzParameter CloneParameter(EfzParameter p) { ICloneable param = p as ICloneable; return(param.Clone() as EfzParameter); }
protected static void OnRowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e) { try { PropertyCollection props = e.Row.Table.ExtendedProperties; if (props.ContainsKey("props")) { props = (PropertyCollection)props["props"]; } if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)) { esDataRequest request = props["esDataRequest"] as esDataRequest; esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"]; string source = props["Source"] as string; if (e.StatementType == StatementType.Insert) { if (props.Contains("AutoInc")) { string autoInc = props["AutoInc"] as string; EfzCommand cmd = new EfzCommand(); cmd.Connection = e.Command.Connection as EfzConnection; cmd.Transaction = e.Command.Transaction as EfzTransaction; cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion { o = cmd.ExecuteScalar(); } if (o != null) { e.Row[autoInc] = o; ((EfzParameter)e.Command.Parameters["@" + autoInc]).Value = o; } } if (props.Contains("EntitySpacesConcurrency")) { string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string; packet.CurrentValues[esConcurrencyColumn] = 1; } } if (props.Contains("Timestamp")) { string column = props["Timestamp"] as string; EfzCommand cmd = new EfzCommand(); cmd.Connection = e.Command.Connection as EfzConnection; cmd.Transaction = e.Command.Transaction as EfzTransaction; cmd.CommandText = "SELECT LastTimestamp('" + source + "');"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion { o = cmd.ExecuteScalar(); } if (o != null) { ((EfzParameter)e.Command.Parameters["@" + column]).Value = o; } } //------------------------------------------------------------------------------------------------- // Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way //------------------------------------------------------------------------------------------------- if (props.Contains("Defaults")) { // Build the Where parameter and parameters EfzCommand cmd = new EfzCommand(); cmd.Connection = e.Command.Connection as EfzConnection; cmd.Transaction = e.Command.Transaction as EfzTransaction; string select = (string)props["Defaults"]; string[] whereParameters = ((string)props["Where"]).Split(','); string comma = String.Empty; string where = String.Empty; int i = 1; foreach (string parameter in whereParameters) { EfzParameter p = new EfzParameter("@p" + i++.ToString(), e.Row[parameter]); cmd.Parameters.Add(p); where += comma + "[" + parameter + "]=" + p.ParameterName; comma = " AND "; } // Okay, now we can execute the sql and get any values that have defaults that were // null at the time of the insert and/or our timestamp cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";"; EfzDataReader rdr = null; try { #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } if (rdr.Read()) { select = select.Replace("[", String.Empty).Replace("]", String.Empty); string[] selectCols = select.Split(','); for (int k = 0; k < selectCols.Length; k++) { packet.CurrentValues[selectCols[k]] = rdr.GetValue(k); } } } finally { // Make sure we close the reader no matter what if (rdr != null) rdr.Close(); } } if (e.StatementType == StatementType.Update) { string colName = props["EntitySpacesConcurrency"] as string; object o = e.Row[colName]; EfzParameter p = e.Command.Parameters["@" + colName] as EfzParameter; object v = null; switch (Type.GetTypeCode(o.GetType())) { case TypeCode.Int16: v = ((System.Int16)o) + 1; break; case TypeCode.Int32: v = ((System.Int32)o) + 1; break; case TypeCode.Int64: v = ((System.Int64)o) + 1; break; case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break; case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break; case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break; } p.Value = v; } } } catch { } }
static private EfzParameter CloneParameter(EfzParameter p) { ICloneable param = p as ICloneable; return param.Clone() as EfzParameter; }
protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List<esComparison> items, string prefix, EfzCommand cmd, ref int pindex) { 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 (esComparison comparisonItem in items) { paramType = DbType.String; esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; std.needsIntegerParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) sql += "("; else sql += ")"; continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary<string, EfzParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if(comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " = " + compareTo; else sql += compareTo + " = " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " <> " + compareTo; else sql += compareTo + " <> " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " > " + compareTo; else sql += compareTo + " > " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " < " + compareTo; else sql += compareTo + " < " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " <= " + compareTo; else sql += compareTo + " <= " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " >= " + compareTo; else sql += compareTo + " >= " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if(String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; paramType = DbType.AnsiStringFixedLength; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(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 esComparisonOperand.Between: sql += ApplyWhereSubOperations(comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); sql += " AND " + compareTo; cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { EfzParameter 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 EfzParameter(compareTo, comparisonData.Value); } cmd.Parameters.Add(p); } } } return sql; }
protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List <esComparison> items, string prefix, EfzCommand cmd, ref int pindex) { 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 (esComparison comparisonItem in items) { paramType = DbType.String; esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; std.needsIntegerParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) { sql += "("; } else { sql += ")"; } continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary <string, EfzParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(comparisonData); } break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; paramType = DbType.AnsiStringFixedLength; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(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 esComparisonOperand.Between: sql += ApplyWhereSubOperations(comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); sql += " AND " + compareTo; cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { EfzParameter 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 EfzParameter(compareTo, comparisonData.Value); } cmd.Parameters.Add(p); } } } return(sql); }
static public EfzCommand BuildDynamicInsertCommand(esDataRequest request, List <string> modifiedColumns) { string sql = String.Empty; string defaults = String.Empty; string into = String.Empty; string values = String.Empty; string comma = String.Empty; string defaultComma = String.Empty; string where = String.Empty; string whereComma = String.Empty; PropertyCollection props = new PropertyCollection(); EfzParameter p = null; Dictionary <string, EfzParameter> types = Cache.GetParameters(request); EfzCommand cmd = new EfzCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } esColumnMetadataCollection cols = request.Columns; foreach (esColumnMetadata col in cols) { bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) { p = types[col.Name]; cmd.Parameters.Add(CloneParameter(p)); into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + p.ParameterName; comma = ", "; } else if (col.IsAutoIncrement) { props["AutoInc"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } else if (col.IsConcurrency) { props["Timestamp"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } else if (col.IsEntitySpacesConcurrency) { into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "1"; comma = ", "; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; p.Value = 1; // Seems to work, We'll take it ... cmd.Parameters.Add(p); } else if (col.IsComputed) { // Do nothing but leave this here } else if (cols.IsSpecialColumn(col)) { // Do nothing but leave this here } else if (col.HasDefault) { defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; defaultComma = ","; string def = col.Default.ToLower(); if (def.Contains("guid") || def.Contains("newid")) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; p.SourceVersion = DataRowVersion.Current; cmd.Parameters.Add(p); sql += " IF " + Delimiters.Param + col.Name + " IS NULL SET " + Delimiters.Param + col.Name + " = NEWID();"; into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + p.ParameterName; comma = ", "; } } if (col.IsInPrimaryKey) { where += whereComma + col.Name; whereComma = ","; } } #region Special Columns if (cols.DateAdded != null && cols.DateAdded.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateAdded.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.DateModified != null && cols.DateModified.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateModified.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.AddedBy != null && cols.AddedBy.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["AddedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } #endregion if (defaults.Length > 0) { comma = String.Empty; props["Defaults"] = defaults; props["Where"] = where; } sql += " INSERT INTO " + CreateFullName(request); if (into.Length != 0) { sql += "(" + into + ") VALUES (" + values + ")"; } else { sql += "DEFAULT VALUES"; } request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
static public EfzCommand BuildDynamicUpdateCommand(esDataRequest request, List <string> modifiedColumns) { string where = String.Empty; string scomma = String.Empty; string wcomma = String.Empty; string defaults = String.Empty; string defaultsWhere = String.Empty; string defaultsComma = String.Empty; string defaultsWhereComma = String.Empty; string sql = "UPDATE " + CreateFullName(request) + " SET "; PropertyCollection props = new PropertyCollection(); EfzParameter p = null; Dictionary <string, EfzParameter> types = Cache.GetParameters(request); EfzCommand cmd = new EfzCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } esColumnMetadataCollection cols = request.Columns; foreach (esColumnMetadata col in cols) { bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) { p = CloneParameter(types[col.Name]); cmd.Parameters.Add(p); sql += scomma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; scomma = ", "; } else if (col.IsAutoIncrement) { // Nothing to do but leave this here } else if (col.IsConcurrency) { props["Timestamp"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; } else if (col.IsEntitySpacesConcurrency) { props["EntitySpacesConcurrency"] = col.Name; p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); sql += scomma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1"; scomma = ", "; where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; } else if (col.IsComputed) { // Do nothing but leave this here } else if (cols.IsSpecialColumn(col)) { // Do nothing but leave this here } else if (col.HasDefault) { // defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; // defaultsComma = ","; } if (col.IsInPrimaryKey) { p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; cmd.Parameters.Add(p); where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; defaultsWhere += defaultsWhereComma + col.Name; defaultsWhereComma = ","; } } #region Special Columns if (cols.DateModified != null && cols.DateModified.IsServerSide) { sql += scomma; sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"]; scomma = ", "; defaults += defaultsComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; defaultsComma = ","; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { sql += scomma; sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"]; scomma = ", "; defaults += defaultsComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; defaultsComma = ","; } #endregion if (defaults.Length > 0) { props["Defaults"] = defaults; props["Where"] = defaultsWhere; } sql += " WHERE " + where + ";"; request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }