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

ado.netconnection-stringnet

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.

Best Answer

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;
Related Topic