Simple Powershell database script

I know that sql server 2008 has a powershell scriptlet that does something similar to this.
However this works without any additional code.
It also works really well in a psake script.
This is a sql script runner that treats each file as a single group of transactions.
$a = new-module script-block {
function Exec-Sql
{
param([System.String] $filename, [System.String] $connectionString)
if (![System.IO.File]::Exists($filename))
{
Write-Host “No such file as $filename”
}
else
{
Write-Host $filename
$connection = new-object System.Data.SqlClient.SqlConnection -argumentList $connectionString
$connection.Open()
$command = “”
foreach ($str2 in [System.IO.File]::ReadAllLines($filename))
{
if (($str2.Trim().ToLower() -eq “go”))
{
$cmd = $connection.CreateCommand();
$cmd.CommandText = $command
$b=$cmd.ExecuteNonQuery()
$command = “”
}
else
{
$command += “`n$str2”
}
}
if ($command -ne “”)
{
$cmd = $connection.CreateCommand();
$cmd.CommandText = $command
$b=$cmd.ExecuteNonQuery()
}
$connection.Close();
}
}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s