Working with UDL (Universal Data Link) files

Hello everyоne.

After 3 long weeks of vacation, I'm finally back!

I this blog post, I will show one simple method for creating connction strings using UDL files, as well as, how to use the UDL file directly from code-behind using C# language (for those working in VB.NET).

Many juniors who have problems creating connection strings manually, use the Microsoft Visual Studio.NET Data Components like SqlDataSource, AccessDataSource or similar so that the Wizard will do the job for them and place the connection string in Web.config.

Sometimes (especially if running on slower machine), the wizards may respond very slow or some developers does not want to use the VS.NET Wizards for connection strings, at all, because besides the connection string, the DataSource wizards usually perform some other actions too.

Hence, using UDL may be useful in such cases.

Let's pass throughout the procedure.

1. On your Desktop, create file with UDL extension i.e. test.UDL
- the file icon will look like this: (in WinXP) or (Win7)
2. Open the file icon and a wizard will appear.
3. Create your connection string. First (in the first tab) chose appropriate Provider. Then (in the second tab) construct the Connection by filling the required information (servername, database or similar).
4. Test Connection. If the connection is successful, click OK.
Once you are done, open the UDL file with NOTEPAD. Right click on it and go to Edit with Notepad or Open With notepad.
In notepad, you will have the connection string i.e.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=MySampleDB;Data Source=.;Initial File Name="";Server SPN=""
So, the wizard has constructed connection string for me. This is for SQL Server. Only the bolded part should be copied and you can use it as connection string in your application.
Moreover, if you've already created an UDL file, you can use it in your application directly by calling the filename.
Here is an example:
Firstly, add the using System.Data; and using System.Data.OleDb; directives.
The test code is as follows:

01	OleDbConnection con = new OleDbConnection("File Name="+Server.MapPath("\\test.udl"));

02

03

try

04

{

05	con.Open();

06	Response.Write("Connection open!");

07

}

08	catch (OleDbException ex)

09

{

10	Response.Write(ex.Message);

11

}

12

finally

13

{

14	con.Close();

15

}

If you can notice, the connection string is only the path to the filename (which in our case is in the web application root folder) preceding with File Name=.
If you run this example, the message Connection Open! should be displayed. Moreover, you can add wrap this Response.Write("Connection open!") inside if (con.State == ConnectionState.Open) { Response.Write("Connection open"); }
I hope this was useful :)