• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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.
2 Solutions
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:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now