DSN to connectionstring?
We've got an ASP.NET website that uses a database that we want to be able to use a connectionstring to get to. We've successfully set up a DSN for connecting to this DB, but I can't seem to discover the correct magic to go with a connectionstring.
Is there a straightforward way to translate the values from the DSN into a connectionstring? I know that from the UI, there isn't an obvious answer for this...each db vendor provides a different UI for creating a DSN based on what they require. However, I was hoping that underneath the UI it might just be doing something like creating a connection string behind the scenes, and I could look at that to see what I'm doing wrong. Any hope of this? If so, any pointers on how to get the info I need?
(I've gone to connectionstrings.com to try to make sure my connection string is in the right format, but nothing seems to be working...which is why I'm trying this strange translate-from-dsn tact.)
EDIT: Something I must not have been clear on is that we do not want to have a DSN entry. We have created one, and have used it for the time being, but we want to be able to get rid of it and use a connectionstring without a dsn.
Answer
If you can use OLEDB, then you can create a UDL file. Just create a new text document, test.udl and double click. Fill out the dialog, then open it back up with Notepad. Voila - there's your connection string.
ODBC is a bit harder - you can either create a file DSN from ODBC Administrator or poke around the registry in HKLM\Software\ODBC\ODBC.INI\<DSN Name> for a system DSN. You'll end up with some name/value pairs. You should be able to translate those into a connection string. The \\Driver will list the actual DLL, so you'll need to get the provider name from HKLM\Software\ODBC\ODBC Data Sources\\<DSN Name>.
If you can use the OLEDB Provider for ODBC, then you can use the UDL trick and have it build a connection string from an ODBC file DSN as well. The ODBC connection string will be in Extended Properties of the UDL.