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