You have probably seen some people who are against asynchronous database calls in ASP.NET web applications so far. They mostly right but there are still some cases that processing database queries asynchronous has very important impact on.

If you are unfamiliar with asynchronous programming model on ASP.NET MVC 4, you might want to read one of my previous posts: Asynchronous Programming in C# 5.0 and ASP.NET MVC Web Applications.

Update on the 11th of April, 2012:

@BradWilson has just started a new blog post series on using Task Parallel Library when writing server applications, especially ASP.NET MVC and ASP.NET Web API applications. You should certainly check them out: Task Parallel Library and Servers, Part 1: Introduction

Update on the 1st of July, 2012:

@RickAndMSFT has a new tutorial on Using Asynchronous Methods in ASP.NET MVC 4. Definitely check that out.

One of the reasons why asynchronous programming is not recommended for database calls is that it is extremely hard to get it right, even if we adopt Task-based Asynchronous Programming in .NET 4.0. But, with the new async / await features of C# 5.0, it is easier and still complex at the same time.

When you call a method which returns Task or Task<T> for some T and await on that, the compiler does the heavy lifting by assigning continuations, handling exceptions and so on. Because of this fact, it adds a little overhead and you might notice this when you are dealing with short running operations. At that point, asynchrony will do more harm than good to your application.

Let’s assume we have a SQL Server database out there somewhere and we want to query against that database in order to get the cars list that we have. I have created a class which will do the query operations and hand us the results as C# CLR objects.

public class GalleryContext : IGalleryContext {

    private readonly string selectStatement = "SELECT * FROM Cars";

    public IEnumerable<Car> GetCars() {

        var connectionString = 
            ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;

        using (var conn = new SqlConnection(connectionString)) {
            using (var cmd = new SqlCommand()) {

                cmd.Connection = conn;
                cmd.CommandText = selectStatement;
                cmd.CommandType = CommandType.Text;

                conn.Open();

                using (var reader = cmd.ExecuteReader()) {

                    return reader.Select(r => carBuilder(r)).ToList();
                }
            }
        }
    }

    public async Task<IEnumerable<Car>> GetCarsAsync() {

        var connectionString = 
            ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;
            
        var asyncConnectionString = new SqlConnectionStringBuilder(connectionString) {
            AsynchronousProcessing = true
        }.ToString();

        using (var conn = new SqlConnection(asyncConnectionString)) {
            using (var cmd = new SqlCommand()) {

                cmd.Connection = conn;
                cmd.CommandText = selectStatement;
                cmd.CommandType = CommandType.Text;

                conn.Open();

                using (var reader = await cmd.ExecuteReaderAsync()) {

                    return reader.Select(r => carBuilder(r)).ToList();
                }
            }
        }
    }

    //private helpers
    private Car carBuilder(SqlDataReader reader) {

        return new Car {

            Id = int.Parse(reader["Id"].ToString()),
            Make = reader["Make"] is DBNull ? null : reader["Make"].ToString(),
            Model = reader["Model"] is DBNull ? null : reader["Model"].ToString(),
            Year = int.Parse(reader["Year"].ToString()),
            Doors = int.Parse(reader["Doors"].ToString()),
            Colour = reader["Colour"] is DBNull ? null : reader["Colour"].ToString(),
            Price = float.Parse(reader["Price"].ToString()),
            Mileage = int.Parse(reader["Mileage"].ToString())
        };
    }
}

You might notice that I used a Select method on SqlDataReader which does not exist. It is a small extension method which makes it look prettier.

public static class Extensions {

    public static IEnumerable<T> Select<T>(
        this SqlDataReader reader, Func<SqlDataReader, T> projection) {

        while (reader.Read()) {
            yield return projection(reader);
        }
    }
}

As you can see, it has two public methods to query the database which does the same thing but one of them doing it as synchronously and the other one as asynchronously.

Inside the GetCarsAsync method, you can see that we append the AsynchronousProcessing property and set it to true in order to run the operation asynchronously. Otherwise, no matter how you implement it, your query will be processed synchronously.

When you look behind the curtain, you will notice that ExecuteReaderAsync method is really using the old Asynchronous Programming Model (APM) under the covers.

