How to get a SQLConnection string from an ODBC DNS?

[Rui Oliveira]

How to get a SQLConnection string from an ODBC DNS?

I have an ODBC DNS string, for example

DSN=DNSNAME;UID=USERNAME;PWD=PASSWORD;

Is possible get a connection string to use in SQLConnection class
(System.Data.SqlClient)?

The SQLConnection connection string is from type:

"Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=DATABASENAME;User ID=
USERNAME; password= PASSWORD;"

Thanks,
Rui

 

[Jeroen Mostert]

How to get a SQLConnection string from an ODBC DNS?

I have an ODBC DNS string, for example

DSN=DNSNAME;UID=USERNAME;PWD=PASSWORD;

Is possible get a connection string to use in SQLConnection class
(System.Data.SqlClient)?

You can manually parse the .DSN file for the relevant data (it's just a text
file). If you just want to make a connection, use OdbcConnection, not
SqlConnection (which would have been more appropriately named
MSSqlConnection, as it's SQL Server-specific).

 

[Arne Vajhøj]

Don't do that.

If you have the option of using a native .NET data provider, you should
definitely use it. ODBC really should be viewed as an absolute "last
resort" these days...

Yep.

Bad performance and absolutely horrible error messages.

Arne

 

[daveL]

if you have like the company i work for legacy code oledb and odbc
just look at those conection strings and rebuild a net native connection
string from them
example below
i use the below to take a stored connection string and can't update the
database cause of legacy applications
and create a adonet connection string from them

public static string ConvertConnectionString(string sConnStr)
{
//odonet-> "server=someserver;database=somedatabase;Integrated
Security=SSPI;CONNECTION TIMEOUT=30"/>
//ado/oledb -> string soleConn = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=somedatabase;Data
Source=someserver";
string[] aConn = sConnStr.ToUpper().Split(';');

string adoConnString="";
for (int x = 0; x < aConn.Length; x++)
{
if (aConn[x].IndexOf("PROVIDER") > -1)
{
aConn[x] = "";
}
else if (aConn[x].IndexOf("INITIAL CATALOG=") > -1)
{
aConn[x]=aConn[x].Replace("INITIAL
CATALOG=","Database=");
}
else if (aConn[x].IndexOf("DATA SOURCE") > -1)
{
aConn[x] = aConn[x].Replace("DATA SOURCE=", "Server=");
}
//clean up the connection string and build a adonet
connectionstring
if (aConn[x].Length > 0)
{
adoConnString += aConn[x] + ";";
}
}
//Console.WriteLine(adoConnString);
//Console.ReadKey();


return adoConnString;
}
DaveL

 

[Arne Vajhøj]

How to get a SQLConnection string from an ODBC DNS?

I have an ODBC DNS string, for example

DSN=DNSNAME;UID=USERNAME;PWD=PASSWORD;

Is possible get a connection string to use in SQLConnection class
(System.Data.SqlClient)?

The SQLConnection connection string is from type:

"Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=DATABASENAME;User ID=
USERNAME; password= PASSWORD;"

Try:

RegistryKey rk =
Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("ODBC").OpenSubKey("ODBC.INI").OpenSubKey(dsnname);
string srv = rk.GetValue("Server").ToString();
string db = rk.GetValue("Database").ToString();
string tc = rk.GetValue("Trusted_Connection").ToString();

Arne