Choose a location:
posted 8/24/2010 by Hajan Selmani
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 initstringProvider=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:
OleDbConnection con = new OleDbConnection("File Name="+Server.MapPath("\\test.udl")); try { con.Open(); Response.Write("Connection open!"); } catch (OleDbException ex) { Response.Write(ex.Message); } finally { con.Close(); }
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 :)
Best Regards,Hajan
Great info!
Hajan
Having a problem with a UDL file. I us it to connect to an AS400 Iseries database using the Provider=IBMDA400.DataSource. It connects fine but it will not save the password. Already tried "Allow Saving Password" and going into a text editor and changing it there.
It just does not save the password and the crazy thing is that it only does it on 2 XP machines, on all of the other computers it works fine. I can copy the file and move it to another machine and it works fine. Every computer is setup with the same AS400 Iseries connection file and running XP using sp3. I'm stump as to why the UDL file will not save the password and it is a pain to have to type a password in everytime the UDL file is used.
Daniel Borras
Programmer Analysis
Hello Daniel,
Honestly, I would not recommend to use the UDL file for saving your connection string. What is good of UDL usage is to create your connection string and verify its working. You can even easily debugging if the connection fails, where it fails and why. However, it saves the connection string in a plain text format so that if you save username/password in it and us it as it is for connecting to your database, its definitely security problem. But, as I've explained in my blog post, UDLs can be very very useful to construct your connection string so use it only for such purpose.
Anyway, I've previously experienced your issue too. Not for saving the password, but in my case was for saving the Connect Timeout (in Advanced tab).
What I did was:
- I opened the UDL file with notepad- I wrote the Connect timeout manually- And saved the file
So, you can wrote the password manually.
If this is your UDL file (example):
You can add Password=<yourpassword>; in it.
[oledb]; Everything after this line is an OLE DB initstringProvider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Password=<yourpassword>;User ID="";Initial Catalog=MySampleDB;Data Source=.;Initial File Name="";Server SPN=""
Again, its not recommended to save your password as a plain text!
Regards,Hajan
Thanks for the response and advice, but changing the password in a text file was one of the first things I tried. That is what is so confusing about this situation, even when I change and save it in text format it still does not allow the change, in fact the change does not even show up when you open the UDL file and the “Blank Password” check box is checked again. It’s doubly strange because if you copy the same file to another compute it works fine; which leads me to believe that it is an XP, ISeries or IBMDA400.DataSource drive setting that I'm running up against. Also it is only when you use the UDL to connect to an AS400 ISeries database that it happens, any other connection works fine.
Again thanks for the help and you are right normally I would not use a UDL file for DB connections in a released application, but sometimes you are stuck with what others have coded.
More than likely I will be rewriting the connection part of this application.
Daniel,
I think your issue is related to something else - not the UDL file itself because if it works as it is in another machine and not in the one where the issue occurs, it says everything.
However, better spend some time rewritting the connection part of the application instead of using the UDL file directly. I would advise you to create Connection class and implement the DB connection logic in there.
If you have any question regarding this, feel free to register in our community (codeasp.net) and post your questions in the forums, we are always here to support all Microsoft .NET / ASP.NET Developers while coding their apps ;).
Figured a work around for this problem, it’s weird but it works. If I open the UDL file in Notepad and add the password and save the file in Notepad, but never open the UDL file itself to “Test Connection”, everything works fine. Every application can use the UDL file connection without entering a password, but the moment you open the UDL file, you’re screwed, everything now requires that a password be entered, it’s weird but it works. Still I think that it’s a setting or property that is set incorrectly, but I will not waste any more of our time on it, because I will be reprogramming the connections without using a UDL file. Thank you for all of your help and time
First of all, I'm glad it has worked
Emm... Maybe I haven't expressed my thoughts well, but I was thinking on the same scenario when I told you to write the password by opening the UDL with notepad, and save that file.
I've experienced previously the same with the Connect Timeout (which is in Advanced tab in the UDL file). When I wrote the string manually in the text file, it was saved correctly. Once the UDL file is opened, its overriding the settings and the changes are lost. Its probably bug in the UDL file that happens in weired circumstances since I didn't find anything that might influence this. The operating system might be possible reason too.
However, once again I'm glad you've solved your issue ;).
Thank you for visiting CodeASP.NET Community and welcome back here anytime you want :).
All the best,Hajan
Hi Hajan:
I am working with ASP.NET 3.5 and I am trying to connect to my database in SQL Server 2008. I used the UDL technique and obtained a connection string similar to the one you have generated in the example and my test connection woks as well. The point is I am using Windows Authentication to access the database and whenever I try to debug the program an exception is thrown that it is unable to retrieve user by login. Do you think it could be because my connection string is wrong which is why it is unable to connect to my login table in my databse and access the right credentials. Could you also explain me if there is any other way to access SQL Server 2008 keeping in my mind i would have use windows authentication for it.
I am actually trying to fix an application ove summer and I have a very minimal experience with ASP.NET
I would appreciate it if you could help!
Thanks
Dhruv
This comment is awaiting moderation.
What kind of email newsletter would you prefer to receive from CodeAsp.Net? 18
