System.Data.SQLite

As stated in the msdn documentation ( http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx )ExecuteScalar must return null if the returned result set is empty. However SqliteCommand.ExecuteScalar returns DBNull.Value on empty result sets.

You can verify it by executing the following code:


SQLiteConnection.CreateFile("test.db");
using (SQLiteConnection cn = new SQLiteConnection("Data Source=test.db"))
using (SQLiteCommand createTableCommand = new SQLiteCommand("CREATE TABLE Foo(Id INTEGER PRIMARY KEY, Value INTEGER)", cn))
{
cn.Open();
createTableCommand.ExecuteNonQuery();

using (SQLiteCommand getValueCommand = new SQLiteCommand("SELECT Value FROM Foo WHERE Id = @Id", cn))
using (SQLiteCommand insertRecordCommand = new SQLiteCommand("INSERT INTO Foo(Id, Value) VALUES (@Id, @Value)", cn))
{

getValueCommand.Parameters.Add("@Id", DbType.Int64).Value = 100;


object ValueObject = getValueCommand.ExecuteScalar();
Debug.Assert(ValueObject != DBNull.Value, "ExecuteScarlar returned DBNull.Value on an empty table");


SQLiteParameter IdParam = insertRecordCommand.Parameters.Add("@Id", DbType.Int64);
SQLiteParameter ValueParam = insertRecordCommand.Parameters.Add("@Value", DbType.Int64);

SQLiteTransaction tran = cn.BeginTransaction();
for (int i = 1; i <= 10; i++)
{
IdParam.Value = i;
ValueParam.Value = i;
insertRecordCommand.ExecuteNonQuery();
}
tran.Commit();

ValueObject = getValueCommand.ExecuteScalar();
Debug.Assert(ValueObject != DBNull.Value, "ExecuteScarlar returned DBNull.Value on an empty result set");
}
}

You know, that's really frustrating.  If you look at the docs for DbCommand.ExecuteScalar, IDbCommand.ExecuteScalar and "Obtaining a single value from a database", it doesn't say anything about a standard return value when the resultset is empty.  Grrr ...

I'll have this fixed in the rev 31 release coming up next week.  Thanks for the report :)

Robert

    Yes, I know and I understand you. Thank you very much for a really nice work.<br>

Hi,

I have some related thoughts. I upgraded from 1.0.29.0 to 1.0.31.0 and discovered that SQLiteDataReader now returns DBNull.Value instead of null if the column value in the database is NULL.

I guess that's fine with SQLiteDataReader.GetValue() even if I prefer null, but SQLiteDataReader.GetString() will throw an InvalidCastException in VerifyType(). Is that really a desired behaviour for strings since there is no ambiguity between an empty string and a null string?

Calling SQLiteDataReader.IsDBNull() for string columns feels like unnecessary work. :-)

I'll look into it.  The desired behavior is for it to behave exactly like SqlDataReader.

Robert

 

Ok, here's the results of my test SqlDataReader vs. SQLiteDataReader (1.0.31.0)

Using the statement:

SELECT 'foo', 'bar', 1
UNION
SELECT NULL, '', NULL

SQLite and SqlClient behaved identically, except for the fact that SQLite actually returned the data in reverse order (the row with NULL,'', NULL came first)

while (reader.Read())
{
    Console.WriteLine(reader.GetValue(0).GetType().ToString());
}

SqlClient Output:
System.String
System.DbNull

SQLite Output (reverse order, but same result):
System.DbNull
System.String

Both engines threw an exception when calling GetString(0) on the NULL row.

So I'm afraid this is the way its going to stay.  Any difference in behavior of the provider at this level could make for larger issues later on with all the technologies built on top of these core functions.

Robert