VFP OleDbDataReader and returning COUNT(*)

I found out something interesting about the VFP OleDb driver yesterday. I’m using a DataReader to return records from a FoxPro table, and one thing about DataReaders in .NET 1.1 is that you can’t get a record count. (There’s a .RowsAffected property but that’s different, as I understand it.)

The usual thing to do is to perform a pre-select to get the record count, and use ExecuteScalar() to return the single count value:

StringBuilder sb = new StringBuilder();
sb.Append("SELECT COUNT(*) FROM customers WHERE name = '");
sb.Append(  cCustomer ) ;
sb.Append(  "'") ;

oCmd = new System.Data.OleDb.OleDbCommand( sb.ToString(), oVfpConn ) ;

int resultCount = (int) oCmd.ExecuteScalar();  

This code works fine if you’re using a SqlClient.SqlDataReader against SQL Server, but here, we get an exception: “specified cast is not valid”.

It turns out that, for the VFP OleDb source, oCmd.ExecuteScalar() returns a System.Decimal object instead of an int. After some experiments, I found this seemed to work best:

	
int resultCount = System.Decimal.ToInt32( (Decimal) oCmd.ExecuteScalar() ); 

if ( resultCount == 0 )
{
	...

This is fun!

Update: David Stevenson has a better solution: Use CAST to force the results to an Integer:

	
StringBuilder sb = new StringBuilder();
sb.Append("SELECT CAST(COUNT(*) AS Integer) FROM customers WHERE name = '");
sb.Append(  cCustomer ) ;
sb.Append(  "'") ;

oCmd = new System.Data.OleDb.OleDbCommand( sb.ToString(), oVfpConn ) ;

int resultCount = (int) oCmd.ExecuteScalar();  

Much cleaner.