Can I make my program to select a database a user?
I need to run a Windows Forms Application (C #) in a client and a database in another.
I want the user to select the database (SQL Server 2005) manually in the program. In case the database can be changed from one PC to another.
I need to avoid the computer name or IP number in the connection string, or something useful.
How can this be achieved?
source to share
You can use the following code to invite the user with the connection assembly dialog box. 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&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;
}
source to share
You can change the computer name based on the user's choice. We've done this before, or have multiple connection strings in app.config and use 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);
}
source to share
Will this suit you? http://support.microsoft.com/default.aspx?scid=kb;EN-US;310083
source to share
I recommend that you use a connection string builder and just keep the persistent 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";
source to share
Microsoft has released the source code for the Data Connection Dialog Box in the Code Gallery.
Here is a blog post from Yaohai with more information and here's home the Data Connection Dialog Box in the Code Gallery .
source to share