Nivot Ink - Get a quick database connection with a nice GUI

Wednesday, July 09, 2008

Get a quick database connection with a nice GUI

I just hacked this one up a few minutes ago to let me get an Oracle, Access, Excel/CSV or SQL Server database connection quickly without fiddling with provider strings and other things that just fall out of my head as fast as they get put in. It uses an old trick of creating a zero-length file with an UDL extension and then executing it. The function returns an operable OleDbConnection object, albeit a closed one unless you specifiy –Open as a switch.

function get-oledbconnection ([switch]$Open) {
$null | set-content ($udl = "$([io.path]::GetTempPath())\temp.udl");
$psi = new-object Diagnostics.ProcessStartInfo
$psi.CreateNoWindow = $true
$psi.UseShellExecute = $true
$psi.FileName = $udl
$pi = [System.Diagnostics.Process]::Start($psi)
$pi.WaitForExit()
write-host (gc $udl)
if (gc $udl) {
$conn = new-object data.oledb.oledbconnection (gc $udl)[2]
if ($Open) { $conn.Open() }
}
$conn
}

You’ll notice I said OleDbConnection. This means you should pick an OLEDB provider on the provider page for best (read: working) results. Cancelling returns nothing.