public static Task<SqlDataReader> ExecuteReaderAsync(this SqlCommand source)
{
    return Task<SqlDataReader>.Factory.FromAsync(
        new Func<AsyncCallback, object, IAsyncResult>(source.BeginExecuteReader), 
        new Func<IAsyncResult, SqlDataReader>(source.EndExecuteReader), 
        null
    );
}

When we try to consume these methods inside our controller, we will have the following implementation.

public class HomeController : Controller {

    private readonly GalleryContext ctx = new GalleryContext();

    public ViewResult Index() {

        return View(ctx.GetCars());
    }

    public async Task<ViewResult> IndexAsync() {

        //workaround: http://aspnetwebstack.codeplex.com/workitem/22
        await TaskEx.Yield();

        return View("Index", await ctx.GetCarsAsync());
    }
}

Now, when we hit /home/Index, we will be querying our database as synchronously. If we navigate to /home/IndexAsync, we will be doing the same thing but asynchronously this time. Let’s do a little benchmarking with Apache HTTP server benchmarking tool.

First, we will simulate 50 concurrent requests on synchronously running wev page:

Let’s do the same thing for asynchronous one:

Did you notice? We have nearly got the same result. In fact, you will see that synchronous version of the operation completes faster than the asynchronous one at some points. The reason is that the SQL query takes small amount of time (approx. 8ms) here to complete.

Let’s take another scenario. Now, we will try to get the same data through a Stored Procedure but this time, the database call will be slow (approx. 1 second). Here are two methods which will nearly the same as others:

private readonly string spName = "sp$GetCars";

public IEnumerable<Car> GetCarsViaSP() {

    var connectionString = ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;

    using (var conn = new SqlConnection(connectionString)) {
        using (var cmd = new SqlCommand()) {

            cmd.Connection = conn;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            using (var reader = cmd.ExecuteReader()) {

                return reader.Select(r => carBuilder(r)).ToList();
            }
        }
    }
}

public async Task<IEnumerable<Car>> GetCarsViaSPAsync() {

    var connectionString = ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;
    var asyncConnectionString = new SqlConnectionStringBuilder(connectionString) {
        AsynchronousProcessing = true
    }.ToString();

    using (var conn = new SqlConnection(asyncConnectionString)) {
        using (var cmd = new SqlCommand()) {

            cmd.Connection = conn;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            using (var reader = await cmd.ExecuteReaderAsync()) {

                return reader.Select(r => carBuilder(r)).ToList();
            }
        }
    }
}

I was able to make the SQL query long running by waiting inside the stored procedure for 1 second:

ALTER PROCEDURE dbo.sp$GetCars
AS

-- wait for 1 second
WAITFOR DELAY '00:00:01';

SELECT * FROM Cars;

Controller actions are nearly the same as before:

public class HomeController : Controller {

    private readonly GalleryContext ctx = new GalleryContext();

    public ViewResult IndexSP() {

        return View("Index", ctx.GetCarsViaSP());
    }

    public async Task<ViewResult> IndexSPAsync() {

        //workaround: http://aspnetwebstack.codeplex.com/workitem/22
        await TaskEx.Yield();

        return View("Index", await ctx.GetCarsViaSPAsync());
    }
}

Let’s simulate 50 concurrent requests on synchronously running web page first:

Ok, that’s apparently not good. As you might see, some requests take more than 8 seconds to complete which is very bad for a web page. Remember, the database call takes approx. 1 second to complete and under a particular number of concurrent requests, we experience a very serious bottleneck here.

Let’s have a look at the asynchronous implementation and see the difference:

Approximately 1 second to complete for each request, pretty impressive compared to synchronous implementation.

Asynchronous database calls are not as straight forward as other types of asynchronous operations but sometimes it will gain so much more responsiveness to our applications. We just need to get it right and implement them properly.

I think one of the most awesome features of Visual Studio 11 beta is SQL Server Database Project (code-named "Juneau"). It is simple, robust and makes it really easy for us to check the database code into our source control system.

I hear you yelling at me "Dude, ever heard about NoSQL? MongoDB? RavenDB?". Yeah, I am aware of those technologies and I think you can achieve lots of things that you can do with a relational database system with a NoSQL database system. On the other hand, there are quite a lot of other things that you cannot do with a NoSQL database system. At least, with the comfort of SQL. What? Did you say "Comfort? Who favors comfort over performance?"? I would like to remind you that all Stack Exchange family sites run on SQL Server.

