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:
- 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
- Dapper:
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("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#.