PowerShell function to connect to MSSQL Database

June 4th at 12:00am

As part of an on-going automation effort I had need recently to have PowerShell connect to a Database and add a few entries. This could not have been easily accomlished with a typical .sql script since I needed to base the new entries off of dynamically generated  entries in another table.

I didn't want to write a C# Console app as I may need to tweak the script on machines without visual studio from time to time (PowerShell is installed on almost all windows machines).

Notes:

 

Here is the method that I use in my scripts, which is based almost entirely on the script by Pete Zerger. I made three small changes:

  • Encapsulation. Basically I just made it a function
  • Credentials. Instead of using Integrated security my function takes an SQL username/password
  • Port. This function will call an SQL Server instance on the port that you specify

 

Here is the function:

# This function executes a query against the specified DB
function ExecuteQuery($server, $port, $db, $user, $password, $query)
{    
    $connString = "Server = $server, $port; Database = $db; Integrated Security = False; User ID=$user; Password=$password"
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $connString
 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $query
    $SqlCmd.Connection = $SqlConnection
 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
 
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
 
    $SqlConnection.Close()
 
    $DataSet.Tables[0]
    return $DataSet
}

 

Since I had a little trouble working with the extracted data, here's an example of how to use the function:

#
# Define the query (and other required parameters)
# Execute the Query against the specified database
#
$SqlQuery = "select top 1 * from table where Parent = 'bob'"
$dtResponse = ExecuteQuery $SqlServer $SqlServerPort $DirDB $SqlUser $SqlPass $SqlQuery

#
# Parse the result until we find the first data-row
#     Something that I noticed is that not all 'entries' in the response are data rows
#     This is why I have this foreach check to 'filter' out the other entries
# 
$dtRow = $null
foreach ($row in $dtResponse)
{
    if( $row.GetType().FullName -eq "System.Data.DataRow")
    {
        $dtRow = $row
        break
    }
}

#
# Extract the desired information from the specified columns of the result row
#     The data can be used in another part of the script
# 
$GUID_Identifier = $dtRow['GUID'].ToString()
$Object_Revision = $dtRow['Revision'].ToString()

 

In my case I just need access to a row of a certain 'type' to capture some generic identifier and revision information. The method I'm using to sort the result rows feels kind of primitive- I'm looking for a cleaner way to work with result data tables in PowerShell