tip2tail
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you try? (in VB.NET)
Dim strConn As String = "Your Connection String"
Settings.Default[someConne ctionStrin g] = strConn
someConnectionString is the name used in App.config
Ashok
Dim strConn As String = "Your Connection String"
Settings.Default[someConne
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.AppSe ttingsRead er
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("/ /appSettin gs")
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("/ /appSettin gs")
If node = Nothing Then
Throw New System.InvalidOperationExc eption("ap pSettings section not found")
End If
Try
' XPath select setting "add" element that contains this key
Dim addElem As XmlElement = DirectCast(node.SelectSing leNode("// add[@key=' " + key + "']"), XmlElement)
If addElem <> Nothing Then
addElem.SetAttribute("valu e", 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("/ /appSettin gs")
If node = Nothing Then
Throw New System.InvalidOperationExc eption("ap pSettings section not found")
End If
Try
' XPath select setting "add" element that contains this key
Dim addElem As XmlElement = DirectCast(node.SelectSing leNode("// 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("/ /appSettin gs")
If node = Nothing Then
Throw New System.InvalidOperationExc eption("ap pSettings section not found")
End If
' XPath select setting "add" element that contains this key to remove
node.RemoveChild(node.Sele ctSingleNo de("//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(ConfigFile Type.AppCo nfig) Then
docName = ((Assembly.GetEntryAssembl y()).GetNa me()).Name
docName += ".exe.config"
Else
docName = System.Web.HttpContext.Cur rent.Serve r.MapPath( "web.confi g")
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
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.AppSe
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("/
For Each elem As XmlElement In node.ChildNodes
Dim data As String() = {elem.Attributes(0).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("/
If node = Nothing Then
Throw New System.InvalidOperationExc
End If
Try
' XPath select setting "add" element that contains this key
Dim addElem As XmlElement = DirectCast(node.SelectSing
If addElem <> Nothing Then
addElem.SetAttribute("valu
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"
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("/
If node = Nothing Then
Throw New System.InvalidOperationExc
End If
Try
' XPath select setting "add" element that contains this key
Dim addElem As XmlElement = DirectCast(node.SelectSing
' 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"
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("/
If node = Nothing Then
Throw New System.InvalidOperationExc
End If
' XPath select setting "add" element that contains this key to remove
node.RemoveChild(node.Sele
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
docName = ((Assembly.GetEntryAssembl
docName += ".exe.config"
Else
docName = System.Web.HttpContext.Cur
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.
' 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
ASKER
No need for all the VB code - juat that little line!
Goddamnit that was simple!
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
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
ASKER
I am creating a standard Windows Forms app in C# Express 2008
My Connection String line that I used is attached...
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;
I was confused because your post mentioned ASP.NET / WEB.
Anyway, thanks for the info.
Ashok
Anyway, thanks for the info.
Ashok
Dim someConnectionString As String = "Your Connection String"
Settings.Default[someConne
Ashok