Return number from a Dataset after calling a SELECT COUNT(*) query

I have this query:

SELECT COUNT(*) From Employees

I execute the query and have this:

    public static int GetNumJobs()
{
    clsJobPosting JobPosting = new clsJobPosting();
    DataSet dsJobs = JobPosting.GetNumRows();
    DataTable dtJobs = dsJobs.Tables[0];
    return Convert.ToInt32(dtJobs.Rows[0]);
}

GetNumRows() returns the dataset, I want to return the number of rows from this query, how can I accompl开发者_运维百科ish this?

try changing this

return Convert.ToInt32(dtJobs.Rows[0]);

to this

return Convert.ToInt32(dtJobs.Rows[0][0]);

Also, why are you returning a DataSet from JobPosting.GetNumRows()? You should return an int there so you don't have to repeat the same logic all over your code.

You need to specify a name for the column in the stored procedure:

SELECT COUNT(*) ItemCount FROM Employees

On a side note, you really shouldn't use COUNT(*). Just use the primary key column, like this:

SELECT COUNT(ItemID) ItemCount FROM Employees

And instead of populating this into a DataSet and grabbing the row, you should use scalar execution. There is way to much overhead filling a dataset for a single value return.

And in your code:

return dtJobs.Rows[0].Field<int>("ItemCount");

In your case, dtJobs.Rows["columnName"]; where columnName is the name of the column returned from the stored procedure.

Just a few code suggestions as well:

1. It's also a little misleading that the method is named GetNumRows() but you are returning a DataSet. Perhaps make it more readable by saying GetJobs or something to that extent.

2. You don't need to prefix your class names with cls. The VS IDE will take care of giving you all the necessary information in a number of ways.