public static SqlBuilder Update(this SqlBuilder builder, RowData row, string[] Output = null)
{
if (!row.HasChanges)
{
return(builder);
}
string TableName = row.Table;
string Schema = null;
if (TableName.IndexOf('.') > 0)
{
Schema = TableName.Substring(0, TableName.IndexOf('.'));
TableName = TableName.Substring(TableName.IndexOf('.') + 1);
}
UpdateTable up = builder.Table(TableName, Schema);
Metadata.MetadataTable mt = row.Metadata;
foreach (string key in row.ChangedValues.Keys)
{
MetadataColumn c = mt[key];
SqlStatementExtensions.Set(up, key, row.ChangedValues[key], c.SqlDataType, c.DataType, c.Length, c.Scale);
}
if (Output != null && Output.Length > 0)
{
TableParameterField tpf = up.Output();
foreach (string s in Output)
{
MetadataColumn c = mt[s];
SqlStatementExtensions.Column(tpf, s, c.SqlDataType, c.Length, c.Scale);
}
}
builder.WhereConditions = row.PrimaryKey(builder);
return(builder);
}
public static SqlBuilder Update(this RowData row, bool OnlyChanges = false, bool OutputPrimaryKey = false, string[] OutputFields = null)
{
if (OnlyChanges && !row.HasChanges)
{
return(null);
}
SqlBuilder builder = SqlBuilder.Update();
string TableName = row.Table;
string Schema = null;
if (TableName.IndexOf('.') > 0)
{
Schema = TableName.Substring(0, TableName.IndexOf('.'));
TableName = TableName.Substring(TableName.IndexOf('.') + 1);
}
UpdateTable up = builder.Table(TableName, Schema);
Metadata.MetadataTable mt = row.Metadata;
if (OnlyChanges)
{
foreach (string key in row.ChangedValues.Keys)
{
object o;
MetadataColumn c;
if (row.ChangedValues.TryGetValue(key, out o) && mt.Columns.TryGetValue(key, out c) && !c.IsReadOnly)
{
SqlStatementExtensions.Set(up, key, o, c.SqlDataType, c.DataType, c.Length, c.Precision, c.Scale);
}
else
{
throw new InvalidOperationException("Cannot get the changed column " + key);
}
}
}
else
{
foreach (string key in row.Columns)
{
MetadataColumn c;
if (mt.Columns.TryGetValue(key, out c) && !c.IsReadOnly)
{
SqlStatementExtensions.Set(up, key, row.Column(key), c.SqlDataType, c.DataType, c.Length, c.Precision, c.Scale);
}
}
}
if (OutputPrimaryKey)
{
TableParameterField tpf = up.Output();
foreach (MetadataColumn key in mt.PrimaryKey.Columns)
{
SqlStatementExtensions.Column(tpf, key.Name, key.SqlDataType, key.Length, key.Precision, key.Scale);
}
}
if (OutputFields != null && OutputFields.Length > 0)
{
TableParameterField tpf = up.Output();
foreach (string s in OutputFields)
{
MetadataColumn c = mt[s];
SqlStatementExtensions.Column(tpf, s, c.SqlDataType, c.Length, c.Precision, c.Scale);
}
}
builder.WhereConditions = row.PrimaryKey(builder);
return(builder);
}
private static JoinConditionGroup To(SqlBuilder Builder, Table FromSqlTable, MetadataTable FromTable, MetadataColumn FromField, MetadataTable ToTable, Join.JoinTypes JoinType, bool PreferForeignKeyOverPrimaryKey = true)
{
MetadataDatabase mdb = Builder.Metadata;
List <MetadataForeignKey> Fks = null;
MetadataForeignKey FK = null;
Join j = null;
MetadataColumnReference mcr = null;
JoinConditionGroup jcg = null;
if (FromField.IsPrimaryKey)
{
if (!FromField.IsForeignKey || !PreferForeignKeyOverPrimaryKey)
{
Fks = ToTable.ForeignKeys.Values.Where(x => x.ReferencedTable.Equals(FromTable.Name) && x.ReferencedSchema.Equals(FromTable.Schema) && x.ColumnReferences.Any(y => y.ReferencedColumn.Equals(FromField))).ToList();
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' is referenced by {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.Name);
return(jcg);
}
}
if (FromField.IsForeignKey)
{
Fks = new List <MetadataForeignKey>(FromTable.FindForeignKeys(FromField, ToTable.Name));
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' resolves to {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.ReferencedColumn.Name);
if (FK.ColumnReferences.Count > 1)
{
foreach (MetadataColumnReference mcr2 in FK.ColumnReferences.Skip(1))
{
if (mcr2.Name.StartsWith("\""))
{
// its a value reference
// jcg.And(FK.ReferencedTable, mcr2.ReferencedColumn.Name, SqlOperators.Equal, mcr2.Name.Trim('\"'),null);
decimal d;
object o;
if (decimal.TryParse(mcr2.Name.Trim('\"'), out d))
{
o = d;
}
else
{
o = (object)mcr2.Name.Trim('\"');
}
jcg.And(mcr2.ReferencedColumn.Name, SqlOperators.Equal, o, null);
}
else
{
jcg.And(mcr2.Column.Name, SqlOperators.Equal, mcr2.ReferencedColumn.Name);
}
}
}
return(jcg);
}
throw new ArgumentException(string.Format("The Column '{0}' in the table '{1}' must be a foreign key or primary key", FromField.Name, FromTable.Fullname), "FromField");
}