public static OleDbCommand BuildDynamicDeleteCommand(tgDataRequest 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 (tgColumnMetadata 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; }
public static void AddParameters(OracleCommand cmd, tgDataRequest request) { if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}")) { int i = 0; string token = String.Empty; string sIndex = String.Empty; string param = String.Empty; foreach (tgParameter esParam in request.Parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = Delimiters.Param + "p" + sIndex; request.QueryText = request.QueryText.Replace(token, param); i++; OracleParameter p = new OracleParameter(Delimiters.Param + esParam.Name, esParam.Value); cmd.Parameters.Add(p); } } else { OracleParameter param; string paramPrefix = request.QueryType == tgQueryType.StoredProcedure ? String.Empty : Delimiters.Param; paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == request.QueryText ? "p" : paramPrefix; foreach (tgParameter esParam in request.Parameters) { param = new OracleParameter(paramPrefix + esParam.Name, esParam.Value); cmd.Parameters.Add(param); // The default is ParameterDirection.Input switch (esParam.Direction) { case tgParameterDirection.InputOutput: param.Direction = ParameterDirection.InputOutput; break; case tgParameterDirection.Output: param.Direction = ParameterDirection.Output; param.DbType = esParam.DbType; param.Size = esParam.Size; // Precision and Scale are obsolete for Oracle // and are ignored. //param.Scale = esParam.Scale; //param.Precision = esParam.Precision; break; case tgParameterDirection.ReturnValue: param.Direction = ParameterDirection.ReturnValue; break; } } } }
/// <summary> /// Used to issue a Save command on an esEntity or tgEntityCollection. /// </summary> /// <param name="request">Contains all of the information necessary to issue and carry out the request</param> /// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param> /// <returns></returns> public tgDataResponse esSaveDataTable(tgDataRequest request, tgProviderSignature sig) { request.DatabaseVersion = sig.DatabaseVersion; tgDataResponse response = tgProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esSaveDataTable(request); // NOTE: New to 1.6.0. We do not rethrow the exception here, we do rethrow it in // tgEntityCollection.SaveToProviderInsertsUpdates after we assign the errors to the proper rows. return response; }
public static VistaDBCommand PrepareCommand(tgDataRequest request) { StandardProviderParameters std = new StandardProviderParameters(); std.cmd = new VistaDBCommand(); std.pindex = NextParamIndex(std.cmd); std.request = request; string sql = BuildQuery(std, request.DynamicQuery); std.cmd.CommandText = sql; return (VistaDBCommand)std.cmd; }
public static void AddParameters(MySqlCommand cmd, tgDataRequest request) { if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}")) { int i = 0; string token = String.Empty; string sIndex = String.Empty; string param = String.Empty; foreach (tgParameter esParam in request.Parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = Delimiters.Param + "p" + sIndex; request.QueryText = request.QueryText.Replace(token, param); i++; cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value); } } else { MySqlParameter param; string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param; foreach (tgParameter esParam in request.Parameters) { param = cmd.Parameters.AddWithValue(paramPrefix + esParam.Name, esParam.Value); // The default is ParameterDirection.Input switch (esParam.Direction) { case tgParameterDirection.InputOutput: param.Direction = ParameterDirection.InputOutput; break; case tgParameterDirection.Output: param.Direction = ParameterDirection.Output; param.DbType = esParam.DbType; param.Size = esParam.Size; param.Scale = esParam.Scale; param.Precision = esParam.Precision; break; case tgParameterDirection.ReturnValue: param.Direction = ParameterDirection.ReturnValue; break; } } } }
public static void AddParameters(SqlCeCommand cmd, tgDataRequest request) { if (request.QueryType == tgQueryType.Text && request.QueryText != null && -1 != request.QueryText.IndexOf("{0}")) { int i = 0; string token = String.Empty; string sIndex = String.Empty; string param = String.Empty; foreach (tgParameter esParam in request.Parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = Delimiters.Param + "p" + sIndex; request.QueryText = request.QueryText.Replace(token, param); i++; cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value); } } else { SqlCeParameter param; foreach (tgParameter esParam in request.Parameters) { param = cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value); switch (esParam.Direction) { case tgParameterDirection.InputOutput: param.Direction = ParameterDirection.InputOutput; break; case tgParameterDirection.Output: param.Direction = ParameterDirection.Output; param.DbType = esParam.DbType; param.Size = esParam.Size; param.Scale = esParam.Scale; param.Precision = esParam.Precision; break; case tgParameterDirection.ReturnValue: param.Direction = ParameterDirection.ReturnValue; break; // The default is ParameterDirection.Input; } } } }
/// <summary> /// Used to populate an esEntity or tgEntityCollection with data. /// </summary> /// <param name="request">Contains all of the information necessary to issue and carry out the request</param> /// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param> /// <returns></returns> public tgDataResponse esLoadDataTable(tgDataRequest request, tgProviderSignature sig) { request.DatabaseVersion = sig.DatabaseVersion; tgDataResponse response = tgProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esLoadDataTable(request); if(request.DynamicQuery != null) { request.DynamicQuery.tg.LastQuery = response.LastQuery; } if (response.IsException) { throw response.Exception; } return response; }
public static string CreateFullName(tgDataRequest request) { string name = String.Empty; name += Delimiters.TableOpen; if (request.DynamicQuery != null && request.DynamicQuery.tg.QuerySource != null) name += request.DynamicQuery.tg.QuerySource; else name += request.QueryText != null ? request.QueryText : request.ProviderMetadata.Destination; name += Delimiters.TableClose; return name; }
public static void PopulateStoredProcParameters(NpgsqlCommand cmd, tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlParameter 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.NpgsqlDbType == NpgsqlDbType.Timestamp) { p.Direction = ParameterDirection.InputOutput; } if (col.IsComputed && col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } cmd.Parameters.Add(p); } }
public static string CreateFullSPName(tgDataRequest request, string spName) { string name = String.Empty; if ((request.Catalog != null || request.ProviderMetadata.Catalog != null) && (request.Schema != null || request.ProviderMetadata.Schema != null)) { name += Delimiters.TableOpen; name += request.Catalog != null ? request.Catalog : request.ProviderMetadata.Catalog; name += Delimiters.TableClose + "."; } if (request.Schema != null || request.ProviderMetadata.Schema != null) { name += Delimiters.TableOpen; name += request.Schema != null ? request.Schema : request.ProviderMetadata.Schema; name += Delimiters.TableClose + "."; } name += Delimiters.StoredProcNameOpen; name += spName; name += Delimiters.StoredProcNameClose; return name; }
public static string CreateFullName(tgDataRequest request, tgDynamicQuerySerializable query) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; tgProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as tgProviderSpecificMetadata; string name = String.Empty; string catalog = iQuery.Catalog ?? request.Catalog ?? providerMetadata.Catalog; string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema; if (catalog != null && schema != null) { name += Delimiters.TableOpen + catalog + Delimiters.TableClose + "."; } if (schema != null) { name += Delimiters.TableOpen + schema + Delimiters.TableClose + "."; } name += Delimiters.TableOpen; if (query.tg.QuerySource != null) name += query.tg.QuerySource; else name += providerMetadata.Destination; name += Delimiters.TableClose; return name; }
public static NpgsqlCommand BuildStoredProcInsertCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlCommand cmd = new NpgsqlCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request, packet); foreach (tgColumnMetadata col in request.Columns) { if (col.HasDefault && col.Default.ToLower().Contains("newid")) { NpgsqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.InputOutput; } else if (col.IsComputed || col.IsAutoIncrement) { NpgsqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.Output; } } return cmd; }
public static NpgsqlCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { string where = String.Empty; string conncur = String.Empty; string scomma = String.Empty; string defaults = String.Empty; string defaultsComma = String.Empty; string and = String.Empty; string sql = "UPDATE " + CreateFullName(request) + " SET "; PropertyCollection props = new PropertyCollection(); NpgsqlParameter p = null; Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlCommand cmd = new NpgsqlCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata 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])); object value = packet.CurrentValues[col.Name]; p.Value = value != null ? value : DBNull.Value; sql += scomma + 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; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; } else if (col.IsEntitySpacesConcurrency) { p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); sql += scomma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName + " + 1"; conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; defaultsComma = ","; } 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.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); where += and + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; and = " AND "; } } #region Special Column Logic if (cols.DateModified != null && cols.DateModified.IsServerSide) { p = CloneParameter(types[cols.DateModified.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += scomma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"]; scomma = ", "; defaults += defaultsComma + cols.DateModified.ColumnName; defaultsComma = ","; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += scomma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"]; scomma = ", "; defaults += defaultsComma + cols.ModifiedBy.ColumnName; defaultsComma = ","; tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName]; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } #endregion sql += " WHERE " + where + ""; if (conncur.Length > 0) { sql += " AND " + conncur; } if (defaults.Length > 0) { sql += "; SELECT " + defaults + " FROM " + CreateFullName(request) + " WHERE (" + where + ")"; } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return cmd; }
//CREATE PROCEDURE "MYGENERATION"."proc_SeqTestUpdate" ( // pID IN "SeqTest"."ID"%type, // pTimeStamp IN OUT "SeqTest"."TimeStamp"%type, // pData IN "SeqTest"."Data"%type //) //IS // pConncurrency "SeqTest"."TimeStamp"%type := pTimeStamp; //BEGIN // UPDATE "SeqTest" // SET // "TimeStamp" = "TimeStamp" + 1, // "Data" = pData // WHERE // "ID" = pID // AND "TimeStamp" = pConncurrency //; // IF SQL%ROWCOUNT = 1 THEN // pTimeStamp := (pConncurrency + 1); // ELSE // Raise_application_error(01403, 'NO RECORDS WERE UPDATED'); // END IF; //END ; public static OracleCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleCommand cmd = new OracleCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; string sql = string.Empty; bool hasConcurrency = false; string concurrencyColumn = String.Empty; List<string> modifiedColumns = packet.ModifiedColumns; foreach (tgColumnMetadata col in request.Columns) { if (col.IsTiraggoConcurrency) { hasConcurrency = true; concurrencyColumn = col.Name; sql += "DECLARE pConncurrency " + request.ProviderMetadata.GetTypeMap(col.PropertyName).NativeType + "; "; break; } } OracleParameter p = null; sql += " BEGIN "; tgColumnMetadataCollection cols = request.Columns; if (cols.DateModified != null && cols.DateModified.IsServerSide) { p = CloneParameter(types[cols.DateModified.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += p.ParameterName + " := " + request.ProviderMetadata["DateModified.ServerSideText"] + "; "; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += p.ParameterName + " := " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + "; "; } if (hasConcurrency) { sql += "pConncurrency := " + Delimiters.Param + concurrencyColumn + "; "; } sql += "UPDATE " + CreateFullName(request) + " SET "; string computed = String.Empty; string comma = String.Empty; string and = String.Empty; string where = string.Empty; foreach (string colName in modifiedColumns) { tgColumnMetadata col = request.Columns[colName]; if (col != null && !col.IsInPrimaryKey && !col.IsTiraggoConcurrency) { p = cmd.Parameters.Add(CloneParameter(types[colName])); 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.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; } } if (col.IsTiraggoConcurrency) { sql += ", "; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1"; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.InputOutput; p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); break; } } sql += " WHERE (" + where + ") "; if (hasConcurrency) { sql += " AND \"" + concurrencyColumn + "\" = pConncurrency; "; sql += "IF SQL%ROWCOUNT = 1 THEN "; sql += Delimiters.Param + concurrencyColumn + " := pConncurrency + 1; ELSE "; sql += "Raise_application_error(-20101, 'NO RECORDS WERE UPDATED'); END IF;"; } else { sql += ";"; } if (computed.Length > 0) { sql += " SELECT " + computed + " FROM " + CreateFullName(request) + " WHERE (" + where + ")"; } sql += " END;"; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return cmd; }
public static OracleCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleCommand cmd = new OracleCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; string comma = String.Empty; string into = String.Empty; string values = String.Empty; string computedColumns = String.Empty; string computedParameters = String.Empty; string computedComma = String.Empty; List<string> modifiedColumns = packet.ModifiedColumns; string sql = "BEGIN "; OracleParameter p = null; foreach (tgColumnMetadata col in request.Columns) { if (col.IsAutoIncrement) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); string AutoKeyText = request.ProviderMetadata["AutoKeyText"]; // SELECT EMPLOYEE_ID.NextVal INTO p_EMPLOYEE_ID FROM DUAL; sql += "SELECT \"" + AutoKeyText + "\".NextVal INTO " + p.ParameterName + " FROM DUAL; "; into += comma; into += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma; values += p.ParameterName; comma = ", "; } else if (col.IsTiraggoConcurrency) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); sql += p.ParameterName + " := 1; "; into += comma; into += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma; values += p.ParameterName; comma = ", "; } else if (col.HasDefault && (modifiedColumns != null && !modifiedColumns.Contains(col.Name))) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); computedColumns += computedComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; computedParameters += computedComma + p.ParameterName; computedComma = ", "; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } } tgColumnMetadataCollection cols = request.Columns; #region Special Column Logic if (cols.DateAdded != null && cols.DateAdded.IsServerSide) { p = CloneParameter(types[cols.DateAdded.ColumnName]); sql += 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) { p = CloneParameter(types[cols.DateModified.ColumnName]); sql += 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) { p = CloneParameter(types[cols.AddedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength; sql += 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) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength; sql += 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 sql += "INSERT INTO " + CreateFullName(request) + " "; if (modifiedColumns != null) { foreach (string colName in modifiedColumns) { tgColumnMetadata col = request.Columns[colName]; if (col != null && !col.IsAutoIncrement) { p = types[colName]; p = cmd.Parameters.Add(CloneParameter(p)); object value = packet.CurrentValues[colName]; p.Value = value != null ? value : DBNull.Value; into += comma; into += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose; values += comma; values += p.ParameterName; comma = ", "; } } } if (into.Length != 0) { sql += "(" + into + ") VALUES (" + values + ");"; } if (computedColumns.Length > 0) { string where = String.Empty; 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); } } } sql += " SELECT " + computedColumns + " INTO " + computedParameters + " FROM " + CreateFullName(request) + " WHERE (" + where + ");"; } sql += " END;"; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return cmd; }
public static OracleCommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleCommand cmd = new OracleCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; string sql = String.Empty; bool hasConcurrency = false; sql += "BEGIN "; sql += "DELETE FROM " + CreateFullName(request) + " "; string where = String.Empty; string comma = String.Empty; comma = String.Empty; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey || col.IsTiraggoConcurrency) { OracleParameter p = CloneParameter(types[col.Name]); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); where += comma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; comma = " AND "; if (col.IsTiraggoConcurrency) hasConcurrency = true; } } sql += " WHERE (" + where + "); "; if (hasConcurrency) { sql += "IF SQL%ROWCOUNT = 0 THEN "; sql += "Raise_application_error(-20101, 'NO RECORDS WERE DELETED'); END IF;"; } sql += " END;"; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return cmd; }
private static DataTable SaveDynamicCollection(tgDataRequest request) { if (request.CollectionSavePacket == null) return null; using (tgTransactionScope scope = new tgTransactionScope()) { NpgsqlCommand cmd = null; bool exception = false; foreach (tgEntitySavePacket packet in request.CollectionSavePacket) { exception = false; cmd = null; switch (packet.RowState) { case tgDataRowState.Added: cmd = Shared.BuildDynamicInsertCommand(request, packet); break; case tgDataRowState.Modified: cmd = Shared.BuildDynamicUpdateCommand(request, packet); break; case tgDataRowState.Deleted: cmd = Shared.BuildDynamicDeleteCommand(request, packet); break; case tgDataRowState.Unchanged: continue; } try { tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); int count; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveCollectionDynamic", System.Environment.StackTrace)) { try { count = cmd.ExecuteNonQuery(); ; } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { count = cmd.ExecuteNonQuery(); } if (count < 1) { throw new tgConcurrencyException("Update failed to update any records"); } } catch (Exception ex) { exception = true; request.FireOnError(packet, ex.Message); if (!request.ContinueUpdateOnError) { throw; } } finally { tgTransactionScope.DeEnlist(cmd); cmd.Dispose(); } if (!exception && packet.RowState != tgDataRowState.Deleted && cmd.Parameters != null) { foreach (NpgsqlParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: packet.CurrentValues[param.SourceColumn] = param.Value; break; } } } } scope.Complete(); } return null; }
private static tgDataResponse LoadManyToMany(tgDataRequest request) { tgDataResponse response = new tgDataResponse(); NpgsqlCommand cmd = null; try { DataTable dataTable = new DataTable(request.ProviderMetadata.Destination); cmd = new NpgsqlCommand(); cmd.CommandType = CommandType.Text; if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; string mmQuery = request.QueryText; string[] sections = mmQuery.Split('|'); string[] tables = sections[0].Split(','); string[] columns = sections[1].Split(','); string prefix = String.Empty; if (request.Catalog != null || request.ProviderMetadata.Catalog != null) { prefix += Delimiters.TableOpen; prefix += request.Catalog != null ? request.Catalog : request.ProviderMetadata.Catalog; prefix += Delimiters.TableClose + "."; } if (request.Schema != null || request.ProviderMetadata.Schema != null) { prefix += Delimiters.TableOpen; prefix += request.Schema != null ? request.Schema : request.ProviderMetadata.Schema; prefix += Delimiters.TableClose + "."; } string table0 = prefix + Delimiters.TableOpen + tables[0] + Delimiters.TableClose; string table1 = prefix + Delimiters.TableOpen + tables[1] + Delimiters.TableClose; string sql = "SELECT * FROM " + table0 + " JOIN " + table1 + " ON " + table0 + ".\"" + columns[0] + "\" = "; sql += table1 + ".\"" + columns[1] + "\" WHERE " + table1 + ".\"" + sections[2] + "\" = @"; if (request.Parameters != null) { foreach (tgParameter esParam in request.Parameters) { sql += esParam.Name; } Shared.AddParameters(cmd, request); } NpgsqlDataAdapter da = new NpgsqlDataAdapter(); cmd.CommandText = sql; da.SelectCommand = cmd; try { tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace)) { try { da.Fill(dataTable); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Fill(dataTable); } } finally { tgTransactionScope.DeEnlist(da.SelectCommand); } response.Table = dataTable; } catch (Exception ex) { CleanupCommand(cmd); throw ex; } finally { } return response; }
public static NpgsqlCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket 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 autoInc = String.Empty; NpgsqlParameter p = null; Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlCommand cmd = new NpgsqlCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata 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])); object value = packet.CurrentValues[col.Name]; p.Value = value != null ? value : DBNull.Value; into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + p.ParameterName; comma = ", "; } else if (col.IsAutoIncrement && request.ProviderMetadata.ContainsKey("AutoKeyText")) { string sequence = request.ProviderMetadata["AutoKeyText"].Replace("nextval", "currval"); if (sequence != null && sequence.Length > 0) { // Our identity column ... p = cmd.Parameters.Add(CloneParameter(types[col.Name])); p.Direction = ParameterDirection.Output; autoInc += " SELECT * FROM " + sequence + " as \"" + col.Name + "\""; } p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } else if (col.IsConcurrency) { // These columns have defaults and they weren't supplied with values, so let's // return them p = cmd.Parameters.Add(CloneParameter(types[col.Name])); p.Direction = ParameterDirection.InputOutput; defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; defaultComma = ", "; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } else if (col.IsEntitySpacesConcurrency) { p = cmd.Parameters.Add(CloneParameter(types[col.Name])); p.Direction = ParameterDirection.Output; into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "1"; comma = ", "; p.Value = 1; // Seems to work, We'll take it ... } 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) { // These columns have defaults and they weren't supplied with values, so let's // return them p = cmd.Parameters.Add(CloneParameter(types[col.Name])); p.Direction = ParameterDirection.InputOutput; defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; defaultComma = ","; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } if (col.IsInPrimaryKey) { 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); } } } #region Special Column Logic if (cols.DateAdded != null && cols.DateAdded.IsServerSide) { p = CloneParameter(types[cols.DateAdded.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateAdded.ServerSideText"]; comma = ", "; defaults += defaultComma + cols.DateAdded.ColumnName; defaultComma = ","; } if (cols.DateModified != null && cols.DateModified.IsServerSide) { p = CloneParameter(types[cols.DateModified.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateModified.ServerSideText"]; comma = ", "; defaults += defaultComma + cols.DateModified.ColumnName; defaultComma = ","; } if (cols.AddedBy != null && cols.AddedBy.IsServerSide) { p = CloneParameter(types[cols.AddedBy.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["AddedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + cols.AddedBy.ColumnName; defaultComma = ","; tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName]; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { p = CloneParameter(types[cols.ModifiedBy.ColumnName]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + cols.ModifiedBy.ColumnName; defaultComma = ","; tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName]; if (col.CharacterMaxLength > 0) { p.Size = (int)col.CharacterMaxLength; } } #endregion string fullName = CreateFullName(request); sql += " INSERT INTO " + fullName; if (into.Length != 0) { sql += " (" + into + ") VALUES (" + values + ");"; } else { sql += " DEFAULT VALUES;"; } sql += autoInc; if (defaults.Length > 0) { sql += " SELECT " + defaults + " FROM " + fullName + " WHERE (" + where + ")"; } cmd.CommandText = sql + String.Empty; cmd.CommandType = CommandType.Text; return cmd; }
public static OracleCommand BuildStoredProcDeleteCommand(tgDataRequest request, tgEntitySavePacket packet) { OracleCommand cmd = new OracleCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spDelete); Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleParameter p; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey || col.IsTiraggoConcurrency) { p = types[col.Name]; p = CloneParameter(p); p.ParameterName = p.ParameterName.Replace(":", "p"); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); } } return cmd; }
public static NpgsqlCommand BuildStoredProcDeleteCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlCommand cmd = new NpgsqlCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose; NpgsqlParameter 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.IsEntitySpacesConcurrency) { p = types[col.Name]; p = CloneParameter(p); p.Value = packet.OriginalValues[col.Name]; cmd.Parameters.Add(p); } } return cmd; }
public static OracleCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleCommand cmd = new OracleCommand(); 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; OracleParameter p = null; foreach (tgColumnMetadata col in request.Columns) { if (col.IsComputed || col.IsTiraggoConcurrency) { p = cmd.Parameters["p" + (col.Name).Replace(" ", String.Empty)]; p.Direction = ParameterDirection.InputOutput; } } if (cols.DateModified != null && cols.DateModified.IsServerSide) { 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) { 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; }
public static NpgsqlCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request); NpgsqlCommand cmd = new NpgsqlCommand(); 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, packet); foreach (tgColumnMetadata col in request.Columns) { if (col.IsComputed) { NpgsqlParameter p = types[col.Name]; p = cmd.Parameters[p.ParameterName]; p.Direction = ParameterDirection.InputOutput; } } return cmd; }
public static void GatherReturnParameters(OracleCommand cmd, tgDataRequest request, tgDataResponse response) { if (cmd.Parameters.Count > 0) { if (request.Parameters != null && request.Parameters.Count > 0) { string paramPrefix = request.QueryType == tgQueryType.StoredProcedure ? String.Empty : Delimiters.Param; paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == request.QueryText ? "p" : paramPrefix; response.Parameters = new tgParameters(); foreach (tgParameter esParam in request.Parameters) { if (esParam.Direction != tgParameterDirection.Input) { response.Parameters.Add(esParam); OracleParameter p = cmd.Parameters[paramPrefix + esParam.Name]; esParam.Value = p.Value; } } } } }
public static string CreateFullName(tgDataRequest request) { string name = String.Empty; string catalog = request.Catalog ?? request.ProviderMetadata.Catalog; string schema = request.Schema ?? request.ProviderMetadata.Schema; if (catalog != null && schema != null) { name += Delimiters.TableOpen + catalog + Delimiters.TableClose + "."; } if (schema != null) { name += Delimiters.TableOpen + schema + Delimiters.TableClose + "."; } name += Delimiters.TableOpen; if (request.DynamicQuery != null && request.DynamicQuery.tg.QuerySource != null) name += request.DynamicQuery.tg.QuerySource; else name += request.QueryText != null ? request.QueryText : request.ProviderMetadata.Destination; name += Delimiters.TableClose; return name; }
public static void PopulateStoredProcParameters(OracleCommand cmd, tgDataRequest request, tgEntitySavePacket packet) { Dictionary<string, OracleParameter> types = Cache.GetParameters(request); OracleParameter p; foreach (tgColumnMetadata col in request.Columns) { p = types[col.Name]; p = CloneParameter(p); p.ParameterName = p.ParameterName.Replace(":", "p"); if (packet.CurrentValues.ContainsKey(col.Name)) { p.Value = packet.CurrentValues[col.Name]; } else { p.Value = DBNull.Value; } if (p.OracleType == OracleType.Timestamp) { p.Direction = ParameterDirection.InputOutput; } cmd.Parameters.Add(p); } }
public static void GatherReturnParameters(NpgsqlCommand cmd, tgDataRequest request, tgDataResponse response) { if (cmd.Parameters.Count > 0) { if (request.Parameters != null && request.Parameters.Count > 0) { response.Parameters = new tgParameters(); foreach (tgParameter esParam in request.Parameters) { if (esParam.Direction != tgParameterDirection.Input) { response.Parameters.Add(esParam); NpgsqlParameter p = cmd.Parameters[Delimiters.Param + esParam.Name]; esParam.Value = p.Value; } } } } }
public static SqlCeCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket 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(); SqlCeParameter p = null; Dictionary<string, SqlCeParameter> types = Cache.GetParameters(request); SqlCeCommand cmd = new SqlCeCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency)) { 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.IsTiraggoConcurrency) { 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) { into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "DEFAULT"; comma = ","; 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) { foreach (tgColumnMetadata col in request.Columns) { if (!col.IsAutoIncrement && !col.IsComputed && !col.IsConcurrency) { into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "DEFAULT"; comma = ","; } } } if (into.Length != 0) { sql += "(" + into + ") VALUES (" + values + ")"; } request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return cmd; }
public static Dictionary<string, VistaDBParameter> GetParameters(tgDataRequest request) { return GetParameters(request.DataID, request.ProviderMetadata, request.Columns); }
public static SqlCeCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket 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; string sql = "UPDATE " + CreateFullName(request) + " SET "; PropertyCollection props = new PropertyCollection(); SqlCeParameter p = null; Dictionary<string, SqlCeParameter> types = Cache.GetParameters(request); SqlCeCommand cmd = new SqlCeCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency)) { 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; cmd.Parameters.Add(p); where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; } else if (col.IsTiraggoConcurrency) { props["EntitySpacesConcurrency"] = col.Name; p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; 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; }