PoshCode Archive  Artifact [c825124050]

Artifact c825124050b72ddaf352792a69e132415b775453af8f9ea9a5aeba085b9c9815:

• File Invoke-SqlCmd2.ps1 — part of check-in [15a0dc019e] at 2018-06-10 13:13:57 on branch trunk — Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout. Put closing connection in finally block. (user: Chad Miller size: 1419)

# encoding: ascii
# api: powershell
# title: Invoke-SqlCmd2
# description: Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout. Put closing connection in finally block.
# version: 0.1
# type: function
# author: Chad Miller
# license: CC0
# function: Invoke-Sqlcmd2
# x-poshcode-id: 2665
# x-archived: 2012-05-11T08:16:25
# x-published: 2012-05-09T00:23:00
#
#
function Invoke-Sqlcmd2
{
    param
    (
        [string]$ServerInstance="localhost",
        [string]$Database,
        [string]$Query,
        [Int32]$CommandTimeout=30
    )
    
    $connectionString = "Server={0};Database={1};Integrated Security=True" -f $ServerInstance, $Database
    $connection = New-Object System.Data.SqlClient.SQLConnection($connectionString)
    
    try
    {
        $connection.Open()
        
        $command = $connection.CreateCommand()
        $command.CommandTimeout = $QueryTimeout
        $command.CommandType = [System.Data.CommandType]::Text
        $command.CommandText = $Query
        
        $dataSet = New-Object System.Data.DataSet
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
        [void]$dataAdapter.Fill($dataSet)
        $dataSet.Tables[0]
    }
    finally
    {
        if ($connection.State -eq [System.Data.ConnectionState]::Open)
        {
            $connection.Close()
        }
    }
}