What is the best way to store and retrieve an SQL connect string in the field?

I see a lot of code samples with embedded connection strings (to MS SQL databases), and connection strings using localhost.  I guess that’s fine for tutorials and quick starts, but what about actually putting it in production?

I need my VB.NET Windows Forms to go somewhere and get the connection string for my rich client application.  The server names will change (and that’s about all that changes) from roll-out to roll-out.  The connection string can’t be in SQL because you need a connection string to get to it!  So where do you put it, and in a form that system administrators can change it?  .INI file?  Registry?  Flat file?  Environment variable?

Any tips?
Thanks!
geekboystevesAsked:
Who is Participating?
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.

doobdaveCommented:
I would suggest using an App.Config file.

It gets stored as a simple XML file, which is deploye to the same dir as your app.
An admin can smilpy open it up and modify the connection string.

Here's an example app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
      <appSettings>
            <add key="UserName" value="usr" />
            <add key="Password" value="pw" />
            <add key="HostString" value="dbloc" />
      </appSettings>
</configuration>

Here's some sample vb.net code to retrieve the info from the config file:

Dim strUserName As String
Dim strHostString As String

strUserName = System.Configuration.ConfigurationSettings.AppSettings.Item("UserName")
strPassword = System.Configuration.ConfigurationSettings.AppSettings.Get("Password")
strHostString = System.Configuration.ConfigurationSettings.AppSettings.Get("HostString")


Hope this helps.

Regards,

David
Erick37Commented:
You can store the data as a dynamic property in the app.config file.  Here is a brief overview:

Introduction to Dynamic Properties
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriintroductiontoapplicationsettingstorage.asp

You will have to encrypt and decrypt the data for security.

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
geekboystevesAuthor Commented:
Thanks David and Erick.  So I create the app.config file and give the system administrator (my customer) instructions on how to edit the server "key"?  Sounds like a plan.  I'll give it a shot!
Steve
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

geekboystevesAuthor Commented:
The app.config file works very well.  For the sake of the next person wanting to use an app.config file, I am including a little summary of my experience.

How do you create the app.config file using Visual Studio .NET (as opposed to Visual Notepad)?

Select any form in your solution, right-click on just about anything and to go Properties.  Since I wanted to make my SQL Connection change without rebuilding, I used the SqlConnection gizmo below the form.  Expand Dynamic Properties > Advanced > […]

A dialog with one or more checkboxes appears.  Check one and save your project.

After that, you will find an app.config file sitting in the folder with your project.  When you build it a copy of app.config is written to the bin folder and is named YourApplicationName.exe.config.  THAT is the one your user will edit.  YOU edit app.config.

Once the files have been created, it’s pretty easy to edit them.

Beware: If you make a bad entry (like forgetting to close /> an open <) your project will not build.

Using a text editor, I added a custom key called “Developer” and put my name there.  Now, in my app I can display it in a messagebox with this simple code per doobdave (sorry about the line wraps):

        Dim strDeveloperName As String
        strDeveloperName = System.Configuration.ConfigurationSettings.AppSettings.Item("Developer")
        MessageBox.Show("Developer: " & strDeveloperName, "app.config custom key", MessageBoxButtons.OK, MessageBoxIcon.Information)

Changing the Data Source (server name) from (local) to a real server name actually changed where my app looked for its database without a rebuild.  Perfect!  I will find many uses for the app.config file.

Thanks for the help!
Steve


Erick37Commented:
Another way to add the app.config file:

Project->Add New Item...

Select the Application Configuration File from the Templates.
geekboystevesAuthor Commented:
LOL well, if you want to do it the EASY way!  ...learn 100 things a day in this biz.  That's why I love it.
Thanks,
Steve
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
Visual Basic.NET

From novice to tech pro — start learning today.