private void SetupTables(ISourceInfo Source, Database Temp) { Contract.Requires<ArgumentNullException>(Temp != null, "Temp"); Contract.Requires<ArgumentNullException>(Temp.Tables != null, "Temp.Tables"); foreach (Utilities.ORM.Manager.Schema.Default.Database.Table Table in Temp.Tables) { IEnumerable<dynamic> Values = Provider.Batch(Source) .AddCommand(null, null, @"SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN_TYPE], sys.columns.max_length as [MAX_LENGTH], sys.columns.is_nullable as [IS_NULLABLE], sys.columns.is_identity as [IS_IDENTITY], sys.index_columns.index_id as [IS_INDEX], key_constraints.name as [PRIMARY_KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN_KEY_TABLE], columns_1.name as [FOREIGN_KEY_COLUMN], sys.default_constraints.definition as [DEFAULT_VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @0) AND (sys.systypes.xusertype <> 256)", CommandType.Text, Table.Name) .Execute()[0]; SetupColumns(Table, Values); SetupTriggers(Source, Table, Values); } foreach (Utilities.ORM.Manager.Schema.Default.Database.Table Table in Temp.Tables) { Table.SetupForeignKeys(); } }
private void SetupViews(ISourceInfo Source, Database Temp) { Contract.Requires<ArgumentNullException>(Temp != null, "Temp"); Contract.Requires<ArgumentNullException>(Temp.Views != null, "Temp.Views"); foreach (View View in Temp.Views) { IEnumerable<dynamic> Values = Provider.Batch(Source) .AddCommand(null, null, @"SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE sys.views.name=@0", CommandType.Text, View.Name) .Execute()[0]; View.Definition = Values.First().Definition; Values = Provider.Batch(Source) .AddCommand(null, null, @"SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN_TYPE], sys.columns.max_length as [MAX_LENGTH], sys.columns.is_nullable as [IS_NULLABLE] FROM sys.views INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id WHERE (sys.views.name = @0) AND (sys.systypes.xusertype <> 256)", CommandType.Text, View.Name) .Execute()[0]; foreach (dynamic Item in Values) { string ColumnName = Item.Column; string ColumnType = Item.COLUMN_TYPE; int MaxLength = Item.MAX_LENGTH; if (ColumnType == "nvarchar") MaxLength /= 2; bool Nullable = Item.IS_NULLABLE; View.AddColumn<string>(ColumnName, ColumnType.To<string, SqlDbType>().To(DbType.Int32), MaxLength, Nullable); } } }
private void SetupStoredProcedures(ISourceInfo Source, Database Temp) { Contract.Requires<ArgumentNullException>(Source != null, "Source"); Contract.Requires<NullReferenceException>(Provider != null, "Provider"); IEnumerable<dynamic> Values = Provider.Batch(Source) .AddCommand(null, null, CommandType.Text, "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures") .Execute()[0]; foreach (dynamic Item in Values) { Temp.AddStoredProcedure(Item.NAME, Item.DEFINITION); } foreach (StoredProcedure Procedure in Temp.StoredProcedures) { Values = Provider.Batch(Source) .AddCommand(null, null, @"SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@0 AND (sys.systypes.xusertype <> 256)", CommandType.Text, Procedure.Name) .Execute()[0]; foreach (dynamic Item in Values) { string Type = Item.TYPE; string Name = Item.NAME; int Length = Item.LENGTH; if (Type == "nvarchar") Length /= 2; string Default = Item.DEFAULT_VALUE; Procedure.AddColumn<string>(Name, Type.To<string, SqlDbType>().To(DbType.Int32), Length, DefaultValue: Default); } } }
private void SetupFunctions(ISourceInfo Source, Database Temp) { Contract.Requires<ArgumentNullException>(Source != null, "Source"); Contract.Requires<NullReferenceException>(Provider != null, "Provider"); IEnumerable<dynamic> Values = Provider.Batch(Source) .AddCommand(null, null, CommandType.Text, "SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'") .Execute()[0]; foreach (dynamic Item in Values) { Temp.AddFunction(Item.NAME, Item.DEFINITION); } }
private void GetTables(ISourceInfo Source, Database Temp) { Contract.Requires<ArgumentNullException>(Source != null, "Source"); Contract.Requires<NullReferenceException>(Provider != null, "Provider"); IEnumerable<dynamic> Values = Provider.Batch(Source) .AddCommand(null, null, CommandType.Text, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES") .Execute()[0]; foreach (dynamic Item in Values) { string TableName = Item.TABLE_NAME; string TableType = Item.TABLE_TYPE; if (TableType == "BASE TABLE") Temp.AddTable(TableName); else if (TableType == "VIEW") Temp.AddView(TableName); } }
/// <summary> /// Sets up the specified database schema /// </summary> /// <param name="Mappings">The mappings.</param> /// <param name="Database">The database.</param> /// <param name="QueryProvider">The query provider.</param> public void Setup(ListMapping<IDatabase, IMapping> Mappings, IDatabase Database, Utilities.ORM.Manager.QueryProvider.Manager QueryProvider, Graph<IMapping> Structure) { var TempSource = SourceProvider.GetSource(Database.Name); var TempDatabase = new Utilities.ORM.Manager.Schema.Default.Database.Database(Regex.Match(TempSource.Connection, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "")); SetupTables(Mappings, Database, TempDatabase); SetupJoiningTables(Mappings, Database, TempDatabase); SetupAuditTables(Database, TempDatabase); foreach (ITable Table in TempDatabase.Tables) { Table.SetupForeignKeys(); } var Commands = GenerateSchema(TempDatabase, SourceProvider.GetSource(Database.Name)).ToList(); var Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name)); for (int x = 0; x < Commands.Count; ++x) { if (Commands[x].ToUpperInvariant().Contains("CREATE DATABASE")) { QueryProvider.Batch(SourceProvider.GetSource(Regex.Replace(SourceProvider.GetSource(Database.Name).Connection, "Initial Catalog=(.*?;)", ""))).AddCommand(null, null, CommandType.Text, Commands[x]).Execute(); } else if (Commands[x].Contains("CREATE TRIGGER") || Commands[x].Contains("CREATE FUNCTION")) { if (Batch.CommandCount > 0) { Batch.Execute(); Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name)); } Batch.AddCommand(null, null, CommandType.Text, Commands[x]); if (x < Commands.Count - 1) { Batch.Execute(); Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name)); } } else { Batch.AddCommand(null, null, CommandType.Text, Commands[x]); } } Batch.Execute(); }
/// <summary> /// Gets the structure of a source /// </summary> /// <param name="Source">Source to use</param> /// <returns>The source structure</returns> public ISource GetSourceStructure(ISourceInfo Source) { var DatabaseName = Regex.Match(Source.Connection, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", ""); var DatabaseSource = SourceProvider.GetSource(Regex.Replace(Source.Connection, "Initial Catalog=(.*?;)", "")); if (!SourceExists(DatabaseName, DatabaseSource)) return null; var Temp = new Database(DatabaseName); GetTables(Source, Temp); SetupTables(Source, Temp); SetupViews(Source, Temp); SetupStoredProcedures(Source, Temp); SetupFunctions(Source, Temp); return Temp; }