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

#region the variables

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

# A scripts folder
$scriptFolder = "C:\Test\ScriptsFolder"
$FormattedFiles = "C:\Test\FormattedFiles\"
$FormatProfile = "C:\Users\Username\AppData\Roaming\Devart\dbForge Studio for SQL Server\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 data generation project file
# Note: .dgen file can be generated using the Data Generator feature
$generatorFile = "C:\Test\TestData\data.dgen"

# A filepath to a data import template 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