Multi-Tenancy System With Separate Databases in MVC

Introduction

With improvements in broadband and web technologies, we are seeing a shift away from traditional desktop applications towards web based systems. The cloud is all the range these days. Accounting packages such as Sage and Quickbooks are being replaced by online alternatives such as Kashflow and Wave Apps.

Rather than creating a unique software instance per customers, the likes of Kashflow and Wave Apps have developed their systems as multi-tenancy applications – a single instance of the software is used by all users. In each case, their data is separated from that of other customers by the architecture of the system.

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple tenants. A tenant is a group of users sharing the same view on the software they use. – Wikipedia

This can be achieved in two ways:

  • Single Database – A single database is created, and all data is stored here. Each record is assigned a tenant key, and only data belonging to that tenant is accessible. Access is restricted by the application software.
  • Multiple Databases – Alternatively, a separate database can be used to store each customers data. Access to the database can then be restricted by using SQL login credentials.

While I have used the single database approach many times, when starting a recent project it became apparent that the multiple database approach may be more suitable.

Advantages of the Multi Database Approach

One of the main advantages of the multi-database approach is that it makes it possible to backup and restore an individual users data. With a single database approach, restoring the database would wipe out changes for all customers, and makes it impossible to offer a roll-back functionality in the event a single customer makes a mistake.

Additionally, should the site become extremely successful, multi-database systems allow data to be moved between servers very easily on an individual basis.

The main selling point however in my case, was the anticipation that a number of clients may require customisation of the system beyond what can be achieved in a multi-tenancy design. By using separate databases, these can be moved to a new server if need be, and fully customised if needed. While this may break the advantage of a multi-tenancy system in the first place, it does offer flexibility and future-proofing that a single database system would not offer.

Architecture of A Multi-Database System

Architecture of a Multi-Tenancy Application

In a multi-database multi-tenancy system, each users data is stored in it’s own database. A separate database is therefore required to hold login details, and provide details of where the users data is stored. This could point to a database on the same server, or a remote data location.

How to Create A Multi-Database System With MVC 6

In Visual Studio, create a new ASP.NET Web Application

Create a new project

 

Select MVC as the template type, and in “Change Authentication”, ensure “Individual User Accounts” is selected. We will use forms authentication for this example.

Select MVC as the type

Ensure Individual User Accounts is selected

First, create a folder called AccountDAL – we will use this to store all the code for accessing the Account data store.

Create a folder for the account DAL

 

Create a new class, and name it DataContext.cs. Add the following code:

public class DataContext : DbContext
{
   public DataContext() : base("accountContext")
   {
   }

   public DbSet<Account> Accounts { get; set; }
   public DbSet<User> Users { get; set; }
}

We will use Entity Framework, code first, to generate a DataContext that represents the data stored in our Account database. There will be two tables:

Accounts – An account represents a single tenant. This data will contain the location of the tenant’s data store. Each account can have multiple users.

Users – contains the login username and password for all users of the system. Each user is tied to an account

Add a connection string to web.config to connect to the account database:

<connectionStrings>
<add name="accountContext"
providerName="System.Data.SqlClient"
connectionString="Server=desktop\SERVER2012; Database=Accounts;
Integrated Security=SSPI" />
</connectionStrings>

While in web.config, we will also check that the auth mode is set to forms authentication:

<authentication mode="Forms">
<forms loginUrl="/Account/Login" cookieless="UseCookies" />
</authentication>

Next, lets create two classes to represent the tables in our database, User.cs and Account.cs:

public class User
{
   public int Id { get; set; }
   public string Email { get; set; }
   public string Password { get; set; }
   public string Name { get; set; }
   public int AccountId { get; set; }
   public virtual Account Account { get; set; }
}

public class Account
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string Database { get; set; }
   public virtual ICollection<User> Users { get; set; }
}

 

We now need to create our database. Create a new database called Accounts, and add two tables called Users and Accounts, as follows:

Accounts:

Users:

Add the following test data to each:

Finally, let’s make a couple of changes to our Login and Logout function to use FormsAuthentication:

public ActionResult Login(LoginViewModel model, string returnUrl)
{
   if (ModelState.IsValid)
   {
      var dataContext = new AccountDAL.DataContext();
      var user = dataContext.Users.FirstOrDefault(x => x.Email == model.UserName && x.Password == model.Password);

      if (user != null)
      {
         FormsAuthentication.SetAuthCookie(model.UserName, false);
         return RedirectToLocal(returnUrl);
      }
      else
      {
          ModelState.AddModelError("", "Invalid username or password.");
      }
   }

   // If we got this far, something failed, redisplay form
   return View(model);
}

 

The above code will create a new instance of our Account DataContext, and check the user and password match an existing user. If so, we will set an auth cookie, which will log in the user.

And logout:

public ActionResult LogOff()
{
   FormsAuthentication.SignOut();
   Session.Abandon();
}

The above code will clear the auth cookie we set earlier. This will have the effect of logging the user out of the system.

If we now run the project, we will now able to log in as either of the two companies we created in the test data. All very straightforward.

 

Now comes the multi database approach.

Let’s create two new databases, one for each of our companies. Call them “Company1” and “Company2”, as we specified in the “Account” table of our test data. In each, create a new table called Jobs, as follows:

 

Add a couple of test jobs in each database:

Company 1 test data:

Company 2 test data:

 

Now, back in Visual Studio, create a folder called SystemDAL to store all our data objects that relate to the actual system.

First, create a new class called DataContext.cs:

public class DataContext : DbContext
{
   public DataContext(string database)
      : base("Data Source=desktop\\Server2012;Initial Catalog=" + database + ";Integrated Security=True")
   {
   }

