Build CI/CD pipelines using Jenkins and PowerShell

**Full script**

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


# A script folder
$scriptFolder = "D:\Test\ScriptsFolder"


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

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

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


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


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


Function check {
		param($result)

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

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

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)

# 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)
~~~

In the above example, the PowerShell script consists of the following segments:

**Database connection settings**

~~~~powershell
$connection = New-DevartSqlDatabaseConnection -Server $serverName -Database $databaseName -WindowsAuthentication $true
~~~~
 
* *Server* is a name or a network address of the SQL Server instance to connect to.

* *Database* is a database to connect to on the server specified in the Server parameter.

* The *WindowsAuthentication* option allows a user to connect through a Windows user account to a server.

**Connection testing**

~~~powershell
$result = Test-DevartDatabaseConnection -Connection $connection;
~~~~
   
* *Connection* is the *DevartDatabaseConnectionInfo* or a connection string that identifies the connection to test.


**Database deployment  from a Scripts Folder**

~~~powershell
$project = Invoke-DevartDatabaseBuild -SourceScriptsFolder $scriptFolder -Connection $connection
~~~~

* *SourceScriptsFolder* is a path to the folder with scripts which will be used to build a database.

* *Connection* is a connection to the server where a database will be built.

**Creating unit tests on a database**

~~~powershell
$result = Invoke-DevartExecuteScript -Input $unitTestsFolder -Connection $connection
~~~~

* *Input* is a full path to the folder with unit tests.

* *Connection* specifies a connection to the server to execute the script on.

**Populating a database with sample data and running unit tests**

~~~powershell
$result = Invoke-DevartDatabaseTests -InputObject $connection -InstalltSQLtFramework -IncludeTestData -DataGeneratorProject $generatorFile -OutReportFileName $testReportOutputFileName -ReportFormat JUnit -RewriteReport
~~~~ 

* *InputObject* is a database to run the tSQLt tests on. It can be a Scripts folder, a NuGet package, or a database connection.

* *InstalltSQLtFramework* installs tSQLtFramework before executing tests.

* IncludeTestData*, along with the *DataGeneratorProject* parameter, adds test data to the database.

* *DataGeneratorProject* specifies a Data Generator project file (.dgen) that defines the data to be added to the test database.

* *OutReportFileName* indicates a file path to export the test results to.

* *ReportFormat* specifies the format of the test results file.

* *RewriteReport* rewrites a report file.

**Publishing a NuGet package**

~~~powershell
Publish-DevartDatabaseProject -Project $project -Repository $publishOutputFolder -AutoIncrementVersion
~~~
  
* *Project* is an instance of a DatabaseProject object to be published. This object is produced by the Invoke-DevartDatabaseBuild and Set-DevartPackageInfo cmdlets.
  
* *Repository* is a NuGet feed that the database build artifact will be published to. It must be a valid URL or local path to a NuGet feed.

* *AutoIncrementVersion* auto-increments the package version if a package with the specified version already exists on the NuGet repository.