public void Write_Schema_Collections_To_Html_Files() { var sut = new SchemaCollectionsProvider(); var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"]; using (var conn = new CrmDbConnection(connectionString.ConnectionString)) { WriteDataTableToHtmlFile("MetaDataCollections", sut.GetMetadataCollections()); WriteDataTableToHtmlFile("Restrictions", sut.GetRestrictions()); WriteDataTableToHtmlFile("DataSourceInformation", sut.GetDataSourceInfo(conn)); //WriteDataTableToHtmlFile("DataTypes", connection); WriteDataTableToHtmlFile("ReservedWords", sut.GetReservedWords()); // WriteDataTableToHtmlFile("Databases", sut.getdata); // WriteDataTableToHtmlFile("Schemata", sut.GetSchema(conn, "Schemata", null)); WriteDataTableToHtmlFile("Tables", sut.GetTables(conn, null)); WriteDataTableToHtmlFile("Columns", sut.GetColumns(conn, null)); WriteDataTableToHtmlFile("Views", sut.GetViews(conn, null)); WriteDataTableToHtmlFile("Users", sut.GetUsers(conn, null)); WriteDataTableToHtmlFile("Indexes", sut.GetIndexes(conn, null)); WriteDataTableToHtmlFile("IndexColumns", sut.GetIndexColumns(conn, null)); // WriteDataTableToHtmlFile("Constraints", sut.get); // WriteDataTableToHtmlFile("PrimaryKey", sut.pr(); // WriteDataTableToHtmlFile("UniqueKeys", sut.Get); WriteDataTableToHtmlFile("ForeignKeys", sut.GetForeignKeys(conn, null)); WriteDataTableToHtmlFile("UniqueKeys", sut.GetUniqueKeys(conn, null)); // WriteDataTableToHtmlFile("ConstraintColumns", sut.get); } }
public void Should_Be_Able_To_Get_IndexColumns_For_Table_And_ColumnName(string tableName, string columnName) { // Arrange var sut = new SchemaCollectionsProvider(); var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"]; using (var conn = new CrmDbConnection(connectionString.ConnectionString)) { var restrictions = new string[] { null, null, tableName, null, columnName }; // Act var collection = sut.GetIndexColumns(conn, restrictions); // Assert Assert.That(collection, Is.Not.Null); Assert.That(collection.Columns, Is.Not.Null); Assert.That(collection.Rows.Count, Is.GreaterThan(0)); foreach (DataRow row in collection.Rows) { // <IndexColumns> // <constraint_catalog>PortalDarrellDev</constraint_catalog> // <constraint_schema>dbo</constraint_schema> // <constraint_name>PK__tmp_ms_x__3214EC0737311087</constraint_name> // <table_catalog>PortalDarrellDev</table_catalog> // <table_schema>dbo</table_schema> // <table_name>Table</table_name> // <column_name>Id</column_name> // <ordinal_position>1</ordinal_position> // <KeyType>36</KeyType> // <index_name>PK__tmp_ms_x__3214EC0737311087</index_name> //</IndexColumns> var val = AssertColVal(collection, row, "constraint_catalog"); Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName)); val = AssertColVal(collection, row, "constraint_schema"); Assert.That(val, Is.EqualTo("dbo")); var constraintName = AssertColVal(collection, row, "constraint_name"); Assert.IsFalse(string.IsNullOrEmpty((string)constraintName)); Assert.That((string)constraintName, Is.StringStarting("PK__")); Console.WriteLine(constraintName); val = AssertColVal(collection, row, "table_catalog"); Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName)); val = AssertColVal(collection, row, "table_schema"); Assert.That(val, Is.EqualTo("dbo")); val = AssertColVal(collection, row, "table_name"); Assert.That(val, Is.EqualTo(tableName)); Console.WriteLine(val); val = AssertColVal(collection, row, "column_name"); Assert.That(val, Is.EqualTo(columnName)); val = AssertColVal(collection, row, "ordinal_position"); Assert.That(val, Is.Not.EqualTo(default(int))); val = AssertColVal(collection, row, "KeyType"); Assert.That(val, Is.EqualTo(36)); // unique identifier. val = AssertColVal(collection, row, "index_name"); Assert.That(val, Is.EqualTo(constraintName)); } } }
public void WriteSchemaFilesForComparison() { PrepareTestData(); var sut = new SchemaCollectionsProvider(); _SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting"); _SqlLocalDbInstance.Start(); var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"]; var builder = GetStringBuilder(); using (var conn = new CrmDbConnection(connectionString.ConnectionString)) { // Output common schema collections (https://msdn.microsoft.com/en-us/library/ms254501(v=vs.110).aspx) // and also sql local db sql server schema collections (https://msdn.microsoft.com/en-us/library/ms254501(v=vs.110).aspx) using (SqlConnection connection = _SqlLocalDbInstance.CreateConnection()) { connection.Open(); // for each connection, we are writing its schema collection to CSV format for easy comparison. // We are writing sql server first, followed by crmado's. WriteDataTableToCsv("Sql MetaDataCollections", builder, connection.GetSchema("MetaDataCollections")); WriteDataTableToCsv("CrmAdo MetaDataCollections", builder, sut.GetMetadataCollections()); WriteDataTableToCsv("Sql DataSourceInformation", builder, connection.GetSchema("DataSourceInformation")); WriteDataTableToCsv("CrmAdo DataSourceInformation", builder, sut.GetDataSourceInfo(conn)); WriteDataTableToCsv("Sql DataTypes", builder, connection.GetSchema("DataTypes")); WriteDataTableToCsv("CrmAdo DataTypes", builder, sut.GetDataTypes()); WriteDataTableToCsv("Sql Restrictions", builder, connection.GetSchema("Restrictions")); WriteDataTableToCsv("CrmAdo Restrictions", builder, sut.GetRestrictions()); WriteDataTableToCsv("Sql ReservedWords", builder, connection.GetSchema("ReservedWords")); WriteDataTableToCsv("CrmAdo ReservedWords", builder, sut.GetReservedWords()); WriteDataTableToCsv("Sql Tables", builder, connection.GetSchema("Tables")); WriteDataTableToCsv("CrmAdo Tables", builder, sut.GetTables(conn, null)); WriteDataTableToCsv("Sql Columns", builder, connection.GetSchema("Columns")); WriteDataTableToCsv("CrmAdo Columns", builder, sut.GetColumns(conn, null)); WriteDataTableToCsv("Sql Views", builder, connection.GetSchema("Views")); WriteDataTableToCsv("CrmAdo Views", builder, sut.GetViews(conn, null)); WriteDataTableToCsv("Sql ViewColumns", builder, connection.GetSchema("ViewColumns")); WriteDataTableToCsv("CrmAdo View Columns", builder, sut.GetViewColumns(conn, null)); WriteDataTableToCsv("Sql Indexes", builder, connection.GetSchema("Indexes")); WriteDataTableToCsv("CrmAdo Indexes", builder, sut.GetIndexes(conn, null)); WriteDataTableToCsv("Sql IndexColumns", builder, connection.GetSchema("IndexColumns")); WriteDataTableToCsv("CrmAdo IndexColumns", builder, sut.GetIndexColumns(conn, null)); WriteDataTableToCsv("Sql ForeignKeys", builder, connection.GetSchema("ForeignKeys")); WriteDataTableToCsv("CrmAdo ForeignKeys", builder, sut.GetForeignKeys(conn, null)); WriteDataTableToCsv("Sql Users", builder, connection.GetSchema("Users")); WriteDataTableToCsv("CrmAdo Users", builder, sut.GetUsers(conn, null)); WriteDataTableToCsv("Sql Databases", builder, connection.GetSchema("Databases")); WriteDataTableToCsv("CrmAdo Databases", builder, sut.GetDatabases(conn, null)); } _SqlLocalDbInstance.Stop(); } // save the csv file to disk var path = System.IO.Path.Combine(Environment.CurrentDirectory, "schema comparison report" + ".csv"); System.IO.File.WriteAllText(path, builder.ToString()); Console.WriteLine("comparison report written to: " + path); }