Mastering SQLite in .NET: Essential Best Practices for Portable Database Development

In "Mastering SQLite in .NET: Essential Best Practices for Portable Database Development," we explore critical techniques for effectively leveraging SQLite within .NET applications. This guide covers vital topics such as optimizing database performance, ensuring data integrity, and implementing efficient data access patterns using Dapper. By following these best practices, developers can create robust, portable applications that are easy to maintain and scale, maximizing the benefits of SQLite's lightweight nature.

SQLite is a lightweight, serverless, self-contained, and embedded relational database management system (RDBMS). Unlike traditional database systems, it does not require a separate server process or system to operate. SQLite stores the entire database as a single file on disk, making it easy to set up and manage.

Creating a full project using SQLite in a .NET Web API involves several steps, including setting up the project, configuring SQLite, implementing data access, and exposing RESTful endpoints. This comprehensive guide will walk you through the entire process, from project setup to deployment.

Comprehensive Guide to Creating a Full Project Using SQLite in .NET Web API

Download Project —Download

Step 1: Prerequisites

Before you begin, ensure you have the following installed:

• .NET SDK (6.0 or later)
• Visual Studio or Visual Studio Code
• SQLite Manage using VS Code Extension

Step 2: Create a New .NET Web API Project

Open a terminal or command prompt and create a new Web API project

dotnet new webapi -n MySQLiteApi
cd MySQLiteApi        

Open the project in your IDE (e.g., Visual Studio or Visual Studio Code).

Step 3: Install Required NuGet Packages

You need to install Entity Framework Core for SQLite support. Run the following command in your terminal:

dotnet add package System.Data.SQLite
dotnet add package Dapper        

Step 4: Create Data Models

Create a Models folder in the project. Add a Product.cs file in the Models folder to define the Product model:

namespace MySQLiteApi.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}        

Step 5:Create the Database & DapperContext

Create Database Table in SQLite:

CREATE TABLE Products (
    ProductId INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT NOT NULL,
    Price REAL NOT NULL CHECK (Price >= 0)
)        

Create a Data folder in the project. Add a DapperContext.cs file in the Data folder:

public class DapperContext
    {
        private readonly IConfiguration _configuration;
        private readonly string _connectionString;

        public DapperContext(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionString = _configuration.GetConnectionString("DefaultConnection");
        }

        public IDbConnection CreateConnection()
        {
            return new SQLiteConnection(_connectionString);
        }
    }        

Step 6: Configure SQLite in the Application

Open appsettings.json and add the connection string for SQLite:

{
    "ConnectionStrings": {
  "DefaultConnection": "Data Source=D:\\TestProject\\SQLiteWebApi\\MySQLiteApi\\productsdb.db"
},
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*"
}        

Step 7: Open Startup.cs (or Program.cs for .NET 6 and later) and configure the services to use SQLite:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddLogging(); // Add this line for logging

builder.Services.AddSingleton<DapperContext>();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAll",
        builder =>
        {
            builder.AllowAnyOrigin()
                   .AllowAnyMethod()
                   .AllowAnyHeader();
        });
});


var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
// Use the CORS policy
app.UseCors("AllowAll");
app.UseRouting();
app.UseAuthorization();
app.MapControllers();

app.Run();        

Step 8: Create the Products Controller

Create a Controllers folder in the project. Add a ProductsController.cs file in the Controllers folder:

[Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly DapperContext _context;

        public ProductsController(DapperContext context)
        {
            _context = context;
        }

        // GET: api/products
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
        {
            using (var connection = _context.CreateConnection())
            {
                string sql = "SELECT * FROM Products";

                var dataList = await connection.QueryAsync<Product>(sql);

                return dataList.ToList();
            }            
        }

        // GET: api/products/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Product>> GetProduct(int id)
        {
            var product = new Product();
            using (var connection = _context.CreateConnection())
            {
                string sql = "SELECT * FROM Products where Id=@Id";

                product = await connection.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
            }

                
            if (product == null)
            {
                return NotFound();
            }
            return product;
        }

        // POST: api/products
        [HttpPost]
        public async Task<ActionResult<Product>> PostProduct(Product product)
        {
            var result = 0; var response = new Product();
            using (var connection = _context.CreateConnection())
            {
                string sql = "INSERT INTO Products(Name,Price) VALUES (@Name,@Price)";

                result = await connection.ExecuteAsync(sql, product);
            }
            if (result > 0) return Ok(product);
            else return response;
        }

        // PUT: api/products/5
        [HttpPut("{id}")]
        public async Task<IActionResult> PutProduct(int id, Product product)
        {
            var result = 0; var response = new Product();
            if (id != product.Id)
            {
                return BadRequest();
            }

            using (var connection = _context.CreateConnection())
            {
                string sql = "UPDATE Products SET Name=@Name,Price=@Price WHERE Id=@id";

                result = await connection.ExecuteAsync(sql, new { product.Name, product.Price, id });
            }

            if (result > 0) return Ok(product);
            else return BadRequest(response);
        }

        // DELETE: api/products/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id)
        {
            var result = 0; 

            using (var connection = _context.CreateConnection())
            {
                string sql = "DELETE FROM Products where ID=@id";

                result = await connection.ExecuteAsync(sql, id);
            }

            if (result > 0) return Ok(id);
            else return BadRequest("0");
        }
    }        

Step 9: Test the API

Run the project:

dotnet run        

Result :

Example API Endpoints:

• GET all products: GET https://localhost:5001/api/products
• GET a specific product: GET https://localhost:5001/api/products/{id}
• POST a new product: POST https://localhost:5001/api/products Request Body:
• PUT to update a product: PUT https://localhost:5001/api/products/{id} Request Body:
• DELETE a product: DELETE https://localhost:5001/api/products/{id}

To sum up, "Mastering SQLite in .NET: Essential Best Practices for Portable Database Development" provides developers with valuable insights into leveraging SQLite effectively. By following best practices like optimizing queries, wisely managing connections, and ensuring data integrity, you can develop applications that are both efficient and scalable. Adopting these approaches not only boosts performance but also simplifies maintenance, resulting in robust and portable .NET projects suited for today's mobile-centric environment.

If you enjoy my content or find my work helpful, consider supporting me — your contribution helps fuel more projects and knowledge sharing! Buy Me a Coffee

I offered you others medium article: Visit

also, GitHub : Visit

Connect with me Linkedin: Md Hasan Monsur