Powershell DB connections

Here is my configuration file. I call it dbConstants.ps1. In this example, it's currently configured to talk to a MySQL database server on the local host. Examples for MS-SQL and MS-Access are included.

#
# dbConstants.ps1
#

Set-Variable dbAccess  -option ReadOnly -value 1
Set-Variable dbMSSQL  -option ReadOnly -value 2
Set-Variable dbMySQL  -option ReadOnly -value 3

#
# Microsoft Office Access Example
#
#Set-Variable dbChoice -option ReadOnly -value $dbAccess
#Set-Variable dbName   -option ReadOnly -value Test.mdb
#Set-Variable dbSource -option ReadOnly -value Not-Applicable
#Set-Variable dbUser   -option ReadOnly -value Not-Applicable
#Set-Variable dbPass   -option ReadOnly -value Not-Applicable

#
# Microsoft SQL Server Example
#
#Set-Variable dbChoice -option ReadOnly -value $dbMSSQL
#Set-Variable dbName   -option ReadOnly -value Test
#Set-Variable dbSource -option ReadOnly -value .\SQLExpress
#Set-Variable dbUser   -option ReadOnly -value Not-Applicable
#Set-Variable dbPass   -option ReadOnly -value Not-Applicable

#
# MySQL Example
#
Set-Variable dbChoice -option ReadOnly -value $dbMySQL
Set-Variable dbName   -option ReadOnly -value Test
Set-Variable dbSource -option ReadOnly -value localhost
Set-Variable dbUser   -option ReadOnly -value root
Set-Variable dbPass   -option ReadOnly -value password

# for Access, dbName is assumed to be located in $pwd
# for MySQL, dbSource should be the IP address or FQDN of the computer running MySQL server
# for MSSQL, dbSource should be the name plus the instance of MS-SQL (e.g., SERVER\Instance2)
#            if not present or "Not-Applicable", then "(local)\Default" is presumed
# for MSSQL, if dbUser is something other than "Not-Applicable" then Windows Auth is used

and now for the code itself. It's pretty simple. :-)

#
# dbODBC.ps1
#

. $pwd/dbConstants.ps1

$global:MLMconnection = $null
$global:MLMcommand    = $null
$global:MLMresult     = $null

function dbOpen
{
$global:MLMconnection = New-Object System.Data.Odbc.OdbcConnection

$global:MLMcommand = New-Object System.Data.Odbc.OdbcCommand
$global:MLMcommand.Connection = $global:MLMconnection

switch ($dbChoice)
{
$dbAccess
{
$cStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$pwd/$dbName;"
}
$dbMSSQL
{
$cStr = "Driver={SQL Server};Server=$dbSource;Database=$dbName;"
if ($dbUser -eq "Not-Applicable")
{
$cStr += "Integrated Security=SSPI;"
}
else
{
$cStr += "UID=$dbUser;PWD=$dbPass;"
}
}
$dbMySQL
{
$cStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=$dbSource;DATABASE=$dbName;"
if ($dbUser -ne "Not-Applicable")
{
$cStr += "UID=$dbUser;PWD=$dbPass;"
}

}
Default
{
throw ("Unknown value of dbChoice $dbChoice can't open db $dbName")
}
}

$global:MLMconnection.ConnectionString = $cStr
$global:MLMconnection.Open()
if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Open)
{
throw ("Cannot open $dbName")
}
}

function dbPrepareResult
{
if ($global:MLMresult)
{
if ($global:MLMresult.Gettype().Name -ne "Int32")
{
$global:MLMresult.Close()
}
$global:MLMresult = $null
}
}

function dbClose
{
dbPrepareResult
if ($global:MLMcommand)
{
$global:MLMcommand.Cancel()
$global:MLMcommand = $null
}

if ($global:MLMconnection)
{
if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Closed)
{
$global:MLMconnection.Close()
}
$global:MLMconnection = $null
}
}

function dbExecute([string]$command)
{
dbPrepareResult
if (!$global:MLMcommand)
{
dbOpen
}

$global:MLMcommand.CommandType = [System.Data.CommandType]::Text
$global:MLMcommand.CommandText = $command

$global:MLMresult = $global:MLMcommand.ExecuteReader()

return $global:MLMresult
}

function dbExecuteNonQuery([string]$command)
{
dbPrepareResult
if (!$global:MLMcommand)
{
dbOpen
}

$global:MLMcommand.CommandType = [System.Data.CommandType]::Text
$global:MLMcommand.CommandText = $command

$global:MLMresult = $global:MLMcommand.ExecuteNonQuery()

return $global:MLMresult
}