In modern software development, accessing databases efficiently is crucial for application performance and scalability. Dapper, a lightweight Object-Relational Mapper (ORM) for .NET, offers a streamlined approach to database interaction. In this article, we'll explore how to use Dapper C# with an SQLite database file, demonstrating its simplicity and effectiveness through code examples. Additionally, I will introduce the remarkable PDF generation library called IronPDF from Iron Software.

What is Dapper?

Dapper is an object-relational mapping (ORM) framework for the .NET platform. It is a simple object mapper that allows you to map an object-oriented domain model to a traditional relational database. Dapper is known for its speed and performance, often referred to as the "King of Micro ORM." It matches the speed of a raw ADO.NET data reader and enhances the IDbConnection interface with useful extension methods for querying SQL databases.

Key Features of Dapper

1. Performance: Dapper is known for its excellent performance due to its lightweight design and efficient object mapping.
2. Simplicity: Dapper's API is minimalistic and intuitive, making it easy for developers to grasp and use effectively.
3. Raw SQL Support: Dapper allows developers to write raw SQL queries, providing full control over database interactions.
4. Object Mapping: Dapper maps query results directly to C# objects, reducing boilerplate code and enhancing code readability.
5. Parameterized Queries: Dapper supports parameterized queries, protecting against SQL injection attacks and improving performance.
6. Multi-Mapping: Dapper seamlessly handles one-to-many and many-to-many relationships, allowing for multiple queries to be executed efficiently, thus simplifying complex data retrieval.

Asynchronous Data Access with Dapper

Dapper offers asynchronous extension methods that mirror its synchronous counterparts, allowing developers to execute database queries asynchronously. These asynchronous methods are ideal for I/O-bound operations, such as database queries, where the main thread can continue executing other tasks while waiting for the database operation to complete.

Key Asynchronous Methods in Dapper

1. QueryAsync: Executes a SQL query asynchronously and returns the result as a sequence of dynamic objects or strongly typed objects.
2. QueryFirstOrDefaultAsync: Executes a SQL query asynchronously and returns the first result or a default value if no result is found.
3. ExecuteAsync: Executes a SQL command asynchronously (e.g., INSERT, UPDATE, DELETE) and returns the number of affected rows.

Setting Up the Environment: Before diving into code examples, ensure you have the necessary tools installed:

1. Visual Studio or Visual Studio Code.
2. .NET SDK.
3. SQLite package for .NET.

To install the SQLite package, execute the following command in your project directory:

dotnet add package Microsoft.Data.Sqlite

dotnet add package Microsoft.Data.Sqlite

Creating an SQLite Database: For demonstration purposes, let's create a simple SQLite database file named "example.db" with a "Users" table containing columns for "Id," "Name," and "Email."

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Name TEXT,
    Email TEXT
);

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Name TEXT,
    Email TEXT
);

Using Dapper with SQLite

1. First, ensure you have the necessary namespaces imported:

   using Microsoft.Data.Sqlite;
   using Dapper;

   Imports Microsoft.Data.Sqlite
   Imports Dapper

   C#VB

2. Establish a connection to the SQLite database:

   string connectionString = "Data Source=example.db"; // SQLite database connection string
   using (var connection = new SqliteConnection(connectionString))
   {
       connection.Open();
       // Your Dapper queries will go here
   }

   Dim connectionString As String = "Data Source=example.db" ' SQLite database connection string
   Using connection = New SqliteConnection(connectionString)
   	connection.Open()
   	' Your Dapper queries will go here
   End Using

   C#VB

3. Execute a query with Dapper:

   // Define a class to represent the structure of a user
   public class User
   {
       public int Id { get; set; }
       public string Name { get; set; }
       public string Email { get; set; }
   }
    
   // Query to select all users
   string query = "SELECT * FROM Users"; // SQL query
   var users = connection.Query<User>(query).ToList();
    
   // Display the results
   foreach (var user in users)
   {
       Console.WriteLine($"Id: {user.Id}, Name: {user.Name}, Email: {user.Email}");
   }

   C#

4. Insert data into the database using Dapper:

   // Define a new user 
   var newUser = new User { Name = "John Doe", Email = "john@example.com" };
    
   // SQL query/stored procedure to insert a new user
   string insertQuery = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
    
   // Execute the query
   connection.Execute(insertQuery, newUser);

   C#

Introducing IronPDF

IronPDF is a C# library from Iron Software that allows developers to create, edit, and manipulate PDF documents programmatically within .NET applications. It provides features like generating PDF documents from HTML, images, and other formats, as well as adding text, images, and various elements to existing PDF files. IronPDF aims to simplify PDF generation and manipulation tasks for .NET developers by providing a comprehensive set of tools and APIs.

