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_ForeignKeys_For_A_Table(string tableName) { // 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 }; // Act var collection = sut.GetForeignKeys(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) { 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")); val = AssertColVal(collection, row, "constraint_name"); Assert.IsFalse(string.IsNullOrEmpty((string)val)); Console.WriteLine(val); 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(tableName); val = AssertColVal(collection, row, "constraint_type"); Assert.That(val, Is.EqualTo("FOREIGN KEY")); val = AssertColVal(collection, row, "is_deferrable"); Assert.That(val, Is.EqualTo("NO")); val = AssertColVal(collection, row, "initially_deferred"); Assert.That(val, Is.EqualTo("NO")); } } }
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); }