/// <summary> /// Gets the list of database tables /// </summary> /// <returns>The list of table descriptions</returns> protected IList<Table> GetTables() { IList<Table> result = new List<Table>(); var connString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringNames.MySQLDB].ConnectionString; var databaseName = new MySqlConnectionStringBuilder(connString).Database; using (var connection = new MySqlConnection(connString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = " SELECT t.TABLE_NAME AS 'Table Name', (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_NAME = t.TABLE_NAME) AS 'Columns', (SELECT GROUP_CONCAT(CONSTRAINT_NAME SEPARATOR ', ') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_NAME = t.TABLE_NAME AND REFERENCED_TABLE_NAME IS NOT NULL) AS 'Foreign Keys' FROM information_schema.TABLES t WHERE (t.TABLE_TYPE = 'BASE TABLE') AND t.TABLE_SCHEMA = @SchemaName"; command.Parameters.Add(new MySqlParameter("@SchemaName", MySqlDbType.Text) { Value = databaseName }); var reader = command.ExecuteReader(); while (reader.Read()) { Table table = new Table() { Name = (string)reader["Table Name"], Columns = (reader["Columns"] != System.DBNull.Value) ? ((string)reader["Columns"]).Split(',').ToList() : new List<string>(), ForeignKeys = (reader["Foreign Keys"] != System.DBNull.Value) ? ((string)reader["Foreign Keys"]).Split(',').ToList() : new List<string>(), }; result.Add(table); } } return result; }
private void ValidateResult(IList<Table> tables) { // Category Table var expectedCategoryTable = new Table() { Name = "CATEGORY", Columns = new string[] { "category_id", "name", "version" }, }; ValidateTable(expectedCategoryTable, tables); // Category Product Table var expectedCategoryProductTable = new Table() { Name = "CATEGORY_PRODUCT", Columns = new string[] { "category_id", "product_id" }, }; ValidateTable(expectedCategoryProductTable, tables); // Customer Table var expectedCustomerTable = new Table() { Name = "CUSTOMER", Columns = new string[] { "customer_id", "first_name", "last_name", "code", "date_of_birth", "address_street", "address_number", "address_city_id", "version" }, }; ValidateTable(expectedCustomerTable, tables); // Product Table var expectedProductTable = new Table() { Name = "PRODUCT", Columns = new string[] { "product_id", "name", "description", "version" }, }; ValidateTable(expectedProductTable, tables); // Products Order Table var expectedProductsOrderTable = new Table() { Name = "PRODUCTS_ORDER", Columns = new string[] { "products_order_id", "date", "shipping_address_street", "shipping_address_number", "shipping_address_city_id", "customer_id", "version" }, }; ValidateTable(expectedProductsOrderTable, tables); // Products Order Items Table var expectedProductsOrderItemsTable = new Table() { Name = "PRODUCTS_ORDER_ITEMS", Columns = new string[] { "products_order_id", "item_index", "quantity", "price", "product_id" }, }; ValidateTable(expectedProductsOrderItemsTable, tables); // City Table var expectedCityTable = new Table() { Name = "CITY", Columns = new string[] { "city_id", "name", "zip_code_id", "state_id", "version" }, }; ValidateTable(expectedCityTable, tables); // City Table var expectedStateTable = new Table() { Name = "STATE", Columns = new string[] { "state_id", "name", "version" }, }; ValidateTable(expectedStateTable, tables); // Zip Code Table var expectedZipCodeTable = new Table() { Name = "ZIP_CODE", Columns = new string[] { "zip_code_id", "postal_code", "city_id", "version" }, }; ValidateTable(expectedZipCodeTable, tables); }
private void ValidateResult(IList<Table> tables) { // Category Table var expectedCategoryTable = new Table() { Name = "category", Columns = new string[] { "categoryId", "name", "version" }, }; ValidateTable(expectedCategoryTable, tables); // Category Product Table var expectedCategoryProductTable = new Table() { Name = "category_product", Columns = new string[] { "categoryId", "productId" }, }; ValidateTable(expectedCategoryProductTable, tables); // Customer Table var expectedCustomerTable = new Table() { Name = "customer", Columns = new string[] { "customerId", "firstName", "lastName", "code", "dateOfBirth", "address_street", "address_number", "address_cityId", "version" }, }; ValidateTable(expectedCustomerTable, tables); // Product Table var expectedProductTable = new Table() { Name = "product", Columns = new string[] { "productId", "name", "description", "version" }, }; ValidateTable(expectedProductTable, tables); // Products Order Table var expectedProductsOrderTable = new Table() { Name = "products_order", Columns = new string[] { "productsOrderId", "date", "shippingAddress_street", "shippingAddress_number", "shippingAddress_cityId", "customerId", "version" }, }; ValidateTable(expectedProductsOrderTable, tables); // Products Order Items Table var expectedProductsOrderItemsTable = new Table() { Name = "products_order_items", Columns = new string[] { "productsOrderId", "itemIndex", "quantity", "price", "productId" }, }; ValidateTable(expectedProductsOrderItemsTable, tables); // City Table var expectedCityTable = new Table() { Name = "city", Columns = new string[] { "cityId", "name", "zipCodeId", "stateId", "version" }, }; ValidateTable(expectedCityTable, tables); // City Table var expectedStateTable = new Table() { Name = "state", Columns = new string[] { "stateId", "name", "version" }, }; ValidateTable(expectedStateTable, tables); // Zip Code Table var expectedZipCodeTable = new Table() { Name = "zip_code", Columns = new string[] { "zipCodeId", "postalCode", "cityId", "version" }, }; ValidateTable(expectedZipCodeTable, tables); }
private void ValidateTable(Table expectedTable, IList<Table> result) { // Table & Name var actualTable = result.SingleOrDefault(t => t.Name.Equals(expectedTable.Name)); Assert.IsNotNull(actualTable); // Columns Assert.AreEqual(expectedTable.Columns.Count, actualTable.Columns.Count); Assert.IsTrue(expectedTable.Columns.All(c => actualTable.Columns.Contains(c))); }
/// <summary> /// Gets the list of database tables /// </summary> /// <returns>The list of table descriptions</returns> protected IList<Table> GetTables() { IList<Table> result = new List<Table>(); var connString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringNames.SQLServerDB].ConnectionString; using (var connection = new SqlConnection(connString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "SELECT t.Table_Schema, t.Table_Name, (SELECT STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = t.TABLE_NAME FOR XML PATH('')) ,1,1,'')) as Column_Names, (SELECT STUFF((SELECT ',' + [name] FROM sysobjects WHERE [xtype] = 'PK'AND [parent_obj] = OBJECT_ID(t.Table_Schema + '.' + t.Table_Name) FOR XML PATH('')) ,1,1,'')) as Primary_Keys, (SELECT STUFF((SELECT ',' + [name] FROM sysobjects WHERE [xtype] = 'F'AND [parent_obj] = OBJECT_ID(t.Table_Schema + '.' + t.Table_Name) FOR XML PATH('')) ,1,1,'')) as Foreign_Keys FROM information_schema.tables t WHERE table_type = 'BASE TABLE' ORDER BY t.Table_Name"; var reader = command.ExecuteReader(); while (reader.Read()) { Table table = new Table() { Schema = (string)reader["Table_Schema"], Name = (string)reader["Table_Name"], Columns = (reader["Column_Names"] != System.DBNull.Value) ? ((string)reader["Column_Names"]).Split(',').ToList() : new List<string>(), PrimaryKey = (reader["Primary_Keys"] != System.DBNull.Value) ? ((string)reader["Primary_Keys"]).Split(',').FirstOrDefault() : default(string), ForeignKeys = (reader["Foreign_Keys"] != System.DBNull.Value) ? ((string)reader["Foreign_Keys"]).Split(',').ToList() : new List<string>(), }; result.Add(table); } } return result; }