IronPDF offers a range of features for PDF generation and manipulation within .NET applications:

1. HTML to PDF Conversion: Convert HTML content, including CSS styles, into PDF documents.
2. Image to PDF Conversion: Convert images (such as JPEG, PNG, BMP) to PDF documents.
3. Text to PDF Conversion: Convert plain text or formatted text (RTF) to PDF documents.
4. PDF Generation: Create PDF documents from scratch programmatically.
5. PDF Editing: Edit existing PDF documents by adding or modifying text, images, and other elements.
6. PDF Merging and Splitting: Combine multiple PDF documents into a single document, or split a PDF document into multiple files.
7. PDF Security: Apply password protection and encryption to PDF documents to restrict access and protect sensitive information.
8. PDF Form Filling: Populate PDF forms with data programmatically.
9. PDF Printing: Print PDF documents directly from your .NET application.
10. PDF Conversion Settings: Customize various settings such as page size, orientation, margins, compression, and more during PDF generation.
11. PDF Text Extraction: Extract text content from PDF documents for further processing or analysis.
12. PDF Metadata: Set metadata (author, title, subject, keywords) for PDF documents.

Generating PDF documents with IronPDF and Dapper

Create a console application in Visual Studio

Provide the project name and location

Select .NET version

Install the following packages either from Visual Studio Package Manager or the console

dotnet add package Microsoft.Data.Sqlite

dotnet add package Microsoft.Data.Sqlite

dotnet add package Dapper --version 2.1.35

dotnet add package Dapper --version 2.1.35

dotnet add package IronPdf --version 2024.4.2

Use the below code to generate a PDF document:

using Dapper; // Import Dapper for ORM functionalities
using IronPdf; // Import IronPDF for PDF generation
using Microsoft.Data.Sqlite; // Import Sqlite for database connection

// Define the connection string for SQLite database
string connectionString = "Data Source=ironPdf.db";

// Create a string to hold the content for the PDF document
var content = "<h1>Demonstrate IronPDF with Dapper</h1>";

// Add HTML content
content += "<h2>Create a new database using Microsoft.Data.Sqlite</h2>";
content += "<p>new SqliteConnection(connectionString) and connection.Open()</p>";

// Open the database connection
using (var connection = new SqliteConnection(connectionString))
{
    connection.Open();
    
    // Create a Users Table using Dapper
    content += "<h2>Create a Users Table using Dapper and SQL insert query</h2>";
    content += "<p>CREATE TABLE IF NOT EXISTS Users</p>";
    
    // SQL statement to create a Users table
    string sql = "CREATE TABLE IF NOT EXISTS Users (\n    Id INTEGER PRIMARY KEY,\n    Name TEXT,\n    Email TEXT\n);";
    connection.Execute(sql);
    
    // Add Users to table using Dapper
    content += "<h2>Add Users to table using Dapper</h2>";
    content += AddUser(connection, new User { Name = "John Doe", Email = "john@example.com" });
    content += AddUser(connection, new User { Name = "Smith William", Email = "Smith@example.com" });
    content += AddUser(connection, new User { Name = "Rock Bill", Email = "Rock@example.com" });
    content += AddUser(connection, new User { Name = "Jack Sparrow", Email = "Jack@example.com" });
    content += AddUser(connection, new User { Name = "Tomus Tibe", Email = "Tomus@example.com" });
    
    // Retrieve and display users from database
    content += "<h2>Get Users From table using Dapper</h2>";
    string query = "SELECT * FROM Users";
    var users = connection.Query<User>(query).ToList();
    
    // Display each user detail retrieved from the database
    foreach (var user in users)
    {
        content += $"<p>Id:{user.Id}, Name:{user.Name}, email: {user.Email}</p>";
        Console.WriteLine($"{user.Id}. User Name:{user.Name}, Email:{user.Email}");
    }
    
    // Create PDF from the accumulated HTML content
    var renderer = new ChromePdfRenderer();
    var pdf = renderer.RenderHtmlAsPdf(content);
    
    // Save the PDF to a file
    pdf.SaveAs("dapper.pdf");
}

// Method to add user to the database and accumulate HTML content
string AddUser(SqliteConnection sqliteConnection, User user)
{
    string insertQuery = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
    sqliteConnection.Execute(insertQuery, user);
    return $"<p>Name:{user.Name}, email: {user.Email}</p>"; 
}

using Dapper; // Import Dapper for ORM functionalities
using IronPdf; // Import IronPDF for PDF generation
using Microsoft.Data.Sqlite; // Import Sqlite for database connection

// Define the connection string for SQLite database
string connectionString = "Data Source=ironPdf.db";

// Create a string to hold the content for the PDF document
var content = "<h1>Demonstrate IronPDF with Dapper</h1>";

