SQLite C#: Selecting Data

Summary: in this tutorial, you will learn how to retrieve data from an SQLite database using ADO.NET in a C# program.

Steps for retrieving data from SQLite

First, open a connection to an SQLite database.

Next, create a command object SqliteCommand with the SELECT statement.

Then, execute the query using one of the following methods:

• ExecuteReader() – executes a query that returns a result set. The method returns a SqliteDataReader that can be used to iterate over the rows.
• ExecuteScalar() – execute a query that returns a single value.

After that, iterate over the result set using the SqliteDataReader object. Use the Get* methods to retrieve values from each row.

Finally, close the database connection.

Selecting all rows from a table

The following program retrieves all rows from the authors table in the pub database:

using Microsoft.Data.Sqlite;

var sql = "SELECT * FROM authors";

try
{
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();

    using var command = new SqliteCommand(sql, connection);

    using var reader = command.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            var id = reader.GetInt32(0);
            var firstName = reader.GetString(1);
            var lastName = reader.GetString(2);
            Console.WriteLine($"{id}\t{firstName}\t{lastName}");
        }
    }
    else
    {
        Console.WriteLine("No authors found.");
    }

}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

Output:

1       Joanne  Rowling
2       Stephen King
3       Agatha  Christie
4       George  R.R. Martin
5       J.R.R.  Tolkien
6       Harper  Lee
7       Dan     Brown
8       Neil    Gaiman
9       Terry   Pratchett
10      Ernest  HemingwayCode language: C# (cs)

Selecting data with parameters

The following illustrates how to retrieve the author with id 1 from the authors table:

using Microsoft.Data.Sqlite;

var sql = "SELECT * FROM authors WHERE id = @id";
int authorId = 1;

try
{
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();

    using var command = new SqliteCommand(sql, connection);
    command.Parameters.AddWithValue("@id", authorId);

    using var reader = command.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            var id = reader.GetInt32(0);
            var firstName = reader.GetString(1);
            var lastName = reader.GetString(2);
            Console.WriteLine($"{id}\t{firstName}\t{lastName}");
        }
    }
    else
    {
        Console.WriteLine("No authors found.");
    }

}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

Output:

1       Joanne  RowlingCode language: C# (cs)

Selecting a scalar value

The following program illustrates how to get the number of rows from the authors table:

using Microsoft.Data.Sqlite;

var sql = "SELECT COUNT(*) FROM authors";

try
{
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();

    using var command = new SqliteCommand(sql, connection);

    var authorCount = command.ExecuteScalar();

    Console.WriteLine($"The number of author is {authorCount}");

}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

Output:

The number of author is 10

Summary

• Use the ExecuteReader() of a SqliteCommand object to execute a query that returns a result set.
• Use the ExecuteScalar() method of the SqliteCommand object to execute a query that returns a single value.