CODEWITHSUNDEEP
c#sqlitewindows-store-appslinqpadsqlite-net
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10267

How can I return an int value from a SQLite query?

I've got this SQLite method:

internal static bool DataContainsAnyLocationInfo()
{
    int latCount = 0;
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT COUNT(Id) FROM PhotraxBaseData WHERE latitude > 0.0";
        latCount = db.Query<PhotraxBaseData>(sql);
    }
    return (latCount > 0);
}

...that won't compile due to: "Cannot implicitly convert type 'System.Collections.Generic.List' to 'int'"

I just want an int back, such as 0 or 1 or 42, not an entire List.

I do have sqlite-net installed, if there's a way to do it with that that would be easier, not excluding a LINQy way, if that'll work.

UPDATE

Maybe I'd be better off doing it this way instead:

internal static bool DataContainsAnyLocationInfo(List<PhotraxBaseData> _pbd)
{
    bool locInfoFound = false;
    foreach (PhotraxBaseData pbd in _pbd)
    {
        if (pbd.latitude > 0.0)
        {
            locInfoFound = true;
            break;
        }
    }
    return locInfoFound;
}

UPDATE 2

John Woo's code compiles, but my SQL must be bad, because, although I have many records in PhotraxBaseData where latitude is greater than 0, the function returns false.

And when I run either of these queries directly in LINQPad4:

SELECT COUNT(Id) FROM PhotraxBaseData WHERE latitude > 0.0
SELECT COUNT(Id) FROM PhotraxBaseData WHERE (latitude > 0.0)

I get:

) or end of expression expected
; expected

Note: latitude is a double.

UPDATE 3

So now I've got two ways that work (my non-database-touching code above, and John Woo's "ExecuteScalar" code), but I don't know which one is preferable. They are both faster than a famished hummingbird zipping toward newly-discovered nectar.

Upvotes: 3

Views: 3365

Answers (1)

John Woo
John Woo

Reputation: 263733

from their documentation, you can use this code: 

latCount = db.ExecuteScalar<int>(sql);

since it will return a row other than a list. I don't know why you are getting that error but your query will run on SQLFiddle.

Upvotes: 4

Related Questions