public static string CreateCSharpClass(SqlTableReference sqlTableReference, string?className = default(string)) { var tableName = className ?? sqlTableReference.TableName; var schemaColumns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference); return(CreateCSharpClass(schemaColumns, tableName, CSharpClassTextGeneratorOptions.Default)); }
public static void CopyTableStructure(SqlTableReference sourceTable, SqlTableReference targetTable) { var sisTable = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sourceTable); var createTableScript = SISToSqlDmlCreateStatementGenerator.GenerateCreateTableScript(sisTable); targetTable.SqlConnectionProvider.Execute(createTableScript); }
private static void VerifyDataTableColumnsMatchDbTableSchema(ISqlConnectionProvider sqlConnectionProvider, string schemaName, string tableName, DataTable dataTable) { var dbTableColumns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns( new SqlTableReference(sqlConnectionProvider, schemaName, tableName)); var columnsFromDbDict = dbTableColumns.ToDictionary(z => z.COLUMN_NAME); var columnsInDataTableButMissingInDbTable = ( from DataColumn dataTableColumn in dataTable.Columns let dbContainsDatatableColumn = columnsFromDbDict.ContainsKey(dataTableColumn.ColumnName) where !dbContainsDatatableColumn select dataTableColumn.ColumnName).ToList(); if (columnsInDataTableButMissingInDbTable.Count > 0) { var message = $"SqlBulkInsert will fail because the following columns are present in the DataTable but not in Sql Database table {schemaName}.{tableName}: {string.Join(", ", columnsInDataTableButMissingInDbTable)}"; throw new InvalidDataException(message); } }
public void RetrieveHumanResourcesEmployeeTableAsJson() { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, "HumanResources.Employee"); var informationSchemaTableDefinition = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sqlTableReference); var tableDefinitionAsJson = JsonConvert.SerializeObject(informationSchemaTableDefinition); Console.WriteLine(tableDefinitionAsJson); }
public void GetInformationSchemaTablesForAllTables() { var schemaTables = InformationSchemaMetadataExplorer .GetInformationSchemaTablesOnly(SqlConnectionProviders.AdventureWorksDb) .OrderBy(x => x.TABLE_SCHEMA) .ThenBy(x => x.TABLE_NAME); Console.WriteLine(schemaTables.ToStringTable()); }
public static SqlTable Create(SqlTableReference sqlTableReference) { var columns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference); var sqlColumns = columns.Select(CSharpClassGeneratorFromInformationSchema.InformationSchemaColumnToSqlColumn).ToList(); var sqlTable = new SqlTable() { Name = sqlTableReference.TableName, Schema = sqlTableReference.SchemaName, SqlColumns = sqlColumns }; return(sqlTable); }
public void CreateTableShouldWork() { ISqlConnectionProvider sourceSqlConnectionProvider = null; UniqueDbConnectionProvider targetSqlConnectionProvider = null; SqlTableReference sourceSqlTableReference = null; SqlTableReference targetSqlTableReference = null; "Given a blank target database" ._(() => { targetSqlConnectionProvider = new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions( "ws2012sqlexp1\\sqlexpress", "TableManipulationTests")); targetSqlConnectionProvider.CreateDatabase(); }); using (targetSqlConnectionProvider.ToSelfDeletingDisposable()) { "Given a source database and a new blank database" ._(() => { sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb; }); "Given a source table to copy" ._(() => { sourceSqlTableReference = new SqlTableReference( sourceSqlConnectionProvider, "Person.Person"); }); "When copying the source table to the target db." ._(() => { targetSqlTableReference = new SqlTableReference( targetSqlConnectionProvider, "dbo.Person"); TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference); }); "Then there should be a copy of the table at the target DB" ._(() => { var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(1); tableSchemas[0].TABLE_NAME.Should().Be("Person"); }); } }
public void DropTableShouldWork() { ISqlConnectionProvider sourceSqlConnectionProvider = null; UniqueDbConnectionProvider targetSqlConnectionProvider = null; SqlTableReference sourceSqlTableReference = null; SqlTableReference targetSqlTableReference = null; "Given a table to drop in the database." ._(() => { targetSqlConnectionProvider = new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions( "ws2012sqlexp1\\sqlexpress", "TableManipulationTests")); targetSqlConnectionProvider.CreateDatabase(); sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb; sourceSqlTableReference = new SqlTableReference( sourceSqlConnectionProvider, "Person.Person"); targetSqlTableReference = new SqlTableReference( targetSqlConnectionProvider, "dbo.Person"); TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference); var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(1); tableSchemas[0].TABLE_NAME.Should().Be("Person"); }); using (targetSqlConnectionProvider.ToSelfDeletingDisposable()) { "When dropping the target table." ._(() => { TableManipulation.DropTable(targetSqlTableReference); }); "Then the table should be removed from the database." ._(() => { var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(0); }); } }
private static async Task GeneratePbsiDatabaseTypes(StaticSqlConnectionProvider scp, bool includeAttributes, bool includeTemporalColumns) { var generatorOptions = new CSharpClassTextGeneratorOptions() { IncludePropertyAnnotationAttributes = includeAttributes }; var tableDefinitions = await InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinitions(scp); foreach (var tableDefinition in tableDefinitions) { if (string.Equals(tableDefinition.InformationSchemaTable.TABLE_SCHEMA, "PbsiSf", StringComparison.CurrentCultureIgnoreCase)) { continue; } var columns = tableDefinition.InformationSchemaColumns; if (!includeTemporalColumns) { columns = columns.Where(z => !z.COLUMN_NAME.InsensitiveEquals("validto") && !z.COLUMN_NAME.InsensitiveEquals("validfrom") ) .ToList(); } var tableName = tableDefinition.InformationSchemaTable.TABLE_NAME .Replace(".", "") .Replace("$", "_"); if (tableName.EndsWith("_History", StringComparison.OrdinalIgnoreCase)) { continue; } var cSharpClass = CSharpClassGeneratorFromInformationSchema.CreateCSharpClass( columns, tableName, generatorOptions ); Console.WriteLine(cSharpClass); } }