Tag Archives: data access

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:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web.Http;
using Dapper;
using Newtonsoft.Json.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
Nito.AsyncEx.AsyncContext.Run(() => MainAsync(args));
}
static async Task MainAsync(string[] args)
{
var config = new HttpConfiguration();
WebApiConfig.Register(config);
using (var server = new HttpServer(config))
using (var client = new HttpClient(server))
{
client.BaseAddress = new Uri("http://localhost/");
var cts = new CancellationTokenSource();
var json = @"{""title"":""Task"",""description"":""The task"",""createdDate"":""" + DateTime.UtcNow.ToString() + "\"}";
var postRequest = new HttpRequestMessage(HttpMethod.Post, "/api/tasks")
{
Content = new StringContent(json, Encoding.UTF8, "application/json")
};
var postResponse = await client.SendAsync(postRequest, cts.Token);
Trace.Assert(postResponse.StatusCode == HttpStatusCode.Created);
var location = postResponse.Headers.Location.AbsoluteUri;
var getResponse = await client.GetAsync(location);
Trace.Assert(getResponse.StatusCode == HttpStatusCode.OK);
var getBody = await getResponse.Content.ReadAsAsync<JObject>();
dynamic data = getBody;
Trace.Assert((string)data.title == "Task");
}
Console.WriteLine("Press any key to quit.");
Console.ReadLine();
}
}
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
}
public class TasksController : ApiController
{
static string _connString = ConfigurationManager.ConnectionStrings["Database1"].ConnectionString;
public async Task<IEnumerable<dynamic>> GetAll()
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable<dynamic> tasks = await connection.QueryAsync<dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks;");
return tasks;
}
}
public async Task<dynamic> Get(int id)
{
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
IEnumerable<dynamic> tasks = await connection.QueryAsync<dynamic>("select Id as id, Title as title, Description as description, CreatedDate as createdDate from Tasks where Id = @id;", new { id = id });
if (!tasks.Any())
throw new HttpResponseException(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Task not found"));
return tasks.First();
}
}
public async Task<HttpResponseMessage> Post(JObject value)
{
dynamic data = value;
IEnumerable<int> result;
using (var connection = new SqlCeConnection(_connString))
{
await connection.OpenAsync();
connection.Execute(
"insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);",
new
{
title = (string)data.title,
description = (string)data.description,
createdDate = DateTime.Parse((string)data.createdDate)
}
);
result = await connection.QueryAsync<int>("select max(Id) as id from Tasks;");
}
int id = result.First();
data.id = id;
var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data);
response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id }));
return response;
}
}
}
view raw Program.cs hosted with ❤ by GitHub

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.