This function will let you create a database connection object for connecting to a Microsoft SQL Server database. It supports the usual parameters; DatabaseServer, DatabaseName, Port and Credential and will return a database connection object.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function New-MsSqlConnection { | |
| <# | |
| .SYNOPSIS | |
| Create a new MSSQL database connection. | |
| .DESCRIPTION | |
| This function will create a new Microsoft SQL Server database connection and return a database connection object. | |
| .EXAMPLE | |
| New-MsSqlConnection -DatabaseServer dbServer01 -DatabaseName 'myDB' | |
| .NOTES | |
| Author: Øyvind Kallstad | |
| Date: 22.01.2015 | |
| Version: 1.0 | |
| #> | |
| [CmdletBinding()] | |
| param ( | |
| # Name of server or instance. | |
| [Parameter(Mandatory = $true, Position = 0)] | |
| [Alias('Server','ComputerName','dbServer')] | |
| [string] $DatabaseServer, | |
| # Name of database. | |
| [Parameter(Mandatory = $true, Position = 1)] | |
| [string] $DatabaseName, | |
| # Port to connect to. Default is 1433. | |
| [Parameter()] | |
| [int] $Port, | |
| # Credential, if not using trusted connection. | |
| [Parameter(Mandatory = $false)] | |
| [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty | |
| ) | |
| try { | |
| # start building the connection string | |
| $connectionStringBuilder = New-Object 'System.Data.SqlClient.SqlConnectionStringBuilder' | |
| if($Port) {$DatabaseServer = "$($DatabaseServer),$($Port);"} | |
| $connectionStringBuilder.Server = $DatabaseServer | |
| $connectionStringBuilder.Database = $DatabaseName | |
| # if credential parameter is not used, trusted connection will be used | |
| if (-not($PSBoundParameters['Credential'])) { | |
| $connectionStringBuilder.Trusted_Connection = $true | |
| } | |
| # otherwise create a sqlcredential object | |
| else { | |
| $Credential.Password.MakeReadOnly() | |
| $sqlCred = New-Object 'System.Data.SqlClient.SqlCredential' -ArgumentList ($Credential.UserName,$Credential.Password) | |
| } | |
| # create database connection | |
| $dbConnection = New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $connectionStringBuilder.ConnectionString | |
| # if needed, add the sqlcredential object | |
| if($PSBoundParameters['Credential']) { | |
| $dbConnection.Credential = $sqlCred | |
| } | |
| # open the connection | |
| [void]$dbConnection.Open() | |
| # return the connection object | |
| Write-Output $dbConnection | |
| } | |
| catch { | |
| Write-Warning "At line:$($_.InvocationInfo.ScriptLineNumber) char:$($_.InvocationInfo.OffsetInLine) Command:$($_.InvocationInfo.InvocationName), Exception: '$($_.Exception.Message.Trim())'" | |
| } | |
| } |
