powershell database connection utilities

psdbutils.ps1

<#
.DESCRIPTION
Create a database connection and add extension methods (script methods) that can load and
save a System.Data.Dataset

.PARAMETER ConnectionString
Connection string to database.

.EXAMPLE
Open connection, load data, update and create new records.

$conn = Get-DatabaseConnection "Data Source=localhost; Initial Catalog=MyDatabase; Integrated security=true"
$ds = $conn.GetDataSet("select Id, Name from Users")
$dt = $ds.Tables[0]
$dt.Rows[0].Name = "New name"
$newUser = $dt.NewRow()
$newUser.Name = "Daniel"
$dt.Rows.Add($newUser)
$conn.SaveDataSet($ds)
$conn.Close()
#>
function Get-DatabaseConnection($ConnectionString)
{
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $connection.Open()
    $connection | Add-Member ScriptMethod GetDataSet { 
        param([string]$sql) 
        $dataset = New-Object System.Data.Dataset 
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sql, $this 
        $dataAdapter.Fill($dataset) | out-null
        $dataset.ExtendedProperties["sql"]= $sql 
        $dataset
    }

    $connection | Add-Member ScriptMethod SaveDataSet { 
        param([System.Data.DataSet]$dataset)
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $dataset.ExtendedProperties["sql"], $this
        $commandBuilder = New-Object System.Data.SqlClient.SqlCommandBuilder $dataAdapter 
        $dataAdapter.UpdateCommand = $commandBuilder.GetUpdateCommand() 
        $dataAdapter.InsertCommand = $commandBuilder.GetInsertCommand() 
        $dataAdapter.DeleteCommand = $commandBuilder.GetDeleteCommand() 
        $dataAdapter.Update($dataset) 
    }

    $connection
}