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