Sqlite C# .NET (How It Works For Developers)

How to Use SQLite in C#

1. Download SQLite library
2. Create a database and insert the data
3. Read using reader
4. Use SQLite using Entity Framework and LINQ query method
5. Generate PDF Report using IronPDF

What is SQLite?

SQLite is a popular open-source database engine that can be embedded in applications without requiring a separate server component. It is fast, reliable, and cross-platform. SQLite can be used with .NET project applications through various libraries that provide ADO.NET interfaces. One of these libraries is Microsoft.Data.SQLite. This allows you to use SQLite as a data source for your .NET applications, whether they are Console, Desktop, Web, or Mobile. You can also use Entity Framework Core to perform object-relational mapping and query your SQLite database using LINQ.

This article shows you how to use Microsoft.Data.Sqlite to develop, connect, and manipulate an SQLite database in a .NET Console Application. You will learn how to:

• Install the Microsoft.Data.Sqlite NuGet package
• Create a SQLite connection and execute SQL commands
• Create and populate tables using data readers and parameters
• Query data using data readers
• Implement EntityFramework Core
• Query data using LINQ
• Generate PDF Report

Installing the SQLite Library

To use Microsoft.Data.Sqlite, you need to install the NuGet package that contains the library and its dependencies. You can do this using the Visual Studio Package Manager, the .NET Core CLI, or any other tool that supports NuGet. This article will use the Package Manager to create a Console Application and install Microsoft.Data.Sqlite. To do this, open a Package Manager Console and run the following commands:

Install-Package Microsoft.Data.Sqlite

The above command will install the package as shown below.

Creating SQLite Database

To work with a SQLite database, you need to create a SqliteConnection object that represents the connection to the database file. You can specify the file name and other options in the connection string. If the file does not exist, it will be created automatically. For example, the below program creates a connection to a database file named employee.db in the current directory:

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    connection.Open();
    // ...
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    connection.Open();
    // ...
}

The using statement ensures that the connection is closed and disposed of when it goes out of scope. To execute SQL commands, you need to create a SqlCommand object and associate it with the connection. You can set the CommandText property to the SQL statement you want to execute, and optionally add parameters to the Parameters collection. You can then call one of the methods of the SqlCommand object to execute the command, such as ExecuteNonQuery, ExecuteScalar, or ExecuteReader.

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = @"CREATE TABLE Employee (
        Id INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName TEXT NOT NULL,
        Designation TEXT NOT NULL )";
    command.ExecuteNonQuery();
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = @"CREATE TABLE Employee (
        Id INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName TEXT NOT NULL,
        Designation TEXT NOT NULL )";
    command.ExecuteNonQuery();
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    var command = connection.CreateCommand();
    StringBuilder builder = new StringBuilder();
    builder.Append("INSERT INTO Employee VALUES(1, 'John', 'Sami', 'CEO');");
    builder.Append("INSERT INTO Employee VALUES(2, 'David', 'Watson', 'Software Engineer');");
    builder.Append("INSERT INTO Employee VALUES(3, 'Victor', 'Khan', 'Content Writer');");
    connection.Open();
    command.CommandText = builder.ToString();
    command.ExecuteNonQuery();
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    var command = connection.CreateCommand();
    StringBuilder builder = new StringBuilder();
    builder.Append("INSERT INTO Employee VALUES(1, 'John', 'Sami', 'CEO');");
    builder.Append("INSERT INTO Employee VALUES(2, 'David', 'Watson', 'Software Engineer');");
    builder.Append("INSERT INTO Employee VALUES(3, 'Victor', 'Khan', 'Content Writer');");
    connection.Open();
    command.CommandText = builder.ToString();
    command.ExecuteNonQuery();
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    var command = connection.CreateCommand();
    command.CommandText = @"SELECT * FROM Employee";
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var id = reader.GetInt32(0);
            string firstName = reader.GetString(1);
            string lastName = reader.GetString(2);
            string designation = reader.GetString(3);
            Console.WriteLine($"{id}: {firstName} - {lastName} - {designation}");
        }
    }
}

using (var connection = new SqliteConnection("Data Source=employee.db"))
{
    var command = connection.CreateCommand();
    command.CommandText = @"SELECT * FROM Employee";
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var id = reader.GetInt32(0);
            string firstName = reader.GetString(1);
            string lastName = reader.GetString(2);
            string designation = reader.GetString(3);
            Console.WriteLine($"{id}: {firstName} - {lastName} - {designation}");
        }
    }
}

Querying Data

