public DatabaseContainer GetDatabaseStructure()
{
Microsoft.Data.SqlClient.SqlConnectionStringBuilder builder =
new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(this._connectionString);
DatabaseContainer container = new DatabaseContainer(builder.InitialCatalog, DatabaseContainer.DatabaseTypeMSSQL);
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
List <Constraint> databaseConstraints = GetIndexesForDatabase();
List <DefaultConstraint> databaseDefaultConstraints = GetDefaultConstraintsForDatabase();
List <ForeignKey> databaseForeignKeys = GetForeignKeysForDatabase();
conn.Open();
SqlCommand command = new SqlCommand(@"SELECT
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
NUMERIC_PRECISION,
CHARACTER_MAXIMUM_LENGTH,
columns.IS_NULLABLE,
columns.TABLE_SCHEMA as [SCHEMA],
sc.is_computed,
sc.is_rowguidcol,
sc.is_identity,
TABLES.TABLE_TYPE as TABLE_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS columns
LEFT JOIN INFORMATION_SCHEMA.TABLES on INFORMATION_SCHEMA.TABLES.TABLE_NAME = columns.TABLE_NAME
AND INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = columns.table_schema
LEFT JOIN sys.columns sc on object_name(sc.object_id) = columns.TABLE_NAME
AND OBJECT_SCHEMA_NAME(sc.object_id) = columns.table_schema
AND sc.name = COLUMN_NAME
WHERE
(TABLES.TABLE_TYPE = 'BASE TABLE' OR TABLES.TABLE_TYPE = 'VIEW')
AND columns.TABLE_SCHEMA <> 'SYS'
AND INFORMATION_SCHEMA.TABLES.TABLE_NAME <> '__EFMigrationsHistory'
ORDER BY [SCHEMA],columns.TABLE_NAME,columns.COLUMN_NAME", conn);
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string table = reader["TABLE_NAME"].ToString();
string schema = reader["SCHEMA"].ToString();
Column column = new Column();
column.Name = reader["COLUMN_NAME"].ToString();
column.DataType = reader["DATA_TYPE"].ToString();
column.NumericPrecision = String.IsNullOrEmpty(reader["NUMERIC_PRECISION"].ToString()) ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]);
column.MaxLength = String.IsNullOrEmpty(reader["CHARACTER_MAXIMUM_LENGTH"].ToString()) ? 0 : Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
column.IsNullable = reader["IS_NULLABLE"].ToString() == "YES" ? true : false;
bool isComputed = Convert.ToBoolean(reader["IS_COMPUTED"].ToString());
bool isRowGuidColumn = Convert.ToBoolean(reader["is_rowguidcol"].ToString());
bool isIdentity = Convert.ToBoolean(reader["is_identity"].ToString());
if (isComputed || isRowGuidColumn || isIdentity || column.DataType.ToUpper() == "TIMESTAMP")
{
column.DatabaseGenerated = true;
}
var existingSchema = container.Schemas.FirstOrDefault(x => x.Name == schema);
if (existingSchema == null)
{
Schema newSchema = new Schema
{
Name = schema,
ForeignKeys = databaseForeignKeys.Where(f => f.Columns.Any(c => c.ForeignKeySchemaName == schema)).ToList()
};
var existingTable = newSchema.Tables.FirstOrDefault(x => x.Name == table);
if (existingTable == null)
{
Table newTable = new Table();
var tableType = reader["TABLE_TYPE"].ToString();
if (tableType.ToUpper() == "VIEW")
{
newTable.IsView = true;
}
newTable.Name = table;
newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
newTable.Columns.Add(column);
newSchema.Tables.Add(newTable);
}
else
{
existingTable.Columns.Add(column);
}
container.Schemas.Add(newSchema);
}
else
{
var existingTable = existingSchema.Tables.FirstOrDefault(x => x.Name == table);
if (existingTable == null)
{
Table newTable = new Table();
var tableType = reader["TABLE_TYPE"].ToString();
if (tableType.ToUpper() == "VIEW")
{
newTable.IsView = true;
}
newTable.Name = table;
newTable.Columns.Add(column);
newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
existingSchema.Tables.Add(newTable);
}
else
{
existingTable.Columns.Add(column);
}
}
}
}
return(container);
}
public void SqlConnectionStringBuilder_Test()
{
var csb = new SqlConnectionStringBuilder("Server=localhost; Database=Hinnova; Trusted_Connection=True;");
csb["Database"].ShouldBe("Hinnova");
}
