SqliteDataReader[<string>] is Case-Sensitive

[BenKoth]

The SqliteDataReader lookup when using a string is case-sensitive:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitedatareader.item?view=msdata-sqlite-5.0.0#Microsoft_Data_Sqlite_SqliteDataReader_Item_System_String_

Why? This isn't true of any of the other data readers that I've used including the Microsoft SQL Data reader.
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.item?view=dotnet-plat-ext-5.0#System_Data_SqlClient_SqlDataReader_Item_System_String_

To make matters worse, it is case-sensitive to the name of the column in the database, not what you select.

For example, if you have a table in your Sqlite database like the following:

CREATE TABLE test (testId INT)

The following code will error:

using (SqliteCommand command = new SqliteCommand("SELECT testID FROM test", conn))
{
  using (SqliteDataReader reader = command.ExecuteReader();
  {
    while (reader.read())
    {
      int testId = Convert.ToInt32(reader["testID"]);
    }
  } 
}

The lookup needs to use the same case as the table definition, not the query.
Microsoft.Data.Sqlite version: 5.0.2
Target framework: .Net Standard 2.0

[bricelam]

Related to #18861

[bricelam]

Note that there are queries where the case matters.

SELECT 1 AS "AB", 2 AS "ab"

We should look for an exact match first before falling back to a case-insensitive match.

It's also worth considering whether reader["Ab"] should throw with the above query.

[roji]

We should look for an exact match first before falling back to a case-insensitive match.

That's what Npgsql does.

It's also worth considering whether reader["Ab"] should throw with the above query.

Good question...