Dan Swearingen's Tech Stuff Blog

One of the most common gotchas to getting any data driven application working is the database connection string. How many times in your development life have you heard “I can’t connect to the database” and the problem ends up a minor detail in the connection string?

When I was working on a project with IdeaBlade one of the developers showed me a neat trick: keep a UDL file on your desktop. 

While there are whole websites devoted to connection string details, a simple UDL file on a Windows system gives you a really easy way to configure and test a connection string directly against the database you want to use.

In Windows the extension “.udl” is registered as a “Microsoft Data Link” and the default program is OLE DB Core Services. Not very intuitive so let me walk through the basics.

A UDL file is actually a text file so start by creating a text file on your desktop. Right-click on the desktop and select New and Text Document.  Note: I did this on Windows Vista but this works the same way on any modern version of Windows (I’ve checked as far back as NT 4.0).

Name your new file Connection.udl – or, whatever but you need the “.udl” extension. Ignore the “If you change a filename extension…” warning . The file will take on an icon appearance that is not a text file. Here’s how it looks on Vista

And XP

Now double click on (or right-click Open) the file.  You should see a familiar database connection configuration dialog.

To connect to your local SQLExpress database go to the Provider tab and select Microsoft OLE DB Provider for SQL Server.

Now go to the Connection tab and select the database to connect to. You may just browse through the list but SQLExpress instances do not always show up. You may also type the server name into the listbox. For SQLExpress it will usually be [machine name]\SQLEXPRESS if you have a full SQL 2005 instance installed it will just be the machine name. In this case I’m connecting to my local SQLExpress database on my machine HAVOCVISTA.

Select the authentication you want to use. If using a username and password choose whether you would like to embed the password in the connection string.

Now select the particular database. If the dropdown is populated when you click on it you already have a good connection. If not, then the problem is going to be the name and/or the authentication provided. Assuming all’s well, select the database and click the Test Connection button.

Now close by clicking the OK button.

Here’s the “Ah Ha” step.

Now open the file in notepad – remember, the .udl file is simply a text file. You should see something like this:

In the file is the connection string you just tested. Copy and paste where needed in your application and you should be good to go.

The story of ATL and WTL (fit for a cocktail napkin):

In the beginning, (at least in *this* beginning) there was COM. As developers embraced COM programming for Windows applications Microsoft created the Active Template Library (ATL) as a framework to simplify and envelop the routine tasks in the creation of COM components.

Using ATL developers were happy with the ease with which small fast components could be created. They were unhappy with all the code wrappers they had to write to use any Windows controls. And, of course, everyone wrote their wrappers differently so this became an entropy generator on projects. While ATL does provide “Window” classes, they really don’t help much outside of COM. They’re mainly intended for COM control property pages.

In response, developers within Microsoft developed (in an unsupported way) the Windows Template Library (WTL). WTL extends ATL and provides a framework of light wrappers to use with Windows controls. Developers competent with ATL find WTL a great framework to quickly build small (in terms of KB EXE size) applications. Versions 7.0-7.5 are available from Microsoft but WTL has been released in the public domain and is maintained at SourceForge (see links below).

On a recent project I found these resources useful in catching up on how to work with ATL/WTL:

ATL resources:

Code:

ATL is part of the Windows SDK. 

Web:

New MFC and ATL Features - MSDN
ATL Samples - MSDN
ATL Server Samples - MSDN 

Code Project: Active Template Library (ATL)

Books:

ATL Internals: Working with ATL 8, 2nd Edition (New! July, 2006) (Amazon), by Christopher Tavares, Kirk Fertitta, Brent Rector, Chris Sells.  Very good but does not have any material on ways to build ATL Window apps without WTL (Note: I stand corrected, see Chris’s post below). Chris Sells’ web page for this book 

Beginning ATL 3 COM Programming (1999)(Amazon), by Julian Templeman, Richard Grimes, Alex Stockton, Karli Watson, George V. Reilly. This is “beginning” as in “beginning cliff diving” – has an ATL Windows app section.

Professional ATL Com Programming (1998) (Amazon), by Richard Grimes.

Developer’s Workshop to COM and ATL 3.0 (2000) (Amazon), by Andrew Troelsen.
 

WTL resources:

Code:

Windows Template Library (WTL) 7.0 4/2/2002
Windows Template Library (WTL) 7.1 12/9/2003
Windows Template Library (WTL) 7.5 6/13/2006
WTL open source at SourceForge. V 7.5 is released, 8.0 in development.

Web:

Chris Sells: Whitepapers, sample code and walkthroughs. Based on an old version of WTL but still the best starting point. WTL Makes UI Programming a Joy, part 1 and part 2, 6/2000 

Yahoo! Tech Group: WTL – online group discussion and help.

Code Project: Windows Template Library (WTL)
Code Project: Michael Dunn’s series on WTL for MFC Programmers is outstanding. Something like ten parts. Starts with WTL for MFC Programmers, Part I – ATL GUI Classes 

Books:

None as far as I know.