CODEWITHSUNDEEP
c#connectionconnection-string
Liveonthree
Liveonthree

Reputation: 1

How to manipulate SQL connection string

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.

Upvotes: 0

Views: 3151

Answers (5)

alwayslearning
alwayslearning

Reputation: 4633

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.

Upvotes: 2

Abdo
Abdo

Reputation: 14071

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 =)

Upvotes: 0

Manuel
Manuel

Reputation: 11

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...

Upvotes: 1

openshac
openshac

Reputation: 5165

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;
        }
    }

Upvotes: 0

Hans Olsson
Hans Olsson

Reputation: 55059

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.

Upvotes: 0

Related Questions