C# (CSharp) DiscoveredDatabase.ExpectTableValuedFunction Beispiele

Beispiel #1

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

Beispiel #2

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