Solved

Change catalog for connection string dynamically

Posted on 2016-09-28
6
41 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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