adeena adeena - 3 months ago 23
C# Question

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

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

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.

Comments