// Add HTML content
content += "<h2>Create a new database using Microsoft.Data.Sqlite</h2>";
content += "<p>new SqliteConnection(connectionString) and connection.Open()</p>";

// Open the database connection
using (var connection = new SqliteConnection(connectionString))
{
    connection.Open();
    
    // Create a Users Table using Dapper
    content += "<h2>Create a Users Table using Dapper and SQL insert query</h2>";
    content += "<p>CREATE TABLE IF NOT EXISTS Users</p>";
    
    // SQL statement to create a Users table
    string sql = "CREATE TABLE IF NOT EXISTS Users (\n    Id INTEGER PRIMARY KEY,\n    Name TEXT,\n    Email TEXT\n);";
    connection.Execute(sql);
    
    // Add Users to table using Dapper
    content += "<h2>Add Users to table using Dapper</h2>";
    content += AddUser(connection, new User { Name = "John Doe", Email = "john@example.com" });
    content += AddUser(connection, new User { Name = "Smith William", Email = "Smith@example.com" });
    content += AddUser(connection, new User { Name = "Rock Bill", Email = "Rock@example.com" });
    content += AddUser(connection, new User { Name = "Jack Sparrow", Email = "Jack@example.com" });
    content += AddUser(connection, new User { Name = "Tomus Tibe", Email = "Tomus@example.com" });
    
    // Retrieve and display users from database
    content += "<h2>Get Users From table using Dapper</h2>";
    string query = "SELECT * FROM Users";
    var users = connection.Query<User>(query).ToList();
    
    // Display each user detail retrieved from the database
    foreach (var user in users)
    {
        content += $"<p>Id:{user.Id}, Name:{user.Name}, email: {user.Email}</p>";
        Console.WriteLine($"{user.Id}. User Name:{user.Name}, Email:{user.Email}");
    }
    
    // Create PDF from the accumulated HTML content
    var renderer = new ChromePdfRenderer();
    var pdf = renderer.RenderHtmlAsPdf(content);
    
    // Save the PDF to a file
    pdf.SaveAs("dapper.pdf");
}

// Method to add user to the database and accumulate HTML content
string AddUser(SqliteConnection sqliteConnection, User user)
{
    string insertQuery = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
    sqliteConnection.Execute(insertQuery, user);
    return $"<p>Name:{user.Name}, email: {user.Email}</p>"; 
}

Imports Dapper ' Import Dapper for ORM functionalities
Imports IronPdf ' Import IronPDF for PDF generation
Imports Microsoft.Data.Sqlite ' Import Sqlite for database connection

' Define the connection string for SQLite database
Dim connectionString As String = "Data Source=ironPdf.db"

' Create a string to hold the content for the PDF document
Dim content As String = "<h1>Demonstrate IronPDF with Dapper</h1>"

' Add HTML content
content += "<h2>Create a new database using Microsoft.Data.Sqlite</h2>"
content += "<p>new SqliteConnection(connectionString) and connection.Open()</p>"

' Open the database connection
Using connection As New SqliteConnection(connectionString)
    connection.Open()

    ' Create a Users Table using Dapper
    content += "<h2>Create a Users Table using Dapper and SQL insert query</h2>"
    content += "<p>CREATE TABLE IF NOT EXISTS Users</p>"

    ' SQL statement to create a Users table
    Dim sql As String = "CREATE TABLE IF NOT EXISTS Users (" & vbCrLf & "    Id INTEGER PRIMARY KEY," & vbCrLf & "    Name TEXT," & vbCrLf & "    Email TEXT" & vbCrLf & ");"
    connection.Execute(sql)

    ' Add Users to table using Dapper
    content += "<h2>Add Users to table using Dapper</h2>"
    content += AddUser(connection, New User With {.Name = "John Doe", .Email = "john@example.com"})
    content += AddUser(connection, New User With {.Name = "Smith William", .Email = "Smith@example.com"})
    content += AddUser(connection, New User With {.Name = "Rock Bill", .Email = "Rock@example.com"})
    content += AddUser(connection, New User With {.Name = "Jack Sparrow", .Email = "Jack@example.com"})
    content += AddUser(connection, New User With {.Name = "Tomus Tibe", .Email = "Tomus@example.com"})

    ' Retrieve and display users from database
    content += "<h2>Get Users From table using Dapper</h2>"
    Dim query As String = "SELECT * FROM Users"
    Dim users = connection.Query(Of User)(query).ToList()

    ' Display each user detail retrieved from the database
    For Each user In users
        content += $"<p>Id:{user.Id}, Name:{user.Name}, email: {user.Email}</p>"
        Console.WriteLine($"{user.Id}. User Name:{user.Name}, Email:{user.Email}")
    Next

    ' Create PDF from the accumulated HTML content
    Dim renderer As New ChromePdfRenderer()
    Dim pdf = renderer.RenderHtmlAsPdf(content)

    ' Save the PDF to a file
    pdf.SaveAs("dapper.pdf")
