Is there an easier way to get record count than I'm doing it?

[Zinderin]

Still learning here...

this is my method to get the record count of a table ... it works, but it seems like a lot of coding for something so simple.

Is there an easier way?

public int GetTableRecCount(string table)
        {
       
            //Set up the connection and adapters...
            OleDbConnection myConn = new OleDbConnection();
            myConn.ConnectionString = strConnStr;
            OleDbCommand sqlCmd = new OleDbCommand("SELECT COUNT(*) FROM " + table);
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
            dbAdapter.SelectCommand = sqlCmd;
            DataSet ds = new DataSet();
            ds.Clear();

            // Open the connection ....
            myConn.Open();
            sqlCmd.Connection = myConn;

            // Get the data and see how many records we got ....
            dbAdapter.Fill(ds, "count");

            string tmpStr = ds.Tables[0].Rows[0][0].ToString();
            // Close the connection and toss it...
            myConn.Close();
            myConn.Dispose();

            return int.Parse(tmpStr);
        }

I'm still a little shakey on the dataset stuff, and retreiving the value to a string, and then parsing it was the only way I could get it to work. When I tied;

return int.Parse(ds.Tables[0].Rows[0][0].ToString());

or

  return ds.Tables[0].Rows[0][0];

... the compiler whined at those two attempts, and the build failed. I'm not sure why, but I'm not stressing over it either.

Just looking for mentoring ... thanks in advance guys.

[pritesh2010]

can you tell me why are you use dataset. when you fire a count(*) query it ultimatly gives only one row and one column . If youwant this result of the query you can directly execute your command With ExecuteScalar. ExecuteScalar this will give you first row of first column.. foe E.g.

con.Open();
OleDbCommand cmd=new OleDbCommand("SELECT count(*) from table",con);
string ans=cmd.ExecuteScalar();
con.close();

i think you want this.other wise tell us in brief wht you want. :)

[kvprajapati]

Correction to @pritesh2010's post.

Return data type of method ExecuteScalar() is object - type cast is needed.

OleDbCommand cmd=new OleDbCommand("SELECT count(*) from table",con);

con.Open();
int count=(int) cmd.ExecuteScalar(); 
con.close();

OR use DataAdapter way:

OleDbDataAdapter adp = new OleDbDataAdapter("select count(*) from " + table, @"put_connection_string_here");

DataTable dt = new DataTable();
adp.Fill(dt);
int count = (int)dt.Rows[0][0];
Console.WriteLine(count);

[Zinderin]

ANSWER TO pritesh2010:

Because that's the only way I know how to do it. :) Like I said, I'm still learning and thus why I come to the mountain and ask.

ADATAPOST:

The DataAdapter actually fills the table tho right? So there's a un-needed transfer of data to then count it locally?

It looks to me like the ExecuteScalar() is what you guys are saying I need, so now its off to the books and web to find out what that is.

As always, you guys rock!!!!

[Zinderin]

Ok, I need some help on the ExecuteScalar() ... I can't get the SqlConnection to work.

And all the examples I can find on the web keep trying to connect to some remote database. I just want to connect to the access db in that's in the same directory.

It doesn't take the same parameters as the OleDBConnection. :(

I'm probably just getting tired, I've been coding for 17 hours now.