where and how to store sql connection string

Hi I have built a win app that will connected to my sql server for the data as multiple sites will use the same data. I am going to set up a database for each user.
 I don't want to store the connection string in the app as each user will have there own login and the app will be an online click once deploy app for updates.

Where would I store this data as I don't want it that when they update the app it asks them again for connection string.

would you add it to the reg and only if blank ask for it? please tell me the best method.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I am going to set up a database for each user.
you may consider setting up a direct link between the user (name?) and the database (name?), so that you can determine the database to connect to from the user. it may require to have a first connection to the "central" database that holds the mapping, and then "reconnect" in the db to the actual database.

apart from that, yes a config file should be used, see this article:
anarki_jimbelSenior DeveloperCommented:
I wouldn't use the registry.

Definitely preferably is to use some kind of a config file.  Commonly a configuration file is just an XML file, so you may create your own one. Custom solutions often are much simpler and easier to maintain.

You just have an XML file in the executables folder, e.g., and read it:
private XmlDocument loadMyConfigFile()
    XmlDocument doc = null;
        doc = new XmlDocument();
        doc.Load(getMyConfigFilePath()); // e.g., from exe folder
        return doc;
    catch (System.IO.FileNotFoundException e)
        throw new Exception("No config file found.", e);
    catch (Exception ex)
        // just handle an exception in some way

Open in new window

When you have a file you may read a node with the required information.
Option 1. Use user scope settings (Project->Settings) with preformatted string like
Server={0};Database={1};User Id={2};Password={3};
and then fill it in code:
connString =  String.Format(my.Setiings.DBConnectionstring,serverName,dbName,login,pwd)

Open in new window

Option 2. Use ConnectionStringBulder
Dim csb As New SqlConnectionStringBuilder("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;")
csb.UserID = login
csb.Password = pwd
connString = csb.ConnectionString

Open in new window

Option 3. Use connection dialog. Add references to Microsoft.Data.ConnectionUI.dll Microsoft.Data.ConnectionUI.Dialog.dll (located at Common7\IDE folder)
Imports Microsoft.Data.ConnectionUI
Public Shared Function SelectConnection(ByRef connString As String) As Boolean
    Dim connDlg As DataConnectionDialog
    connDlg = New DataConnectionDialog
    connDlg.SelectedDataSource = DataSource.SqlDataSource
    connDlg.SelectedDataProvider = DataProvider.SqlDataProvider
    connDlg.ConnectionString = connString
    If DataConnectionDialog.Show(connDlg) = Windows.Forms.DialogResult.OK Then
        Using conn As New SQLConnection(connDlg.ConnectionString)
                connString = connDlg.ConnectionString
                Return True
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Invalid connection")
                Return False
            End Try                
        End Using
    End If
    Return False
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.