How to check SQLite DB integrity with Dapper in C#

I'm trying to track down a database corruption bug in a C# project using Dapper and SQLite. So I'm looking for a way to check the db integrity in code. I've found multiple places saying I can send the command "PRAGMA integrity_check" for this, but the Dapper Execute function only returns an int for the number of rows effected, which doesn't really make sense here (and just seems to return 0).

Is there a way to do this integrity check or something similar?

Solution

I'm not familiar with Dapper, but if you are using SQLite 3.16.0 or newer, I think you have at least 1 option. Maybe 2. Since version 3.16.0, SQLite has a new experimental feature, called PRAGMA functions.

This essentially means that now you can use side effect free built-in PRAGMAs as tables. For example, additionally to the old syntax:

PRAGMA integrity_check;

Now you can also check the database integrity like this:

SELECT integrity_check FROM pragma_integrity_check();

Or:

SELECT * FROM pragma_integrity_check();

So if you can run arbitrary SELECTs on arbitrary tables through Dapper, and access their results, then that's all.

But if you can't, and the only information you get really is just the number of rows affected, there's still a solution. You can construct a query which deletes 1 row if integrity_check is 'ok' and 0 rows if it isn't:

DROP TABLE IF EXISTS Dummy;
CREATE TABLE Dummy as SELECT 'ok' as Value;
DELETE FROM Dummy WHERE Value = (SELECT * FROM pragma_integrity_check());

This works for me on SQLite version 3.25.3, but as I said it's just an experimental feature still subject to changes, exact syntax might or might not be different between versions.