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