Organize an extended Continuous Integration process with Data Import and formatting of SQL files

This topic provides an example of using PowerShell cmdlets for organizing an extended Continuous Integration process that includes the following stages:

Creating a database connection.
Testing the database connection.
Creating a database using the ‘state based’ method. This method allows creating (or updating) a database with the help of the scripts ‘from scratch’. That is a database is specified in the script “as it has to be”; herewith, practically all scripts are stored as the CREATE instructions.
Restoring unit tests on the database.
Filling the database with sample data using Data Generator.
Filling the database using data import from a file.
Running the tSQLt unit tests.
Formatting the scripts.
Publishing the package.

#region the variables

$serverName = "(LocalDb)\MSSQLLocalDB"
$databaseName = "TestDB"

# A script folder
$scriptFolder = "C:\Test\ScriptsFolder"
$FormattedFiles = "C:\Test\FormattedFiles\"
$FormatProfile = "C:\Users\Username\AppData\Roaming\Devart\dbForge SQL Complete\FormatProfiles\CustomProfile.xml"

# A folder where the sql unit-tests are located, for example:
# ...\Test1.sql
# ...\Test2.sql
# ...\Test4.sql
$unitTestsFolder = "C:\Test\UnitTests"

# A filepath to a datagenerator file
$generatorFile = "C:\Test\TestData\data.dgen"

# A filepath to a dataimport file
$importDataFileName = "C:\Test\CSV\sample_data.csv"
$importTemplateFileName = "C:\Test\CSV\template.dit"

# An output report file for unit tests
$testReportOutputFileName = "C:\Test\TestReport\report.xml"

# A file that is created When publishing
$publishFileName = "Test.DevOpsAutomation.Database"

# A path for publishing
$publishOutputFolder = "C:\Test\NuGet\Repository"

#endregion

#region the check function

Function check {
    param($result)

    if ($result) {
        Write-Host "Success" -ForegroundColor Green; Write-host ""
    }
    else {
        Write-Host "Failed" -ForegroundColor Red;
        Exit(1)
    }
}

#endregion

#region CI pipeline

# Create database connection
Write-Host "Creating database connection..."
$connection = New-DevartSqlDatabaseConnection -Server $serverName -Database $databaseName -WindowsAuthentication $true

# Test database connection
Write-Host "Testing database connection..."
$result = Test-DevartDatabaseConnection -Connection $connection;
check($result)

# Recreating database
Write-Host "Recreating database..."
$project = Invoke-DevartDatabaseBuild -SourceScriptsFolder $scriptFolder -Connection $connection
check($result)

# Restore unit tests on the database
Write-Host "Restoring unit tests..."
$result = Invoke-DevartExecuteScript -Input $unitTestsFolder -Connection $connection
check($result)

# Fill database with sample data and run unit tests
Write-Host "Filling database with sample data and running unit tests..."
$result = Invoke-DevartDatabaseTests -InputObject $connection -InstalltSQLtFramework -IncludeTestData -DataGeneratorProject $generatorFile -OutReportFileName $testReportOutputFileName -ReportFormat JUnit -RewriteReport
check($result)

# Import sample data from a file to certain table
Write-Host "Importing sample data from a file..."
$result = Invoke-DevartDataImport -InputFile $importDataFileName -TemplateFile $importTemplateFileName
check($result)

# Format scripts
Write-Host "Formatting migration scripts..."
$Result = Invoke-DevartFormatScript -Source $scriptFolder -FileExtension *.sql -IncludeSubfolders -Output $FormattedFiles -Profile $FormatProfile
check($result)

# Publish project
Write-Host "Publishing project..."
Set-DevartPackageInfo -Project $project -Id $publishFileName -Version 3.0.0
Publish-DevartDatabaseProject -Project $project -Repository $publishOutputFolder -AutoIncrementVersion
check($result)

#endregion