As a DBA working daily with SQL Servers I use Powershell from time to time to access my sql servers mostly for automation tasks but there are plenty of other reasons as well.
In order to shorten my Powershell code and simplify my work I have created the bellow Powershell function years a go and I find it very much convenience. The function handles the authentication type internally so we also don’t need the if…else blocks of code in our script to determine the authentication type and act accordingly. The function utilizes the .net System.Data.SqlClient Namespace
In the bellow 2 code snippets you will find:
- The Exec-Sql Powershell function
- An example of how to use the function
One point that I would like to point out is that I usually dot source a helper file with all my functions. This makes the functions available in the scope of my Powershell scripts while eliminating the need to stick them in each and every script making the scripts shorter and allow me to have a single copy of the functions instead of multiple copies all around. An alternate option would be deploying the functions as Powershell modules.
For more information about dot sourcing see the section “Script scope and dot sourcing” in this this kb.
Exec-Sql – A Powershell function to simplify sql server data access
<#
.Synopsis
Executes sql command
.DESCRIPTION
A generic code to execute sql commands
.EXAMPLE
ExecuteScalar
$command_type = 'Scalar';
$val = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;
DataSet
$command_type = 'DateSet';
$ds = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;
#>
function Exec-Sql
{
[CmdletBinding()]
[Alias()]
#[OutputType([int])]
Param
(
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=0)]
[string]$Server,
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=1)]
[string]$Database,
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=2)]
[string]$CommandText,
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=3)]
[string]$CommandType,
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=4)]
[bool]$IntegratedSecurity,
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=5)]
[System.Data.SqlClient.SqlCredential]$Credentials,
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=6)]
[int32]$CommandTimeOut = 30,
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=7)]
[string]$ApplicationName = "CloudMonitoring",
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=8)]
[string]$ApplicationIntent
)
Begin
{
$ConnectionString = "Server=$Server; Database=$Database; Integrated Security=$IntegratedSecurity; Application Name=$ApplicationName;";
if($CommandType -notin ('NonQuery' ,'Scalar' ,'DataSet') )
{
throw 'The ''$CommandType'' parameter contains an invalid value Valid values are: ''NonQuery'' ,''Scalar'' ,''DataSet''';
}
try
{
if ($windows_authentication)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
}
else
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString, $Credentials);
$SqlConnection.Credential = $Credentials;
}
$SqlCommand = $sqlConnection.CreateCommand();
$SqlConnection.Open();
$SqlCommand.CommandText = $CommandText;
# NonQuery
if($CommandType -eq 'NonQuery')
{
$sqlCommand.ExecuteNonQuery();
return;
}
# Scalar
if($CommandType -eq 'Scalar')
{
$Val = $sqlCommand.ExecuteScalar();
return $Val;
}
# DataSet
if($CommandType -eq "DataSet")
{
$DataSet = New-Object System.Data.DataSet;
$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlDataAdapter.SelectCommand = $SqlCommand;
$SqlDataAdapter.Fill($DataSet);
return $DataSet;
}
}
catch
{
Throw;
}
finally
{
#$SqlConnection.Close();
$SqlConnection.Dispose();
#[System.Data.SqlClient.SqlConnection]::ClearAllPools();
}
}
}The bellow code snippet shows how to use the exec-sql function for 3 types of sql server operations: retrieving a data set, a scalar value and performing an operation that changes the data (insert, update, delete, merge, select…into).
The current code example uses Windows Authentication (IntegratedSecurity) so that control flow does not get into the “if (-not $windows_authentication)” block. However, if you use SQL Authentication then you need to have the sql login password stored as an encrypted string.
The bellow example creates the encrypted password file C:\temp\sql_password.txt based on your input from the Read-Host command.
An example of how to create an encrypted password file to be used with sql authentication
# Generate the sql_password as an encrypted secure string $secure_string = Read-Host -AsSecureString; $encrypted = ConvertFrom-SecureString -SecureString $secure_string; $encrypted | Set-Content 'C:\temp\sql_password.txt';
An example of how to use the exec-sql Powershell function
#region <database>
[string]$server = $env:COMPUTERNAME;
[string]$database = 'master';
[bool]$windows_authentication = $true;
if (-not $windows_authentication)
{
[string]$sql_user = 'Yaniv';
[string]$sql_password_full_name = Join-Path $PSScriptRoot 'sql_password.txt';
$password = Get-Content $sql_password_full_name | ConvertTo-SecureString;
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sql_user, $password;
$cred.Password.MakeReadOnly();
$credentials = New-Object System.Data.SqlClient.SqlCredential($cred.username, $cred.password);
}
#endregion
[string]$query = 'SELECT @@SERVERNAME AS server_name;';
# Execute NonQuery
[string]$command_type = 'NonQuery';
Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;
# Execute Scalar
[string]$command_type = 'Scalar';
$val = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;
Write-Host -ForegroundColor Green $val;
# Execute DataSet
[string]$command_type = 'DataSet';
$ds = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;
Write-Host -ForegroundColor Yellow $ds.Tables[0].Rows[0].Column1;
# Iterate through the data table
foreach ($row in $ds.Tables[0].Rows)
{
$server_name = $row.Item('server_name');
}
Here is how I dot source my functions file in my scripts.
$PSScriptRoot is a Powershell variable that contains the full path of the executing script’s parent directory so all we have to do is have the script file and the helpers.ps1 file conitaing the function at the same directory.
The first line assigns the variable $helpers_file_full_name the full file name (path + file) of the helpers.ps1 file and the second line dot sources the variable.
[string]$helpers_file_full_name = Join-Path $PSScriptRoot 'helpers.ps1'; . $helpers_file_full_name;
Latest posts by Yaniv Etrogi (see all)
- Monitor AlwaysOn Availabilty Groups - July 6, 2023
- SQL Server – The secret index syntax - February 8, 2023
- Use .net SqlClient with Powershell to access data - January 25, 2023
- Use Powershell to find unused resources in Azure - August 5, 2022