Building MVC Application With Multiple Database Support Using Ninject

13. May 2013 14:33

API ASP.NET MVC C# 

I used to have BlogEngine.NET as my blogging platform before switching to WordPress, and that is because of my poor hosting provider who can’t afford few MBs on the shared hosting plans. I like BlogEngine.NET a lot because it is in .NET and on the other hand WordPress is on PHP which I don’t know. BlogEngine.NET is still evolving and is getting better with every release. BlogEngine.NET supports multiple databases like SQL Server, SQL Server CE, MySQL, XML file, VistaDB and may be few more. As it is open-source, I jumped into the code and found it to be a bit hard to get through its implementation. To be frank, I didn’t look into that deep!! but it is not implementing what I was expecting it was. What I was expecting was the use of dependency injection (DI).

I am constantly working on new projects open-source and freelanced and few of them are for fun. The application I am working on right now requires a multiple DB support. The main problem I am going to face is not writing different functions for different DB operations, but having them integrated at the time when the user selects the database. BlogEngine.NET implements it pretty impressively, but I was expecting something else. For my project I will be using Ninject. If you want to do for your application, then this is how you can do it.

Create a new MVC internet application. First thing that we need to do is to add Ninject reference to the project by firing the below Nuget command. Remember that for MVC application you need to use Ninject.MVC3 and for other applications you need to use Ninject only: Install-Package Ninject

Install-Package Ninject.MVC3

When the library is installed successfully, you will notice a file under App_Start folder named NinjectWebCommon.cs. This is a very important file and we will look into it this later when we are done with other things.

What I am trying to achieve here is that my application would be able to support multiple databases and I also want to keep my code clean. I don’t want to handle it using some if..else conditions. Ninject does the work for me pretty well here. I will create an interface that will have all the functions which my wrapper classes will implement. For the sake of simplicity for this example I am going to perform basis CRUD operations, I will be writing code to implement two database support and will be using a custom written ORM called Dapper to communicate to the DB (you can use EF if you wish). I will name my interface IDBProvider.

public interface IDBProvider
{
    bool Add(Friends friend);
    bool Update(int Id, Friends friend);
    bool Delete(int Id);
    IEnumerable ListFriends();
    Friends ListFriend(int Id);
}

As you can see from the above code that I am using Friends model to save details of my friend. So here is my model.

public class Friends
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Skype { get; set; }
}

After this I am going to have support for two databases, SQL Server and SQL Server CE (4.0). For this I will create 2 classes which will implement the IDBProvider interface naming SQLHelper.cs and SQLCEHelper.cs respectively.

SQLHelper.cs

public class SQLHelper : IDBProvider
{
    string ConStr = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
 
    public bool Add(Friends friend)
    {
        bool Added = false;
 
        try
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                string insertSQL = "INSERT INTO Friends(Name, Email, Skype) VALUES (@Name, @Email, @Skype)";
                con.Open();
                con.Execute(insertSQL, new
                {
                    friend.Name,
                    friend.Email,
                    friend.Skype
                });
 
                Added = true;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
 
        return Added;
    }
 
    public IEnumerable ListFriends()
    {
        try
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                string strQuery = "Select Id, Name, Email, Skype FROM Friends";
                con.Open();
                var AllFriends = con.Query(strQuery);
 
                return AllFriends;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
    }
 
    public Friends ListFriend(int Id)
    {
        try
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                string strQuery = "SELECT Id, Name, Email, Skype FROM Friends WHERE Id=" + Id;
                con.Open();
                var friend = con.Query(strQuery).Single();
                return friend;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
    }
 
    public bool Update(int Id, Friends friend)
    {
        bool Update = false;
        try
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                string strUpdate = "UPDATE Friends SET Name = @Name, Email = @Email, Skype = @Skype WHERE Id = " + Id;
                con.Open();
                con.Execute(strUpdate, new
                {
                    friend.Name,
                    friend.Email,
                    friend.Skype
                });
 
                Update = true;
            }
 
        }
        catch (Exception x)
        {
            throw x;
        }
 
        return Update;
    }
 
    public bool Delete(int Id)
    {
        bool Deleted = false;
        try
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                string strDelete = "DELETE FROM Friends WHERE Id = " + Id;
                con.Open();
                int i = con.Execute(strDelete);
                if (i > 0)
                    Deleted = true;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
 
        return Deleted;
    }
}

