C# SQLite (How it Works For Developers)

Introduction to SQLite

SQLite is a self-contained, serverless, and zero-configuration database engine used in various applications, including desktop, web, and mobile applications. In this tutorial, we will dive into using SQLite with C#. Using simple and easily understandable examples, you'll learn how to create, manage, and interact with an SQLite database.

What is SQLite?

SQLite is a lightweight and efficient database that stores data in a single file. Unlike traditional databases, it doesn't require a separate server. This makes it a great choice for applications that need a database without the complexity of a full-fledged database system.

Setting Up SQLite in C#

Using NuGet Package Manager

To work with SQLite in a C# project, you'll need to install the necessary SQLite library. This can be done through the NuGet Package Manager.

1. Open Visual Studio and create a new Console Application.
2. Right-click on the project and select "Manage NuGet Packages."
3. Search for "SQLite" and install the package.

Establishing a Connection

Connection String

A connection string is a string that specifies information about a data source and the means of connecting to it. In SQLite, the connection string will often look like this:

string connectionString = "Data Source=mydatabase.db;";

string connectionString = "Data Source=mydatabase.db;";

Dim connectionString As String = "Data Source=mydatabase.db;"

Connection Object

You can create a connection object using the SQLiteConnection class from the System.Data.SQLite namespace.

using System.Data.SQLite;

// Initialize a connection to the SQLite database
var connection = new SQLiteConnection(connectionString);

// Open the connection
connection.Open();

using System.Data.SQLite;

// Initialize a connection to the SQLite database
var connection = new SQLiteConnection(connectionString);

// Open the connection
connection.Open();

Imports System.Data.SQLite

' Initialize a connection to the SQLite database
Private connection = New SQLiteConnection(connectionString)

' Open the connection
connection.Open()

Creating a Table

Create Table

Creating a table is fundamental when working with any database. Here's how you create a table using SQLite code.

// SQL command to create a new table "person"
string query = "CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY, name TEXT)";

// Create a command object with the SQL query and connection
var command = new SQLiteCommand(query, connection);

// Execute the command to create the table
command.ExecuteNonQuery();

// SQL command to create a new table "person"
string query = "CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY, name TEXT)";

// Create a command object with the SQL query and connection
var command = new SQLiteCommand(query, connection);

// Execute the command to create the table
command.ExecuteNonQuery();

' SQL command to create a new table "person"
Dim query As String = "CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY, name TEXT)"

' Create a command object with the SQL query and connection
Dim command = New SQLiteCommand(query, connection)

' Execute the command to create the table
command.ExecuteNonQuery()

• Id Integer Primary Key: Sets the 'id' column as the primary key.
• Table Name: The name you want to give your database table.

Inserting Data

Insert Rows

To insert data into a table, you'll need to use an INSERT command.

// SQL command to insert a new row into the "person" table
string query = "INSERT INTO person (name) VALUES ('John')";
var command = new SQLiteCommand(query, connection);
command.ExecuteNonQuery();

// SQL command to insert a new row into the "person" table
string query = "INSERT INTO person (name) VALUES ('John')";
var command = new SQLiteCommand(query, connection);
command.ExecuteNonQuery();

' SQL command to insert a new row into the "person" table
Dim query As String = "INSERT INTO person (name) VALUES ('John')"
Dim command = New SQLiteCommand(query, connection)
command.ExecuteNonQuery()

Parameterized Command

Parameterized commands can protect your application from SQL Injection attacks. This approach uses parameters instead of inserting values directly into the query.

// SQL command with a parameter to insert data safely
string query = "INSERT INTO person (name) VALUES (@name)";
var command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@name", "Iron Developer");
command.ExecuteNonQuery();

// SQL command with a parameter to insert data safely
string query = "INSERT INTO person (name) VALUES (@name)";
var command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@name", "Iron Developer");
command.ExecuteNonQuery();

' SQL command with a parameter to insert data safely
Dim query As String = "INSERT INTO person (name) VALUES (@name)"
Dim command = New SQLiteCommand(query, connection)
command.Parameters.AddWithValue("@name", "Iron Developer")
command.ExecuteNonQuery()

Retrieving Data

Select Statement

To retrieve data from the database table, use a SELECT statement.

// SQL command to select all rows from the "person" table
string query = "SELECT * FROM person";

var command = new SQLiteCommand(query, connection);
var reader = command.ExecuteReader();

// Loop through the result set and read data
while (reader.Read())
{
    Console.WriteLine(reader["name"]);
}

// SQL command to select all rows from the "person" table
string query = "SELECT * FROM person";

var command = new SQLiteCommand(query, connection);
var reader = command.ExecuteReader();

// Loop through the result set and read data
while (reader.Read())
{
    Console.WriteLine(reader["name"]);
}

' SQL command to select all rows from the "person" table
Dim query As String = "SELECT * FROM person"

