We’re going to explore how to create an end-to-end dynamic 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 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. C# SQL Prerequisites Before we begin, make sure you have the following: Visual Studio (or any C# development environment) installed. 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: Create a new C# Console Application project in Visual Studio. 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 Class TasksController In the project, create a new C# class named that inherits from . This class will handle HTTP requests for tasks. TasksController ApiController 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 and methods to retrieve tasks from the database. GetAll Get 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 method to add a new task to the database. Post 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 class, register the Web API routes: WebApiConfig 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 Class Program In the class, we will execute the Web API using HttpClient: Program 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("http://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#.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

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

Too Long; Didn't Read

Mostafa Safarian

STORY’S CREDIBILITY

Guide

Code License

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...