Is there a generic way of dealing with varying connection strings in C#?

Question

I have an application that needs to connect to a SQL database, and execute a SQL Agent Job.

The connection string I am trying to access is stored in the registry, which is easily enough pulled out.

This appliction is to be run on multiple computers, and I cannot guarantee the format of this connection string being consistent across these computers. Two that I have pulled out for example are:

Data Source=Server1;Initial Catalog=DB1;Integrated Security=SSPI;
Data Source=Server2;Initial Catalog=DB1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

I can use an object of type System.Data.SqlClient.SqlConnection to connect to the database with the first connection string, howevever, I get the following error when I pass the second to it:

keyword not supported: 'provider'

Similarly, I can use the an object of type System.Data.OleDb.OleDbConnection to connect to the database with the second connection string, howevever, I get the following error when I pass the first to it:

An OLEDB Provider was not specified in the ConnectionString'

I can solve this by scanning the string for 'Provider' and doing the connect conditionally, however I can't help but feel that there is a better way of doing this, and handle the connection strings in a more generic fashion.

Does anyone have any suggestions?

Answer 1

The normal way of handling this is storing the ADO.NET provider unique name (separately from the connection string) and using a DB Provider Factory.

Answer 2

Use a SqlConnectionStringBuilder, initialize it with the connection string you find in registry and then read its ConnectionString property, which would normalize the connection string to proper SQL Client syntax.