Incorporating the Data Link Properties Editor into a VB.NET Application

If you want to learn a bit about COM Interop or how to incorporate the Data Link Properties applet seamlessly into your Visual Basic .NET application then this is a good article for you.

Introduction

Working on CodeDOMinator, a Rapid Application Development tool, we needed to provide the user with an easy way to dynamically connect to a data source. From experience I knew that there are native ODBC calls that will return aliases and datasources and the ODBC.ini files contain this information too, but practicing what I preach it seemed like reusing the Data Link Properties applet was a better use of my time.

The Data Link Properties applet is associated with files with a .udl extension. One can use rundll32.exe to run an application inside of a DLL or, in .NET employ COM Interop and link to the oledb32.dll. The oledb32.dll contains the definition of the DataLinksClass. The DataLinksClass is the key to reusing the Data Link Properties dialog and easing a dynamic database connection tool into your applications.

If you don't want to have to remember that the provider name for Jet databases is Microsoft.OleDb.Jet.4.0 (or is Microsoft.Jet.OLEDB.4.0?) then you can use the Data Link Properties dialog to build a syntactically perfect connection string for you. If you want to learn a bit about COM Interop or how to incorporate the Data Link Properties applet seamlessly into your application then this is a good article for you.

Creating a UDL File

An easy way to obtain a valid connection and connection string in .NET is to drag a connection control from the Data tab of the Toolbox. Unfortunately using the connection control in a Windows Form lends itself to muddling the presentation layer and the data access layer. Production applications often separate presentation and data access. If you are building a .NET database application (or a VB6) application then the Data Link Properties (see figure 1) applet is a convenient way to build the connection string without a lot of hassle.

Figure 1: The Data Link Properties applet is part of the capabilities of the oledb32.dll and is a convenient way to build a connection string.

To create a perfect connection string every time, follow these steps:

Open Windows Explorer

Select File|New|Text Document and rename the document with a .udl file extension

Double-click on the .udl file and the file associations will open the .udl file with the Data Link Properties applet.

The command line approach is to enter rundll32.exe c:\Program Files\Common Files\System\Ole DB\oledb32.dll OpenDSLFile filename.udl, where filename.udl is a pre-existing Data Link file.

When you click OK in the Data Link Properties the selections you make are written as a connection string in the .udl file. You can open the .udl file to copy and paste the connection string, for example, into your VB .NET application's App.config file. Figure 2 shows a .udl file opened in Notepad containing the connection string to an instance of an Access database.

Referencing the Data Link Properties Library and COM Interop

Running an external process with a shell command—using the Process class in VB .NET—leaves a to-obvious trace that your application is shelling to an external process. By leaning on COM Interop in VB.NET we can interact with the Data Link Properties applet in an integrated way, resulting in a more seamless integration and professional fit and finish.

COM Interop is something that is pretty automatic thanks to the CodeDOM. To use a COM library like oledb32.dll we use Add Reference in the Solution Explorer and select the oledb32.dll from the COM tab, as shown in figure 3. The name of this component is the Microsoft OLEDB Service Component 1.0 Type Library.

Figure 3: Add the oledb32.dll to a .NET project using the Add References dialog.

When you add a COM component to a .NET project an assembly with an Interop prefix is added to the project. For example, adding the service component adds a dynamically generated wrapper for the COM component named Interop.MSDASC.dll. This Interop assembly is generated and compiled using the CodeDOM behind the scenes, and you will need to deploy the COM DLL and the Interop assembly with you application. We will also need the ADODB COM component. The adobdb.dll contains the ConnectionClass, which is used as a reference argument to the DataLinksClass' PromptEdit method.

After adding the COM assemblies to the project the types defined by the assemblies are available. You will need to add an Imports statement to your project, and you can explore the new types by opening the Object Browser with View|Object Browser. The code for showing the Data Link Properties applet and retrieving the connection string are provided in Listing 1.

The two namespaces refer to the adodb.dll and oledb32.dll DLLs. An Interop assembly Interop.MSDASC.dll is added for the oledb32.dll and the adodb.dll is an Interop assembly already. I added the Imports statements for convenience. The basic behavior of this example is a Form with a Button and TextBox. Click the button and the event handler Button1_Click is invoked. The results are written to the TextBox.

In the Click event handler we declare and create an instance of the DataLinksClass. On line 2 we declare and initialize a ConnectionClass (defined in ADODB.dll) object. The If-conditional checks the return value of DataLinksClass.PromptEdit; if it is True then we retrieve the connection string from the ConnectionClass object.

There are many properties defined in ConnectionClass. I leave it as an exercise for you to explore these COM classes further.

Summary

How important are techniques like these? Well, a reasonably competent programmer might be able to create the interface, figure out the database provider, and format the connection string for each provider in a 1,000 lines of code or so. Doesn't sound like much right?!

Let's look at it from a different perspective: our example is less than ten lines of code. That means that a reasonably good programmer could do it in two orders of magnitude more lines of code, or a hundred times more code by re-inventing a connection string builder. Rotating our perspective-tron one more time, industry surveys suggest that the average programmer writes 9,000 lines of production code per year. So our custom solution would take 1/9 of our productive year or, at 2,000 hours per year, 222 hours. At an average rate of $50/hour for a programmer (consultants and employees) that is $11,100 dollars from scratch or 1 hour to read this article and write the code—50 bucks.

How important is reusing code and tools? Adam Smith, Henry Ford, and countless others figured out that if we systematize process (do things systematically), reuse tools (i.e. reuse code, especially components) and specialize roles (i.e. "no jacks-of-all-trades") then we can reach a critical mass of productivity. That's pretty important.

About the Author

Paul Kimmel is a software architect, writer, and columnist for codeguru.com. Look for his recent book "Visual Basic .NET Power Coding" from Addison-Wesley. Paul Kimmel is available to help design and build your .NET solutions and can be contacted at pkimmel@softconcepts.com.

# # #

Comments

Figure 2 link

Posted by curcio1
on 03/09/2004 01:22pm

Thanks for this article. I hope to show it to my students. Could you please fix the link for Figure 2.
Thank you

Top White Papers and Webcasts

Live Event Date: March 19, 2015 @ 1:00 p.m. ET / 10:00 a.m. PT
The 2015 Enterprise Mobile Application Survey asked 250 mobility professionals what their biggest mobile challenges are, how many employees they are equipping with mobile apps, and their methods for driving value with mobility.
Join Dan Woods, Editor and CTO of CITO Research, and Alan Murray, SVP of Products at Apperian, as they break down the results of this survey and discuss how enterprises are using mobile application management and private …

On-demand Event
Event Date: February 12, 2015
The evolution of systems engineering with the SysML modeling language has resulted in improved requirements specification, better architectural definition, and better hand-off to downstream engineering. Agile methods have proven successful in the software domain, but how can these methods be applied to systems engineering? Check out this webcast and join Bruce Powel Douglass, author of Real-Time Agility, as he discusses how agile methods have had a tremendous …