SQL Azure Federation with S#arp Architecture

Question

I’m using S#harp Architecture, has anyone found a way to access SQL Azure Federations with it?

I am aware that the following command must be executed outside of the transaction since SQL Azure does not allow “use Federation” statements within a transaction.

use Federation CustomerFederation (CustomerID=2) with reset, filtering=on 
GO
<some sql statement...>
GO

There is another post on here that shows an example with creating a custom NHibernate Session class, but how can this be accomplished/extended using S#arp Architecture?

I'm also aware that there are other sharding options to SQL Azure Federation such as NHibernate.Shards or a multi-tenant S#arp Architecture extension but, please, keep to answering the question as opposed to providing other options.

I know I’m not the only person using S#arp Architecture and SQL Azure Federations and Google hasn't provided much so if anyone else out their has found a solution then, please, share.

Answer 1

Since no one has yet to respond to my post I am responding to it after several days of research. I was able to integrated with S#harp with 1 interface and 3 classes (I was hoping their would be an out of the box solution?).

The code provided below can be copied and pasted to any application and it should just work. The only exception is the FederationSessionHelper class. This is specific to each application as to were you are getting the info may change. I have an app setting section within my web.config that has the Federation name etc. Also, when the user authenticates, I parse the root url they are comming from then query the Federation Root to find out what tenant they are (I have a custom Tenant table I created). I then place the tenant ID in session under key "FederationKeyValue_Key" which will then be used in the FederationSession class to build the Use Federation statement.

/// <summary>
/// Interface used to retrieve app specific info about your federation.
/// </summary>
public interface IFederationSessionHelper
{
    string ConnectionString { get; }
    string FederationName { get; }
    string DistributionName { get; }
    string FederationKeyValue { get; }
}

/// <summary>
/// This is were you would get things specific for your application. I have 3 items in the web.config file and 1 stored in session. You could easily change this to get them all from the repository or wherever meets the needs of your application.
/// </summary>
public class FederationSessionHelper : IFederationSessionHelper
{
    private const string ConnectionStringKey = "ConnectionString_Key";
    private const string FederationNameKey = "FederationName_Key";
    private const string DistributionNameKey = "DistributionName_Key";
    private const string FederationKeyValueKey = "FederationKeyValue_Key";

    public string ConnectionString { get { return ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString; } }
    public string FederationName { get { return ConfigurationManager.AppSettings[FederationNameKey]; } }
    public string DistributionName { get { return ConfigurationManager.AppSettings[DistributionNameKey]; } }

    //When user authenitcates, retrieve key value and store in session. This will allow to retrieve here.
    public string FederationKeyValue { get { return Session[FederationKeyValueKey]; } }       
}

/// <summary>
/// This is were the magic begins and where the integration with S#arp occurs. It manually creates a Sql Connections and adds it the S#arps storage.  It then runs the Use Federation command and leaves the connection open. So now when you use an NhibernateSession.Current it will work with Sql Azure Federation.
/// </summary>
public class FederationSession : IDisposable
{
    private SqlConnection _sqlConnection;

    public void Init(string factoryKey,
                       string federationName,
                       string distributionName,
                       string federationKeyValue,
                       bool doesFilter,
                       string connectionString)
    {
        var sql = string.Format("USE FEDERATION {0}({1} = '{2}') WITH RESET, FILTERING = {3};", federationName, distributionName, federationKeyValue, (doesFilter) ? "ON" : "OFF");
        _sqlConnection = new SqlConnection(connectionString);

        _sqlConnection.Open();
        var session = NHibernateSession.GetSessionFactoryFor(factoryKey).OpenSession(_sqlConnection);
        NHibernateSession.Storage.SetSessionForKey(factoryKey, session);

        var query = NHibernateSession.Current.CreateSQLQuery(sql);
        query.UniqueResult();
    }

    public void Dispose()
    {
        if (_sqlConnection != null && _sqlConnection.State != ConnectionState.Closed)
            _sqlConnection.Close();
    }
}

/// <summary>
/// This was just icing on the cake.  It inherits from S#arps TransactionAttribute and calls the FederationSession helper to open a connection.  That way all you need to do in decorate your controller with the newly created [FederationTransaction] attribute and thats it.
/// </summary>
public class FederationTransactionAttribute : TransactionAttribute
{
    private readonly string _factoryKey = string.Empty;
    private bool _doesFilter = true;

    /// <summary>
    ///     When used, assumes the <see cref = "factoryKey" /> to be NHibernateSession.DefaultFactoryKey
    /// </summary>
    public FederationTransactionAttribute()
    { }

    /// <summary>
    ///     Overrides the default <see cref = "factoryKey" /> with a specific factory key
    /// </summary>
    public FederationTransactionAttribute(string factoryKey = "", bool doesFilter = true)
        : base(factoryKey)
    {
        _factoryKey = factoryKey;
        _doesFilter = doesFilter;
    }        

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        var federationSessionHelper = ServiceLocator.Current.GetInstance<IFederationSessionHelper>();
        var factoryKey = GetEffectiveFactoryKey();
        new FederationSession().Init(factoryKey,
                                        federationSessionHelper.FederationName,
                                        federationSessionHelper.DistributionName,
                                        federationSessionHelper.FederationKeyValue,
                                        _doesFilter,
                                        federationSessionHelper.ConnectionString);

        NHibernateSession.CurrentFor(factoryKey).BeginTransaction();
    }

    private string GetEffectiveFactoryKey()
    {
        return String.IsNullOrEmpty(_factoryKey) ? SessionFactoryKeyHelper.GetKey() : _factoryKey;
    }        
}

Now I am able to replace S#arp's [Transaction] attribute with the newly created [FederationTransaction] as follows:

[HttpGet] 
[FederationTransaction]
public ActionResult Index()
{
   var viewModel = NHibernateSession.Current.QueryOver<SomeDemoModel>().List()
   return View(viewModel);            
}

None of the code within the Controller needs to know that its using Sql Azure Federation. It should all just work.

Any thoughts? Anyone found a better solution? Please, share.