The problem is that I could not see any exposed properties of a table adapter to allow the connection to be changed dynamically.

My solution is to set up the application to have four connection strings for the four database logins, and create an overridden constructor for each TableAdapter that requires role specific database connections.

For the purpose of this example I have created the following database logins.

The application is written in a layered architecture with the web front end having visibility of the Interfaces layer and the Services layer, and the Services layer having visibility of the Interfaces layer, the Business Entities layer, and the Data Access layer. The data access layer contains all the typed datasets and any other database related objects.

The four connection strings are entered into the Settings tab of the Properties page of the DataAccess project.

As we need to provide a role specific connection to the table adapter we need an object that can create and return a connection.

using System;
using System.Data.SqlClient;
using ConnectionStrings = MyProject.DataAccess.Properties.Settings;

namespace MyProject.DataAccess
{
    public class DatabaseConnection
    {
        /// <summary>
        /// Returns a connection for use with the role name passed in
        /// </summary>
        /// <param name="roleName">
        /// Represents the name of the role to get connection string for.
        /// </param>
        /// <returns>
        /// Returns a Sql Server connection.
        /// </returns>
        public SqlConnection GetConnectionForRole(string roleName)
        {
            SqlConnection connection = null;
            string connectionString = string.Empty;

                connectionString = GetConnectionStringForRole(roleName);
                connection = new SqlConnection(connectionString);
            return connection;

        }

        /// <summary>
        /// Returns a connection string based on the passed in rolename.
        /// </summary>
        /// <param name="roleName">
        /// Represents the name of the role to get connection string for.
        /// </param>
        /// <returns></returns>
        private string GetConnectionStringForRole(string roleName)
        {
            string connectionString = string.Empty;            

            switch (roleName)
            {
                case "DatabaseAdmin":
                    connectionString = ConnectionStrings.Default.DatabaseAdminConnectionString;
                    break;

                case "WebsiteAdmin":
                    connectionString = ConnectionStrings.Default.WebsiteAdminConnectionString;
                    break;

                case "ProductSeller":
                    connectionString = ConnectionStrings.Default.ProductSellerConnectionString;
                    break;

                case "ProductBuyer":
                    connectionString = ConnectionStrings.Default.ProductBuyerConnectionString;
                    break;

                default:
                    throw new ArgumentOutOfRangeException("roleName", "Unknown role name encountered.");
            }

            return connectionString;
        }

    }
}

Next we need to look at the TableAdapter and create a constructor that overrides the default non parametricised constructor. The default constructor for our table adapter looks like this.

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public MyTableAdapter() {
    this.ClearBeforeFill = true;
}

To override this constructor we are going to take advantage of .Net 2.0’s partial classes. Using these we do not have to modify the auto-generated code of the dataset, and neither do we have to attempt to create a class that inherits from the dataset.

using System;
using System.Data.SqlClient;

namespace MyProject.DataAccess.ApplicationDataSetTableAdapters
{
    public partial class MyTableAdapter: System.ComponentModel.Component
    {
        /// <summary>
        /// Used to provide an overridden constructor for the MyTableAdapter
        /// allowing a connection to be passed in.
        /// </summary>
        /// <param name="sqlConnection"></param>
        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
        public MyTableAdapter (SqlConnection sqlConnection)
        {
            this.ClearBeforeFill = true;
            this.Connection = sqlConnection;
        }
    }
}

We take care to set the connection via the “Connection” property rather than just setting the _connection fields as the auto-generated "Connection" property ensure all adapter commands get the correct connection assigned to them, see below.

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
 internal System.Data.SqlClient.SqlConnection Connection {
     get {
         if ((this._connection == null)) {
             this.InitConnection();
         }
         return this._connection;
     }
     set {
         this._connection = value;
         if ((this.Adapter.InsertCommand != null)) {
             this.Adapter.InsertCommand.Connection = value;
         }
         if ((this.Adapter.DeleteCommand != null)) {
             this.Adapter.DeleteCommand.Connection = value;
         }
         if ((this.Adapter.UpdateCommand != null)) {
             this.Adapter.UpdateCommand.Connection = value;
         }
         for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1)) {
             if ((this.CommandCollection[i] != null)) {
                 ((System.Data.SqlClient.SqlCommand)(this.CommandCollection[i])).Connection = value;
             }
         }
     }
 }

So now the code for accessing the adapter should look something like this.

/// <summary>
 /// Used to add an object that implements the IMyItem interface to the database.
 /// </summary>
 /// <param name="product">
 /// Represents the object implementing the IMyItem interface to add to the database.
 /// </param>
 void IMyItem.AddToDatabase(IMyItem product)
 {
     ApplicationAdapters.MyTableAdapter myTableAdapter = null;
     SqlConnection connection = null;
     try
     {
         connection = SqlServerConnectionService.GetConnectionForRole(RoleName);
         myTableAdapter = new ApplicationAdapters. MyTableAdapter (connection);
         MyTableAdapter.Add(product.Id, product.Name);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         SqlServerConnectionService.CloseConnection(connection);
         connection.Dispose();
         myTableAdapter.Dispose();
     }
 }

This code still allows the adapter to open and close the connection as necessary, we have just provided the connection we want. This approach has allowed me to ensure that a user logged in as a ProductBuyer should not have the permission to call procedures they shouldn’t even if I end up with a bug in the website code that allows a non authorised user to reach an administration page with add / update / delete functions.

As always your opinions and comments on this subject or the approach taken to overcome the problem are most welcome. I hope some one may find this useful.

Some error handling in code has been omitted for sake of clarity