JonM posted on Tuesday, April 17, 2007

It has been mandated that the application that I'm currently working on must use system DSNs for database connections. Does this mean I'll have to move to the OLEDBClient instead of the SQLClient?? If so, are there any disadvantages here?

tetranz replied on Tuesday, April 17, 2007

I think the official answer is yes, you need to use odbc. I was curious and did a quick test to see if you could open the DSN once with OdbcConnection and gather enough information to then open a SQLConnection and forgot about Odbc.

Once the OdbcConnection is open you can read its DataSource property which turns out to be the Windows name of the server. In my case that is resolvable but it wouldn't be if it was on a remote network. You probably want the tcp/ip host name or ip number. Unfortunately I couldn't see that anywhere.

Cheers
Ross

JoeFallon1 replied on Wednesday, April 18, 2007

"Are there any disadvantages here?"

Of course there are.

OLEDB is slower than SqlClient. It is not optimized for SQL Server. There may be some advanced tasks which you can't perform. Standard queries should work fine.

That restriction is really silly. There is no reason that they can't use both techniques. ODBC for those apps that talk to the DB separate from yours. And SqlClient for yours.

Joe

JonM replied on Wednesday, April 18, 2007

Basically, they want to use system DSNs because it give them one easy place to configure all of the database connections on a customer's system. Even a non programmer could follow instructions how to setup a DSN on a new system. Right now, I'm just going the CRUD operations (which should still work). However, will I still be able to associate a connection with a transaction like I do with the sql client?