If you prefer to use LINQ to query your SQLite database, you can use Entity Framework Core to map your tables to classes and perform queries using LINQ expressions. To do this, you need to install the Microsoft.EntityFrameworkCore.Sqlite package, which depends on Microsoft.Data.Sqlite package. You also need to create a class that inherits from DbContext and represents the database context. This class must have a constructor that accepts a DbContextOptions parameter and passes it to the base constructor. It must also have a DbSet property for each table you want to map to a class. For example, the following code defines a Stock class and a DatabaseContext class:

using Microsoft.EntityFrameworkCore;
public class Stock
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Symbol { get; set; } = string.Empty;
    public double Price { get; set; }
}
public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions options) : base(options) { }
    public DbSet<Stock> Stock { get; set; }
}

using Microsoft.EntityFrameworkCore;
public class Stock
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Symbol { get; set; } = string.Empty;
    public double Price { get; set; }
}
public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions options) : base(options) { }
    public DbSet<Stock> Stock { get; set; }
}

To create the database context, you need to use the DbContextOptionsBuilder class and specify the connection string and the database provider. You can also use the Database.EnsureCreated method to create the database and the tables if they do not exist. For example, the following code creates the database context and the database:

var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
optionsBuilder.UseSqlite("Data Source=stock.db");
using (var context = new DatabaseContext(optionsBuilder.Options))
{
    context.Database.EnsureCreated();
}

var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
optionsBuilder.UseSqlite("Data Source=stock.db");
using (var context = new DatabaseContext(optionsBuilder.Options))
{
    context.Database.EnsureCreated();
}

To insert data into the database, you can use the Add or AddRange methods of the DbSet property and pass the objects you want to insert. You can also use the SaveChanges method to commit the changes to the database. For example, the following code inserts three Stocks into the database:

static void Main(string[] args)
{
    var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
    optionsBuilder.UseSqlite("Data Source=stock.db");
    using (var context = new DatabaseContext(optionsBuilder.Options))
    {
        context.Database.EnsureCreated();
        var stock = new List<Stock> {
            new Stock { Id = 1, Name = "NCR", Symbol = "$$", Price = 5.6 },
            new Stock { Id = 2, Name = "Google", Symbol = "GG", Price = 10.6 },
            new Stock { Id = 3, Name = "Apple", Symbol = "AA", Price = 3.6 }
        };
        context.AddRange(stock);
        context.SaveChanges();
    }
}

static void Main(string[] args)
{
    var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
    optionsBuilder.UseSqlite("Data Source=stock.db");
    using (var context = new DatabaseContext(optionsBuilder.Options))
    {
        context.Database.EnsureCreated();
        var stock = new List<Stock> {
            new Stock { Id = 1, Name = "NCR", Symbol = "$$", Price = 5.6 },
            new Stock { Id = 2, Name = "Google", Symbol = "GG", Price = 10.6 },
            new Stock { Id = 3, Name = "Apple", Symbol = "AA", Price = 3.6 }
        };
        context.AddRange(stock);
        context.SaveChanges();
    }
}

To query data, you can use the LINQ methods or expressions on the DbSet property and apply filters, projections, aggregations, and other operations. The queries will be translated to SQL statements and executed on the database. For example, the following code queries the stocks whose price is less than six dollars and prints their names:

using (var context = new DatabaseContext(optionsBuilder.Options))
{
    var cheapStocks = context.Stock.Where(p => p.Price < 6).Select(p => p.Name);
    Console.WriteLine("Stock Less than $6 are:");
    foreach (string stock in cheapStocks)
    {
        Console.WriteLine(stock);
    }
}

using (var context = new DatabaseContext(optionsBuilder.Options))
{
    var cheapStocks = context.Stock.Where(p => p.Price < 6).Select(p => p.Name);
    Console.WriteLine("Stock Less than $6 are:");
    foreach (string stock in cheapStocks)
    {
        Console.WriteLine(stock);
    }
}

Let's suppose we have a scenario where we need to create a PDF file containing the list of Stocks. We can easily do that by using IronPDF.

Introducing IronPDF

IronPDF is a library that helps you create, edit, and read PDF files in your .NET applications without the need for multiple packages. It can generate PDFs from HTML, URL, JavaScript, CSS, and many image formats, as well as add headers, footers, signatures, attachments, and passwords. It also supports cross-platform compatibility, multithreading, and async support.

IronPDF's HTML to PDF Feature is its main highlight, preserving all layouts and styles. It generates PDFs from web content, ideal for reports, invoices, and documentation. You can convert HTML files, URLs, and HTML strings to PDFs seamlessly.

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}

