Dapper and SQLite. Checking whether the table exists in the database does not work. How to fix?

Question

SQLite

public class Storage
    {
        public Storage()
        {
        }

        public async Task Initialize()
        {
            string connectionString = $"FileName=Data.sqlite";

            SqliteConnection connection = null;
            try
            {
                await Task.Run(async () =>
                {
                    connection = new SqliteConnection(connectionString);
                    connection.Open();
                    
                    var parameters = new { TableName = "options" };
                    int result = await connection.ExecuteAsync(
                           "SELECT count(*) FROM sqlite_master WHERE type='table' AND [email protected]", parameters);

                    if (result < 1)
                    {
                        result = await createTables(connection);
                    }
                });
            }
            finally
            {
                if (connection is not null)
                {
                    await connection.DisposeAsync();
                }
            }
        }

        private async Task<int> createTables(SqliteConnection connection)
        {
            return await connection.ExecuteAsync(@"
create table options
(
  parameter_name text not null,
  value text not null
);
create unique index options_parameter_name_uindex
  on options (parameter_name);");
        }
    }

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <LangVersion>9</LangVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.90" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="5.0.6" />
  </ItemGroup>

</Project>

The query always returns -1. Why and how to fix?

Answer 1

Just use the CREATE TABLE IF NOT EXISTS construct