Introduction

Updating a connection string programatically can be a pain. If you’re doing this in production application code then I would suggest revisiting how you are providing the connection string value in the first place and whether it should be more than 1 configuration value. But at some point you will likely stumble across this requirement and in this post I will show you have it can be done with minimal pain.

What is a connection string?

If you are unfamilar with what a connection string is it is a relatively simple construct. It’s a defined string format which allows database connectivity code to access the target database and run queries against it. These are used all the time.

A very helpful resource is https://www.connectionstrings.com/ as it helps give examples across a wide variety of data sources.

So why would I want to update it?

You may have a connection string setup and you want to change the target db the query is looking at for example. This could be due to multi tenant requirements or integration testing for dynamic test containers being created on the fly etc. This later scenario is what I found myself doing it for this week.

Don’t Regex

On initial thought you may look at a connection string and think “I can solve this by using regex”. Please don’t. This can work but could also be painful, cause issues and make you pull out your hair.

ConnectionStringBuilder to the Rescue

What is a ConnectionString Builder? It is a class which allows for aiding with constructing a connectionstring. The main one I have used over the years is the SqlConnectionStringBuilder which is used for Microsoft SQL Server connectivity.

The Sql version, as well as others including PostgreSQL, derive from the System.Data.Common.DbConnectionStringBuilder base class.

How do we use it?

First you need access to the connection string value. This should be coming from through from the IConfiguration system of your application, assuming ASP.NET Core, and is loaded through the GetConnectionString method on IConfiguration.

var connectionString = "Data Source=myServerAddress;Initial Catalog=db_1;User Id=my_user;Password=Pa55w0rd!;";

This value can then get used as a constructor argument for the connection string builder.

var builder = new SqlConnectionStringBuilder(connectionString);

Once loaded we can then change whatever values in the connection string we like. In this example I am updating the database name, or initial catalog, through assigning the updated value to the property.

builder.InitialCatalog = "new_db_name";

Then when you’re done manipulating the connection details you can access the updated connectionstring from the builder and then can use it as you did previously.

var updatedConnectionString = builder.ConnectionString;

With the updated example the new connectionstring will be:

Data Source=myServerAddress;Initial Catalog=new_db_name;User ID=my_user;Password=Pa55w0rd!

See how only the initial catalog value has changed and the structure is still as expected.

Conclusion

In this post we have looked briefly at how to manipulate a database connection string in code without using regular expressions. This allows for a safer way to update a connection string on the fly.

What are your thoughts on Connection String Builders? Have you found them helpful? Found some crazy usages of them? Any questions/comments then please reach out to me on Twitter/X @WestDiscGolf.