End Using

' Method to add user to the database and accumulate HTML content
Function AddUser(sqliteConnection As SqliteConnection, user As User) As String
    Dim insertQuery As String = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)"
    sqliteConnection.Execute(insertQuery, user)
    Return $"<p>Name:{user.Name}, email: {user.Email}</p>"
End Function

Imports Dapper ' Import Dapper for ORM functionalities
Imports IronPdf ' Import IronPDF for PDF generation
Imports Microsoft.Data.Sqlite ' Import Sqlite for database connection

' Define the connection string for SQLite database
Dim connectionString As String = "Data Source=ironPdf.db"

' Create a string to hold the content for the PDF document
Dim content As String = "<h1>Demonstrate IronPDF with Dapper</h1>"

' Add HTML content
content += "<h2>Create a new database using Microsoft.Data.Sqlite</h2>"
content += "<p>new SqliteConnection(connectionString) and connection.Open()</p>"

' Open the database connection
Using connection As New SqliteConnection(connectionString)
    connection.Open()

    ' Create a Users Table using Dapper
    content += "<h2>Create a Users Table using Dapper and SQL insert query</h2>"
    content += "<p>CREATE TABLE IF NOT EXISTS Users</p>"

    ' SQL statement to create a Users table
    Dim sql As String = "CREATE TABLE IF NOT EXISTS Users (" & vbCrLf & "    Id INTEGER PRIMARY KEY," & vbCrLf & "    Name TEXT," & vbCrLf & "    Email TEXT" & vbCrLf & ");"
    connection.Execute(sql)

    ' Add Users to table using Dapper
    content += "<h2>Add Users to table using Dapper</h2>"
    content += AddUser(connection, New User With {.Name = "John Doe", .Email = "john@example.com"})
    content += AddUser(connection, New User With {.Name = "Smith William", .Email = "Smith@example.com"})
    content += AddUser(connection, New User With {.Name = "Rock Bill", .Email = "Rock@example.com"})
    content += AddUser(connection, New User With {.Name = "Jack Sparrow", .Email = "Jack@example.com"})
    content += AddUser(connection, New User With {.Name = "Tomus Tibe", .Email = "Tomus@example.com"})

    ' Retrieve and display users from database
    content += "<h2>Get Users From table using Dapper</h2>"
    Dim query As String = "SELECT * FROM Users"
    Dim users = connection.Query(Of User)(query).ToList()

    ' Display each user detail retrieved from the database
    For Each user In users
        content += $"<p>Id:{user.Id}, Name:{user.Name}, email: {user.Email}</p>"
        Console.WriteLine($"{user.Id}. User Name:{user.Name}, Email:{user.Email}")
    Next

    ' Create PDF from the accumulated HTML content
    Dim renderer As New ChromePdfRenderer()
    Dim pdf = renderer.RenderHtmlAsPdf(content)

    ' Save the PDF to a file
    pdf.SaveAs("dapper.pdf")
End Using

' Method to add user to the database and accumulate HTML content
Function AddUser(sqliteConnection As SqliteConnection, user As User) As String
    Dim insertQuery As String = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)"
    sqliteConnection.Execute(insertQuery, user)
    Return $"<p>Name:{user.Name}, email: {user.Email}</p>"
End Function

Code Explanation

1. Start with creating a string content holder for PDF generation.
2. Create a new database using Microsoft.Data.Sqlite, connection.Open() will create an empty database.
3. Create a Users Table using Dapper and execute SQL queries for insertion.
4. Add users to the table using Dapper with insert queries.
5. Query to select all users from the database.
6. Save the content generated as a PDF using ChromePdfRenderer and SaveAs methods provided by IronPDF.

Output

License (Trial Available for IronPDF)

IronPDF's licensing information is available to ensure compliance and usage within your project.

A trial license for developers can be obtained through the IronPDF trial license page.

Please replace the Key in the appSettings.json file shown below:

{
  "IronPdf.License.LicenseKey" : "The Key Goes Here"
}

{
  "IronPdf.License.LicenseKey" : "The Key Goes Here"
}

Conclusion

Dapper simplifies data access in .NET applications, and when combined with SQLite, it provides a lightweight and efficient solution for managing databases. By following the steps outlined in this article, you can leverage Dapper to interact with SQLite databases seamlessly, enabling you to build robust and scalable applications with ease. Along with IronPDF, developers can acquire skills related to ORM databases like Dapper and PDF generation libraries like IronPDF.