7

We are currently developing an application based on NHibernate and ASP.NET MVC and a SQL Server backend. Since I'm fairly new to NHibernate, I'm tryig to understand best practices.

Our application requires every user to have it's own SQL Server database. These databases all have an identical structure.

Our customers are identified with a customercode, e.g. 1500.

We've come up with a custom connection provider for nHibernate, which we already use in our nServiceBus backend services:

public class DynamicConnectionProvider : DriverConnectionProvider
{
    public override IDbConnection GetConnection()
    {
        IDbConnection conn = Driver.CreateConnection();

        try
        {
            var messageExecutionContext = ServiceLocator.Current.GetInstance<ITTTContextProvider>().CurrentContext;
            if (messageExecutionContext.CustomerId == 0)
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dev"]
                    .ConnectionString;
            }
            else
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["default"]
                    .ConnectionString
                    .FormatWith(messageExecutionContext.CustomerId);
            }

            conn.Open();
        }
        catch (Exception)
        {
            conn.Dispose();
            throw;
        }
        return conn;
    }
}

This connection provider checks the customer code in a context object and sets the connectionstring accordingly.

We are planning to provide a HttpContext aware ITTTContextProvider. For this I have two questions:

  1. How can we retrieve the customer code from the url and put it into our context object for every request? when we use the following route?

    <main-site-url>/{customercode}/{controller}/{action}/{id}

  2. Is this method of connecting to several identical databases valid or is it better practice to construct a sessionfactory foreach customer database?

Community
  • 1
  • 1
Rik
  • 483
  • 3
  • 12

2 Answers2

1

In order to get the customercode you need to access the route data, something along the lines of

HttpContextBase currentContext = new HttpContextWrapper(HttpContext.Current); //ServiceLocator.Current.GetInstance<ITTTContextProvider>().CurrentContext; 
RouteData routeData = RouteTable.Routes.GetRouteData(currentContext);  
var cusomterCode = routeData.Values["customercode"]

My second suggestion would be not to put this code in the above snippet provided. Abstract it away. See Joshua's answer which highlights the approach I am thinking of.

Can't really help on the second question, actually not familiar with both frameworks mentioned.

Community
  • 1
  • 1
Ahmad
  • 22,657
  • 9
  • 52
  • 84
  • 1
    Originally in the answer - From what I recall a creating session factory is an expensive operation which is probably something that will factor into your decision. Now considering as well that there are multiple databases, and also dependent on the expected amount of hits per database this may be a key factor. Does't session factories have benefits like caching etc which may be useful in your situation? This may be of help - http://www.codeproject.com/KB/aspnet/NHibernateMultipleDBs.aspx – Ahmad Nov 09 '10 at 12:18
  • Caching is something we will propable need in the future. However, how is 2nd level caching implemented? Per SessionFactory or per connection(string)? – Rik Nov 11 '10 at 10:03
  • @Rik - sorry, as i said I'm not too familiar and don't have enough hands on experience with either framework – Ahmad Nov 11 '10 at 10:13
1

See my recent blog post which shows how to use a subdomain to connect to different databases, although it would be easy to implement your own version of ITenantContext that grabbed the customer code from the request url. Also uses separate session factories for each tenant.

http://www.yellowfeather.co.uk/2011/01/multi-tenancy-on-sharp-architecture/

ChrisR
  • 1,308
  • 1
  • 14
  • 27