Dim command = New SQLiteCommand(query, connection)
Dim reader = command.ExecuteReader()

' Loop through the result set and read data
Do While reader.Read()
	Console.WriteLine(reader("name"))
Loop

Advanced Features

SQLite Transactions

Transactions allow you to execute multiple operations in a single atomic action. Here's how to use transactions:

var transaction = connection.BeginTransaction();
try
{
    // Example of multiple operations in a transaction
    var insertCommand = new SQLiteCommand("INSERT INTO person (name) VALUES ('Alice')", connection, transaction);
    insertCommand.ExecuteNonQuery();

    var updateCommand = new SQLiteCommand("UPDATE person SET name = 'Bob' WHERE name = 'Alice'", connection, transaction);
    updateCommand.ExecuteNonQuery();

    transaction.Commit(); // Commit the transaction if all operations succeed
}
catch
{
    transaction.Rollback(); // Rollback the transaction if any operation fails
}

var transaction = connection.BeginTransaction();
try
{
    // Example of multiple operations in a transaction
    var insertCommand = new SQLiteCommand("INSERT INTO person (name) VALUES ('Alice')", connection, transaction);
    insertCommand.ExecuteNonQuery();

    var updateCommand = new SQLiteCommand("UPDATE person SET name = 'Bob' WHERE name = 'Alice'", connection, transaction);
    updateCommand.ExecuteNonQuery();

    transaction.Commit(); // Commit the transaction if all operations succeed
}
catch
{
    transaction.Rollback(); // Rollback the transaction if any operation fails
}

Dim transaction = connection.BeginTransaction()
Try
	' Example of multiple operations in a transaction
	Dim insertCommand = New SQLiteCommand("INSERT INTO person (name) VALUES ('Alice')", connection, transaction)
	insertCommand.ExecuteNonQuery()

	Dim updateCommand = New SQLiteCommand("UPDATE person SET name = 'Bob' WHERE name = 'Alice'", connection, transaction)
	updateCommand.ExecuteNonQuery()

	transaction.Commit() ' Commit the transaction if all operations succeed
Catch
	transaction.Rollback() ' Rollback the transaction if any operation fails
End Try

Object-Relational Mapping (ORM) with Entity Framework

Entity Framework (EF) is a widely used ORM tool within the .NET ecosystem. It simplifies database programming by allowing developers to work with relational data using domain-specific objects. Here's how you can use Entity Framework with SQLite.

1. Installing Entity Framework

First, make sure you have installed the Entity Framework NuGet package specific to SQLite:

1. Open the NuGet Package Manager in Visual Studio.
2. Search for "Entity Framework SQLite" and install it.

2. Creating Entity Classes

Entity classes are representations of database tables. You can create a class for each table with which you intend to interact.

public class Person
{
    public int Id { get; set; } // Primary Key
    public string Name { get; set; }
}

public class Person
{
    public int Id { get; set; } // Primary Key
    public string Name { get; set; }
}

Public Class Person
	Public Property Id() As Integer ' -  Primary Key
	Public Property Name() As String
End Class

3. DbContext

You'll need to create a class that inherits from DbContext. This class represents the session with the database and allows you to query and save instances of the entities.

using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=mydatabase.db;");
    }
}

using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=mydatabase.db;");
    }
}

Imports Microsoft.EntityFrameworkCore

Public Class MyDbContext
	Inherits DbContext

	Public Property Persons() As DbSet(Of Person)

	Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
		optionsBuilder.UseSqlite("Data Source=mydatabase.db;")
	End Sub
End Class

4. CRUD Operations

Entity Framework simplifies Create, Read, Update, and Delete (CRUD) operations. Here's how you can insert a new record:

using (var db = new MyDbContext())
{
    db.Persons.Add(new Person { Name = "John" });
    db.SaveChanges();
}

using (var db = new MyDbContext())
{
    db.Persons.Add(new Person { Name = "John" });
    db.SaveChanges();
}

Using db = New MyDbContext()
	db.Persons.Add(New Person With {.Name = "John"})
	db.SaveChanges()
End Using

Reading, updating, and deleting records are similarly streamlined and straightforward with Entity Framework, allowing for concise and maintainable code.

Working with XML Files and Other Data Providers

SQLite is not limited to relational data; it also provides flexibility in handling other data types, including XML files.

1. Storing XML Data

You can store XML data within an SQLite database. This might be useful if you work with configuration data or other hierarchical structures.

string xmlData = "<person><name>John</name></person>";
string query = "INSERT INTO xmltable (data) VALUES (@data)";
var command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@data", xmlData);
command.ExecuteNonQuery();

string xmlData = "<person><name>John</name></person>";
string query = "INSERT INTO xmltable (data) VALUES (@data)";
var command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@data", xmlData);
command.ExecuteNonQuery();

