I am getting inconsistent results with Sqlite Command

Question

Hi Friends. I am getting inconsistent results when I run sqlite commands. For example look at the code below. dt.Rows.Count is always 1 even if I delete the table PayerList. I havent worked much with sqlite to know its quirks or there is some underlying issue somewhere, maybe I need to refersh something but its very frustrating.

SQLiteConnection connection= new SQLiteConnection(@"Data Source= path; version");
connection.Open();
var sql = "SELECT count(*) FROM sqlite_master where type='table' AND name='PayerList';";
SQLiteCommand command = new SQLiteCommand(sql,connection);

try
{
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
connection.Close();

if (dt.Rows.Count>0)
{
//rows exist
}

Accepted Answer

Misread the sample - apologies. This is because by selecting count(*) you're querying for an aggregation over a group of rows, rather than the rows itself. If the table doesn't exist then you'll still get a row, but the field in the row will contain the value 0 instead of 1. You can check like this instead:

if (dt.Rows[0].Field("count(*)") > 0) {
	// 
}

Answer

Hi @Ronald Rex , Welcome to Microsoft Q&A,

Your SQL query will only return a single value, the table number, not the table details. This means that whether or not the table exists, the result will be a data table with one row and one column, with only one cell's value.

If you want to get more details about the table, such as column names, data types, etc., you need to perform a different query to retrieve metadata information about the table. This usually requires querying system tables, such as sqlite_master, to obtain information about the database structure.

You can try to find more detailed information.

This is the official reference use case: https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=visual-studio

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

    var command = connection.CreateCommand();
    command.CommandText =
    @"
        SELECT name
        FROM user
        WHERE id = $id
    ";
    command.Parameters.AddWithValue("$id", id);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(0);

            Console.WriteLine($"Hello, {name}!");
        }
    }
}

Best Regards,

Jiale

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

I am getting inconsistent results with Sqlite Command

1 additional answer