ASP.net C# SQL count(*)

Question

I have a query that I'm running in C# .cs file:

DataSet ds = db.ExecuteDataSet(System.Data.CommandType.Text, "SELECT count(*) as counter FROM [Table] where [Table].[Field] = 'test'");

I want to do is retrieve the value of "counter" in this query and "return" it as my functions return value.

How can I do that?

You have already gotten answers to your question. I just want to add that if the table contains a large amount of data, you may get significantly better performance by specifying an indexed field (such as the primary key) in the count function: "SELECT count(tableId) FROM Table ..." — Fredrik Mörk
– Fredrik Mörk, Commented Sep 24, 2009 at 18:37
"...you may get significantly better performance by specifying an indexed field...". Not sure why this would be so. Surely the important point for performance is whether there is an index on the column(s) in the where clause, (in this case "where [Table].[Field] = 'test'"). If not, there is going to be a table scan no matter whether you specify COUNT(*) or COUNT(PKColumn). — to StackOverflow
– to StackOverflow, Commented Sep 24, 2009 at 19:10

Tomas Aschan · Accepted Answer · 2009-09-24 19:19:55Z

11

As the SQL query won't return a dataset but a scalar, you should use the .ExecuteScalar() method:

int count = (int)db.ExecuteScalar(System.Data.COmmandType.Text, "SELECT count(*) as counter FROM [Table] where [Table].[Field] = 'test'");

(It would be easier for us to provide an answer if you told us what type the db instance is of...)

edited Sep 24, 2009 at 19:19

answered Sep 24, 2009 at 18:30

Tomas Aschan

61.2k62 gold badges267 silver badges424 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sev Over a year ago

Database db = DatabaseFactory.CreateDatabase -- is this what you're referring to?

Sev Over a year ago

With your suggestion, I get an error stating cannot implicitly convert type object to int.

JohnFx Over a year ago

You have to cast. It. I updated the example to fix the error you received.

Sev Over a year ago

Changed it and got: Invalid expression term 'int'

Sev Over a year ago

Okay got it. I just removed the Ctype and casted it using (int) and it works now. Thank you.

|

Juliet · Accepted Answer · 2009-09-24 18:32:44Z

1

DataSet ds = db.ExecuteDataSet(System.Data.CommandType.Text, "SELECT count(*) as counter FROM [Table] where [Table].[Field] = 'test'");
return Convert.ToInt32(ds.Tables[0].Rows[0]["counter"]);

answered Sep 24, 2009 at 18:32

Juliet

81.7k46 gold badges201 silver badges230 bronze badges

2 Comments

Sev Over a year ago

I get an error: Cannot apply indexing with [] to an expression of type 'System.Data.DataTable'

James Black Over a year ago

When you initialized the dataset did you create a datatable for it?

JonH · Accepted Answer · 2009-09-24 19:23:34Z

-2

You also shouldn't be using inline SQL. This SELECT count should all be thrown into a stored procedure. The stored procedure should be called from your applications data access layer.

That is where reusability comes in, you start noticing that you just might need to call this function across various places in your web site / client app.

Just some tips!

answered Sep 24, 2009 at 19:23

JonH

33.2k13 gold badges93 silver badges149 bronze badges

Collectives™ on Stack Overflow

ASP.net C# SQL count(*)

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related