The DataLinksClass class in the Microsoft OLE DB Service Component Library can be used to dynamically create or modify a connection string during execution. The DataLinksClass class will show a dialog similar to the one shown by the Server Explorer in Visual Studio. I am not aware of an equivalent for ADO or for .Net but the connection string created by the DataLinksClass class can be used in the .Net OLE DB classes. The DataLinksClass can be used to create a connection string for most databases, including Access and SQL Server. The following shows samples of the dialog box from the DataLinksClass.
To use the DataLinksClass class, you must have a reference to the "Microsoft OLE DB Service Component 1.0 Type Library". Use the COM tab of the dialog for adding references to a project to add the reference. You will also need to use the OleDbConnection class in the COM version of ADO so you will need to have a reference to the "Microsoft ActiveX Data Objects 6.0 Library", except the version (6.0) can probably be a different version.
Creating a Connection String
Creating a connection string can be as easy as:
MSDASC.DataLinksClass dl = new MSDASC.DataLinksClass();
ADODB.Connection c = dl.PromptNew() as ADODB.Connection;
if (c == null)
ConnectionString = "";
else
ConnectionString = c.ConnectionString;
The DataLinksClass class can do all the prompting for a connection string.
Modifying a Connection String
Modifying a connection string can be as easy as:
MSDASC.DataLinksClass dl = new MSDASC.DataLinksClass();
ADODB.Connection c = new ADODB.Connection();
c.ConnectionString = ConnectionString;
Object o = c;
if (!dl.PromptEdit(ref o))
ConnectionString = "";
else
ConnectionString = c.ConnectionString;
The DataLinksClass class can do all the prompting for a connection string.
SQL Server Express
If you are not familiar with the Express edition of SQL Server, then you might have difficulty figuring out what to use for the server name. In the Provider property page, select SQL Server Native Client. The default for the server name is "(local)\sqlexpress". Also, ensure that SQL Server Express is started; it appears to be installed by Visual Studio in a stopped state and it does not start on it's own. If no servers are listed when you click on the dropdown for the server names, then it is likely that SQL Server is not started. Also use Windows authentication. The following shows what works for me with the Express edition of SQL Server.
Final Comment
There is not much relevant documentation so a lot of this is the result of guessing and such. I hope it works but I can't guarantee it.
I have not explored the possibility of using the connection string with ADO .Net but it should be relatively easy to do.