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 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 OracleCommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, OracleParameter> types = Cache.GetParameters(request);
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
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);
}
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];
}
}
}
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 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 private DataTable SaveDynamicCollection(esDataRequest request)
{
esEntitySavePacket pkt = request.CollectionSavePacket[0];
if (pkt.RowState == esDataRowState.Deleted)
{
//============================================================================
// We do all our deletes at once, so if the first one is a delete they all are
//============================================================================
return(SaveDynamicCollection_Deletes(request));
}
else
{
//============================================================================
// We do all our Inserts and Updates at once
//============================================================================
return(SaveDynamicCollection_InsertsUpdates(request));
}
}
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);
}
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 BuildDynamicInsertCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, OracleParameter> types = Cache.GetParameters(request);
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
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 + ")";
sql += "; ";
}
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);
}
//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();
cmd.BindByName = true;
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);
}
static public NpgsqlCommand BuildStoredProcInsertCommand(esDataRequest request, esEntitySavePacket 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 (esColumnMetadata 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);
}
static public SQLiteCommand 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();
Dictionary <string, SQLiteParameter> types = Cache.GetParameters(request);
SQLiteCommand cmd = new SQLiteCommand();
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))
{
SQLiteParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + p.ParameterName;
comma = ", ";
}
else if (col.IsAutoIncrement)
{
props["AutoInc"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
}
else if (col.IsConcurrency)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
}
else if (col.IsEntitySpacesConcurrency)
{
props["EntitySpacesConcurrency"] = col.Name;
SQLiteParameter p = types[col.Name];
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + "1";
comma = ", ";
SQLiteParameter clone = CloneParameter(p);
clone.Value = 1;
cmd.Parameters.Add(clone);
}
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 SQLiteCommand 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();
Dictionary <string, SQLiteParameter> types = Cache.GetParameters(request);
SQLiteCommand cmd = new SQLiteCommand();
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))
{
SQLiteParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += scomma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
scomma = ", ";
}
else if (col.IsAutoIncrement)
{
// Nothing to do but leave this here
}
else if (col.IsConcurrency)
{
SQLiteParameter p = types[col.Name];
p = CloneParameter(p);
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;
SQLiteParameter p = types[col.Name];
p = CloneParameter(p);
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)
{
SQLiteParameter p = types[col.Name];
p = CloneParameter(p);
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);
}
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
string source = props["Source"] as string;
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]";
object o = null;
o = cmd.ExecuteScalar();
if (o != null)
{
e.Row[autoInc] = o;
e.Command.Parameters["@" + autoInc].Value = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
if (props.Contains("Timestamp"))
{
string column = props["Timestamp"] as string;
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT LastTimestamp('" + source + "');";
object o = null;
o = cmd.ExecuteScalar();
if (o != null)
{
e.Command.Parameters["@" + column].Value = o;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
VistaDBCommand cmd = new VistaDBCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
VistaDBDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null)
{
rdr.Close();
}
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
VistaDBParameter p = e.Command.Parameters["@" + colName];
object v = null;
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: v = ((System.Int16)o) + 1; break;
case TypeCode.Int32: v = ((System.Int32)o) + 1; break;
case TypeCode.Int64: v = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break;
}
p.Value = v;
}
}
}
catch { }
}
static public NpgsqlCommand BuildStoredProcUpdateCommand(esDataRequest request, esEntitySavePacket 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 (esColumnMetadata col in request.Columns)
{
if (col.IsComputed)
{
NpgsqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
return(cmd);
}
static public NpgsqlCommand 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 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;
}
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]));
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 = ",";
esColumnMetadata 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 = ",";
esColumnMetadata 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);
}
static public OracleCommand BuildStoredProcInsertCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, OracleParameter> types = Cache.GetParameters(request);
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
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 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 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 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 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.OracleDbType == OracleDbType.TimeStamp)
{
p.Direction = ParameterDirection.InputOutput;
}
cmd.Parameters.Add(p);
}
}
static public NpgsqlCommand BuildDynamicUpdateCommand(esDataRequest request, esEntitySavePacket 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;
}
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]));
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 = ",";
esColumnMetadata 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);
}
