Web api and Dapper for super-simple REST APIs

Recently c# got the dynamic keyword, which allows for truly dynamic typing (like in Ruby, Python or Javascript), and allow you to define objects to which you can add fields at runtime. In fact, we now have ExpandoObject in the standard library.

An obvious place to take advantage of that is with communicating to databases; Most times, we're still using a relational database, and use SQL to communicate with it. SQL is dynamically typed (at least in the sense that your types are coming from a database, independently of your program), and translating it to our objects usually involves lots of gruntwork; it would be nice if the language helps with that gruntwork; with dynamic and ExpandoObject being available, there are now a few micro-frameworks for DB access; one of the simplest is Dapper ; it just adds a few extension methods to the SqlConnection class, and can either automatically put data into our classes, or into dynamic objects (which is what we'll do).

The new-ish web api in ASP.net automatically generates REST APIs with very little code; with that, and Dapper, we can give access to our database in very little time; for example, if we have a database with one table, called People, with two fields, id, and name; we can generate a controller with code as simple as:

	using Dapper;
	namespace DapperCleanDemo.Controllers
	{
	public class PeopleController : ApiController
	{
	public IEnumerable<dynamic> GetResultById(string id)
	{
	using (var conn = new SqlConnection(@"Server=localhost\sqlexpress;Database=XmlTest;Trusted_Connection=True"))
	{
	var rows = conn.Query("select id,name FROM people WHERE Id=@Id", new { Id = id });
	return rows;
	}
	}
	}
	}

view raw PersonController.cs hosted with ❤ by GitHub

Of course, eventually you may want to use a more complete ORM, like EntityFramework, but for quick prototyping, it doesn't get any simpler than this.