Query Microsoft SQL Server from Powershell

Issue:

I needed a good example on how to query Microsoft SQL Server from powershell.  I ended up writing this code below.

 

Solution:

function ExecuteReader-SQL($sqlText, $database, $server, $parameters=@{})
{
    #Execute a sql statement.  Parameters are allowed.
    #Input parameters should be a dictionary of parameter names and values.
    #Return value will usually be a list of datarows.
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $results = @()
    Try
    {
        $connection.Open();
        $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

        foreach($p in $parameters.Keys)
        {
            [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
        }
        $reader = $cmd.ExecuteReader()

        $results = @()
        while ($reader.Read())
        {
            $row = @{}
            for ($i = 0; $i -lt $reader.FieldCount; $i++)
            {
                $row[$reader.GetName($i)] = $reader.GetValue($i)
            }
            $results += new-object psobject -property $row            
        }
    }
    catch
    {
        $results = $Null
    }
    finally
    {
        $connection.Close();
    }

    $results
}


function ExecuteNonQuery-SQL($sqlText, $database, $server, $parameters=@{})
{
    #Execute a sql statement.  Parameters are allowed.
    #Input parameters should be a dictionary of parameter names and values.
    #Return value will usually be a list of datarows.
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $rowsAffected = 0
    try
    {
        $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

        $connection.Open();
        foreach($p in $parameters.Keys)
        {
            [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
        }
        $rowsAffected = $cmd.ExecuteNonQuery()
        
    }
    catch
    {
        $rowsAffected = -1
    }
    finally
    {
        $connection.Close();
    }
    $rowsAffected
}


#Example Usage
#$query = 'EXECUTE dbo.spGetSomeRecords'
#$params = @{};
#$params.Add($key, $value)
#$rows = ExecuteReader-SQL -sqlText $query -database 'SOMEDATABASE' -server 'MSDATABASESERVER' -parameters $params

Resources:

http://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell

 

Leave a Reply

%d bloggers like this: