Is there a generic way of dealing with varying connection strings in C#?
- by James Wiseman
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?