Solved

Change catalog for connection string dynamically

Posted on 2016-09-28
6
32 Views
Last Modified: 2016-10-23
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
0
Comment
Question by:peternordberg
  • 3
  • 2
6 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41820284
Yes you can change the database name dynamically as long as the other information in the connection string is correct.
0
 

Author Comment

by:peternordberg
ID: 41820410
How?
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 250 total points (awarded by participants)
ID: 41820775
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 9

Accepted Solution

by:
Karen earned 250 total points (awarded by participants)
ID: 41827490
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
 

Author Comment

by:peternordberg
ID: 41827666
Hi and thanks for answers,

I will test both approaches and come back.

Peter
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41855855
Two working solutions posted.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question