This topic provides an example of using PowerShell Cmdlets for organizing extended Continuous Integration process that includes the following stages:
#region the variables
$serverName = "(LocalDb)\MSSQLLocalDB"
$databaseName = "TestDB"
# A script folder
$scriptFolder = "C:\Test\ScriptsFolder"
# 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 $migrationScriptsFolder
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