Getting Started with Dapper in .NET Core

Getting Started with Dapper in .NET Core

Dapper is a lightweight and high-performance micro-ORM (Object Relational Mapper) for .NET that enables efficient database interactions. It is ideal for applications that prioritize speed and minimal overhead, offering a streamlined alternative to Entity Framework. This guide provides a comprehensive approach to integrating Dapper into your .NET Core projects.

 

1. Installing Dapper

To install Dapper, use the following command in your terminal or command prompt:

dotnet add package Dapper

Alternatively, you can install it via the NuGet Package Manager in Visual Studio.

 

2. Setting Up Database Connection

We'll use SQL Server for this example.

Configure Connection String

Add your database connection string to appsettings.json:

{

  "ConnectionStrings": {

    "DefaultConnection": "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASSWORD;"

  }

}

Inject Database Connection in Program.cs

Use dependency injection (DI) to provide an IDbConnection instance:

using Microsoft.Data.SqlClient;

using System.Data;

 

var builder = WebApplication.CreateBuilder(args);

 

builder.Services.AddScoped<IDbConnection>(sp =>

    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

 

var app = builder.Build();

 

// API endpoints will be added here

 

app.Run();

 

3. Defining the Product Class

Create a Product class to represent your data model:

public class Product

{

    public int Id { get; set; }

    public string Name { get; set; }

    public decimal Price { get; set; }

}

 

4. Executing Queries with Dapper (Asynchronous)

Dapper makes it easy to execute database operations using simple SQL queries.

Retrieving Data (GET)

app.MapGet("/products", async (IDbConnection db) =>

{

    try

    {

        string sql = "SELECT * FROM Products";

        var products = await db.QueryAsync<Product>(sql);

        return Results.Ok(products);

    }

    catch (Exception ex)

    {

        return Results.Problem(ex.Message);

    }

});

Inserting Data (POST)

app.MapPost("/products", async (Product newProduct, IDbConnection db) =>

{

    try

    {

        string insertQuery = "INSERT INTO Products (Name, Price) OUTPUT INSERTED.Id VALUES (@Name, @Price)";

        newProduct.Id = await db.ExecuteScalarAsync<int>(insertQuery, newProduct);

        return Results.Created($"/products/{newProduct.Id}", newProduct);

    }

    catch (Exception ex)

    {

        return Results.Problem(ex.Message);

    }

});

Updating Data (PUT)

app.MapPut("/products/{id}", async (int id, Product updatedProduct, IDbConnection db) =>

{

    try

    {

        string updateQuery = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";

        await db.ExecuteAsync(updateQuery, new { updatedProduct.Name, updatedProduct.Price, Id = id });

        return Results.Ok(new { Message = "Product updated successfully." });

    }

    catch (Exception ex)

    {

        return Results.Problem(ex.Message);

    }

});

Deleting Data (DELETE)

app.MapDelete("/products/{id}", async (int id, IDbConnection db) =>

{

    try

    {

        string deleteQuery = "DELETE FROM Products WHERE Id = @Id";

        await db.ExecuteAsync(deleteQuery, new { Id = id });

        return Results.Ok(new { Message = "Product deleted successfully." });

    }

    catch (Exception ex)

    {

        return Results.Problem(ex.Message);

    }

});

 

5. Key Best Practices

  • Efficient Connection Management: Use dependency injection to manage IDbConnection lifecycle efficiently.
  • Asynchronous Execution: Always use asynchronous methods (QueryAsync, ExecuteAsync, ExecuteScalarAsync) to improve application responsiveness.
  • Error Handling: Implement try-catch blocks to handle database errors gracefully.
  • SQL Optimization: Use parameterized queries (@parameterName) to prevent SQL injection.
  • Proper API Responses: Return structured responses using the Results class for better API handling.
  • Configuration Management: Store connection strings in appsettings.json rather than hardcoding them in the source code.

 

6. Conclusion

Dapper provides a lightweight, efficient, and high-performance way to interact with databases in .NET Core applications. By following the steps outlined in this guide, you can integrate Dapper seamlessly into your project while following best practices for database access.

Would you like to explore advanced topics such as stored procedures, transactions, or bulk operations with Dapper? Let us know!

 

Comments

Post a Comment

Popular posts from this blog

Multiline to singleline IN C# - CODING

Multiline to singleline IN C# - CODING using System; using System.Linq; class Program {     static void Main(string[] args)     {         string input = @"This is                  multiline";                  // Split the input into lines, trim each line, and filter out empty lines         string result = string.Join(" ", input.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)                                                .Select(line => line.Trim()));         Console.WriteLine(result);     } } Output: This is  multiline
Read more

EF Core interview questions for beginners

1. What is Entity Framework Core (EF Core)? EF Core is an open-source Object-Relational Mapper (ORM) that enables .NET developers to work with databases using .NET objects. It allows you to interact with a database using strongly typed classes and LINQ queries, rather than writing raw SQL queries. EF Core supports various database providers, such as SQL Server, SQLite, PostgreSQL, MySQL, and more. 2. What are the advantages of using EF Core over traditional ADO.NET? Productivity:  EF Core allows you to work with databases using high-level objects, reducing the amount of boilerplate code you need to write. Cross-platform:  EF Core supports multiple databases and works on Windows, Linux, and macOS. LINQ Support:  You can query the database using LINQ, which is more type-safe and easier to maintain than raw SQL queries. Code-First & Database-First:  EF Core supports both approaches to model databases. Change Tra...
Read more

EF Core interview questions for experienced

EF Core interview questions for experienced 1. What are the main differences between Entity Framework (EF) and Entity Framework Core (EF Core)? EF Core is a complete rewrite of EF. Unlike EF, EF Core supports cross-platform development, improved performance, LINQ query translation, and has a more modular architecture. EF Core does not support all features that EF 6 does (like lazy loading and some database providers). 2. How does EF Core handle migrations, and what are some common pitfalls? EF Core uses migrations to update the database schema based on model changes. Common pitfalls include not applying migrations correctly, misalignment between model and database state, or missing/overwriting migrations during collaboration in a team environment. 3. Explain the difference between Add and Attach methods in EF Core. Add method is used to add a new entity to the context and mark it as Added , while Attach attaches an existing entity to the context with no changes. The Attach meth...
Read more