Change catalog for connection string dynamically

Hi,

I have this scenario where I have several organisations using the same application but have different databases. Right now I solve this through having different installations for each organisation. However when I update this gets tedious of course. So I wonder is it possible for me to just alter the database catalog in the connectionstring and use the same application code for all organisations?

My connecdtion string looks like this in web.config:
<connectionStrings>
    <!--Test-->
     <!--DEFAULT(Peter) CONNECTION--> 
    <add name="connDB" connectionString=" Data Source=.\SQLEXPRESS;Initial Catalog=developmentDB;Integrated Security=True" providerName="System.Data.SqlClient"/>   
  </connectionStrings>

Open in new window


So, my question can I change the database name (catalog=developmentDB) to another database based on some variable I give it dynamically?

Thanks for help!

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?
 
KarenConnect With a Mentor Analyst programmerCommented:
Easiest way is to use a SqlConnectionStringBuilder object to change the catalog, then just use builder.ConnectionString to give you your new connection string.

Dim builder As New SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings("connDB").ConnectionString)
builder.InitialCatalog = "whatever you wish"
' builder.ConnectionString now contains your new connection string

Open in new window

0
 
Fernando SotoRetiredCommented:
Yes you can change the database name dynamically as long as the other information in the connection string is correct.
0
 
Peter NordbergIT ManagerAuthor Commented:
How?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi Peter

Code like the following should do what you want.
Imports System.Web.Configuration

'' Open the Web config file
Dim Configuration = WebConfigurationManager.OpenWebConfiguration("~/")
'' Get the connection string section of web config
Dim section = CType(Configuration.GetSection("connectionStrings"), ConnectionStringsSection)
'' The name of the connection string
Dim csName = "connDB"
'' Connection string to be modified
Dim cs As String = Nothing

'' Make sure we opened the web config file
If Not section Is Nothing Then
    '' Get the current connection string
    cs = section.ConnectionStrings(csName).ConnectionString
    '' Find the start of the string to be modified
    Dim startIdx = cs.IndexOf("Initial Catalog=") + 16
    '' Find the end of the string to be changed
    Dim endIdx = cs.IndexOf(";", startIdx)
    '' The length of the string to change
    Dim length = endIdx - startIdx
    '' Get the string to be replaced
    Dim replaceThis = cs.Substring(startIdx, length)
    '' The catalog name to insert in to the connection string
    Dim replaceWith = "New Catalog Value"
    '' Replace the string
    cs = cs.Replace(replaceThis, replaceWith)
    '' Insert the new connection string into the web section.
    section.ConnectionStrings(csName).ConnectionString = cs
    '' Save the data back to the web config
    Configuration.Save()
End If

Open in new window

0
 
Peter NordbergIT ManagerAuthor Commented:
Hi and thanks for answers,

I will test both approaches and come back.

Peter
0
 
Fernando SotoRetiredCommented:
Two working solutions posted.
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.