CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
Figure 2: The contents of a .udl file shown in Notepad.
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.
Listing 1: Integrating the Data Link Properties applet into a Windows application.
Imports MSDASC
Imports ADODB
Public Class Form1
Inherits System.Windows.Forms.Form
[ Windows Form Designer generated code ]
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim Instance As DataLinksClass = New DataLinksClass
Dim connection As ConnectionClass = New ConnectionClass
If (Instance.PromptEdit(connection)) Then
TextBox1.Text = connection.ConnectionString
End If
End Sub
End Class
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.
# # #
