How to manipulate sql connection string in C#

Question

Hi i'm trying to manipulate the sql connection string so instead of running the original copy of our database it runs from the copy one folder up in our C# project.

Answer 1

The easiest way would probably be that instead of storing the connectionstring as is, put in placeholders for the bits you want to replace with different values. If you use {0}, {1} etc, you could then just use string.Format to insert the correct values during runtime.

Answer 2

I'm not 100% sure what the context of your problem is but the following might work for you. One way would be to let the App.Config do the work:

<configuration>
  <configSections>
    <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />
  </configSections>
<dataConfiguration defaultDatabase="DEV" />
<connectionStrings>
    <add name="DEV" connectionString="Database=xxx;Server=xxx;Trusted_Connection=True" providerName="System.Data.SqlClient" />
    <add name="LIVE" connectionString="Database=xxx;Server=xxx;Trusted_Connection=True" providerName="System.Data.SqlClient" />
</connectionStrings>...

You can then get the default connection string using the following code (which you might want to put in a separate class so you can use it throughout your code).

    public static string DefaultDatabase
    {
        get
        {
            SystemConfigurationSource scs = new SystemConfigurationSource();
            return DatabaseSettings.GetDatabaseSettings(scs).DefaultDatabase;
        }
    }

    public static string DefaultConnectionString
    {
        get
        {
            return ConfigurationManager.ConnectionStrings[Sql.DefaultDatabase].ConnectionString;
        }
    }

Answer 3

Try look at this: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

If you use SQL Server it's much better than trying to manipulate a string...

Answer 4

If you're using xsds/adapters, you'll need to set the connectionString of the adapter manually on application start. If you have your adapters in a separate library you need to overload the adapter's constructor with a signature to which you can pass a connectionString (read below if this is the case). The first-time connectionString you used to create an xsd is cached.

If this is the case, do the following:

Let's say you have User.xsd. Right click then view code. You'll notice User.cs is created if you expand (+) the xsd.

You might have this code generated:

namespace Adapters {


    public partial class User
    {
    }
}

You should add the following:

namespace Adapters.UserTableAdapters
{
    public partial class UsersTableAdapter : global::System.ComponentModel.Component
    {
        public UsersTableAdapter(string connectionString)
        {
            this._clearBeforeFill = true;
            this._connection = new System.Data.SqlClient.SqlConnection();
            this._connection.ConnectionString = connectionString;
        }
    }
}

Now, when you're initializing UsersTableAdapter, you pass the connectionString to it.

Because I use the connectionString all over the website and it's annoying to keep reading web.config for it, I created this simple class:

namespace Data
{
    public class DataModule
    {
        private static string _connectionString = String.Empty;


        public static string ConnectionString
        {
            get
            {
                if (_connectionString == String.Empty)
                    throw new Exception("DataModule not initialized");
                return _connectionString;
            }
        }

        public static void initialize(string connectionString)
        {
            _connectionString = connectionString;
        }
    }
}

On ApplicationStart, I call

DataModule.Initialize(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

Note that this class supports only 1 connectionString. You can easily modify it to support many.

We can help you more if you describe your environment better =)

Answer 5

The DbConnectionStringBuilder class is a nice way to manipulate various key/value pairs of a connection string. You should use the relevant provider specific connectionstringbuilder class.