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