How Do I Change the SQL Server Connection During Startup Before Dataset Connects?

I have a program that I want the user to be able to set the online SQL Server  Express connection and the local SQL Server CE file location on the first time it is run before the program tries to connect to either database. I have tried the following on the application events before startup but get denied permission:
        Protected Overrides Function OnInitialize(commandLineArgs As ObjectModel.ReadOnlyCollection(Of String)) As Boolean
            ' My.Settings.FirstUse = True 'comment out after testing.
            If My.Settings.FirstUse = True Then
                Dim dlgFirstUse As New dlgFirstUse
                If dlgFirstUse.ShowDialog = Windows.Forms.DialogResult.OK Then
                    My.Settings.FirstUse = False
                    My.Settings.Save()
                End If
            End If
            Return MyBase.OnInitialize(commandLineArgs)
        End Function

Open in new window


The First Use dialog sets and tests the two connections. I have also tried doing it during main form load but the connections are already attempted or made by then. What I do in dlgFirstUse is modify the app.config file for the server connection string and set the |DataDirectory| for the client connection string.
Well, I need a better way and would appreciate suggestions.
LVL 1
rkulpAsked:
Who is Participating?
 
arnoldCommented:
Unfortunately, my suggestions are more of an abstract, you as the developer need to identify where within your application the handling ......
I.e. The flow of your application in the absence of app.config or config.app or if there are no valid parameters that are needed, what does your application do?

Once you identify that, that is where you would be in a position to introduce the user prompt when it runs everytime.
0
 
arnoldCommented:
Not sure I understand, are you looking to prompt the user everytime the application starts for them to confirm where the application should connect?

While you can retain the app.config file that will have the list of all possible/previously used servers, do not proceed until the user's input is received.

In your current rest for app.config you should not take the information set there as authoritative, I.e. Everytime the application starts it should be as though my.settings.firstuse is always true.
0
 
rkulpAuthor Commented:
Actually, I want the user to set up the connections on first use and then not have to work with them again unless they change. I have incorporated dialogs in the application for changing the online server and also the local database location. When I wrote the program for my own use, the connection strings were in app.config and never changed. Now, some others want to use it which has led me to try these things.
What I would like to do is something like |DataDirectory| for both the server and client.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arnoldCommented:
Depending where you are storing app.config.
An alternative could be to store it within the user's settings hkcu\software\yoursoftware\parameters.

The issue with your might be where you have your app.config set which might not be available to other users.
0
 
rkulpAuthor Commented:
Arnold,
Thanks for the input. I'll check it out. This appears to work:

  <connectionStrings>
    <add name="HHGCMS.My.MySettings.ServerHouseholdInventoryConnectionString"
      connectionString="Data Source=|ServerDirectory|;Initial Catalog=HouseholdInventory;Persist Security Info=True;User ID=|UserID|;Password=|Password|;Encrypt=True;TrustServerCertificate=True"
      providerName="System.Data.SqlClient" />
    <add name="HHGCMS.My.MySettings.ClientHouseholdInventoryConnectionString"
      connectionString="Data Source=|DataDirectory|\HouseholdInventory.sdf;Max Database Size=2047"
      providerName="Microsoft.SqlServerCe.Client.3.5" />
    <add name="HouseholdInventoryEntities" connectionString="metadata=res://*/HHGCMS.csdl|res://*/HHGCMS.ssdl|res://*/HHGCMS.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=D:\Household Inventory\HouseholdInventory.sdf;Max Database Size=2047&quot;"
      providerName="System.Data.EntityClient" />
  </connectionStrings>

Open in new window


I have some more testing. This got over the permissions issue.
0
 
arnoldCommented:
One option to store the app.confignin %programdata%\ storing the login credentials or are you encrypting, the other option is to use the integrated network credentials, .....


Glad to hear.
0
 
rkulpAuthor Commented:
I'm using SSL. I'll look at moving app.config if this "solution" fails. I'll have to read up on integrated network credentials.
Thanks for sticking with me.
0
 
arnoldCommented:
Encrypting references storing the connection string in a way that is not humanly readable as your current connection configuration is.
0
 
rkulpAuthor Commented:
I understand how to encrypt and decrypt the connection string in app.config. My real problem is how to make it possible for a user to set up the server, etc., before the program uses it to attempt a connection when I've used drag and drop and the data designer to build the dataset. If I wanted to build it all in code I could and that may be what I have to do. Thus far, I haven't been able to figure out (or find) how to modify the connection string when the IDE builds the data source. Everything I've tried thus far fails to work. The server connection is only for synchronization so I may be able to set it all after the program starts anyway, but I'd still like to know the answer to this question anyway.
0
 
arnoldCommented:
Clarified my encryption suggestion based on your ssl comment.

Currently, though, you've not included the initialization part where your application checks for config.app at which point it loads the config and proceeds along to get the data set.  One option is to treat the data in the config.app as a source of non authoritative data where user intervention is needed to confirm it.
0
 
rkulpAuthor Commented:
How and where do I check config.app (or is this app.config?).  I really would want to have confirmation during setup where there is a button to test the connection. The app has a way to change the parameters in the help menu. That, of course, might change depending on what is done first.
You have put up with a lot and I appreciate it.
0
 
rkulpAuthor Commented:
I think it is best to close this question now. I will continue working on the concept. Thanks for your help.
0
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.

All Courses

From novice to tech pro — start learning today.