Building an End-to-End Dynamic C# Web API with Dapper for Data Access

We’re going to explore how to create an end-to-end dynamic C# Web API project using Dapper for data access. Dapper is a lightweight Object-Relational Mapping (ORM) library that simplifies database access by allowing us to write dynamic SQL queries and map the results to .NET objects. Our goal is to build a simple Web API that performs CRUD (Create, Read, Update, Delete) operations on a "Tasks" table in a SQL Server Compact Edition database.

Prerequisites

Before we begin, make sure you have the following:

1. Visual Studio (or any C# development environment) installed.
2. SQL Server Compact Edition (SQL CE) installed.

Setting Up the Project

Let's start by setting up the project and installing the required NuGet packages. Follow these steps:

1. Create a new C# Console Application project in Visual Studio.
2. Install the following NuGet packages:
   • Dapper: Install-Package Dapper
   • Newtonsoft.Json: Install-Package Newtonsoft.Json
   • Microsoft.AspNet.WebApi.Core: Install-Package Microsoft.AspNet.WebApi.Core

Building the Web API

Step 1: Create the TasksController Class

In the project, create a new C# class named TasksController that inherits from ApiController. This class will handle HTTP requests for tasks.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlServerCe;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using Dapper;
using Newtonsoft.Json.Linq;

public class TasksController : ApiController
{
    // Database connection string
    static string _connString = ConfigurationManager.ConnectionStrings["Database1"].ConnectionString;

    // Web API methods for CRUD operations
    // ...
}

Step 2: Implement the GET Methods

Let's implement the GetAll and Get methods to retrieve tasks from the database.

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();
    }
}

Step 3: Implement the POST Method

Next, let's create the Post method to add a new task to the database.

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;
}

Step 4: Register Routes in WebApiConfig

In the WebApiConfig class, register the Web API routes:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );
    }
}

Executing the Web API

Now that we have built the Web API, it's time to execute it and perform some API calls. We will use the HttpClient class to simulate HTTP requests.

Step 1: Create the Program Class

In the Program class, we will execute the Web API using HttpClient:

using System;
using System.Configuration;
using System.Diagnostics;
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)
        {
            // ... HttpClient code goes here ...
        }
    }

    // ... WebApiConfig and TasksController classes go here ...
}

Step 2: Performing HTTP Requests

Now, let's perform some HTTP requests to test our API:

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))
    {
        // Set the base address for the HttpClient
        client.BaseAddress = new Uri("//localhost/");

        // Create a CancellationTokenSource
        var cts = new CancellationTokenSource();

        // Sample JSON data for the POST request
        var json = @"{""title"":""Task"",""description"":""The task"",""createdDate"":""" + DateTime.UtcNow.ToString() + "\"}";

        // Create a POST request to add a new task
        var postRequest = new HttpRequestMessage(HttpMethod.Post, "/api/tasks")
        {
            Content = new StringContent(json, Encoding.UTF8, "application/json")
        };

        // Send the POST request
        var postResponse = await client.SendAsync(postRequest, cts.Token);
        Trace.Assert(postResponse.StatusCode == HttpStatusCode.Created);

        // Get the location of the newly created task
        var location = postResponse.Headers.Location.AbsoluteUri;

        // Create a GET request to retrieve the task
        var getResponse = await client.GetAsync(location);
        Trace.Assert(getResponse.StatusCode == HttpStatusCode.OK);

        // Deserialize the response body into a JObject
        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();
}

Conclusion

In this tutorial, we have built an end-to-end dynamic C# Web API project using Dapper for data access. We implemented CRUD operations to manage tasks in a SQL Server Compact Edition database. Dapper provided a straightforward way to interact with the database and map the results to dynamic objects.

You can use this project as a foundation for creating more complex Web APIs with additional features. Remember to handle error cases and add security measures as per your application's requirements.

I hope this article was helpful in understanding how to create a dynamic Web API using Dapper and C#.