Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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
0
peternordberg
Asked:
peternordberg
  • 3
  • 2
2 Solutions
 
Fernando SotoCommented:
Yes you can change the database name dynamically as long as the other information in the connection string is correct.
0
 
peternordbergAuthor Commented:
How?
0
 
Fernando SotoCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
KarenAnalyst 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
 
peternordbergAuthor Commented:
Hi and thanks for answers,

I will test both approaches and come back.

Peter
0
 
Fernando SotoCommented:
Two working solutions posted.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now