I am getting inconsistent results with Sqlite Command

Ronald Rex 1,666 Reputation points
2023-09-08T13:50:58.5133333+00:00

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
}
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,197 questions
{count} votes

Accepted answer
  1. P a u l 10,406 Reputation points
    2023-09-08T14:08:24.8733333+00:00

    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<long>("count(*)") > 0) {
    	// 
    }
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jiale Xue - MSFT 30,581 Reputation points Microsoft Vendor
    2023-09-08T14:16:07.32+00:00

    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.

    0 comments No comments