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 Insert <T>(T instance, string TableName = null, string[] Properties = null, string[] ExcludeProperties = null)
{
InsertIntoTable table = SqlBuilder.Insert()
.Into(TableName ?? instance.GetType().Name);
Metadata.MetadataTable mt = SqlBuilder.DefaultMetadata.FindTable(TableName ?? instance.GetType().Name);
if (Properties == null)
{
Properties = instance.GetType().GetProperties().Select(x => x.Name).ToArray();
}
if (ExcludeProperties != null)
{
Properties = Properties.Except(ExcludeProperties).ToArray();
}
foreach (Metadata.MetadataColumn col in mt.Columns.Values)
{
if (Properties.Contains(col.Name) && !col.IsIdentity && !col.IsReadOnly)
{
PropertyInfo prop = instance.GetType().GetProperty(col.Name);
if (prop.CanRead && prop.CanWrite)
{
table.Value(prop.Name, prop.GetValue(instance));
}
}
}
return(table.Output().PrimaryKey().Builder());
}
private void BuildVirtualKeys(List <VirtualForeignKey> keys, MetadataTable mt, MetadataDatabase mdb, bool PrimaryKeyIndexOnly)
{
foreach (VirtualForeignKey vfk in keys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = 0,
Name = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[0],
ReferencedKey = "",
ReferencedTable = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[1],
ReferencedSchema = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[2],
Parent = mt,
IsVirtual = true
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if (mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name)
{
self = true;
}
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
for (int i = 1; i < vfk.values.Length; i++)
{
MetadataColumnReference mcf = new MetadataColumnReference()
{
ReferencedColumn = mtref[vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1]],
};
string from = vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[0];
if (from.StartsWith("\""))
{
MetadataColumn mcVirtual = new MetadataColumn()
{
Name = from,
IsForeignKey = true,
ID = 0,
SqlDataType = SqlDbType.NVarChar,
Nullable = false,
Length = 0,
IsComputed = true,
DataType = typeof(string),
};
mcf.Column = mcVirtual;
mcf.Name = from;
}
else
{
mcf.Column = mt[from];
mcf.Name = mcf.Column.Name;
}
mfk.ColumnReferences.Add(mcf);
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (k, v) => { return(mfk); });
}
}
public MetadataTable BuildMetadata(MetadataDatabase mdb, string TableName, string Schema = "dbo", bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
if (mdb.Tables.TryGetValue(TableName, out mt))
{
return(mt);
}
Microsoft.SqlServer.Management.Smo.Table t = new Microsoft.SqlServer.Management.Smo.Table(SqlDatabase, TableName, Schema);
t.Refresh();
return(BuildMetadata(mdb, t, PrimaryKeyIndexOnly, SelfJoin));
}
public static SqlBuilder Update <T>(T instance, string TableName = null, string Schema = null, string[] Properties = null, string[] ExcludeProperties = null, bool OutputPrimaryKey = false)
{
UpdateTable table = SqlBuilder.Update()
.Table(TableName ?? instance.GetType().Name, Schema);
Metadata.MetadataTable mt = SqlBuilder.DefaultMetadata.FindTable(TableName ?? instance.GetType().Name);
if (Properties == null)
{
Properties = instance.GetType().GetProperties().Select(x => x.Name).ToArray();
}
if (ExcludeProperties != null)
{
Properties = Properties.Except(ExcludeProperties).ToArray();
}
foreach (Metadata.MetadataColumn col in mt.Columns.Values)
{
if (Properties.Contains(col.Name) && !col.IsIdentity && !col.IsReadOnly)
{
PropertyInfo prop = instance.GetType().GetProperty(col.Name);
if (prop.CanRead && prop.CanWrite)
{
table.Set(col.Name, prop.GetValue(instance), col.SqlDataType, prop.PropertyType);
}
}
}
List <object> pk = new List <object>();
mt.PrimaryKey.Columns.ForEach((col) =>
{
PropertyInfo prop = instance.GetType().GetProperty(col.Name);
pk.Add(prop.GetValue(instance));
});
table.WithMetadata().WherePrimaryKey(pk.ToArray());
if (OutputPrimaryKey)
{
return(table.Output().PrimaryKey().Builder());
}
else
{
return(table.Builder());
}
}
public MetadataTable FindTable(string Name, StringComparison CompareOption = StringComparison.OrdinalIgnoreCase)
{
MetadataTable mt = null;
string Schema = null;
if (!Name.Contains('.'))
{
Schema = "dbo.";
}
if (Tables.TryGetValue(Schema + Name, out mt))
{
return(mt);
}
string[] keys = Tables.Keys.Where(x => x.EndsWith(Name, CompareOption)).ToArray();
if (keys.Length != 1)
{
return(null);
}
return(this[keys[0]]);
}
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 MetadataTable BuildMetadata(MetadataDatabase mdb, Microsoft.SqlServer.Management.Smo.Table table, bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
List <VirtualForeignKey> VirtualKeys = new List <VirtualForeignKey>();
table.Refresh();
if (mdb.Tables.TryGetValue(table.Name, out mt))
{
return(mt);
}
mt = new MetadataTable()
{
ID = table.ID,
Schema = table.Schema,
Name = table.Name,
//Parent = mdb
};
mt.TitleColumn = GetExtendedProperty("TitleColumn", table.ExtendedProperties);
string[] values = GetExtendedProperty("DisplayName", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
mt.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
values = GetExtendedProperty("Lists", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
List <string> v2 = v[1].Split(',').ToList();
if (!mt.ListDefinitions.TryAdd(v[0].Trim(), v2))
{
throw new InvalidOperationException(string.Format("The TinySql.Lists extended property is invalid for the table '{0}'", table.Name));
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.Column column in table.Columns)
{
try
{
MetadataColumn col = new MetadataColumn()
{
ID = column.ID,
Parent = mt,
//Database = mdb,
Name = column.Name,
Collation = column.Collation,
Default = column.Default,
IsComputed = column.Computed,
ComputedText = column.ComputedText,
IsPrimaryKey = column.InPrimaryKey,
IsIdentity = column.Identity,
IsForeignKey = column.IsForeignKey,
IdentityIncrement = column.IdentityIncrement,
IdentitySeed = column.IdentitySeed,
Nullable = column.Nullable,
IsRowGuid = column.RowGuidCol
};
BuildColumnDataType(col, column);
values = GetExtendedProperty("DisplayName", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
if (!value.Contains("="))
{
col.DisplayNames.TryAdd(SqlBuilder.DefaultCulture.LCID, value);
}
else
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
col.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
}
col.IncludeColumns = GetExtendedProperty("IncludeColumns", column.ExtendedProperties, new char[] { ',' });
values = GetExtendedProperty("FK", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
VirtualKeys.Add(new VirtualForeignKey()
{
Column = col, values = values
});
col.IsForeignKey = true;
}
mt.Columns.AddOrUpdate(col.Name, col, (k, v) => { return(col); });
}
catch (Exception exColumn)
{
throw new InvalidOperationException(string.Format("Unable to generate the column {0}", column.Name), exColumn);
}
}
foreach (Index idx in table.Indexes)
{
if (!PrimaryKeyIndexOnly || idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
Key key = new Key()
{
ID = idx.ID,
Parent = mt,
Database = mdb,
Name = idx.Name,
IsUnique = idx.IsUnique,
IsPrimaryKey = idx.IndexKeyType == IndexKeyType.DriPrimaryKey
};
foreach (IndexedColumn c in idx.IndexedColumns)
{
key.Columns.Add(mt[c.Name]);
}
mt.Indexes.AddOrUpdate(key.Name, key, (k, v) => { return(key); });
}
}
if (!SelfJoin)
{
foreach (ForeignKey FK in table.ForeignKeys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = FK.ID,
Parent = mt,
Database = mdb,
Name = FK.Name,
ReferencedKey = FK.ReferencedKey,
ReferencedSchema = FK.ReferencedTableSchema,
ReferencedTable = FK.ReferencedTable
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if ((mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) || TablesInProgress.Contains(mfk.ReferencedSchema + "." + mfk.ReferencedTable))
{
self = true;
}
TablesInProgress.Add(mfk.ReferencedSchema + "." + mfk.ReferencedTable);
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
foreach (ForeignKeyColumn cc in FK.Columns)
{
mfk.ColumnReferences.Add(new MetadataColumnReference()
{
Name = cc.Name,
Column = mt[cc.Name],
ReferencedColumn = mtref[cc.ReferencedColumn]
});
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (key, existing) =>
{
return(mfk);
});
}
}
if (VirtualKeys.Count > 0)
{
BuildVirtualKeys(VirtualKeys, mt, mdb, PrimaryKeyIndexOnly);
}
mdb.Tables.AddOrUpdate(mt.Schema + "." + mt.Name, mt, (key, existing) =>
{
return(mt);
});
return(mt);
}
public MetadataDatabase BuildMetadata(bool PrimaryKeyIndexOnly = true, string[] Tables = null, bool UpdateExisting = false)
{
MetadataDatabase mdb = FromCache();
string[] Changes = null;
if (mdb != null && !UpdateExisting)
{
// mdb.Builder = builder;
return(mdb);
}
Guid g = Guid.NewGuid();
try
{
g = SqlDatabase.DatabaseGuid;
}
catch (Exception)
{
}
if (UpdateExisting)
{
if (mdb == null)
{
throw new ArgumentException("Update was specified but the metadata was not found in cache or file", "UpdateExisting");
}
long v = GetVersion();
if (v <= mdb.Version)
{
RaiseUpdateEvent(100, "The database is up to date");
return(mdb);
}
else
{
Changes = GetChanges(new DateTime(mdb.Version));
RaiseUpdateEvent(0, string.Format("{0} Changed tables identified", Changes.Length));
foreach (string change in Changes)
{
MetadataTable mt = null;
if (mdb.Tables.TryRemove(change, out mt))
{
RaiseUpdateEvent(0, string.Format("{0} removed from Metadata pending update", change));
}
else
{
throw new InvalidOperationException("Could not remove the table " + change + " pending update");
}
}
mdb.Version = v;
}
}
else
{
mdb = new MetadataDatabase()
{
ID = g,
Name = SqlDatabase.Name,
Server = SqlServer.Name + (!string.IsNullOrEmpty(SqlServer.InstanceName) && SqlServer.Name.IndexOf('\\') == -1 ? "" : ""),
Builder = builder,
Version = GetVersion()
};
}
double t = 0;
double total = Changes != null ? Changes.Length : Tables != null ? Tables.Length : SqlDatabase.Tables.Count + SqlDatabase.Views.Count;
foreach (Microsoft.SqlServer.Management.Smo.Table table in SqlDatabase.Tables)
{
if (Tables == null || Tables.Contains(table.Name))
{
if (Changes == null || Changes.Contains(table.Schema + "." + table.Name))
{
table.Refresh();
BuildMetadata(mdb, table);
if (MetadataUpdateEvent != null)
{
t++;
RaiseUpdateEvent(Convert.ToInt32((t / total) * 100), table.Schema + "." + table.Name + " built");
}
if (t == total)
{
break;
}
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.View view in SqlDatabase.Views)
{
if (view.IsSystemObject)
{
t++;
continue;
}
if (Tables == null || Tables.Contains(view.Name))
{
if (Changes == null || Changes.Contains(view.Schema + "." + view.Name))
{
view.Refresh();
BuildMetadataView(mdb, view);
if (MetadataUpdateEvent != null)
{
t++;
RaiseUpdateEvent(Convert.ToInt32((t / total) * 100), view.Schema + "." + view.Name + " built");
}
if (t == total)
{
break;
}
}
}
}
ToCache(mdb);
return(mdb);
}
