//public static string CreateTable(SysTable table, SysColumn[] columns) //{ // var pKey = columns.ToList().Find(c => c.primary_key != null); // return string.Join("\n", new string[] // { // $"create table {ToObjectName(table)} (", // string.Join(",\n", // columns.Select(column => { // bool // isString = column.data_type.Contains("varchar"), // isNullable = Convert.ToBoolean(column.is_nullable), // isIdentity = Convert.ToBoolean(column.is_identity); // string maxLength = column.max_length == "-1"? "max": column.max_length; // return $"\t[{column.name}] [{column.data_type}]{(isString? $"({maxLength})": "")} {(isIdentity? "identity(1, 1) ": "")}{(isNullable? "": "not")} null{(column.default_constraint_name == null? "": $" constraint [{column.default_constraint_name}] default {column.default_constraint_definition}")}"; // }) // ), // pKey == null? "": $"\tconstraint [{pKey.primary_key}] primary key {pKey.index_type_desc} ( [{pKey.name}] asc )", // ")" // }); //} public static string CreateTable(SysTable table, List <SysColumn> columns) { var pKey = columns.Find(c => c.primary_key != null); var cmd = new List <string> { $"IF (OBJECT_ID('{table.qualified_name}') IS NULL)", "BEGIN", $"\tCREATE TABLE {table.qualified_name} (", string.Join(",\n", columns.Select(column => "\t\t" + column.column_definition)), }; if (pKey != null) { cmd.Add($"\t\tCONSTRAINT [{pKey.primary_key}] PRIMARY KEY {pKey.index_type_desc} ( [{pKey.name}] ASC )"); } if (table.history_table != null) { var rowStart = columns.Find(c => c.generated_always_type == "1").name; var rowEnd = columns.Find(c => c.generated_always_type == "2").name; cmd[cmd.Count() - 1] = string.Join("", cmd.Last().Append(',')); cmd.Add($"\t\tPERIOD FOR SYSTEM_TIME ([{rowStart}], [{rowEnd}])"); cmd.Add("\t)"); cmd.Add($"WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = {table.history_table} ))"); } else { cmd.Add("\t)"); } cmd.Add("END"); cmd.Add(BatchSeperator); return(string.Join("\n", cmd)); }
public FamilyEditViewModel(Family family) { SysTable = new SysTable(); SysTable.Name = "taxonomy_family"; base.TableName = "taxonomy_family"; ID = family.ID; CurrentID = family.ParentID; Name = family.Name; Authority = family.Authority; TypeGenusID = family.TypeGenusID; TypeGenusName = family.TypeGenus.Name; SuprafamilyRankCode = family.SuprafamilyRankCode; SuprafamilyRankName = family.SuprafamilyRankName; AlternateName = family.AlternateName; SubFamilyName = family.SubFamilyName; TribeName = family.TribeName; SubTribeName = family.SubTribeName; Note = family.Note; CreatedByCooperatorID = family.CreatedByCooperatorID; CreatedByCooperatorName = family.CreatedByCooperatorName; CreatedDate = family.CreatedDate; ModifiedByCooperatorID = family.ModifiedByCooperatorID; ModifiedByCooperatorName = family.ModifiedByCooperatorName; ModifiedDate = family.ModifiedDate; Genera = family.Genera; SubFamilies = family.SubFamilies; SubTribes = family.SubTribes; }
private void AddData(DataDictionary dict, SysTable table, RowCollection rows) { if (!dict.ContainsKey(table.qualified_name)) { dict.Add(table.qualified_name, rows); } }
private void btSaveGrid_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { try { SysTable stb = new SysTable(); foreach (CDTGridColumn col in gvMain.Columns) { if (!col.isExCol) { stb.UpdateColWidth(col.MasterRow, col.Width); } stb.UpdateColIndex(col.MasterRow, col.VisibleIndex); stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0); } for (int i = 0; i < this._frmDesigner._gcDetail.Count; i++) { GridControl gr = this._frmDesigner._gcDetail[i]; GridView gv = gr.MainView as GridView; foreach (CDTGridColumn col in gv.Columns) { if (!col.isExCol) { stb.UpdateColWidth(col.MasterRow, col.Width); } stb.UpdateColIndex(col.MasterRow, col.VisibleIndex); stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0); } } } catch { } }
private bool IsTargetEmpty(SysTable table) { return(Helpers .ExecuteCommand <bool>( new SqlCommand($"select case when not exists (select top(1) * from {table.qualified_name}) then cast(1 as bit) else cast(0 as bit) end"), targetConn) .FirstOrDefault()); }
public static string DropTable(SysTable table) { return(string.Join("\n", new string[] { $"IF EXISTS (SELECT object_id FROM sys.tables WHERE object_id = OBJECT_ID('{table.qualified_name}'))", $"\tDROP TABLE {table.qualified_name}\n", })); }
public FamilyEditViewModel() { SysTable = new SysTable(); SysTable.Name = "taxonomy_family"; base.TableName = "taxonomy_family"; Genera = new List <Genus>().AsEnumerable(); SubFamilies = new List <Family>().AsEnumerable(); SubTribes = new List <Family>().AsEnumerable(); Citations = new List <Citation>().AsEnumerable(); }
public static string DropSystemVersioning(SysTable table) { return(string.Join("\n", new string[] { SetSystemVersioning(table, false), $"ALTER TABLE {table.qualified_name}", $"DROP PERIOD FOR SYSTEM_TIME", BatchSeperator })); }
private RowCollection GetTableData(SysTable table) { if (usingSourceFile) { return(dbData.Seed.GetValueOrDefault(table.qualified_name)); } var rows = Helpers.ExecuteCommandToDictionary(new SqlCommand($"select * from {table.qualified_name}"), sourceConn); return(Query.ConvertRows(rows)); }
public static string SeedIf(SysTable table, IEnumerable <SysColumn> columns, RowCollection rows) { var tableName = table.qualified_name; return(string.Join("\n", new string[] { $"IF NOT EXISTS (SELECT * FROM {tableName})", "BEGIN", DistributeSeed(tableName, columns, rows), "END\n" })); }
private void btSaveGrid_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { SysTable stb = new SysTable(); foreach (CDTGridColumn col in gvMain.Columns) { if (!col.isExCol) { stb.UpdateColWidth(col.MasterRow, col.Width); } stb.UpdateColIndex(col.MasterRow, col.VisibleIndex); stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0); } }
// alter public static string AddSystemVersioning(SysTable table) { return(string.Join("\n", new string[] { $"ALTER TABLE {table.qualified_name}", $"ADD", $"\tSysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN", $"\t\tCONSTRAINT [DF_{table.name}_SysStartTime] DEFAULT SYSUTCDATETIME(),", $"\tSysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN", $"\t\tCONSTRAINT [DF_{table.name}_SysEndTime] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),", $"\tPERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)", BatchSeperator, SetSystemVersioning(table, true) })); }
private void SeedTable(StringBuilder builder, SysTable table, List <SysColumn> columns, RowCollection rows) { var hasIdentity = table.has_identity != null; builder.Append($"-- {table.qualified_name} --\n\n"); if (hasIdentity) { builder.Append(Query.ToggleIdInsert(table.qualified_name, "ON")); } builder.Append(Query.SeedIf(table, columns, rows)); if (hasIdentity) { builder.Append(Query.ToggleIdInsert(table.qualified_name, "OFF")); } builder.Append(Query.BatchSeperator); }
public static string UpdateIf(SysTable table, IEnumerable <SysColumn> columns, IDictionary <string, string> data) { var pKey = columns.ToList().Find(c => c.primary_key != null); var pKeyColumn = pKey.name; var pKeyValue = data[pKeyColumn]; return(string.Join("\n", new string[] { $"IF NOT EXISTS (SELECT {pKeyColumn} FROM {table.qualified_name} WHERE {pKeyColumn} = {pKeyValue})", $"\tINSERT INTO {table.qualified_name} ({string.Join(", ", columns.Select(c => c.name))})", $"\tVALUES ({string.Join(", ", columns.Select(c => data[c.name]))})", "ELSE", $"\tUPDATE {table.qualified_name} SET", $"\t{string.Join(", ", columns.Where(c => c.primary_key == null).Select(c => $"{c.name} = {data[c.name]}"))} WHERE", $"\t{pKeyColumn} = {data[pKeyColumn]}\n" })); }
public static string SetSystemVersioning(SysTable table, bool onOff) { if (onOff) { return(string.Join("\n", new string[] { $"ALTER TABLE {table.qualified_name}", $"SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {table.history_table}))\n" })); } else { return(string.Join("\n", new string[] { $"ALTER TABLE {table.qualified_name}", $"SET (SYSTEM_VERSIONING = OFF)\n" })); } }
//执行生成代码 private void btnRun_Click(object sender, RoutedEventArgs e) { string basePath = AppDomain.CurrentDomain.BaseDirectory; ComboBoxItem itemType = T4Template.SelectedItem as ComboBoxItem; if (null == itemType) { return; } string type = itemType.Content.ToString(); foreach (var item in tables.SelectedItems) { SysTable table = item as SysTable; if (null != table) { IGenerator gen = GeneratorFactory.GetInstance(type, table.name, txtNamespace.Text, txtPrefix.Text); gen.SaveToCodeFile(); } } }
void btSaveGrid_ItemPress(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { bool admin = bool.Parse(Config.GetValue("Admin").ToString()); if (!admin) { return; } try { SysTable stb = new SysTable(); foreach (CDTGridColumn col in gvMain.Columns) { if (!col.isExCol) { stb.UpdateColWidth(col.MasterRow, col.Width); } stb.UpdateColIndex(col.MasterRow, col.VisibleIndex); stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0); } for (int i = 0; i < this._frmDesigner._gcDetail.Count; i++) { GridControl gr = this._frmDesigner._gcDetail[i]; GridView gv = gr.MainView as GridView; foreach (CDTGridColumn col in gv.Columns) { if (!col.isExCol) { stb.UpdateColWidth(col.MasterRow, col.Width); } stb.UpdateColIndex(col.MasterRow, col.VisibleIndex); stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0); } } } catch { } }
protected static SDatabase System() { return(SysTable.SysTables(new SDatabase())); }
public SysTable GetSysTable(int id) { SysTable sysTable = new SysTable(); return(GetSysTables().Where(x => x.ID == id).FirstOrDefault()); }
public DatabaseImp( string dirName ) { Name = dirName; Log = new Log( Name ); Sys = GetSchema( "sys", true, null ); { SysString = OpenFile( FileType.System, 0 ); SysStringReader = new BinaryReader( SysString ); SysStringWriter = new BinaryWriter( SysString ); IndexFileInfo ii = new IndexFileInfo(); ii.KeyCount = 1; ii.Types = new DataType[] { DataType.String }; var f = OpenFile( FileType.System, 1 ); SysStringIndex = new IndexFile( f, ii, this, -1 ); } { SysBinary = OpenFile( FileType.System, 2 ); SysBinaryReader = new BinaryReader( SysBinary ); SysBinaryWriter = new BinaryWriter( SysBinary ); IndexFileInfo ii = new IndexFileInfo(); ii.KeyCount = 1; ii.Types = new DataType[] { DataType.Binary }; var f = OpenFile( FileType.System, 3 ); SysBinaryIndex = new IndexFile( f, ii, this, -2 ); } var cb = new ColBuilder(); cb.Add( "Name", DataType.String ); SysSchema = NewSysTable( 1, "Schema", cb.Get() ); cb.Add( "Schema", DataType.Int ); cb.Add( "Name", DataType.String ); cb.Add( "IsView", DataType.Tinyint ); cb.Add( "Definition", DataType.String ); SysTable = NewSysTable( 2, "Table", cb.Get() ); cb.Add( "Table", DataType.Int ); cb.Add( "Name", DataType.String ); cb.Add( "Type", DataType.Int ); SysColumn = NewSysTable( 3, "Column", cb.Get() ); cb.Add( "Table", DataType.Int ); cb.Add( "Name", DataType.String ); cb.Add( "Modified", DataType.Tinyint ); SysIndex = NewSysTable( 4, "Index", cb.Get() ); cb.Add( "Table", DataType.Int ); cb.Add( "Index", DataType.Int ); cb.Add( "ColId", DataType.Int ); SysIndexCol = NewSysTable( 5, "IndexCol", cb.Get() ); SysColumn.OpenIndexes( IndexInfo.Single( 1, 1 ) ); SysColumnIndex = SysColumn.FindIndex( 1 ); SysIndexCol.OpenIndexes( IndexInfo.Single( 2, 1) ); SysIndexColIndex = SysIndexCol.FindIndex( 1 ); SysSchema.OpenIndexes( IndexInfo.Single( 3, 1 ) ); SysSchemaByName = SysSchema.FindIndex( 1 ); SysTable.OpenIndexes( IndexInfo.Single( 4, 2 ) ); SysTableByName = SysTable.FindIndex( 2 ); if ( SysSchema.RowCount == 0 ) { IsNew = true; Sql( "CREATE SCHEMA sys" ); // Note these are created in TableId order. Sql( "CREATE TABLE sys.Schema( Name string )" ); Sql( "CREATE TABLE sys.Table( Schema int, Name string, IsView tinyint, Definition string )" ); Sql( "CREATE TABLE sys.Column( Table int, Name string, Type int )" ); Sql( "CREATE TABLE sys.Index( Table int, Name string, Modified tinyint )" ); Sql( "CREATE TABLE sys.IndexCol( Table int, Index int, ColId int )" ); Sql( "CREATE INDEX ColumnByTable on sys.Column(Table)" ); Sql( "CREATE INDEX IndexColByTable on sys.IndexCol(Table)" ); Sql( "CREATE INDEX SchemaByName on sys.Schema(Name)" ); Sql( "CREATE INDEX TableByName on sys.Table(Name)" ); Normal = true; Sql( "CREATE TABLE sys.Function( Schema int, Name string, Definition string )" ); Sql( "CREATE INDEX FunctionByName on sys.Function(Name)" ); Sql( "CREATE TABLE sys.Procedure( Schema int, Name string, Definition string )" ); Sql( "CREATE INDEX ProcedureByName on sys.Procedure(Name)" ); } RollbackOrCommit(); Normal = true; } // end DatabaseImp