C# SQLite: How to Use SQLite with C#

Let’s look at how to perform common database operations including creating tables, inserting data, running queries, using transactions, and more – all from C# using the System.Data.SQLite library. Real-world examples demonstrate SQLite’s capabilities for rapid prototyping, internal data storage, and complex analytics pipelines.

Whether you’re a C# developer looking to add database functionality to your application, or a data professional seeking a lightweight SQL engine for analytics, this guide has you covered. Let’s dive in!

Getting Started with System.Data.SQLite

To use SQLite in a C# application, add the System.Data.SQLite NuGet package:

dotnet add package System.Data.SQLite

This provides the SQLiteConnection and related classes that allow interaction with SQLite databases. Next, create a connection to a new or existing SQLite database file:

string connectionString = "Data Source=mydatabase.db;Version=3;";
using var connection = new SQLiteConnection(connectionString);
connection.Open();

Now you have a connection open and can start executing SQL commands!

Using C# for Creating SQLite Tables

Let’s create a simple table to track orders:

using var command = new SQLiteCommand(connection);

command.CommandText = @"
    CREATE TABLE Orders (
        OrderID INTEGER PRIMARY KEY,
        ProductName TEXT, 
        Quantity INTEGER,
        TotalCost REAL
    )";

command.ExecuteNonQuery();

The ExecuteNonQuery() method executes a SQL statement that modifies the database, like CREATE TABLE, INSERT or UPDATE.

To verify creation, query the sqlite_master table:

command.CommandText = "SELECT * FROM sqlite_master WHERE type='table'";
using var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader.GetString(1)); 
}

This prints out all user-created tables, confirming our Orders table exists!

Inserting Data

With the table created, now we can insert some rows:

command.CommandText = "INSERT INTO Orders VALUES (1, 'Product 1', 10, 12.50)"; 
command.ExecuteNonQuery();

command.CommandText = "INSERT INTO Orders VALUES (2, 'Product 2', 5, 15.00)";
command.ExecuteNonQuery(); 

To query the inserted rows:

command.CommandText = "SELECT * FROM Orders";
using var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} {reader.GetInt32(2)} {reader.GetDouble(3)}");
} 

The ExecuteReader() method returns a SQLiteDataReader for easily iterating over results, while methods like GetInt32() and GetString() access column data by index.

This prints:

1 Product 1 10 12.5 
2 Product 2 5 15

Confirming our two rows were inserted successfully!

Updating and Deleting

To modify existing data, use the SQL UPDATE statement:

command.CommandText = "UPDATE Orders SET Quantity = 15 WHERE OrderId = 1";  
command.ExecuteNonQuery();

Similarly, DELETE statements remove rows entirely:

command.CommandText = "DELETE FROM Orders WHERE OrderID = 2";
command.ExecuteNonQuery();

These statements modify the table based on a condition in the WHERE clause. Always test updates and deletes carefully!

Transactions

When performing multiple operations as an atomic work unit, wrap them in a transaction:

connection.BeginTransaction(); 

try
{
    command.CommandText = "INSERT INTO..."; 
    command.ExecuteNonQuery();

    command.CommandText = "UPDATE...";
    command.ExecuteNonQuery();

    connection.CommitTransaction();
    Console.WriteLine("Success!");
}
catch (Exception ex)
{
   connection.RollbackTransaction(); 
   Console.WriteLine("Transaction rolled back due to error.");
} 

This ensures if one operation fails, all previous operations of the transaction are rolled back.

Example: Sales Reporting Database

Let’s tie together what we’ve learned by creating an example sales reporting database.

First, the schema:

CREATE TABLE Sales (
    TransactionID INTEGER PRIMARY KEY,
    ProductID INTEGER NOT NULL,
    Quantity INTEGER NOT NULL, 
    SalePrice REAL NOT NULL,
    SaleDate TEXT NOT NULL 
)

Next, insert a few days worth of sales data:

command.CommandText = @"
    INSERT INTO Sales VALUES (1, 102, 2, 5.50, '2023-02-01');
    INSERT INTO Sales VALUES (2, 105, 1, 15.25, '2023-02-01');
    INSERT INTO Sales VALUES (3, 102, 1, 5.50, '2023-02-02');
    INSERT INTO Sales VALUES (4, 107, 1, 9.99, '2023-02-02');
";
command.ExecuteNonQuery(); 

Now let’s connect to Excel and perform some analysis:

using Excel = Microsoft.Office.Interop.Excel;

//...

var salesData = new DataSet();
var adapter = new SQLiteDataAdapter("SELECT * FROM Sales", connection);
adapter.Fill(salesData);

var excelApp = new Excel.Application();
//... code to output dataset to spreadsheet ...

With our data now easily accessible in Excel, we can analyze sales figures, create pivot tables and charts for business intelligence, generate reports, and more!

Wrap Up

In this guide, you learned:

• How to install System.Data.SQLite and connect to a database from C#
• SQL basics like CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
• Using transactions to group operations
• Binding result sets to objects like DataSets and DataTables
• Examples for analytics, reporting, and other applications

SQLite’s flexibility makes it the perfect lightweight relational data store for integrating persistence into .NET apps.

Now you know to build feature-rich applications – like sales dashboards, inventory management, user analytics, and beyond!