Ben Reichelt’s Weblog

When I was developing a custom datagrid control, I ran into the problem
of paging.  Frequently you need to show the results in sets of 25,
50, 100, etc. because there can be thousands of records making it
impossible to show them all at once.  So with our custom datagrid,
the user could supply a sql statement that would drive the datasource
of the grid.  In order to get the paging correct, I needed to know
the total number of records, so that I could calculate the number of
pages that were available, so at first we had the user calculate the
total themserlves, and supply that to the datagrid, which worked fine,
but I wanted it to be easier to use.  So I thought about it for a
little bit, and realized that with a little manipulation of the
supplied sql statement, you could get the total record count for any
query.  Heres a snippet of the code that I used to accomplish this:

string s = sql.ToLower();
int index = s.IndexOf(” from “);
string end = s.Substring(index);
string countSql = “select count(*) ” + end;

What this does is strip the “SELECT Column1, Column2, ….” from a sql
statement and replaces it with  “SELECT COUNT(*)”, leaving the
user’s WHERE clause intact, so that the resulting sql statement has the
same logic, its just selecting a count of records, instead of the
columns the user wanted.  Its worked pretty slick since then, I
can now give my datagrid a sql statement and the paging works
automatically, it makes our life a lot easier.