public void AutoUpdateFromRowDataObject()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select().WithMetadata(true, SetupData.MetadataFileName)
.From("Account")
.AllColumns(false)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526)
.Builder;
Console.WriteLine(builder.ToSql());
ResultTable r = builder.Execute();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account selected in {0}ms"));
g = StopWatch.Start();
Assert.IsTrue(r.Count == 1, "Executed 1 account");
RowData row = r.First();
row.Column("Name", Guid.NewGuid().ToString());
builder = SqlBuilder.Update().Update(row, new string[] { "AccountID", "Name" });
Console.WriteLine(builder.ToSql());
r = builder.Execute();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account updated in {0}ms"));
row.AcceptChanges();
Assert.IsTrue(r.First().Column <string>("Name") == row.Column <string>("Name"), "Names are equal");
Assert.IsFalse(row.HasChanges, "The row does not have changes");
}
public ContentResult Update(string rowData)
{
RowData row = SerializationExtensions.FromJson <RowData>(rowData);
row.LoadMetadata();
row.Column("Name", "Random " + Guid.NewGuid().ToString());
SqlBuilder builder = row.Update(false, true);
ResultTable result = builder.Execute(30, false);
if (result.Count == 1)
{
builder = SqlBuilder.Select()
.From("Contact")
.Column("ContactID")
.Column("Name")
.Column("Telephone")
.Column("WorkEmail")
.Column("ModifiedOn")
.WithMetadata().InnerJoin("AccountID")
.Column("Name", "AccountName")
.From("Contact")
.Where <decimal>("Contact", "ContactID", SqlOperators.Equal, result.First().Column <decimal>("ContactID"))
.Builder();
result = builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate);
row = result.First();
return(Content(SerializationExtensions.ToJson <dynamic>(row), "application/json"));
}
return(Content("Hmmmm...?", "application/text"));
}
private static object PopulateObject(Type t, RowData row)
{
object instance = Activator.CreateInstance(t);
foreach (string prop in row.GetDynamicMemberNames())
{
PropertyInfo p = instance.GetType().GetProperty(prop);
if (p != null && p.CanWrite)
{
object o = row.Column(prop);
if (o is ResultTable && p.PropertyType.GetInterface("IList", true) != null)
{
Type listType = typeof(List <>);
Type[] args = p.PropertyType.GetGenericArguments();
Type genericList = listType.MakeGenericType(args);
object listInstance = Activator.CreateInstance(genericList);
foreach (RowData r in (o as ResultTable))
{
((IList)listInstance).Add(PopulateObject(args[0], r));
}
p.SetValue(instance, listInstance);
}
else
{
p.SetValue(instance, o);
}
}
}
return(instance);
}
public FieldModel GetFieldModel(FormField Field, MetadataTable Model, RowData Data)
{
MetadataColumn mc = null;
MetadataTable mt = Model;
if (Field.ID.StartsWith("__"))
{
return(new FieldModel(Field, null, Data.Column(Field.Name), SectionLayout));
}
if (Field.TableName != Model.Fullname)
{
mt = SqlBuilder.DefaultMetadata.FindTable(Field.TableName);
}
if (!mt.Columns.TryGetValue(Field.Name, out mc))
{
throw new InvalidOperationException("Cannot get a model for " + Field.Name);
}
return(new FieldModel(Field, mc, Data.Column(Field.Alias ?? Field.Name), SectionLayout));
}
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 ResultTable ExecuteRelatedInternal(SqlBuilder builder, Dictionary <string, RowData> results)
{
if (results.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
foreach (string key in results.Keys)
{
foreach (MetadataForeignKey fk in mt.ForeignKeys.Values.Where(x => (x.ReferencedSchema + "." + x.ReferencedTable).Equals(key, StringComparison.OrdinalIgnoreCase)))
{
RowData row = results[key];
foreach (MetadataColumnReference mcr in fk.ColumnReferences)
{
if (row.Columns.Contains(mcr.Column.Name))
{
Field f = builder.BaseTable().FindField(mcr.Column.Name);
if (f != null)
{
f.Value = row.Column(mcr.Column.Name);
}
else
{
(builder.BaseTable() as InsertIntoTable).Value(mcr.Column.Name, row.Column(mcr.Column.Name), SqlDbType.VarChar);
}
}
}
}
}
}
DataTable dt = new DataTable();
ResultTable table = new ResultTable();
using (SqlConnection context = new SqlConnection(builder.ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(builder.ToSql(), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(dt);
context.Close();
}
if (builder.SubQueries.Count > 0)
{
Dictionary <string, RowData> subresults = new System.Collections.Generic.Dictionary <string, RowData>(results);
if (dt.Rows.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
if (!subresults.ContainsKey(mt.Fullname))
{
ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None);
RowData row = rt.First();
table.Add(row);
subresults.Add(mt.Fullname, row);
}
}
foreach (SqlBuilder Builder in builder.SubQueries.Values)
{
ResultTable sub = ExecuteRelatedInternal(Builder, subresults);
foreach (RowData row in sub)
{
table.Add(row);
}
}
}
return(table);
}
public FieldModel GetFieldModel(FormField Field, MetadataTable Model, RowData Data)
{
MetadataColumn mc = null;
MetadataTable mt = Model;
if (Field.ID.StartsWith("__"))
{
return new FieldModel(Field, null, Data.Column(Field.Name), SectionLayout);
}
if (Field.TableName != Model.Fullname)
{
mt = SqlBuilder.DefaultMetadata.FindTable(Field.TableName);
}
if (!mt.Columns.TryGetValue(Field.Name, out mc))
{
throw new InvalidOperationException("Cannot get a model for " + Field.Name);
}
return new FieldModel(Field, mc, Data.Column(Field.Alias ?? Field.Name), SectionLayout);
}
