Solved

Change catalog for connection string dynamically

Posted on 2016-09-28
6
21 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 62

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 62

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 62

Expert Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now