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"); }