Storing SQL Con String settings

[dagoose]

Hi All,

I have an app that currently connects to a SQL Server. I am going to change this to allow me to change which SQL Server it is connected to.

Is using the app.config the best place to store these settings?

Does anyone have an example of accessing code from within the app.config?

I have added a config file to my project but not using it before I am fumbling around a bit.

Any help appreciated.

[thecow95]

Here is how I handle mine.

Public Class AppSettingsWriter
    Public Sub SetValue(ByVal key As String, ByVal value As Object)
        Dim ds As New DataSet
        ds.ReadXml(LocalGetConfigFilename)
        Dim dr() As DataRow = ds.Tables("add").Select(String.Concat("key='", key, "'"))
        dr(0).Item("value") = value
        ds.WriteXml(LocalGetConfigFilename)
        ds.Clear()
    End Sub
    Public Function GetValue(ByVal key As String) As String
        Dim ds As New DataSet
        Dim retVal As String
        ds.ReadXml(LocalGetConfigFilename)
 
        Dim dr() As DataRow = ds.Tables("add").Select(String.Concat("key='", key, "'"))
        retVal = dr(0).Item("value")
        ds.Clear()
        Return retVal
    End Function
    Private Function LocalGetConfigFilename() As String
        Return PathtoYourConfigFile & "yourprogram.exe.config"
    End Function
End Class

To set a variable called ServerName to the value in strServer

Dim AW as new AppSettingsWriter
 
  AW.SetValue("ServerName", strServer)

To retrieve the value

Dim AW as new AppSettingsWriter
 
  AW.GetValue("ServerName")

Just make sure you get the right path & name for you config file. It will change the name from App.config to YourProgram.exe.config upon deploying.

I put this code in a seperate class and usually include it in to each of the separate projects.

Good Luck - hope this helps.

[kleinma]

where you store the connection string should also be determined by the security requirements of your app. I would always recommend AT LEAST storing the password in an encrypted file or even prompting for the password at run time. Otherwise it will be stored in viewable plain text and anyone can get it and get into the DB.

Unless this is for an app where security is not an issue?

[dagoose]

Many thanks to you both for your code and your advice, off to the battlefield now to make it work.

Thanks Again

[Zakary]

What is the advantage of using App.Config rather than any other xml file ?

[kleinma]

I think because its easier in code to read in values from it because there are built in functions to obtain data from the app.config file.

like
Dim MyConnString as string = ConfigurationSettings.AppSettings("SQLConnString")

where the app.config looks something like this
<appSettings>
<add key="SQLConnString" value="ConnectionStringHere"/>
</appSettings>

I wrote all that freehand.. so it might take some modification to actually work.. but u should get the idea zak

[Zakary]

I think because its easier in code to read in values from it because there are built in functions to obtain data from the app.config file.

like
Dim MyConnString as string = ConfigurationSettings.AppSettings("SQLConnString")

where the app.config looks something like this
<appSettings>
<add key="SQLConnString" value="ConnectionStringHere"/>
</appSettings>

I wrote all that freehand.. so it might take some modification to actually work.. but u should get the idea zak

Yeah i catch the idea.... it might be easier to read it, but not to write in it, Writing in XML via DataSet is much more easier, and reading form it is not really a big problem too...

Anyways .. everybody might have is own idea on that ... my 0.02¢

[texas]

where you store the connection string should also be determined by the security requirements of your app.

I dont understand what you are saying?

[kleinma]

What I am saying is this:

If I was writing an app that would be used in my office (which is just a few programmers) I would just store the connection string in a constant/variable or in a config file or something of that nature.

If I was going to write an app that is supposed to go out to customers, but it connected to a central SQL Server somewhere, then I would not want to do that, as it would be easy for someone with bad intent to find your connection string, get into your database, and do whatever they want based on what permissions the account they have the info for has. Usually a sql account that is used in apps like .net ones have the ability to add/edit/delete info in tables. What if you put your app out, I download it, get your connection string, log into your SQL Server and delete all your tables. That would not be a very fun day for you.