Showing a Connection String prompt in a WinForm application
When I was putting together the Data Dictionary Creator program, I needed to allow users to input a connection string. A lot of winform applications that require data connections give you a textbox and tell you to figure it out yourself, but I really wanted to show a prompt that let you test your connections, select databases on a server, etc.
I was surprised that I couldn't find a nice, free, winform control to build a connection string. I found something on CodeProject with a smart solution - it pops up the standard Data Link Properties dialog. The code mostly worked, but had some problems (passwords were lost if user didn't check "Allow Saving Password", problems with integrated security). I found some general suggestions in the comments and newsgroup postings and threw in some exception handling, and came up with something that worked pretty well for me. It's a little trickier than it looks, since setting certain properties in just the right order shifts the dialog into modes which hide tabs and stuff.
This requires references to a few COM objects:
%PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll
%PROGRAMFILES%\Common Files\System\Ole DB\OLEDB32.DLL
I found an article about using CreateObject or GetTypeByProgID to avoid the interop references, I think I'd rather ship the interop stubs than worry about dynamic object calls failing when I'm shipping an application, but it's interesting.
The next step for this would be bundle this up in a user control (textbox with an ellipses button to launch the dialog, maybe a balloon tip to show connection errors), but I'm not sure I'll get to that any time soon. Here's the code as a simple function that pops the dialog and returns the selected connection string:
/// <summary> /// Displays a Connection String Builder (DataLinks) dialog. /// /// Credits: /// http://www.codeproject.com/cs/database/DataLinks.asp /// http://www.codeproject.com/cs/database/DataLinks.asp?df=100&forumid=33457&select=1560237#xx1560237xx /// /// Required COM references: /// %PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll /// %PROGRAMFILES%\Common Files\System\Ole DB\OLEDB32.DLL /// </summary> /// <param name="currentConnectionString">Previous database connection string</param> /// <returns>Selected connection string</returns> private string PromptForConnectionString(string currentConnectionString) { MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass(); ADODB.Connection dialogConnection; string generatedConnectionString = string.Empty; if (currentConnectionString == String.Empty) { dialogConnection = (ADODB.Connection)dataLinks.PromptNew(); generatedConnectionString = dialogConnection.ConnectionString.ToString(); } else { dialogConnection = new ADODB.Connection(); dialogConnection.Provider = "SQLOLEDB.1"; ADODB.Property persistProperty = dialogConnection.Properties["Persist Security Info"]; persistProperty.Value = true; dialogConnection.ConnectionString = currentConnectionString; dataLinks = new MSDASC.DataLinks(); object objConn = dialogConnection; if (dataLinks.PromptEdit(ref objConn)) { generatedConnectionString = dialogConnection.ConnectionString.ToString(); } } generatedConnectionString = generatedConnectionString.Replace("Provider=SQLOLEDB.1;", string.Empty); if ( !generatedConnectionString.Contains("Integrated Security=SSPI") && !generatedConnectionString.Contains("Trusted_Connection=True") && !generatedConnectionString.Contains("Password=") && !generatedConnectionString.Contains("Pwd=") ) if(dialogConnection.Properties["Password"] != null) generatedConnectionString += ";Password=" + dialogConnection.Properties["Password"].Value.ToString(); return generatedConnectionString; }