/// <summary>
/// Called by the DataProviders to invoke the OnError event handler
/// </summary>
/// <param name="packet">The esEntityPacket in error</param>
/// <param name="ex">The exception that was thrown</param>
public void FireOnError(esEntitySavePacket packet, string error)
{
OnErrorHandler handler = OnError;
if (handler != null)
{
handler(packet, error);
}
}
/// <summary>
/// Called by the DataProviders to invoke the OnError event handler
/// </summary>
/// <param name="packet">The esEntityPacket in error</param>
/// <param name="ex">The exception that was thrown</param>
public void FireOnError(esEntitySavePacket packet, string error)
{
OnErrorHandler handler = OnError;
if (handler != null)
{
handler(packet, error);
}
}
static public SqlCeCommand 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();
SqlCeParameter p = null;
Dictionary<string, SqlCeParameter> types = Cache.GetParameters(request);
SqlCeCommand cmd = new SqlCeCommand();
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)
{
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 (esColumnMetadata 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;
}
static public SqlCeCommand 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;
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;
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;
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;
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;
}
static public SACommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SAParameter> types = Cache.GetParameters(request);
SACommand cmd = new SACommand();
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)
{
SAParameter 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(SqlCommand cmd, esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlParameter p;
foreach (esColumnMetadata col in request.Columns)
{
p = types[col.Name];
p = CloneParameter(p);
if (packet.CurrentValues.ContainsKey(col.Name))
{
p.Value = packet.CurrentValues[col.Name];
}
if (p.SqlDbType == SqlDbType.Timestamp)
{
p.Direction = ParameterDirection.InputOutput;
}
if (col.IsComputed && col.CharacterMaxLength > 0)
{
p.Size = (int)col.CharacterMaxLength;
}
cmd.Parameters.Add(p);
}
}
static public OracleCommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket 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 (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
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.IsEntitySpacesConcurrency) 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;
}
public esTraceArguments(esDataRequest request, IDbCommand cmd, esEntitySavePacket packet, string action, string callStack)
{
PacketOrder = Interlocked.Increment(ref esTraceArguments.packetOrder);
this.command = cmd;
TraceChannel = DataProvider.sTraceChannel;
Syntax = "SYBASE";
Request = request;
ThreadId = Thread.CurrentThread.ManagedThreadId;
Action = action;
CallStack = callStack;
SqlCommand = cmd;
ApplicationName = System.IO.Path.GetFileName(System.Reflection.Assembly.GetExecutingAssembly().Location);
IDataParameterCollection parameters = cmd.Parameters;
if (parameters.Count > 0)
{
Parameters = new List<ITraceParameter>(parameters.Count);
for (int i = 0; i < parameters.Count; i++)
{
SAParameter param = parameters[i] as SAParameter;
esTraceParameter p = new esTraceParameter()
{
Name = param.ParameterName,
Direction = param.Direction.ToString(),
ParamType = param.SADbType.ToString().ToUpper(),
BeforeValue = param.Value != null && param.Value != DBNull.Value ? Convert.ToString(param.Value) : "null"
};
try
{
// Let's make it look like we're using parameters for the profiler
if (param.Value == null || param.Value == DBNull.Value)
{
if (param.SourceVersion == DataRowVersion.Current)
{
object o = packet.CurrentValues[param.SourceColumn];
if (o != null && o != DBNull.Value)
{
p.BeforeValue = Convert.ToString(o);
}
}
else if (param.SourceVersion == DataRowVersion.Original)
{
object o = packet.OriginalValues[param.SourceColumn];
if (o != null && o != DBNull.Value)
{
p.BeforeValue = Convert.ToString(o);
}
}
}
}
catch { }
this.Parameters.Add(p);
}
}
stopwatch = Stopwatch.StartNew();
}
static public SqlCommand BuildStoredProcInsertCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spInsert);
PopulateStoredProcParameters(cmd, request, packet);
esColumnMetadataCollection cols = request.Columns;
foreach (esColumnMetadata col in cols)
{
if (col.HasDefault &&
(col.Default.ToLower().Contains("newid") || col.Default.ToLower().Contains("newsequentialid")))
{
// They could pre-assign this even though it has a default
SqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
if (packet.ModifiedColumns.Contains(col.Name))
{
p.Direction = ParameterDirection.InputOutput;
}
else
{
p.Direction = ParameterDirection.Output;
}
}
else if (col.IsComputed || col.IsAutoIncrement || col.IsEntitySpacesConcurrency)
{
SqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
}
if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.DateAdded.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.AddedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
return cmd;
}
static public SqlCommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string sql = "SET NOCOUNT OFF; ";
sql += "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
string concur = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
SqlParameter p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
else if (col.IsConcurrency || col.IsEntitySpacesConcurrency)
{
SqlParameter p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008" || col.IsEntitySpacesConcurrency)
concur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
else
concur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")";
}
}
if (concur.Length > 0)
{
sql += " AND " + concur;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
static public SqlCommand BuildDynamicInsertCommand(esDataRequest request, esEntitySavePacket packet)
{
string into = String.Empty;
string values = String.Empty;
string computed = String.Empty;
string computedComma = String.Empty;
string autoInc = String.Empty;
string comma = String.Empty;
string where = String.Empty;
// newsequentialid variables
int seqCount = 0;
string seqDeclare = " DECLARE @table_ids TABLE (";
string seqOutput = " OUTPUT ";
string seqSelect = " SELECT ";
List<string> modifiedColumns = packet.ModifiedColumns;
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
SqlParameter p = null;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string sql = "SET NOCOUNT OFF";
foreach (esColumnMetadata col in request.Columns)
{
string colName = col.Name;
if (request.SelectedColumns != null && !request.SelectedColumns.ContainsKey(colName)) continue;
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && !col.IsComputed && !col.IsConcurrency && !col.IsAutoIncrement)
{
p = types[colName];
p = cmd.Parameters.Add(CloneParameter(p));
object value = packet.CurrentValues[colName];
p.Value = value != null ? value : DBNull.Value;
CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma);
}
else
{
bool needOutputParam = false;
bool needsFetchedAfterSave = false;
SqlParameter clone = null;
if (col.HasDefault)
{
p = types[colName];
if (col.esType == esSystemType.Guid)
{
if (col.Default.ToLower().Contains("newid"))
{
// Special logic for newid()'s that weren't supplied with a value, they
// go into the SELECT INTO as well
sql += " SET " + p.ParameterName + " = NEWID(); ";
CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma);
needOutputParam = true;
}
else if (col.Default.ToLower().Contains("newsequentialid"))
{
if (seqCount > 0)
{
seqDeclare += ", ";
seqOutput += ", ";
seqSelect += ", ";
}
seqCount++;
seqDeclare += col.Name + " uniqueidentifier";
seqOutput += "INSERTED." + col.Name;
seqSelect += Delimiters.Param + col.PropertyName + "=" + col.Name;
needOutputParam = true;
}
}
else
{
// 11/15/2009 Let's return all default values
needOutputParam = true;
needsFetchedAfterSave = true;
}
}
else if (col.IsEntitySpacesConcurrency)
{
p = types[colName];
sql += " SET " + p.ParameterName + " = 1; ";
into += comma;
into += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose;
values += comma;
values += "1";
comma = ", ";
needOutputParam = true;
}
else if (col.IsAutoIncrement)
{
p = types[colName];
autoInc += " SELECT " + p.ParameterName + " = SCOPE_IDENTITY() ";
needOutputParam = true;
}
else if (col.IsComputed || col.IsConcurrency)
{
p = types[colName];
needOutputParam = true;
needsFetchedAfterSave = true;
}
if (needOutputParam)
{
clone = CloneParameter(p);
clone.Direction = ParameterDirection.Output;
cmd.Parameters.Add(clone);
}
if (needsFetchedAfterSave)
{
computed += computedComma;
computed += p.ParameterName + " = " + Delimiters.ColumnOpen + colName + Delimiters.ColumnClose;
computedComma = ", ";
if (col.CharacterMaxLength > 0)
{
clone.Size = (int)col.CharacterMaxLength;
}
}
}
}
esColumnMetadataCollection cols = request.Columns;
#region Special Column Logic
if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
{
p = CloneParameter(types[cols.DateAdded.ColumnName]);
sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateAdded.ServerSideText"] + ";";
CreateInsertSQLSnippet(cols.DateAdded.ColumnName, p, ref into, ref values, ref comma);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
p = CloneParameter(types[cols.DateModified.ColumnName]);
sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";";
CreateInsertSQLSnippet(cols.DateModified.ColumnName, p, ref into, ref values, ref comma);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
{
p = CloneParameter(types[cols.AddedBy.ColumnName]);
p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["AddedBy.ServerSideText"] + ";";
CreateInsertSQLSnippet(cols.AddedBy.ColumnName, p, ref into, ref values, ref comma);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";";
CreateInsertSQLSnippet(cols.ModifiedBy.ColumnName, p, ref into, ref values, ref comma);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
#endregion
seqDeclare += ")";
seqOutput += " INTO @table_ids";
seqSelect += " FROM @table_ids";
if (computed.Length > 0)
{
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
// We need the were clause if there are defaults to bring back
p = types[col.Name];
if (where.Length > 0) where += " AND ";
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
if (!cmd.Parameters.Contains(p.ParameterName))
{
p = CloneParameter(p);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
}
}
}
string fullName = CreateFullName(request);
if (seqCount > 0)
{
sql += seqDeclare;
}
sql += " INSERT INTO " + fullName;
if (into.Length != 0 && seqCount > 0)
{
sql += "(" + into + ") " + seqOutput + " VALUES (" + values + ")";
}
else if (into.Length != 0)
{
sql += "(" + into + ") VALUES (" + values + ")";
}
else
{
sql += "DEFAULT VALUES";
}
sql += autoInc;
if (seqCount > 0)
{
sql += seqSelect;
}
if (computed.Length > 0)
{
sql += " SELECT " + computed + " FROM " + fullName + " WHERE (" + where + ")";
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
static public SqlCommand BuildDynamicUpdateCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string set = string.Empty;
string sql = "SET NOCOUNT OFF ";
sql += "UPDATE " + CreateFullName(request) + " SET ";
string where = String.Empty;
string conncur = String.Empty;
string computed = String.Empty;
string comma = String.Empty;
string and = String.Empty;
string prolog = String.Empty;
SqlParameter p = null;
List<string> modifiedColumns = packet.ModifiedColumns;
foreach (string colName in modifiedColumns)
{
esColumnMetadata col = request.Columns[colName];
if (col == null) continue;
if (!col.IsInPrimaryKey && !col.IsComputed)
{
p = CloneParameter(types[colName]);
p = cmd.Parameters.Add(p);
object value = packet.CurrentValues[colName];
p.Value = value != null ? value : DBNull.Value;
sql += comma;
sql += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = ", ";
}
}
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
where += and;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
and = " AND ";
}
else if (col.IsConcurrency)
{
p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
p.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(p);
if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008")
conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
else
conncur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")";
if (computed.Length > 0) computed += ", ";
computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
}
else if (col.IsComputed && !col.IsAutoIncrement)
{
if (request.SelectedColumns != null && request.SelectedColumns.ContainsKey(col.Name))
{
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
if (col.CharacterMaxLength > 0)
{
p.Size = (int)col.CharacterMaxLength;
}
cmd.Parameters.Add(p);
if (computed.Length > 0) computed += ", ";
computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
}
}
else if (col.IsEntitySpacesConcurrency)
{
if (packet.OriginalValues != null && packet.OriginalValues.ContainsKey(col.Name))
{
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.InputOutput;
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
sql += comma.Length > 0 ? ", " : string.Empty;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " +
Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1";
conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
prolog += " SET " + p.ParameterName + " = " + p.ParameterName + " + 1";
}
}
}
esColumnMetadataCollection 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 += comma;
sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = ", ";
set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";";
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
sql += comma;
sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = ", ";
set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";";
}
sql = set + sql + " WHERE (" + where + ")";
if (conncur.Length > 0)
{
sql += " AND " + conncur;
}
if (computed.Length > 0)
{
sql += " SELECT " + computed + " FROM " + CreateFullName(request) + " WHERE (" + where + ")";
}
if (prolog.Length > 0)
{
sql += prolog;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
static public void PopulateStoredProcParameters(OracleCommand cmd, esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, OracleParameter> types = Cache.GetParameters(request);
OracleParameter p;
foreach (esColumnMetadata 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);
}
}
static public OracleCommand BuildStoredProcInsertCommand(esDataRequest request, esEntitySavePacket 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.spInsert);
PopulateStoredProcParameters(cmd, request, packet);
esColumnMetadataCollection cols = request.Columns;
OracleParameter p = null;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsAutoIncrement || col.IsEntitySpacesConcurrency)
{
p = cmd.Parameters["p" + (col.Name).Replace(" ", String.Empty)];
p.Direction = ParameterDirection.Output;
}
}
if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
{
p = cmd.Parameters[types[cols.DateAdded.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
{
p = cmd.Parameters[types[cols.AddedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
return cmd;
}
static public OracleCommand BuildDynamicInsertCommand(esDataRequest request, esEntitySavePacket 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 (esColumnMetadata 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.IsEntitySpacesConcurrency)
{
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;
}
}
}
esColumnMetadataCollection 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)
{
esColumnMetadata 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 (esColumnMetadata 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;
}
private static void SetModifiedValues(esDataRequest request, esEntitySavePacket packet, DataRow row)
{
foreach (string column in packet.ModifiedColumns)
{
if (request.Columns.FindByColumnName(column) != null)
{
row[column] = packet.CurrentValues[column];
}
}
}
private static void SetOriginalValues(esDataRequest request, esEntitySavePacket packet, DataRow row, bool primaryKeysAndConcurrencyOnly)
{
foreach (esColumnMetadata col in request.Columns)
{
if (primaryKeysAndConcurrencyOnly &&
(!col.IsInPrimaryKey && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) continue;
string columnName = col.Name;
if (packet.OriginalValues.ContainsKey(columnName))
{
row[columnName] = packet.OriginalValues[columnName];
}
}
}
static public SqlCommand BuildStoredProcUpdateCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate);
PopulateStoredProcParameters(cmd, request, packet);
esColumnMetadataCollection cols = request.Columns;
foreach (esColumnMetadata col in cols)
{
if (col.IsComputed || col.IsEntitySpacesConcurrency)
{
SqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Value = null;
p.Direction = ParameterDirection.Output;
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
SqlParameter p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Value = null;
p.Direction = ParameterDirection.Output;
}
return cmd;
}
static public SqlCommand BuildStoredProcDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary<string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spDelete);
SqlParameter p;
foreach (esColumnMetadata 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;
}
//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 ;
static public OracleCommand BuildDynamicUpdateCommand(esDataRequest request, esEntitySavePacket 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 (esColumnMetadata col in request.Columns)
{
if (col.IsEntitySpacesConcurrency)
{
hasConcurrency = true;
concurrencyColumn = col.Name;
sql += "DECLARE pConncurrency " + request.ProviderMetadata.GetTypeMap(col.PropertyName).NativeType + "; ";
break;
}
}
OracleParameter p = null;
sql += " BEGIN ";
esColumnMetadataCollection 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)
{
esColumnMetadata col = request.Columns[colName];
if (col != null && !col.IsInPrimaryKey && !col.IsEntitySpacesConcurrency)
{
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 (esColumnMetadata 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.IsEntitySpacesConcurrency)
{
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;
}