static public OleDbCommand BuildStoredProcDeleteCommand(esDataRequest request) { OleDbCommand cmd = new OleDbCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose; Dictionary <string, OleDbParameter> types = Cache.GetParameters(request); OleDbParameter p; foreach (esColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { p = types[col.Name]; p = CloneParameter(p); p.SourceVersion = DataRowVersion.Current; cmd.Parameters.Add(p); } } return(cmd); }
static public OleDbCommand BuildDynamicDeleteCommand(esDataRequest request, List <string> modifiedColumns) { Dictionary <string, OleDbParameter> types = Cache.GetParameters(request); OleDbCommand cmd = new OleDbCommand(); 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) { OleDbParameter 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 PopulateStoredProcParameters(OleDbCommand cmd, esDataRequest request) { Dictionary <string, OleDbParameter> types = Cache.GetParameters(request); OleDbParameter p; foreach (esColumnMetadata col in request.Columns) { p = types[col.Name]; p = CloneParameter(p); p.SourceVersion = DataRowVersion.Current; if (p.OleDbType == OleDbType.DBTimeStamp) { p.Direction = ParameterDirection.InputOutput; } cmd.Parameters.Add(p); } }
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuerySerializable query, List <esComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = 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, OleDbParameter> 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) { 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 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 + (++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 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(std, query, comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.Like: sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; needsStringParameter = true; break; case esComparisonOperand.NotLike: sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, 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(std, query, comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.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 && !(str.StartsWith("#"))) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (str != null) { // DateTime sql += comma + Convert.ToString(oin); 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 && !(soin.StartsWith("#"))) { 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: OleDbCommand sqlCommand = std.cmd as OleDbCommand; 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; esColumnMetadataCollection columns = (esColumnMetadataCollection)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) { OleDbParameter 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 = DbType.String; } } else { p = new OleDbParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return(sql); }
static public OleDbCommand BuildDynamicInsertCommand(esDataRequest request, esEntitySavePacket packet) { 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(); OleDbParameter p = null; Dictionary <string, OleDbParameter> types = Cache.GetParameters(request); OleDbCommand cmd = new OleDbCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } esColumnMetadataCollection cols = request.Columns; foreach (esColumnMetadata col in cols) { bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) { p = cmd.Parameters.Add(CloneParameter(types[col.Name])); 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; } else if (col.IsConcurrency) { props["Timestamp"] = col.Name; props["Source"] = request.ProviderMetadata.Source; } else if (col.IsEntitySpacesConcurrency) { props["EntitySpacesConcurrency"] = col.Name; into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "1"; comma = ", "; p = CloneParameter(types[col.Name]); p.Value = 1; 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 = ","; } 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 OleDbCommand BuildDynamicUpdateCommand(esDataRequest request, esEntitySavePacket packet) { 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; List <OleDbParameter> whereParams = new List <OleDbParameter>(); string sql = "UPDATE " + CreateFullName(request) + " SET "; PropertyCollection props = new PropertyCollection(); OleDbParameter p = null; Dictionary <string, OleDbParameter> types = Cache.GetParameters(request); OleDbCommand cmd = new OleDbCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } esColumnMetadataCollection cols = request.Columns; foreach (esColumnMetadata col in cols) { bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) { p = cmd.Parameters.Add(CloneParameter(types[col.Name])); 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) { p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; whereParams.Add(p); //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; whereParams.Add(p); //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; whereParams.Add(p); //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; } if (whereParams.Count > 0) { foreach (OleDbParameter parm in whereParams) { cmd.Parameters.Add(parm); } } sql += " WHERE (" + where + ")"; request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }