Dapper

Dapper is a simple and lightweight Object-Relational Mapper (ORM) for .NET. It extends IDbConnection and provides an easy-to-use API for performing database operations. Unlike traditional ORMs like Entity Framework, Dapper focuses on raw performance and simplicity, making it ideal for applications where performance is critical.

• Performance: Dapper is extremely fast because it uses raw SQL queries and maps the results to objects efficiently.

• Simplicity: It has a minimal API surface, making it easy to learn and use.

• Flexibility: Dapper works with any ADO.NET provider, so you can use it with various databases like SQL Server, MySQL, PostgreSQL, etc.

• Control: Since you write the SQL queries, you have complete control over the query logic and optimization.

1. Install Dapper: You can install Dapper via NuGet Package Manager or the Package Manager Console:

   Install-Package Dapper

2. Set Up the Model: Define your data model, e.g., BlogModel.

3. Implement Database Operations: Use Dapper's Query and Execute methods to interact with the database.

Example with Dapper

BlogModel.cs

public class BlogModel
{
    public int BlogId { get; set; }
    public string BlogTitle { get; set; }
    public string BlogAuthor { get; set; }
    public string BlogContent { get; set; }
}

Dapper Example

1. Constructor

public class DapperExample
{
    private readonly IDbConnection _dbConnection;

    public DapperExample(IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }
}

Summary: The constructor initializes the class with an IDbConnection object, which is used to manage the database connection.

1. Read Method

public void Read()
{
    string query = "SELECT * FROM tbl_blog";
    var blogs = _dbConnection.Query<BlogModel>(query).ToList();

    foreach (var blog in blogs)
    {
        Console.WriteLine($"Blog Id => {blog.BlogId}");
        Console.WriteLine($"Blog Title => {blog.BlogTitle}");
        Console.WriteLine($"Blog Author => {blog.BlogAuthor}");
        Console.WriteLine($"Blog Content => {blog.BlogContent}");
        Console.WriteLine("--------------------------------");
    }
}

Summary: This method executes a SELECT query to retrieve all rows from the tbl_blog table, converts the results to a list of BlogModel objects, and prints each blog's data to the console.

1. Edit Method

public void Edit(int id)
{
    string query = "SELECT * FROM tbl_blog WHERE BlogId = @BlogId";
    var blog = _dbConnection.QuerySingleOrDefault<BlogModel>(query, new { BlogId = id });

    if (blog == null)
    {
        Console.WriteLine("No data found.");
        return;
    }

    Console.WriteLine($"Blog Id => {blog.BlogId}");
    Console.WriteLine($"Blog Title => {blog.BlogTitle}");
    Console.WriteLine($"Blog Author => {blog.BlogAuthor}");
    Console.WriteLine($"Blog Content => {blog.BlogContent}");
}

Summary: This method retrieves a specific blog entry based on the provided ID, prints the details if found, or indicates that no data was found.

1. Create Method

public void Create(string title, string author, string content)
{
    string query = @"INSERT INTO [dbo].[Tbl_Blog] ([BlogTitle], [BlogAuthor], [BlogContent])
                     VALUES (@BlogTitle, @BlogAuthor, @BlogContent)";
    int result = _dbConnection.Execute(query, new { BlogTitle = title, BlogAuthor = author, BlogContent = content });

    Console.WriteLine(result > 0 ? "Saving Successful." : "Saving Failed.");
}

Summary: This method inserts a new blog entry into the Tbl_Blog table with the provided title, author, and content, and indicates whether the save operation was successful.

1. Update Method

public void Update(int id, string title, string author, string content)
{
    string query = @"UPDATE [dbo].[Tbl_Blog]
                     SET [BlogTitle] = @BlogTitle,
                         [BlogAuthor] = @BlogAuthor,
                         [BlogContent] = @BlogContent
                     WHERE BlogId = @BlogId";
    int result = _dbConnection.Execute(query, new { BlogId = id, BlogTitle = title, BlogAuthor = author, BlogContent = content });

    Console.WriteLine(result > 0 ? "Updating Successful." : "Updating Failed.");
}

Summary: This method updates an existing blog entry in the Tbl_Blog table based on the provided ID, title, author, and content, and indicates whether the update operation was successful.

1. Delete Method

public void Delete(int id)
{
    string query = "DELETE FROM Tbl_Blog WHERE BlogId = @BlogId";
    int result = _dbConnection.Execute(query, new { BlogId = id });

    Console.WriteLine(result > 0 ? "Deleting Successful." : "Deleting Failed.");
}

Summary: This method deletes a blog entry from the Tbl_Blog table based on the provided ID, and indicates whether the delete operation was successful.

Helper Method: Convert DataTable to List of BlogModel

In Dapper, you don't need a helper method to convert a DataTable to a list of models because Dapper handles this conversion internally.

Summary: By using Dapper, you can simplify database operations while maintaining readability and performance. It eliminates the need for boilerplate code required to convert data between database tables and application models, making your code more concise and easier to maintain.