Install-Package IronPdf

IronPdf.License.LicenseKey = "IRONSUITE.ABC.XYZ.MYCOMPANY.COM.ABC-DEPLOYMENT.TRIAL-P45MXL.TRIAL.EXPIRES.31.JAN.2028";

IronPdf.License.LicenseKey = "IRONSUITE.ABC.XYZ.MYCOMPANY.COM.ABC-DEPLOYMENT.TRIAL-P45MXL.TRIAL.EXPIRES.31.JAN.2028";

var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
optionsBuilder.UseSqlite("Data Source=stock.db");
StringBuilder builder = new StringBuilder();
string style = "<!DOCTYPE html>\r\n<html>\r\n<head>\r\n<style>\r\n#stock {\r\n  font-family: Arial, Helvetica, sans-serif;\r\n  border-collapse: collapse;\r\n  width: 100%;\r\n}\r\n\r\n#stock td, #stock th {\r\n  border: 1px solid #ddd;\r\n  padding: 8px;\r\n}\r\n\r\n#stock tr:nth-child(even){background-color: #f2f2f2;}\r\n\r\n#stock tr:hover {background-color: #ddd;}\r\n\r\n#stock th {\r\n  padding-top: 12px;\r\n  padding-bottom: 12px;\r\n  text-align: left;\r\n  background-color: #04AA6D;\r\n  color: white;\r\n}\r\n</style>\r\n</head>\r\n<body>\r\n\r\n<h1>A Stock Table</h1>";
builder.Append(style);
builder.Append("<table id=\"stock\"><tr><td>Stock Symbol</td><td>Stock Name</td><td>Stock Price</td></tr>");
using (var context = new DatabaseContext(optionsBuilder.Options))
{
    var stocks = context.Stock;
    foreach (Stock stock in stocks)
    {
        builder.Append($"<tr><td>{stock.Symbol}</td><td>{stock.Name}</td><td>{stock.Price}</td></tr>");
    }
}
builder.Append("</table></body></html>");
var renderer = new ChromePdfRenderer();
var pdf = renderer.RenderHtmlAsPdf(builder.ToString());
pdf.SaveAs("stock.pdf");

var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
optionsBuilder.UseSqlite("Data Source=stock.db");
StringBuilder builder = new StringBuilder();
string style = "<!DOCTYPE html>\r\n<html>\r\n<head>\r\n<style>\r\n#stock {\r\n  font-family: Arial, Helvetica, sans-serif;\r\n  border-collapse: collapse;\r\n  width: 100%;\r\n}\r\n\r\n#stock td, #stock th {\r\n  border: 1px solid #ddd;\r\n  padding: 8px;\r\n}\r\n\r\n#stock tr:nth-child(even){background-color: #f2f2f2;}\r\n\r\n#stock tr:hover {background-color: #ddd;}\r\n\r\n#stock th {\r\n  padding-top: 12px;\r\n  padding-bottom: 12px;\r\n  text-align: left;\r\n  background-color: #04AA6D;\r\n  color: white;\r\n}\r\n</style>\r\n</head>\r\n<body>\r\n\r\n<h1>A Stock Table</h1>";
builder.Append(style);
builder.Append("<table id=\"stock\"><tr><td>Stock Symbol</td><td>Stock Name</td><td>Stock Price</td></tr>");
using (var context = new DatabaseContext(optionsBuilder.Options))
{
    var stocks = context.Stock;
    foreach (Stock stock in stocks)
    {
        builder.Append($"<tr><td>{stock.Symbol}</td><td>{stock.Name}</td><td>{stock.Price}</td></tr>");
    }
}
builder.Append("</table></body></html>");
var renderer = new ChromePdfRenderer();
var pdf = renderer.RenderHtmlAsPdf(builder.ToString());
pdf.SaveAs("stock.pdf");

Conclusion

In conclusion, leveraging SQLite in .NET for database management offers a lightweight and versatile solution. This article explored the integration of SQLite into .NET applications, showcasing its key features and advantages. The provided code demonstrated practical steps for creating, connecting, and manipulating an SQLite database in a .NET console application. Additionally, it highlighted the use of Microsoft.Data.Sqlite and Entity Framework Core for efficient data handling. The incorporation of IronPDF illustrated how to seamlessly generate a PDF report from the SQLite database, enhancing the application's capabilities for reporting and data distribution.

IronPDF offers various licensing options, depending on the number of developers, locations, projects, and redistribution needs. The licenses are perpetual and include one year of free support and updates.