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?

Answer 1

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 2

Try this,

SQLiteCommand cmd = new SQLiteCommand(conn);

cmd.CommandText = "select id from myTable where word = '" + word + "';";

SQLiteDataReader reader = cmd.ExecuteReader();

while (reader.HasRows)

     reader.Read();

int total_rows_in_resultset = reader.StepCount;

total_rows_in_resultset gives you the number of rows in resultset after processing query

remember that if you wanna use the same reader then close this reader and start it again.