To sum up, SQL Server will not go anywhere at all. It will always be around and we will keep using it. So, I‘d pay attention to this post

This project template carries lots of features inside it and despite the fact that it is so easy to get started with it, you might drown inside its options very quickly. Honest to say, I am not so experienced with this feature but I try to use this on some personal projects for now as much as I can.

Let’s first have a look at Visual Studio 2010 because if you play with SQL Server project templates with VS 2010, you will be in panic because lots of them are now gone.

Ok, then. What do we do now? Well, it is simple: they are now all in one project template. From database projects to SQL Server CLR assembly projects, SQL Server Database Project template can do all of them (I have never tired creating a SQL Server CLR project with VS 11 Database Project template so far. So, I might not be entirely correct here).

Let’s come back to VS 11 beta. Creating a SQL Server Database Project is very straight forward. File > New > Project and navigate to SQL Server language tab or search against "SQL Server". SQL Server Database Project option will be available.

When we create the project, we get not many things out of the box which is nice.

From this point, you can either start a brand new project or import your existing database.

When you double-click on the Properties, we will get properties window which we can set lots of configuration about the database, build process and so on.

When you are working on a project, make sure you set the target properly as shown below.

While we are working on our database project, we will be creating files for Tables, User Defined Functions, Stored Procedures, etc. Don’t worry about where you put them. VS is smart about your files and it orders them properly on your deployment. This is the experience I have had so far.

As usual, we can add new items via Add New Item dialog box and Database Project ships with several item templates.

User experience is also incredibly high. For example, you can create your tables through design surface or directly with T-SQL code. No matter which one you pick, VS will always keep those in sync.

As you can see below, we have a file named Products.sql for Products table.

Let’s create a foreign key constraint for CategoryId column by referencing a table which does not exist yet.

Save the file and build the solution. Now, we are getting errors since the Categories table does not exist.

When we add the necessary Categories table and its columns, the build process should run smoothly without any errors.

Let’s now open up the Products.sql table file again. Did you notice the right side of the design surface? Yes, it really knows what is going on outside of this file and it keeps the information in sync. As soon as you make a chance anywhere inside your project, proper places get updated.

You can also create script files which will be executed at particular point of your deployment. When you go Add New Item dialog box and navigate to User Scripts tab, you will see some options there. Let’s add Post-Deployment Script file to our project.

This file is nothing but a regular file except for the fact that its build action has been set to PostDeploy.

What about IntelliSense? Well, it is quite nice as you can see from the below screenshots.

I don’t know why but at some points, I was unable to kick off the intellisense, though.

One more thing to mention in this post would be the deployment process but I thought it would be too much for a blog post. So, I will (hopefully) try to blog about that separately by demonstrating different scenarios.

I am very excited about this feature and I am sure you have something to benefit from it as well.

Useful References

• The 'Juneau' Database Project
• Visual Studio 11 Beta is Here
• Visual Studio 11 Beta in Context
• Get Your Database Under Version Control

As you already probably know, GitHub is amazingly awesome and keeps beating crap out of others (cough, Codeplex, cough). As a company, they have a very unique organization and that amazes me as well. And I am also going to tell this: GitHub is not #1 because of the fact that they support Git. They are #1 because they know what we want and how important UX is for the overall quality.

Anyway, couple of days ago I came across that GitHub offers Educational Accounts for academic people (student, teacher, etc.) and that covers free micro plan for students for 2 years.

As I am (still!) a collage student, I filled up the form and sent it. I was expecting to be rejected since I thought they wouldn't accept students outside of the USA for this plan but they were very positive about it and set my account in a short time.

If you are student, go to https://github.com/edu, fill up the form indicating that you are a student, want to benefit from the free edu plan and get the micro plan as free of charge for two years.

God bless Octocats

Today, I was at Computer Engineering Department of Mugla University and I gave two introduction talks on MS Web Platform and ASP.NET MVC 101. I would like to thank Selin Kirik who organized this event.

The slides and codes can be downloaded from the below links.

Credits: I stole some content from @shanselman’s pervious talks to use in the slides and the slide template belongs to MIX11. I also used a picture of Eric Sowell and I encourage you to check that picture out

• MS Web Platform - Slides
• ASP.NET MVC 101 - Slides
• ASP.NET MVC 101 - Code Samples (Barney Stinson’s Blog)