SQLCEHelper.cs

public class SQLCEHelper : IDBProvider
{
    string ConStr = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
 
    public bool Add(Friends friend)
    {
        bool Added = false;
 
        try
        {
            using (SqlCeConnection con = new SqlCeConnection(ConStr))
            {
                string insertSQL = "INSERT INTO Friends(Name, Email, Skype) VALUES (@Name, @Email, @Skype)";
                con.Open();
                con.Execute(insertSQL, new
                {
                    friend.Name,
                    friend.Email,
                    friend.Skype
                });
 
                Added = true;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
 
        return Added;
 
    }
 
    public bool Update(int Id, Friends friend)
    {
        bool Update = false;
        try
        {
            using (SqlCeConnection con = new SqlCeConnection(ConStr))
            {
                string strUpdate = "UPDATE Friends SET Name = @Name, Email = @Email, Skype = @Skype WHERE Id = "+Id;
                con.Open();
                con.Execute(strUpdate, new
                {
                    friend.Name,
                    friend.Email,
                    friend.Skype
                });
 
                Update = true;
            }
 
        }
        catch (Exception x)
        {
            throw x;
        }
 
        return Update;
    }
 
    public bool Delete(int Id)
    {
         bool Deleted = false;
         try
         {
             using (SqlCeConnection con = new SqlCeConnection(ConStr))
             {
                 string strDelete = "DELETE FROM Friends WHERE Id = " + Id;
                 con.Open();
                 int i = con.Execute(strDelete);
                 if (i > 0)
                     Deleted = true;
             }
         }
         catch (Exception x)
         {
             throw x;
         }
 
         return Deleted;
    }
 
    public IEnumerable ListFriends()
    {
        try
        {
            using (SqlCeConnection con = new SqlCeConnection(ConStr))
            {
                string strQuery = "Select Id, Name, Email, Skype FROM Friends";
                con.Open();
                var AllFriends = con.Query(strQuery);
 
                return AllFriends;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
    }
 
    public Friends ListFriend(int Id)
    {
        try
        {
            using (SqlCeConnection con = new SqlCeConnection(ConStr))
            {
                string strQuery = "SELECT Id, Name, Email, Skype FROM Friends WHERE Id=" + Id;
                con.Open();
                var friend = con.Query(strQuery).Single();
                return friend;
            }
        }
        catch (Exception x)
        {
            throw x;
        }
    }
}

The question now here is that how would the application will know which DB to use as a data store? For this I made few settings in my web.config file. In the appSettings section I added a setting that will hold the value which we use as an identifier and connect to the DB. You can have different approach to achieve this, it is not at all necessary to use what I have used here. My app setting in web.config looks something like this:

<add key="DBProvider" value="SQL"/>

When reading this settings I will be dynamically calling or injecting the code on the basis of the value of DBProvider in my app settings. In this case I will bind SQLHelper when the value is SQL and SQLCEHelper when the value is SQLCE. In the start of the tutorial I mentioned about the NinjectWebCommon.cs file in the App_Start folder. Here we will be looking into CreateKernel method as this is the place where I implement my helper classes.

private static IKernel CreateKernel()
{
    var kernel = new StandardKernel();
    kernel.Bind<Func<IKernel>>().ToMethod(ctx => () => new Bootstrapper().Kernel);
    kernel.Bind<IHttpModule>().To<HttpApplicationInitializationHttpModule>();
 
    if (ConfigurationManager.AppSettings["DBProvider"].ToString().ToLower() == "sql")
    {
        kernel.Bind<IDBProvider>().To<SQLHelper>();
    }
    else
    {
        kernel.Bind<IDBProvider>().To<SQLCEHelper>();
    }
 
    RegisterServices(kernel);
    return kernel;
}

Notice the highlighted code. I am conditionally binding the implementation on the basis of the settings in my web.config file. What exactly is happening here is that the code is being injected depending on the conditions and this is why we call it dependency injection.

To get the benefit of this, we need to make few changes to our controller.

private readonly IDBProvider _dbProvider;
 
public HomeController(IDBProvider dbProvider)
{
    this._dbProvider = dbProvider;
}

I have a default constructor which takes the IDBProvider as a parameter. The IDBProvider object is all we need which provides us all its implementation. To add a friend to DB you just have to call the Add method like _dbProvider.Add(friend). Same for edit, update, delete and list all the friends. You noticed that no where in the code I have made use of the SQLHelper.cs or SQLCEHelper.cs classes. This is the awesomeness of DI.

This is all it and now your application is flexible enough to use multiple databases. I strongly recommend you to read the documentation on Ninject at Ninject.org as it is vast and can be a life saver in many scenarios.

 No Rating

ASYNC Controller In MVC 4

30. April 2013 22:17

.NET Framework ASP.NET MVC C# Visual Studio 

I have build many small applications so far in MVC to help myself in understanding the framework more deeply. Now I am planning to write a blog engine on MVC. It will not be an easy task but I will be going to learn a lot of things. As I was planning things ahead I came across a problem where I have to load my blog stats like FeedBurner readers, Facebook page likes, Google +1 count, twitter followers inside a div on the page. The problem I see here is that I am going to make multiple cross-domain calls in order to get the stats. I can’t avoid that though but this will cause my blog to load slowly. I know about Async and Task Parallel Library(TPL) but the question is how do I implement this in my application? I do a quick web search and found something which is called Async controller in MVC4. You can also have AsynController in MVC3 but that is not pretty if you start comparing it with MVC4.

For the simplicity of the tutorial I am going to make 2 calls: get twitter followers count and facebook page likes. In MVC3, if you want to perform Async operations then you have to inherit your controller with AsyncController like this.

public class HomeController : AsyncController

But MVC4 is much cleaner. Let’s take a look at the default implementation of the calls I am making to Twitter and Facebook to get the follower count and page likes respectively. Here are my 2 methods in HomeController.

private string GetTwitterFollowersCount(string Username)
{
    XDocument xdoc = XDocument.Load("http://api.twitter.com/1/users/show.xml?screen_name=" + Username + "&include_entities=false");
    return (from item in xdoc.Descendants("user")
            select item.Element("followers_count").Value)
            .SingleOrDefault();
}
 
private string GetFacebookLikes(string FaceBookPageURL)
{
    string uri = "https://api.facebook.com/method/fql.query?query=select%20%20like_count,%20total_count,%20share_count,%20click_count%20from%20link_stat%20where%20url=%22" + FaceBookPageURL + "%22";
    return Convert.ToString((from elem in XElement.Load(uri).Descendants()
                   where elem.Name.LocalName == "like_count"
                   select elem).First<XElement>().Value);
}

Nothing fancy in the above two methods. When I call these 2 methods one after the other, they will just get the data from a different source and render the result in the view. That’s fine as it’s my requirement. Now when I run my application it now takes a longer time to render the view. Before I explain this further take a look at my Index().

public ActionResult Index()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
    ViewBag.TwitterFollowers = GetTwitterFollowersCount("audi");
    ViewBag.FacebookLikes = GetFacebookLikes("http://facebook.com/audi");
    watch.Stop();
    Int64 elapsedTime = watch.ElapsedMilliseconds;
    ViewBag.Time = elapsedTime.ToString();
    return View();
}

This is because Controllers are synchronous by default and it will process the request one after the other i.e. it will wait for the first task to complete and then move on to the next task. In my case the controller will execute the GetFollowersCount method and only after the completion of this method it will shift to the next method GetFacebookLikes. This could be a long running task or just imagine if you are retrieving heavy data from different sources. The view or the page will not render till the execution of both the methods will not get completed. To get a rough idea of the time elapsed in making synchronous calls in the above controller, I have used StopWatch(System.Diagnostics) class. I don’t know how accurate, but I still used it to have an approximate idea of the time. This is the output of the above code.

It took 1588ms to complete 2 requests. Now if I implement async in the controller I will see a drastic improvement. It is not just the controller ActionResult which I have to change to support async calls but I also have to update the 2 methods which I am using to collect the stats. Here are my updated methods.

private Task<string> GetTwitterFollowersCountAsync(string Username)
{
    return Task.Run(() =>
    {
        XDocument xdoc = XDocument.Load("http://api.twitter.com/1/users/show.xml?screen_name=" + Username + "&include_entities=false");
        return (from item in xdoc.Descendants("user")
                select item.Element("followers_count").Value).SingleOrDefault();
    });
}
 
private Task<string> GetFacebookLikesAsync(string FaceBookPageURL)
{
    return Task.Run(() =>
    {
        string uri = "https://api.facebook.com/method/fql.query?query=select%20%20like_count,%20total_count,%20share_count,%20click_count%20from%20link_stat%20where%20url=%22" + FaceBookPageURL + "%22";
        return Convert.ToString((from elem in XElement.Load(uri).Descendants()
                                 where elem.Name.LocalName == "like_count"
                                 select elem).First<XElement>().Value);
    });
}

Pay attention to the GetTwitterFollwersCountAsync and GetFacebookLikesAsync return type. Previously they were only string, and now I have changed their return type to Task. I have put the code inside Task.Run() as it will queues the specified work to run on the Thread Pool and returns a Task(TResult) handle for that work. I have changed the ActionResult to handle the async feature. As you can see that this controller method does not return ActionResult instead it return Task. Here is my updated ActionResult.

public async Task<ActionResult> Index()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
    Task<string> twitterFollowers = GetTwitterFollowersCountAsync("audi");
    Task<string> facebookLikes = GetFacebookLikesAsync("http://facebook.com/audi");
    await Task.WhenAll(twitterFollowers, facebookLikes);
    ViewBag.TwitterFollowers = await twitterFollowers;
    ViewBag.FacebookLikes = await facebookLikes;
    watch.Stop();
    Int64 elapsedTime = watch.ElapsedMilliseconds;
    ViewBag.Time = elapsedTime.ToString();
    return View();
}

The code is almost the same but 3 lines are making a difference (highlighted). Task will hold the value returned from the updated async methods. MSDN defines the await operator as:

The await operator is applied to a task in an asynchronous method to suspend the execution of the method until the awaited task completes. The task represents ongoing work.

Task.WhenAll() will creates a task that will complete when all of the supplied tasks have completed. This actually parallelize the request. Lets check the output and compare both.

The difference is noticeable…607ms!! is half way down and a huge performance benefit. This is a small example and the real performance can be noticed when you work with real scenarios. I have used MVC4 with .NET 4.5 to use awesomeness of asynchronous controller. I hope this gives you an idea on how you can use async controllers in MVC4.

 No Rating

Attribute Routing In MVC

26. August 2012 14:33

ASP.NET ASP.NET MVC Web 

I took a look at Stack Overflow Stack-Exchange Data Explorer that is built in MVC and is open-source application to query the Stack Exchange data dump that is being provided by Stack Exchange team in every month or few. I do have a dump, but I was more curious to know about the application that they built to query the database. And I must admit that I learned tons of things. I came to know the power of Micro-ORM (Dapper) over Entity-Framework (EF) and one more pretty thing that attracted me was the routing mechanism used in the application. I strongly recommend that you check out this tool/application and take a deep dive in the code, lot of things to learn.

Before we start writing the code, we'll take a quick look at a NuGet package AttributeRouting. You can find the source code at GitHub in case if you are interested. The documentation is simple and explains almost every bit of the package. It's incredibly easy.

The NuGet package I am using is for MVC, if you want to do the same with your Web API application then use this package.

To see how easy it is to implement routing in your MVC application. Start Visual Studio and create a new MVC4 (Internet) application and fire up the below NuGet command.

This command just don't install one single package but also some other dependent packages. Here is the output when I fire the above NuGet command:

PM> Install-Package AttributeRouting
Attempting to resolve dependency 'AttributeRouting.Core (≥ 2.5.1.0)'.
Attempting to resolve dependency 'AttributeRouting.Core.Web (≥ 2.5.1.0)'.
Attempting to resolve dependency 'WebActivator (≥ 1.0.0.0)'.
Successfully installed 'AttributeRouting.Core 2.5.1.0'.
Successfully installed 'AttributeRouting.Core.Web 2.5.1.0'.
Successfully installed 'WebActivator 1.0.0.0'.
Successfully installed 'AttributeRouting 2.5.1.0'.
Successfully added 'AttributeRouting.Core 2.5.1.0' to MVCAttrRouteTest.
Successfully added 'AttributeRouting.Core.Web 2.5.1.0' to MVCAttrRouteTest.
Successfully added 'WebActivator 1.0.0.0' to MVCAttrRouteTest.
Successfully added 'AttributeRouting 2.5.1.0' to MVCAttrRouteTest.

Now before you make any changes to the web application, hit CTRL+F5. Hover or click on the links which are on the top which are About and Contact. The URL rendered in the address bar is something like this (as what specified in your application Global.asax file).

For About View:

/Home/About

For Contact View:

/Home/Contact

The Home that we see here is the name of the controller being used. I don't want the name of the controller to be visible to my site visitors or maybe I just want a different name there. Usually, I would have switch to my application Global.asax file and register a new route there. But now I can now control my routes within controller itself. This is pretty cool for and a time saver, as I do not have to avoid working with Global.asax. This is the simplest implementation of AttributeRouting. Below are the 2 Home controller methods About and Contact:

[GET("About")]
public ActionResult About()
{
    ViewBag.Message = "Your app description page.";
    return View();
}

[GET("Contact")]
public ActionResult Contact()
{
    ViewBag.Message = "Your contact page.";
    return View();
}

There are no changes in the method, only the routing attribute has been added. The URLs for About and Contact are now free from the controller name Home. Now we look at a second condition where I want to have a totally different URL for About view or to be specific I would say I want to have outbound URLs and this will require RouteName property with the routing attribute. This is called Named Routes. I am going to change the URL to /App/AboutMe.

[GET("App/AboutMe", RouteName = "About")]
public ActionResult About()
{
    ViewBag.Message = "Your app description page.";
    return View();
}

Working with parameters without routing can lead to very ugly URLs or some un-wanted params in the URLs. Again to have a clean URL we can handle the parameters passed to the controller action. I have added a new view named Welcome (no scaffolding) in the Home controller and added a ViewBag property GreetMessage. When this view is called, it will display a greeting message in a heading. The GreetMessage will hold the name of the person, which is our parameter. Below is our controller action:

[GET("App/Greet/{name}")]
public ActionResult Welcome(string Name)
{
    ViewBag.GreetMessage = "Welcome " + Name;
    return View();
}

The URL that is rendered on the browser is:

http://domain.com/App/Greet/Prashant

/App/Greet/ is the URL we want to show and Prashant is the parameter is being passed to the Welcome view.

This is it for this tutorial but this is not it. The AttributeRouting package has to offer a lot of things and has a very powerful and easy way of routing. There are very advance topics that are documented at GitHub.

Related Links:

 No Rating

Deploying .NET Application To Appharbor Using Git

7. August 2012 13:12

ASP.NET MVC Utils 

I have an account with Appharbor but never tried to deploy an application. Appharbor has an excellent integration with Github and Codeplex and if you are planning to showcase you application latest build then Appharbor is the best thing to do that. The Appharbor site has a detailed tutorial on how you can deploy your application from Github and Codeplex. But the question here is what will a programmer do if he just wants to deploy the application from his dektop or in other words if he wants to test his application which is not an open source application. Fortunately, it is possible and is pretty easy to use by using Git Bash. All a programmer need to to is to execute a bunch of commands to deploy the application. I have already deployed one of my application called MP3app to Appharbor (just for few days) which was previously hosted on sub-domain.

If you have an application and you want to showcase it then you can deploy the application to Appharbor. The application I have with me right now is an ASP.NET MVC application which demonstrates the use of Micro-ORM Dapper. This application is with me and is not hosted on Github or Codeplex and therefore I cannot take the advantage of deploying it from the code hosting service. 

Before you proceed I assume that you have an account with Appharbor and have Git bash installed on your machine. If you don't have Git bash installed on your machine, download it from here. After successfull login you can create a new application with the name of your choice. You can also select the datacenter location for your application, either United States or Europe.

Click Create New button to create your application, after which you will be shown with the application settings screen. On the right-hand side you can also see the links which will help you to get started with Appharbor using Bitbucket, Github and Codeplex. Moreover, you can also select add-on services like SQL Server with 20MB of space. You can see the complete list of add-ons and their charges here.

I am calling my application DapperTest which demonstrates the use of Micro-ORM with SQL ServerCE 4.0 in ASP.NET MVC3. On the left side under the application name and other configurations you can see two buttons, REPOSITORY URL and BUILD URL. Click on the REPOSITORY URL button and you will have the URL copied to your clipboard. Open any text editor and paste the copied URL over there as you will be need it afterwards.

Open Git bash and then navigate to the project/solution directory. This will be the original project/solution with source code and not the published version. Also you need to be sure that the project/solution has Visual Studio solutiuon (.sln) file. If the file is not present then you will not be able to deploy your application to Appharbor.

To navigate to the project directory fire the below command in the Git bash console:

cd D:\DapperApp

To initialize empty Git repository, execute the below command:

git init

If you navigate to your project folder, then you can see a folder named .git. By default, this folder is hidden. Now the repositiry is created, I can now add all the files to the repo using the below command:

git add .

It might be possible that you don't want to add certain files to the repository then you can add a .gitignore file. You can see an example here. After this, I can then commit it using the below command:

git commit -m "initial commit"

After the commit is successful, copy the repository URL that you saved initially in your text editor. Add the remote repository (in our case it is Appharbor) using the below command:

git remote add appharbor https://prashantkhandelwal@appharbor.com/dappertest.git

Make sure to replace the URL with the correct one. Once this command executed successfully, you can then deploy/push to Appharbor by using below command:

git push appharbor master

Before the application is being deployed, it will ask you for your Appharbor password. Make sure that you type it slowly because you will not be able to see the password being typed on the Git bash console.

This is it, enjoy the power of cloud.

Here is the list of few applications hosted on Appharbor:

Currently rated 3.0 by 1 person

MP3APP - A Web Application To Get Lyrics, Album Name, Album Art and More

29. June 2012 20:53

ASP.NET MVC Utils Web 

I was busy in few of my freelance projects and got a very rough weekends this whole month. But still I get a bit of time to work on my so called in-house project and somehow I was able to get it completed after a bit of testing. The application I built is a simple web application which will allow you to enter artist name and the song name and let you search the lyrics, album art, artist image, album name and album release date. I hope this application help few people out there.

Now, something about the internals and history of the application. I created a windows application 2 years back and thought of porting the same application for the web. For the, web I used ASP.NET MVC 3 with jQuery but the internals of the application remains the same as it was for my windows app. To fetch the lyrics I use LyrDB and WikiLyrics web services and for album art, album name, artist image etc. I used Last.FM web service. I package the whole stuff and what I got is the application which I was running since 2 years on my machine and now the same application is out for you, use it and suggest me something new if you anything in your minds.

I am not yet finished with this application and you'll see more in coming days.

Try out the application here: http://mp3app.midnightprogrammer.net

 No Rating