Build CI/CD pipelines using Jenkins and PowerShell

$serverName = "dbfsqlsrv\SQL2025"
$databaseName = "DemoAccounting"


# 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 file path to a datagenerator file
$generatorFile = "c:\Test\TestData\data.dgen"

# 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"


Function check {
		param($result)

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

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

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

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 the 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)

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