Web API and Dynamic Data Access

In .NET Rocks episode 855, Jeff Fritz commented on ASP.NET Web API being somewhat confusing in terms of its intended use. I don’t tend to agree, but I thought I would address one point he made in particular: that Web API is perhaps just another form of repository.

Web API is much more than a repository. And yes, it is indeed a protocol mapping layer. As Uncle Bob once noted, a web or api front end is just a mapping layer and is not really your application.

In many cases, however, one could argue that a web-api-as-repository is a fairly solid use case. OData is a great example. However, I was thinking of yet another argument I’ve heard for dynamic languages: when you are just going from web to database and back, you are not really working with types.

In that spirit, I set out to write a simple Web API using SQL and JSON with no explicit class definitions. You can see the results in this gist:

I used Dapper to simplify the data access, though I just as well could have used Massive, PetaPoco, or Simple.Data. Mostly I wanted to use SQL, so I went with Dapper.

I also model bind to a JObject, which I immediately cast to dynamic. I use an anonymous object to supply the values for the parameters in the SQL statements, casting the fields from the dynamic object to satisfy Dapper.

All in all, I kinda like this. Everything is tiny, and I can work directly with SQL, which doesn’t bother me one bit. I have a single class to manage my data access and API translation, but the ultimate goal of each method is still small: retrieve data and present it over HTTP. That violates SRP, but I don’t mind in this case. The code above is not very testable, but with an API like this I’d be more inclined to do top level testing anyway. It’s just not deep enough to require a lot of very specific, low-level testing, IMHO.

Also, note again that this is just retrieving data and pushing it up through an API. This is not rocket science. An F# type provider over SQL would give a good enough sanity check. Why bother generating a bunch of types?

Which brings up another point for another post: what would I do if I needed to add some logic to process or transform the data I retrieved?

As a future exercise, I want to see what it would take to cap this with the Web API OData extensions. That could be fun.