C# SQL if query returns any rows count

Question

What is the simplest and most efficient way to find if a data returns using a query? I'm using DataTable like sqlAdapter.Fill(_table1) and then doing _table1.Rows.Count to see if a datatable has any rows. Is there any classes and functions in C# that just gives me if there are any rows. I don't need the data of the rows. Just the count is what I need. I'm running this query against very large datasets so I don't wanna fill the datatable with all the row info.

TGH · Accepted Answer · 2012-05-26 00:29:12Z

9

string myScalarQuery = "select count(*) from TableName";

SqlCommand myCommand = new SqlCommand(myScalarQuery, myConnection);
myCommand.Connection.Open();
int count = (int) myCommand.ExecuteScalar();
myConnection.Close();

Possible optimization of the query per the comments bellow: Select Top 1 * FROM TableName

edited May 26, 2012 at 0:29

answered May 26, 2012 at 0:15

TGH

39.4k13 gold badges106 silver badges140 bronze badges

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

8 Comments

TGH Over a year ago

My understanding is that you want a query to determine if a table contains any rows? So why not just run a count query

user166390 Over a year ago

Looks good to me (+1). Well, except not being a statically-typed query ;-) However, ExecuteScalar has an object return type so a Convert.ToInt32 might be in order.

user1166147 Over a year ago

If you just want to see rows and don't actually need the count maybe SELECT TOP 1 1 FROM instead of * - to stop processing as soon as it sees a row.

TGH Over a year ago

@user1166147 I agree. I included the count just for the flexibility if the count is needed

TGH Over a year ago

Added your optimization suggestion @user1166147

|

detale · Accepted Answer · 2012-05-26 05:06:24Z

8

The least expensive way is using SqlDataReader's HasRows property
UPDATE: of course, the most efficient SELECT query will be like "Select Top 1 1 FROM TableName", which doesn't even need to pull any column data.

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        if (rdr.HasRows)
            ...
    }
}

edited May 26, 2012 at 5:06

answered May 26, 2012 at 0:32

detale

13k6 gold badges44 silver badges43 bronze badges

2 Comments

user166390 Over a year ago

What would the SqlCommand look like in this case (for completeness)? Is there as reason to favor this over a COUNT?

TGH Over a year ago

The slowest component will always be the query itself, so the C# part is imo less important.

Collectives™ on Stack Overflow

C# SQL if query returns any rows count

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related