How do I count the number of rows returned in my SQLite reader (in C#)?

Question

I'm working in Microsoft Visual C# 2008 Express and with SQLite.

I'm querying my database with something like this:

SQLiteCommand cmd = new SQLiteCommand(conn);

cmd.CommandText = "select id from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
SQLiteDataReader reader = cmd.ExecuteReader();

Then I do something like this:

if (reader.HasRows == true) {
   while (reader.Read()) {
     // I do stuff here
}
}

What I want to do is count the number of rows before I do "reader.Read()" since the number returned will affect what I want/need to do. I know I can add a count within the while statement, but I really need to know the count before.

Any suggestions?

Thanks!

-Adeena

Answer 1

Do a second query:

cmd.CommandText = "select count(id) from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
SQLiteDataReader reader = cmd.ExecuteReader();

Your reader will then contain a single row with one column containing the number of rows in the result set. The count will have been performed on the server, so it should be nicely quick.

Answer 2

If you are only loading an id column from the database, would it not be easier to simply load into a List<string> and then work from there in memory?

Answer 3

The DataReader runs lazily, so it doesn't pick up the entirety of the rowset before beginning. This leaves you with two choices:

1. Iterate through and count
2. Count in the SQL statement.

Because I'm more of a SQL guy, I'll do the count in the SQL statement:

cmd.CommandText = "select count(id) from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
int RowCount = 0;

RowCount = Convert.ToInt32(cmd.ExecuteScalar());

cmd.CommandText = "select id from myTable where word = '" + word + "';";
SQLiteDataReader reader = cmd.ExecuteReader();

//...

Note how I counted *, not id in the beginning. This is because count(id) will ignore id's, while count(*) will only ignore completely null rows. If you have no null id's, then use count(id) (it's a tad bit faster, depending on your table size).

Update: Changed to ExecuteScalar, and also count(id) based on comments.

Answer 4

What you request is not feasible -- to quote Igor Tandetnik, my emphasis:

SQLite produces records one by one, on request, every time you call sqlite3_step. It simply doesn't know how many there are going to be, until on some sqlite3_step call it discovers there are no more.

(sqlite3_step is the function in SQLite's C API that the C# interface is calling here for each row in the result).

You could rather do a "SELECT COUNT(*) from myTable where word = '" + word + "';" first, before your "real" query -- that will tell you how many rows you're going to get from the real query.

Answer 5

but I really need to know the count before

Why is that ? this is usually not necessary, if you use adequate in-memory data structures (Dataset, List...). There is probably a way to do what you want that doesn't require to count the rows beforehand.

Answer 6

Normally i would do

select count(1) from myTable where word = '" + word + "';";

to get the result as fast as possible. In the case where id is an int then it won't make much difference. If it was something a bit bigger like a string type then you'll notice a difference over a large dataset.

Reasoning about it count(1) will include the null rows. But i'm prepared to be corrected if i'm wrong about that.

Answer 7

You do have to count with select count... from...

This will make your application slower. However there is an easy way to make your app faster and that way is using parameterized queries.

See here: http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251

(So besides speed parameterized queries have 2 other advantages too.)