Dim xmlData As String = "<person><name>John</name></person>"
Dim query As String = "INSERT INTO xmltable (data) VALUES (@data)"
Dim command = New SQLiteCommand(query, connection)
command.Parameters.AddWithValue("@data", xmlData)
command.ExecuteNonQuery()

Retrieving XML Data

You can retrieve and work with XML data using standard XML parsing techniques in C#.

string query = "SELECT data FROM xmltable WHERE id = 1";
var command = new SQLiteCommand(query, connection);
var reader = command.ExecuteReader();

string xmlData;

// Read the XML data from the query result
if (reader.Read())
{
    xmlData = reader["data"].ToString();
}

// Parse the XML data as needed using an XML parser

string query = "SELECT data FROM xmltable WHERE id = 1";
var command = new SQLiteCommand(query, connection);
var reader = command.ExecuteReader();

string xmlData;

// Read the XML data from the query result
if (reader.Read())
{
    xmlData = reader["data"].ToString();
}

// Parse the XML data as needed using an XML parser

Dim query As String = "SELECT data FROM xmltable WHERE id = 1"
Dim command = New SQLiteCommand(query, connection)
Dim reader = command.ExecuteReader()

Dim xmlData As String

' Read the XML data from the query result
If reader.Read() Then
	xmlData = reader("data").ToString()
End If

' Parse the XML data as needed using an XML parser

Working with Other Data Providers

SQLite also integrates well with various data providers, allowing for interoperability and flexibility. This means that you can seamlessly switch between different databases or even combine different data sources within a single application.

Introducing the Iron Suit: A Powerful Set of Libraries

After exploring the realms of SQLite and logical operators in C#, it's time to introduce a remarkable collection of tools that complement and enhance the development experience in the .NET environment. The Iron Suit is a collection of powerful libraries comprising IronPDF, IronXL, IronOCR, and IronBarcode, each serving distinct purposes.

IronPDF: C# PDF Library

Comprehensive Guide to IronPDF is a comprehensive library designed to create, read, and manipulate PDF files in C#. Whether you need to generate reports, invoices, or any documents in PDF format, IronPDF has you covered. A unique feature of IronPDF is the ability to convert HTML to PDF. You can render HTML as a PDF document, including CSS, JavaScript, and images, making it a potent tool. Check out this tutorial on Converting HTML to PDF with IronPDF for a step-by-step guide.

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");
    }
}

Imports IronPdf

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim renderer = New ChromePdfRenderer()

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

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

		' 3. Convert URL to PDF
		Dim url = "http://ironpdf.com" ' Specify the URL
		Dim pdfFromUrl = renderer.RenderUrlAsPdf(url)
		pdfFromUrl.SaveAs("URLToPDF.pdf")
	End Sub
End Class

IronPDF can be an essential tool when working with SQLite databases. You can generate PDF reports from your SQLite database data, allowing for seamless data presentation and sharing.

IronXL: Excel File Management Made Easy

Explore IronXL for Excel Integration which allows developers to read, write, and manipulate Excel files effortlessly. It's compatible with XLS, XLSX, and more, making it an ideal tool for handling spreadsheet data. You can read Excel files, manipulate them, and even create new files from scratch. IronXL's functionality integrates well with database management, including SQLite, for exporting and importing data.

IronOCR: Optical Character Recognition in C#

With IronOCR for Text Recognition, scanning text from images and PDF files is a breeze. It's a versatile OCR (Optical Character Recognition) library that recognizes text from various sources.

Imagine storing scanned documents in an SQLite database and using IronOCR to retrieve and recognize the text within those documents. The possibilities are endless, providing powerful text retrieval and search functionality.

IronBarcode: The Ultimate Barcode Generation and Reading Library

Barcode generation and reading are made simple with Powerful Barcode Integration via IronBarcode. It supports multiple barcode formats and provides a robust API for all barcode-related needs. IronBarcode can play an essential role in applications using SQLite, where barcodes might represent products or other data entities. Storing and retrieving barcodes from the SQLite database enhances data integrity and facilitates quick access.

Conclusion

SQLite is a powerful yet lightweight database engine that's great for beginners and professionals alike. From creating tables and inserting rows to managing transactions and preventing SQL Injection attacks, SQLite offers many features. Whether you're building a console or mobile application or need to work with foreign keys and datasets, SQLite is an excellent choice.

The Iron Suit, comprising IronPDF, IronXL, IronOCR, and IronBarcode, is a treasure trove of tools that extend the capabilities of your C# development projects, whether you're working with SQLite databases or any other domains.

What's even more appealing is that each of these products offers a free trial for Iron Software products, giving you ample time to explore and understand the vast array of functionalities they provide. Once you decide to continue with these tools, the licensing starts from $749 per product. You can also buy the complete Iron Suit bundle at the price of just two individual products.