   public DbSet<Job> Jobs { get; set; }
}

This is where we implement our multi-database logic. Rather than pass in the name of a connection string to the DataContext base constructor, we will instead build our own, using a database name passed in to the DataContext constructor. This will be taken from the Account table in our database.

Create a second class to represent a job object:

public class Job
{
   public string JobName { get; set; }
   public int Id { get; set; }
}

We will now modify the Home\Index() function to load the current users data:

[Authorize]
public ActionResult Index()
{
   // get the current user:
   var accountContext = new AccountDAL.DataContext();
   var user = accountContext.Users.FirstOrDefault(x => x.Email == User.Identity.Name);

   if (user != null)
   {
      // now we have the current user, we can use their Account to create a new DataContext to access system data:
      var systemContext = new SystemDAL.DataContext(user.Account.Database);
      return View(systemContext.Jobs);
   }
   return View();
}

The above code first creates an instance of our Account DataContext, and gets an object representing the current logged in user. From this, we can then create a System DataContext instance, passing in the name of the database we wish to connect to.

Once connected, we can then pass a list of all the companies jobs to the View.

Modify the Index view as follows, replacing the existing code:

@model IQueryable<MultiTenancy.SystemDAL.Job>

@{
   ViewBag.Title = "Home Page";
}

<ul>
@if (Model != null)
{
   foreach (var job in Model)
   {
      <li>@job.JobName</li>
   }
}
</ul>

 

There we have it – a multi-tenancy web application that stores each users data in a separate database!

Read More

Always Hide Your Webstats Code On Your Dev Server

While Google Analytics may have been the bees knees when it comes to web stats in the past, it seems to have taken an almighty tumble from the top spot in recent years. The interface has gone from being one of the cleanest, easiest to use, to one of the most confusing out there.

I decided it was time to consider moving away from Google Analytics, so recently I have been playing around with a few different web stat packages, running multiple at once on the same page. In doing so, I began to notice that some packages were reporting a higher number of views than others.

When I started to dig closer to see what was causing this, I discovered that some of the packages were showing a large number of hits to pages `localhost:page_name`.

Yep, it seems that not all of the packages are clever enough to filter out page views from your development server.

To resolve the issue – I found a nice little hack that will only display the tracking code on the live server.

The ASP.NET property `Request.Url.Host` will reveal the URL by which the site was accessed – by simply checking this value you can tell whether the site is live, or running on a dev server. You can then hide the tracking code accordingly.

@if(Request.Url.Host != "localhost")
{
// show tracking code - site is live
}

Read More

Accessing an ActiveMQ Stomp Feed In C# With Apache.NMS

The following tutorial explains how to connect to a Stomp data feed in C#, by using the Apache.NMS client.

To start with, download the Apache.NMS.Stomp Client.

Once downloaded, open the zip file and extract the following DLLs – copy them to the desktop for now:

/build/<.NET or Mono Version Required>/Release/Apache.NMS.Stomp.dll
/lib/Apache.NMS/<.NET or Mono Version Required>/Apache.NMS.dll

Next, start a new Console Application project in Visual Studio. Add a reference to the two DLLs selected earlier. Also add the following using statement – `using Apache.NMS;`

NOTE: You may receive a compile error that “apache” could not be found. this seems to occur when your project target type is set to “.NET Framework 4 Client Profile”. If you change it to “.NET Framework 4″ this error goes away.

Ok, lets write some code.

In your main() function, create a ConnectionFactory, which we will then use to create a connection and session:

IConnectionFactory factory = new NMSConnectionFactory(new Uri("stomp:tcp://datafeed:61618"));

IConnection connection = factory.CreateConnection("username", "password");
ISession session = connection.CreateSession();

Next subscribe to the feed you wish to access:

IDestination destination = session.GetDestination("topic://topic_name");
IMessageConsumer consumer = session.CreateConsumer(destination);
connection.Start();

We have now connected to the data source and subscribed to a particular feed. Next we need to handle any messages that are received from the feed. We do this by creating a MessageListener:

consumer.Listener += new MessageListener(OnMessage);

That is all that we need in main – simply add a readline statement to stop the program from exiting immediately:

Console.ReadLine();
connection.Close();

We now create a new function called OnMessage, which will be called every time a message is received:

private static void OnMessage(IMessage message)
{
ITextMessage msg = (ITextMessage)message;
message.Acknowledge();

Console.WriteLine(msg.Text);
}

And that is all there is to it! If you run this now, you should see messages arriving from your data feed. Of course you may need to do additional processing on these to extract the data in a more useful format, but that’s a tutorial for another day.

Full application code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Apache.NMS;

namespace ApacheNMS
{
class Program
{
static void Main(string[] args)
{
IConnectionFactory factory = new NMSConnectionFactory(new Uri("stomp:tcp://datafeed:61618"));

IConnection connection = factory.CreateConnection("username", "password");
ISession session = connection.CreateSession();

IDestination destination = session.GetDestination("topic://" + "DATA_FEED");
IMessageConsumer consumer = session.CreateConsumer(destination);

connection.Start();
consumer.Listener += new MessageListener(OnMessage);
Console.WriteLine("Consumer started, waiting for messages... (Press ENTER to stop.)");

Console.ReadLine();
connection.Close();
}

private static void OnMessage(IMessage message)
{
try
{
Console.WriteLine("Median-Server (.NET): Message received");

ITextMessage msg = (ITextMessage)message;
message.Acknowledge();

Console.WriteLine(msg.Text);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("---");
Console.WriteLine(ex.InnerException);
Console.WriteLine("---");
Console.WriteLine(ex.InnerException.Message);
}
}
}
}

Read More