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.

  1. function get-oledbconnection ([switch]$Open) {  
  2.     $null | set-content ($udl = "$([io.path]::GetTempPath())\temp.udl");  
  3.     $psi = new-object Diagnostics.ProcessStartInfo  
  4.     $psi.CreateNoWindow = $true 
  5.     $psi.UseShellExecute = $true 
  6.     $psi.FileName = $udl 
  7.     $pi = [System.Diagnostics.Process]::Start($psi)  
  8.     $pi.WaitForExit()  
  9.     write-host (gc $udl)  
  10.     if (gc $udl) {  
  11.         $conn = new-object data.oledb.oledbconnection (gc $udl)[2]  
  12.         if ($Open) { $conn.Open() }  
  13.     }  
  14.     $conn 
  15. }  

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.

image

Ah, how I love the little blue box of typographical terrific-ness.

blog comments powered by Disqus

About the author

Irish, PowerShell MVP, .NET/ASP.NET/SharePoint Developer, Budding Architect. Developer. Montrealer. Opinionated. Montreal, Quebec.

Month List

Page List