public void Create(DiscoveredDatabase databaseICanCreateRandomTablesIn, ICatalogueRepository catalogueRepository) { CreateFunctionSQL = @" if exists (select 1 from sys.objects where name = 'MyAwesomeFunction') drop function MyAwesomeFunction GO CREATE FUNCTION MyAwesomeFunction ( -- Add the parameters for the function here @startNumber int , @stopNumber int, @name varchar(50) ) RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here Number int, Name varchar(50) ) AS BEGIN -- Fill the table variable with the rows for your result set DECLARE @i int; set @i = @startNumber while(@i < @stopNumber) begin INSERT INTO @ReturnTable(Name,Number) VALUES (@name,@i); set @i = @i + 1; end RETURN END "; using (var con = databaseICanCreateRandomTablesIn.Server.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(CreateFunctionSQL, con); } var tbl = databaseICanCreateRandomTablesIn.ExpectTableValuedFunction("MyAwesomeFunction"); TableValuedFunctionImporter importer = new TableValuedFunctionImporter(catalogueRepository, tbl); importer.DoImport(out TableInfoCreated, out ColumnInfosCreated); importer.ParametersCreated[0].Value = "5"; importer.ParametersCreated[0].SaveToDatabase(); importer.ParametersCreated[1].Value = "10"; importer.ParametersCreated[1].SaveToDatabase(); importer.ParametersCreated[2].Value = "'fish'"; importer.ParametersCreated[2].SaveToDatabase(); ForwardEngineerCatalogue forwardEngineerCatalogue = new ForwardEngineerCatalogue(TableInfoCreated, ColumnInfosCreated, true); forwardEngineerCatalogue.ExecuteForwardEngineering(out Cata, out CataItems, out ExtractionInformations); }
public void EndToEndTest() { var cohortDatabaseNameWillBe = TestDatabaseNames.GetConsistentName("TbvCohort"); _discoveredCohortDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(cohortDatabaseNameWillBe); //cleanup if (_discoveredCohortDatabase.Exists()) { _discoveredCohortDatabase.Drop(); } //create a normal catalogue CreateANormalCatalogue(); //create a cohort database using wizard CreateNewCohortDatabaseWizard cohortDatabaseWizard = new CreateNewCohortDatabaseWizard(_discoveredCohortDatabase, CatalogueRepository, DataExportRepository, false); _externalCohortTable = cohortDatabaseWizard.CreateDatabase( new PrivateIdentifierPrototype(_nonTvfExtractionIdentifier) , new ThrowImmediatelyCheckNotifier()); //create a table valued function CreateTvfCatalogue(cohortDatabaseNameWillBe); //Test 1 TestThatQueryBuilderWithoutParametersBeingSetThrowsQueryBuildingException(); PopulateCohortDatabaseWithRecordsFromNonTvfCatalogue(); //Test 2 TestWithParameterValueThatRowsAreReturned(); //Test 3 TestUsingTvfForAggregates(); //Test 4 TestAddingTvfToCIC(); //Test 5 TestDataExportOfTvf(); //tear down DataExportRepository.GetAllObjects <ExtractableCohort>().Single().DeleteInDatabase(); _externalCohortTable.DeleteInDatabase(); _database.ExpectTable("NonTVFTable").Drop(); _database.ExpectTableValuedFunction("GetTopXRandom").Drop(); //delete global parameter ((AnyTableSqlParameter)_aggregate.GetAllParameters().Single()).DeleteInDatabase(); //delete aggregate _aggregate.DeleteInDatabase(); ((AnyTableSqlParameter)_cicAggregate.GetAllParameters().Single()).DeleteInDatabase(); //delete aggregate _cicAggregate.DeleteInDatabase(); //get rid of the cohort identification configuration _cic.DeleteInDatabase(); _pipe.DeleteInDatabase(); //get rid of the cohort database _discoveredCohortDatabase.Drop(); _nonTvfCatalogue.DeleteInDatabase(); _nonTvfTableInfo.DeleteInDatabase(); _tvfCatalogue.DeleteInDatabase(); _tvfTableInfo.DeleteInDatabase(); }