Are there any classes (or methods) that can create a formatted connection string, given a provider name and user id, password etc?

Question

Eg.

ConnectionDetails cd = new ConnectionDetails ();
cd.ProviderName = "System.Data.OleDb";
cd.DataSource = "serverAddress";
cd.Catalog = "database";
cd.UserId = "userId";
cd.Password = "password";

string connectionString = cs.CreateConnectionString();
// Should return:
// "Provider=SQLOLEDB;Data Source=serverAddress;Initial Catalog=database;User Id=userId;Password=password;"

I'd write my own class but I'm not sure how to retrieve a connection string provider property (SQLOLEDB in this example) programmatically from an invariant db provider name (System.Data.OleDb).

Edit:

You can do a

DbProviderFactories.GetFactory("System.Data.OleDB").CreateConnectionStringBuilder()

But the DBConnectionStringBuilder that is returned still doesn't know it's connection string provider property, even though in this case it the derived class has a "Provider" property.

Answer 1

The closest thing I know of is DbConnectionStringBuilder.

Because the properties required by different providers vary, it uses an associative array (name value collection) rather than fixed properties.

So your example would look like

DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
csb["ProviderName"] = "System.Data.OleDb";
csb["DataSource"] = "serverAddress";
csb["Catalog"] = "database";
csb["UserId"] = "userId";
csb["Password"] = "password";

string connectionString = csb.ConnectionString;

Answer 2

There is an OleDbConnectionStringBuilder class that will create the connection string for you.

But it sounds like you are trying to get it to generate the provider name for you, and I don't think it really works that way. Since the provider name can vary, that is something that you are going to have to supply to tell the connection what to do. Whatever mechanism you are using to select your database connection (or to drive your database connection factory methods if you're going that route), it will also have to provide the provider name.