Home > English > Dynamically changing the connection string for WCF RIA Services Linq2Sql

Dynamically changing the connection string for WCF RIA Services Linq2Sql

In my last posts, I demonstrated how to provide a dynamic connection string for WCF RIA Services Linq2Entities. I will now show how this is done with Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added a Linq2Sql classes model as well a DomainService Class.

WCF RIA Services - Linq2Sql

One thing that you will notice, is that I also manually created the DashboardDomainService.partial.vb file. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

The main class derives from LinqToSqlDomainService where T is the model we are building our DomainService. In this case it would be DashboardDataContext.

Let’s take a look at partial class now:

public partial class DashboardDomainService
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DashboardDataContext CreateDataContext()
        DashboardDataContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
            if (userSession != null)
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                    // Initialize the connection string builder for the
                    // underlying provider.
                    SqlConnectionStringBuilder sqlBuilder =
                         new SqlConnectionStringBuilder();

                    // Set the properties for the data source.
                    sqlBuilder.DataSource = serverName;
                    sqlBuilder.InitialCatalog = databaseName;
                    sqlBuilder.IntegratedSecurity = false;
                    sqlBuilder.UserID = userName;
                    sqlBuilder.Password = password;

                    string connString = sqlBuilder.ToString();
                    ctx = new DashboardDataContext(connString);

        return ctx;


Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class. I then just call the ToString method to get the newly created connection string.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:


    <add name="Common.Dashboard.Data.Web.My.MySettings.emgov_dataConnectionString"
        connectionString="Data Source=BUNKERHILL;
        Initial Catalog=emgov_data;User Id=emgovuser;Password=xxxx;"
        providerName="System.Data.SqlClient" />


(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…

  1. No comments yet.
  1. November 29, 2014 at 1:20 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: