How to get a SQLConnection string from an ODBC DNS?

  • Thread starter Thread starter Rui Oliveira
  • Start date Start date
R

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
 
Rui said:
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)?
Click to expand...
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).
 
Mark said:
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...
Click to expand...

Yep.

Bad performance and absolutely horrible error messages.

Arne
 
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
 
Rui said:
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;"
Click to expand...

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
 
Back
Top