protected AspNetUserClaimsMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(AspNetUserClaimsMetadata.ColumnNames.Id, 0, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = AspNetUserClaimsMetadata.PropertyNames.Id;
c.IsInPrimaryKey = true;
c.IsAutoIncrement = true;
c.NumericPrecision = 10;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUserClaimsMetadata.ColumnNames.UserId, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserClaimsMetadata.PropertyNames.UserId;
c.CharacterMaxLength = 128;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUserClaimsMetadata.ColumnNames.ClaimType, 2, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserClaimsMetadata.PropertyNames.ClaimType;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUserClaimsMetadata.ColumnNames.ClaimValue, 3, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserClaimsMetadata.PropertyNames.ClaimValue;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
}
static public OracleCommand BuildStoredProcInsertCommand(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.spInsert);
PopulateStoredProcParameters(cmd, request, packet);
tgColumnMetadataCollection cols = request.Columns;
OracleParameter p = null;
foreach (tgColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsAutoIncrement || col.IsTiraggoConcurrency)
{
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);
}
protected TVShowsMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.Id, 0, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = TVShowsMetadata.PropertyNames.Id;
c.IsInPrimaryKey = true;
m_columns.Add(c);
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.Title, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = TVShowsMetadata.PropertyNames.Title;
m_columns.Add(c);
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.MazeId, 2, typeof(System.String), tgSystemType.String);
c.PropertyName = TVShowsMetadata.PropertyNames.MazeId;
m_columns.Add(c);
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.Synopsis, 3, typeof(System.String), tgSystemType.String);
c.PropertyName = TVShowsMetadata.PropertyNames.Synopsis;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.Image, 4, typeof(System.String), tgSystemType.String);
c.PropertyName = TVShowsMetadata.PropertyNames.Image;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(TVShowsMetadata.ColumnNames.NextAirDate, 5, typeof(System.String), tgSystemType.String);
c.PropertyName = TVShowsMetadata.PropertyNames.NextAirDate;
c.IsNullable = true;
m_columns.Add(c);
}
static public SqlCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket 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);
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata col in cols)
{
if (col.IsComputed || col.IsTiraggoConcurrency)
{
SqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.InputOutput;
}
}
if (cols.DateModified != null && cols.DateModified.IsServerSide &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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);
}
protected AspNetRolesMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(AspNetRolesMetadata.ColumnNames.Id, 0, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetRolesMetadata.PropertyNames.Id;
c.IsInPrimaryKey = true;
c.CharacterMaxLength = 128;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetRolesMetadata.ColumnNames.Name, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetRolesMetadata.PropertyNames.Name;
c.CharacterMaxLength = 256;
m_columns.Add(c);
}
static public Dictionary<string, VistaDBParameter> GetParameters(Guid dataID,
tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns)
{
lock (parameterCache)
{
if (!parameterCache.ContainsKey(dataID))
{
// The Parameters for this Table haven't been cached yet, this is a one time operation
Dictionary<string, VistaDBParameter> types = new Dictionary<string, VistaDBParameter>();
VistaDBParameter param1;
foreach (tgColumnMetadata col in columns)
{
tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName);
if (typeMap != null)
{
string nativeType = typeMap.NativeType;
VistaDBType dbType = Cache.NativeTypeToDbType(nativeType);
param1 = new VistaDBParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name);
param1.SourceColumn = col.Name;
switch (dbType)
{
case VistaDBType.BigInt:
case VistaDBType.Int:
case VistaDBType.SmallInt:
case VistaDBType.Decimal:
case VistaDBType.Float:
case VistaDBType.Money:
case VistaDBType.SmallMoney:
param1.Size = (int)col.CharacterMaxLength;
break;
}
types[col.Name] = param1;
}
}
parameterCache[dataID] = types;
}
}
return parameterCache[dataID];
}
protected RecipientsMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(RecipientsMetadata.ColumnNames.Id, 0, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = RecipientsMetadata.PropertyNames.Id;
c.IsInPrimaryKey = true;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(RecipientsMetadata.ColumnNames.EmailAddress, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = RecipientsMetadata.PropertyNames.EmailAddress;
c.IsNullable = true;
c.CharacterMaxLength = 255;
m_columns.Add(c);
}
protected AspNetUserLoginsMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(AspNetUserLoginsMetadata.ColumnNames.LoginProvider, 0, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserLoginsMetadata.PropertyNames.LoginProvider;
c.IsInPrimaryKey = true;
c.CharacterMaxLength = 128;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUserLoginsMetadata.ColumnNames.ProviderKey, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserLoginsMetadata.PropertyNames.ProviderKey;
c.IsInPrimaryKey = true;
c.CharacterMaxLength = 128;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUserLoginsMetadata.ColumnNames.UserId, 2, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUserLoginsMetadata.PropertyNames.UserId;
c.IsInPrimaryKey = true;
c.CharacterMaxLength = 128;
m_columns.Add(c);
}
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List <tgComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (tgComparison comparisonItem in items)
{
tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem;
tgDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
std.needsStringParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == tgParenthesis.Open)
{
sql += "(";
}
else
{
sql += ")";
}
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case tgConjunction.And: sql += " AND "; break;
case tgConjunction.Or: sql += " OR "; break;
case tgConjunction.AndNot: sql += " AND NOT "; break;
case tgConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary <string, MySqlParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as tgDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case tgComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case tgComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case tgComparisonOperand.Equal:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
}
else
{
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
}
else
{
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
}
else
{
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
}
else
{
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
}
else
{
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
}
else
{
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) +
", " + compareTo + ")";
needsStringParameter = true;
break;
case tgComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case tgComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case tgComparisonOperand.In:
case tgComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == tgComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
{
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
}
else
{
sql += comma + Convert.ToString(o);
}
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case tgComparisonOperand.Between:
MySqlCommand sqlCommand = std.cmd as MySqlCommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
MySqlParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = DbType.String;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
}
else
{
p = new MySqlParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return(sql);
}
static public 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.IsTiraggoConcurrency))
{
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.IsTiraggoConcurrency)
{
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 &&
cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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);
}
static public 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.IsTiraggoConcurrency))
{
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.IsTiraggoConcurrency)
{
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 &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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);
}
static public Dictionary <string, MySqlParameter> GetParameters(Guid dataID,
tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns)
{
lock (parameterCache)
{
if (!parameterCache.ContainsKey(dataID))
{
// The Parameters for this Table haven't been cached yet, this is a one time operation
Dictionary <string, MySqlParameter> types = new Dictionary <string, MySqlParameter>();
MySqlParameter param1;
foreach (tgColumnMetadata col in columns)
{
tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName);
if (typeMap != null)
{
string nativeType = typeMap.NativeType;
MySqlDbType dbType = Cache.NativeTypeToDbType(nativeType);
param1 = new MySqlParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name);
param1.SourceColumn = col.Name;
switch (dbType)
{
case MySqlDbType.Decimal:
case MySqlDbType.NewDecimal:
case MySqlDbType.Double:
case MySqlDbType.Float:
case MySqlDbType.Int16:
case MySqlDbType.Int24:
case MySqlDbType.Int32:
case MySqlDbType.Int64:
case MySqlDbType.UInt16:
case MySqlDbType.UInt24:
case MySqlDbType.UInt32:
case MySqlDbType.UInt64:
param1.Size = (int)col.CharacterMaxLength;
param1.Precision = (byte)col.NumericPrecision;
param1.Scale = (byte)col.NumericScale;
break;
case MySqlDbType.String:
case MySqlDbType.VarString:
case MySqlDbType.VarChar:
param1.Size = (int)col.CharacterMaxLength;
break;
}
types[col.Name] = param1;
}
}
parameterCache[dataID] = types;
}
}
return(parameterCache[dataID]);
}
protected EmployeesMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.EmployeeID, 0, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = EmployeesMetadata.PropertyNames.EmployeeID;
c.IsInPrimaryKey = true;
c.IsAutoIncrement = true;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.LastName, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.LastName;
c.CharacterMaxLength = 20;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.FirstName, 2, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.FirstName;
c.CharacterMaxLength = 10;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Title, 3, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Title;
c.IsNullable = true;
c.CharacterMaxLength = 30;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.TitleOfCourtesy, 4, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.TitleOfCourtesy;
c.IsNullable = true;
c.CharacterMaxLength = 25;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.BirthDate, 5, typeof(System.DateTime), tgSystemType.DateTime);
c.PropertyName = EmployeesMetadata.PropertyNames.BirthDate;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.HireDate, 6, typeof(System.DateTime), tgSystemType.DateTime);
c.PropertyName = EmployeesMetadata.PropertyNames.HireDate;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Address, 7, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Address;
c.IsNullable = true;
c.CharacterMaxLength = 60;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.City, 8, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.City;
c.IsNullable = true;
c.CharacterMaxLength = 15;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Region, 9, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Region;
c.IsNullable = true;
c.CharacterMaxLength = 15;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.PostalCode, 10, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.PostalCode;
c.IsNullable = true;
c.CharacterMaxLength = 10;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Country, 11, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Country;
c.IsNullable = true;
c.CharacterMaxLength = 15;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.HomePhone, 12, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.HomePhone;
c.IsNullable = true;
c.CharacterMaxLength = 24;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Extension, 13, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Extension;
c.IsNullable = true;
c.CharacterMaxLength = 4;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Photo, 14, typeof(System.Byte[]), tgSystemType.Byte);
c.PropertyName = EmployeesMetadata.PropertyNames.Photo;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.Notes, 15, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.Notes;
c.IsNullable = true;
c.CharacterMaxLength = 1073741823;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.ReportsTo, 16, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = EmployeesMetadata.PropertyNames.ReportsTo;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(EmployeesMetadata.ColumnNames.PhotoPath, 17, typeof(System.String), tgSystemType.String);
c.PropertyName = EmployeesMetadata.PropertyNames.PhotoPath;
c.IsNullable = true;
c.CharacterMaxLength = 255;
m_columns.Add(c);
}
static public VistaDBCommand BuildDynamicUpdateCommand(tgDataRequest request, List <string> modifiedColumns)
{
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();
VistaDBParameter p = null;
Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request);
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency))
{
p = CloneParameter(types[col.Name]);
cmd.Parameters.Add(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)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
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;
p.Direction = ParameterDirection.InputOutput;
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);
}
//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(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);
}
static public SQLiteCommand 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();
Dictionary <string, SQLiteParameter> types = Cache.GetParameters(request);
SQLiteCommand cmd = new SQLiteCommand();
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))
{
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.IsTiraggoConcurrency)
{
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 &&
cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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 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);
}
static public SqlCommand BuildStoredProcInsertCommand(tgDataRequest request, tgEntitySavePacket 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);
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata 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.IsTiraggoConcurrency)
{
SqlParameter p = types[col.Name];
p = cmd.Parameters[p.ParameterName];
p.Direction = ParameterDirection.Output;
}
}
if (cols.DateAdded != null && cols.DateAdded.IsServerSide &&
cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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(tgDataRequest request, tgEntitySavePacket 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 (tgColumnMetadata 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 == tgSystemType.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.IsTiraggoConcurrency)
{
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;
}
}
}
}
tgColumnMetadataCollection cols = request.Columns;
#region Special Column Logic
if (cols.DateAdded != null && cols.DateAdded.IsServerSide &&
cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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 (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);
}
}
}
}
string fullName = CreateFullName(request);
if (seqCount > 0)
{
sql += seqDeclare;
}
sql += " INSERT INTO " + fullName;
if (into.Length != 0 && seqCount > 0)
{
sql += "(" + into + ") " + seqOutput + " VALUES (" + values + ")";
}
else if (into.Length != 0)
{
sql += "(" + into + ") VALUES (" + values + ")";
}
else
{
sql += "DEFAULT VALUES";
}
sql += autoInc;
if (seqCount > 0)
{
sql += seqSelect;
}
if (computed.Length > 0)
{
sql += " SELECT " + computed + " FROM " + fullName + " WHERE (" + where + ")";
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
static public SqlCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket 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)
{
tgColumnMetadata 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 (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.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.IsTiraggoConcurrency)
{
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";
}
}
}
tgColumnMetadataCollection cols = request.Columns;
if (cols.DateModified != null && cols.DateModified.IsServerSide &&
cols.FindByColumnName(cols.DateModified.ColumnName) != null)
{
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 &&
cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
{
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 Dictionary <string, SAParameter> GetParameters(Guid dataID,
tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns)
{
lock (parameterCache)
{
if (!parameterCache.ContainsKey(dataID))
{
// The Parameters for this Table haven't been cached yet, this is a one time operation
Dictionary <string, SAParameter> types = new Dictionary <string, SAParameter>();
SAParameter param1;
foreach (tgColumnMetadata col in columns)
{
tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName);
if (typeMap != null)
{
string nativeType = typeMap.NativeType;
SADbType dbType = Cache.NativeTypeToDbType(nativeType);
param1 = new SAParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name);
param1.SourceColumn = col.Name;
switch (dbType)
{
case SADbType.BigInt:
case SADbType.Decimal:
case SADbType.Float:
case SADbType.Integer:
case SADbType.Money:
case SADbType.Real:
case SADbType.SmallMoney:
case SADbType.TinyInt:
case SADbType.SmallInt:
param1.Size = (int)col.CharacterMaxLength;
param1.Precision = (byte)col.NumericPrecision;
param1.Scale = (byte)col.NumericScale;
break;
case SADbType.VarChar:
param1.Size = (int)col.CharacterMaxLength;
break;
//case SADbType.Integer:
// param1.Size = 4;
// break;
case SADbType.Date:
case SADbType.DateTime:
param1.Precision = 23;
param1.Scale = 3;
break;
case SADbType.SmallDateTime:
param1.Precision = 16;
break;
}
types[col.Name] = param1;
}
}
parameterCache[dataID] = types;
}
}
return(parameterCache[dataID]);
}
internal override PropertyDescriptorCollection GetProperties(tgEntity entity, tgEntityCollectionBase baseCollection)
{
bool weHaveData = false;
int lastOrdinal = 0;
tgColumnMetadataCollection esMetaCols = entity.tg.Meta.Columns;
tgEntityCollectionBase theBaseCollection = baseCollection != null ? baseCollection : entity.Collection;
bool enableHierarchcialBinding = theBaseCollection != null ? theBaseCollection.EnableHierarchicalBinding : true;
if (theBaseCollection != null)
{
if (theBaseCollection.GetList() != null)
{
// Do we have any entities?
weHaveData = theBaseCollection.GetList().Count > 0;
if (weHaveData == false)
{
// If selectedColumns has data then they attempted a load and we know the columns based on thier select statement
weHaveData = theBaseCollection.selectedColumns != null && theBaseCollection.selectedColumns.Keys.Count > 0;
}
}
}
//------------------------------------------------------------
// First we deal with Properties from the DataTable.Columns
// or from the tgColumnMetadataCollection.
//------------------------------------------------------------
ArrayList collNested = new ArrayList();
SortedList <int, PropertyDescriptor> coll = new SortedList <int, PropertyDescriptor>();
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(entity, true);
// Note, we check for selectedColumns because we might be a deserialized collection in which
// case there will not be any selectedColumns
if (weHaveData && theBaseCollection.selectedColumns != null)
{
SortedList list = GetSortedListOfProperties(entity, baseCollection);
for (int i = 0; i < list.Count; i++)
{
string column = (string)list.GetByIndex(i);
if (column == "ESRN")
{
continue;
}
tgColumnMetadata esCol = entity.tg.Meta.Columns.FindByColumnName(column);
if (esCol != null)
{
PropertyDescriptor prop = props[esCol.PropertyName];
if (prop != null)
{
coll.Add(lastOrdinal++, prop);
}
}
else
{
esCol = theBaseCollection.extraColumnMetadata[column];
if (esCol != null)
{
// Extra or Extended Properties
tgPropertyDescriptor dpd = new tgPropertyDescriptor
(
typeof(T),
column,
esCol != null ? esCol.Type : typeof(string),
delegate(object p)
{
return(((tgEntity)p).currentValues[column]);
},
delegate(object p, object data)
{
((tgEntity)p).currentValues[column] = data;
((tgEntity)p).OnPropertyChanged(column);
}
);
coll.Add(lastOrdinal++, dpd);
}
}
}
}
else
{
foreach (tgColumnMetadata esCol in esMetaCols)
{
coll.Add(lastOrdinal++, props[esCol.PropertyName]);
}
}
//------------------------------------------------------------
// Now we deal with extended properties that are using the
// esExtendedPropertyAttribute technique
//------------------------------------------------------------
foreach (PropertyDescriptor prop in props)
{
if (prop.Attributes.Contains(tgEntityCollection <T> .extendedPropertyAttribute))
{
coll.Add(lastOrdinal++, prop);
}
}
//------------------------------------------------------------
// Now we deal with any local properties. Local properties are
// properties that users may want to bind with that are
// NOT backed by data in the DataTable
//------------------------------------------------------------
List <tgPropertyDescriptor> localProps = entity.GetLocalBindingProperties();
if (localProps != null)
{
foreach (tgPropertyDescriptor esProp in localProps)
{
// We check this incase they add a local based property for a DataColumn
// based property, they would do this so it appears in design time, and
// we don't want to add a duplicate
bool exists = coll.ContainsValue(props[esProp.Name]);
if (!exists)
{
if (props[esProp.Name] != null)
{
coll.Add(lastOrdinal++, props[esProp.Name]);
}
else
{
coll.Add(lastOrdinal++, esProp);
}
}
}
}
ArrayList tempColl = new ArrayList();
if (enableHierarchcialBinding)
{
List <tgPropertyDescriptor> hierProps = entity.GetHierarchicalProperties();
if (hierProps != null)
{
foreach (tgPropertyDescriptor esProp in hierProps)
{
esProp.TrueDescriptor = props[esProp.Name];
// coll.Add(lastOrdinal++, esProp);
tempColl.Add(esProp);
}
}
}
// Create the collection
foreach (PropertyDescriptor p in coll.Values)
{
tempColl.Add(p);
}
tempColl.AddRange(collNested);
PropertyDescriptorCollection theProps =
new PropertyDescriptorCollection((PropertyDescriptor[])tempColl.ToArray(typeof(PropertyDescriptor)));
return(theProps);
}
static public VistaDBCommand BuildDynamicInsertCommand(tgDataRequest request, List <string> modifiedColumns)
{
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();
VistaDBParameter p = null;
Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request);
VistaDBCommand cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
tgColumnMetadataCollection cols = request.Columns;
foreach (tgColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency))
{
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;
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
else if (col.IsConcurrency)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
else if (col.IsTiraggoConcurrency)
{
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + "1";
comma = ", ";
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
p.Value = 1; // Seems to work, We'll take it ...
cmd.Parameters.Add(p);
}
else if (col.IsComputed)
{
// Do nothing but leave this here
}
else if (cols.IsSpecialColumn(col))
{
// Do nothing but leave this here
}
else if (col.HasDefault)
{
defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
defaultComma = ",";
string def = col.Default.ToLower();
if (def.Contains("guid") || def.Contains("newid"))
{
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
sql += " IF " + Delimiters.Param + col.Name + " IS NULL SET " +
Delimiters.Param + col.Name + " = NEWID();";
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + p.ParameterName;
comma = ", ";
}
}
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);
}
protected AspNetUsersMetadata()
{
m_columns = new tgColumnMetadataCollection();
tgColumnMetadata c;
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.Id, 0, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.Id;
c.IsInPrimaryKey = true;
c.CharacterMaxLength = 128;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.Email, 1, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.Email;
c.IsNullable = true;
c.CharacterMaxLength = 256;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.EmailConfirmed, 2, typeof(System.Boolean), tgSystemType.Boolean);
c.PropertyName = AspNetUsersMetadata.PropertyNames.EmailConfirmed;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.PasswordHash, 3, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.PasswordHash;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.SecurityStamp, 4, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.SecurityStamp;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.PhoneNumber, 5, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.PhoneNumber;
c.IsNullable = true;
c.CharacterMaxLength = 2147483647;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.PhoneNumberConfirmed, 6, typeof(System.Boolean), tgSystemType.Boolean);
c.PropertyName = AspNetUsersMetadata.PropertyNames.PhoneNumberConfirmed;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.TwoFactorEnabled, 7, typeof(System.Boolean), tgSystemType.Boolean);
c.PropertyName = AspNetUsersMetadata.PropertyNames.TwoFactorEnabled;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.LockoutEndDateUtc, 8, typeof(System.DateTime), tgSystemType.DateTime);
c.PropertyName = AspNetUsersMetadata.PropertyNames.LockoutEndDateUtc;
c.IsNullable = true;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.LockoutEnabled, 9, typeof(System.Boolean), tgSystemType.Boolean);
c.PropertyName = AspNetUsersMetadata.PropertyNames.LockoutEnabled;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.AccessFailedCount, 10, typeof(System.Int32), tgSystemType.Int32);
c.PropertyName = AspNetUsersMetadata.PropertyNames.AccessFailedCount;
c.NumericPrecision = 10;
m_columns.Add(c);
c = new tgColumnMetadata(AspNetUsersMetadata.ColumnNames.UserName, 11, typeof(System.String), tgSystemType.String);
c.PropertyName = AspNetUsersMetadata.PropertyNames.UserName;
c.CharacterMaxLength = 256;
m_columns.Add(c);
}
