10

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.

Beska
  • 12,445
  • 14
  • 77
  • 112

4 Answers4

8

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.

Mark Brackett
  • 84,552
  • 17
  • 108
  • 152
  • Ahh! File DSN! Good thinking! That's exactly what I was looking for. (I figured that I could pull the info out of the Registry, but wasn't sure where to look, but the file DSN is much simpler!) Sadly, it didn't solve the problem...it just verified that all the attributes match up. Now I'm guessing it may be some kind of permissions issue...setting up the DSN may have different permissions than trying to connect directly from ASP.NET...sadly, this *not* my area of expertise. *sigh*. – Beska Jun 17 '09 at 18:37
5

If you have created a DSN, then the DSN is the ConnectionString !

You can simply use DSN=<YourDSNName> and pass it to an OdbcConnection object.

For instance, using C#:

string dsnName = "DSN=MyDSN";
using (OdbcConnection conn = new OdbcConnection(dsnName))
{
  conn.Open();
}

Alternatively, you can use the OdbcConnectionStringBuilder class and set its DSN property.

Cerebrus
  • 25,615
  • 8
  • 56
  • 70
3

To expand on Mark Brackett's answer about the registry: for a 32-bit ODBC on a 64-bit Windows, the registry path is HKLM\Software\Wow6432Node\ODBC\ODBC.INI\

JF Menard
  • 93
  • 1
  • 12
3

In my case it was sufficient to:

  • Remove the header
  • Replace all the new lines with semicolons
  • Use braces as a group separator

Here's my DSN File as created by ODBC Data Source Administrator (3rd tab—File DSN)

[ODBC]
DRIVER=MySQL ODBC 5.3 ANSI Driver
UID=MyUserName
PORT=3306
DATABASE=mydatabasename
SERVER=localhost

And here's what my connection string looked like:

DRIVER={MySQL ODBC 5.3 ANSI Driver};UID=MyUserName;PORT=3306;DATABASE=mydatabasename;SERVER=localhost
Ivan Gagne
  • 31
  • 2