Can I make my program select the database by user?

Question

I need to run a Windows Forms application (C#) in a client and the database on the other.

I want the user to select the database (SQL Server 2005) manually in the program. In case of that the database can be changed from one PC to other.

I need to avoid the computer name or IP number in the connection string or something helpful.

How can this be achieved?

Answer 1

You can change the computer name based on the user selection. We have done that before, or have multiple connection strings in the app.config, and make use of the user selection to open the appropriate connection string from there.

<connectionStrings>
     <add name="Default" connectionString="....
     <add name="Second" connectionString="....
     ...
</connectionStrings>


m_connectionStringCollection = new Hashtable();

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConnectionStringsSection csSection = config.ConnectionStrings;

for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)
{
    ConnectionStringSettings cs = csSection.ConnectionStrings[i];
    string connectionName = cs.Name;
    m_connectionStringCollection.Add(connectionName, cs);
}

Answer 2

Will this one suites you? http://support.microsoft.com/default.aspx?scid=kb;EN-US;310083

Answer 3

You can use following code to prompt user with Build connection string dialog You can find the original article here.

/// <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&amp;forumid=33457&amp;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;
}

Answer 4

I recommend you use the connection string builder, and just store the constant parts in your config file, pulling the rest from your user.

http://msdn.microsoft.com/en-us/library/system.data.common.dbconnectionstringbuilder.aspx

System.Data.Common.DbConnectionStringBuilder builder = 
    new System.Data.Common.DbConnectionStringBuilder();
builder["Data Source"] = "(local)";
builder["integrated Security"] = true;
builder["Initial Catalog"] = "AdventureWorks;NewValue=Bad";