How do I find the record count using Ole in C#?


I'm using OleDbConnection and OleDbCommand objects, but seem to have forgotten how to check the record count.  Do I use the OleDbDataReader?

Who is Participating?
123654789987Connect With a Mentor Commented:
Sorry.. I have mentioned the command for SqlConnection .For OledbConnection it has to be

DataSet dataset1 = OleDbCommand.ExecuteDataset();

int recordCount;

recordCount = dataset1.Tables[0].rows.count();
Mohammed NasmanSoftware DeveloperCommented:
  Do it using Sql statement as "Select Count(*) from mytable where ...."

using OleDBCommand and call ExecuteScalar method, which return the first value, this way is the fastest and give you the best performance, other way like filling Dataset will require from you to return all records to know it's count



DataSet dataset1 = SqlCommand.ExecuteDataset();

int recordCount;

recordCount = dataset1.Tables[0].rows.count();

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Mohammed NasmanSoftware DeveloperCommented:
if you have 1 million records, you will fill the dataset with the Million records to get the count???

using OleDbDataReader will not help in that, as I said before using select count(*) is better
If the question is about just getting the Count of records then your solution is acceptable. But if the user wants to use the data from a particular table then why should he fire a separate query to get the count when the data has to be retrieved from the table.

I don't know what the requirement is exactly so let ba272 deceide based on his requirment
Mohammed NasmanSoftware DeveloperCommented:
he already mentioned that he used OleDbConnection and OleDbCommand
ba272Author Commented:
I actually like Mohammed's approach, since it would empower me to do many more kinds of things than just get a row count.  I could use SQL to retrieve anything I want, and I'm sure the opportunity will arise.  But I don't know how to actually get a value back when I execute the SQL command.  Here's how far I've gotten...

OleDbConnection conn1 = new OleDbConnection();
conn1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
              @"Data source=c:\data\datafile.mdb";

OleDbCommand dbSelectCommand1 = new OleDbCommand();
dbSelectCommand1.Connection = conn1;
string sql;
sql = "SELECT COUNT(*) FROM DataTable";
dbSelectCommand1. CommandText = sql;                        
OleDbDataReader reader1 = dbSelectCommand1.ExecuteReader();      

But where would I actually get the return value?  I'd most like an answer to this question.

I also did try the following approach to getting a row count, but the compiler would not swallow it.  I must have either done something wrong, or this would require the discussion of DataSets, which I don't have time to learn about at the moment.  

DataSet dataset1 = OleDbCommand.ExecuteDataset();

But the return value for an SQL intersts me greatly.  If anyone has the answer at their fingertips, I'd love to hear from you.

Jeremy DaleySenior DeveloperCommented:
I'm simply not seeing an executeDataSet() method in .NET.

to get the row count, i've simply done this:

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=zipcodes.mdb";
OleDbCommand cmd = new OleDbCommand("Select Count(*) FROM Zips", conn);
int recordCount = (Int32)cmd.ExecuteScalar();

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.