Link to home
Start Free TrialLog in
Avatar of tip2tail
tip2tailFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Set Database Location/ConnectionString at runtime

Hi there,

I have a new application and I'd like to be able to chnage the location of the access database it connects to at runtime.  At the moment the installer I have created installs the database file to the %appdata%\company\app folder, which is different to the location of the debug DB file (next to the application itselft).

I want to have a line of code that changes the connection string to point at the correct location but I cannot seem to find a way to change the connectionstring property.  Every method I try seems to say Read Only and doing a search seems to throw up more ASP.NET / WEb / VB code than what I need!

I really hope someone can help as, eventually, I plan on giving the user the chance to change the location of the database via my settings dialog and then store that information for each run - so I would need to load that then change the connectionstring on each run.

Thanks,

Mark Young
ASKER CERTIFIED SOLUTION
Avatar of Ashok
Ashok
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Could you try?  (in VB.NET)

Dim someConnectionString As String = "Your Connection String"
Settings.Default[someConnectionString] = strConn

Ashok
Could you try?  (in VB.NET)

Dim strConn As String = "Your Connection String"
Settings.Default[someConnectionString] = strConn

someConnectionString is the name used in App.config

Ashok
If above shortcut way does not work.....

Imports System
Imports System.Xml
Imports System.Configuration
Imports System.Collections
Imports System.Reflection
Imports System.Diagnostics
Imports System.Data

Namespace BDS.Core.Config
      Public Enum ConfigFileType
            WebConfig
            AppConfig
      End Enum

      Public Class AppConfig
            Inherits System.Configuration.AppSettingsReader
            Public docName As String = [String].Empty
            Private node As XmlNode = Nothing

            Private _configType As Integer
            Public Property ConfigType() As Integer
                  Get
                        Return _configType
                  End Get
                  Set
                        _configType = value
                  End Set
            End Property

            Public Function GetAppSettings() As DataSet
                  Dim cfgDoc As New XmlDocument()
                  LoadConfigDoc(cfgDoc)

                  ' store it in a dataset
                  Dim ds As New DataSet()

                  ' create the dataset
                  Dim table As DataTable = ds.Tables.Add()
                  table.Columns.Add("Key")
                  table.Columns.Add("Value")

                  ' retrieve the appSettings node
                  node = cfgDoc.SelectSingleNode("//appSettings")

                  For Each elem As XmlElement In node.ChildNodes
                        Dim data As String() = {elem.Attributes(0).Value, elem.Attributes(1).Value}
                        table.Rows.Add(data)
                  Next

                  Return ds
            End Function

            Public Function SetValue(key As String, value As String) As Boolean
                  Dim cfgDoc As New XmlDocument()
                  LoadConfigDoc(cfgDoc)
                  ' retrieve the appSettings node
                  node = cfgDoc.SelectSingleNode("//appSettings")

                  If node = Nothing Then
                        Throw New System.InvalidOperationException("appSettings section not found")
                  End If

                  Try
                        ' XPath select setting "add" element that contains this key    
                        Dim addElem As XmlElement = DirectCast(node.SelectSingleNode("//add[@key='" + key + "']"), XmlElement)
                        If addElem <> Nothing Then
                              addElem.SetAttribute("value", value)
                        Else
                              ' not found, so we need to add the element, key and value
                              Dim entry As XmlElement = cfgDoc.CreateElement("add")
                              entry.SetAttribute("key", key)
                              entry.SetAttribute("value", value)
                              node.AppendChild(entry)
                        End If
                        'save it
                        SaveConfigDoc(cfgDoc, docName)
                        Return True
                  Catch
                        Return False
                  End Try
            End Function

            Public Function CreateValue(key As String, value As String) As Boolean
                  Dim cfgDoc As New XmlDocument()
                  LoadConfigDoc(cfgDoc)
                  ' retrieve the appSettings node
                  node = cfgDoc.SelectSingleNode("//appSettings")

                  If node = Nothing Then
                        Throw New System.InvalidOperationException("appSettings section not found")
                  End If

                  Try
                        ' XPath select setting "add" element that contains this key    
                        Dim addElem As XmlElement = DirectCast(node.SelectSingleNode("//add[@key='" + key + "']"), XmlElement)
                        ' not found, so we need to add the element, key and value
                        If addElem = Nothing Then
                              Dim entry As XmlElement = cfgDoc.CreateElement("add")
                              entry.SetAttribute("key", key)
                              entry.SetAttribute("value", value)
                              node.AppendChild(entry)
                        End If
                        'save it
                        SaveConfigDoc(cfgDoc, docName)
                        Return True
                  Catch
                        Return False
                  End Try
            End Function

            Private Sub SaveConfigDoc(cfgDoc As XmlDocument, cfgDocPath As String)
                  Try
                        Dim writer As New XmlTextWriter(cfgDocPath, Nothing)
                        writer.Formatting = Formatting.Indented
                        cfgDoc.WriteTo(writer)
                        writer.Flush()
                        writer.Close()
                        Return
                  Catch
                        Throw
                  End Try
            End Sub

            Public Function RemoveElement(elementKey As String) As Boolean
                  Try
                        Dim cfgDoc As New XmlDocument()
                        LoadConfigDoc(cfgDoc)
                        ' retrieve the appSettings node
                        node = cfgDoc.SelectSingleNode("//appSettings")
                        If node = Nothing Then
                              Throw New System.InvalidOperationException("appSettings section not found")
                        End If
                        ' XPath select setting "add" element that contains this key to remove  
                        node.RemoveChild(node.SelectSingleNode("//add[@key='" + elementKey + "']"))

                        SaveConfigDoc(cfgDoc, docName)
                        Return True
                  Catch
                        Return False
                  End Try
            End Function


            Private Function LoadConfigDoc(cfgDoc As XmlDocument) As XmlDocument
                  ' load the config file
                  If Convert.ToInt32(ConfigType) = Convert.ToInt32(ConfigFileType.AppConfig) Then

                        docName = ((Assembly.GetEntryAssembly()).GetName()).Name
                        docName += ".exe.config"
                  Else
                        docName = System.Web.HttpContext.Current.Server.MapPath("web.config")
                  End If
                  cfgDoc.Load(docName)
                  Return cfgDoc
            End Function

      End Class
End Namespace

' this should work

Dim ac As New AppConfig()
ac.ConfigType = DirectCast(ConfigFileType.AppConfig, Integer)

' create a new key
ac.CreateValue("MyKey", "This is the value")
' set the value of the ConnString key
ac.SetValue("ConnString", "this is my connection string")

HTH
Ashok
Avatar of tip2tail

ASKER

No need for all the VB code - juat that little line!

Goddamnit that was simple!
I cannot believe it worked.

Could you tell me what type of project did you create?

I have ASP.NET website, where I was not able to test this.
Also, is your project using App.config or Web.Config?
If you paste part of your connection string section, it would help.

Thanks,
Ashok
I am creating a standard Windows Forms app in C# Express 2008

My Connection String line that I used is attached...


string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\tip2tail.co.uk\\diskLibrary\\softwaredb.mdb;Persist Security Info=True";
            Settings.Default["connectionStr"] = connStr;

Open in new window

I was confused because your post mentioned ASP.NET / WEB.

Anyway, thanks for the info.

Ashok