Articles → ADO.NET → Executescalar Method In Ado.Net

Executescalar Method In Ado.Net

Sometimes it is required to fetch a single value from the database instead of a list of records. To fetch a single record it is not a good practice to use the same method which you use when you are fetching a list of records like ExecuteReader() because it creates an unnecessary overhead on the system. Microsoft had provided a method which is dedicated for this task. In this tutorial we will discuss about the dedicated method i.e. ExecuteScalar().

Prerequisite

Before you read this article you should have hands on experience in creating a .net web application.

Sample

Consider the following code

SqlConnection conn = new SqlConnection("<Connection_String>");
SqlCommand cmd =  new SqlCommand("<Select_Query>",conn);
conn.Open();
int count = (int)cmd.ExecuteScalar();
conn.Close();
conn.Dispose();

Please note that the code above is for the explanation of concept only so the code doesn’t fulfill any quality measures.

In the above code a query is fired on a sql server database which returns a single value like count, sum or any aggregate function. The query is executed using ExecuteScalar() function and the result of the query is assigned to a variable count.

Please note that you can cast the output in any data type like string, float etc depends upon the requirement.

NullReference Exception was unhandled by user code. Object reference not set to an instance of an object

If the query is not returning any row in the result set then the code

int count = (int)cmd.ExecuteScalar();

will give this error. To handle this change the code to

object IsUserValid;
IsUserValid = accessObject.ExecuteScalar(query);
if (IsUserValid != null) {
  int count = (int) IsUserValid;
}

Multiple Records With Executescalar() Function

Now an obvious question that comes to your mind is what will happen if we execute the query using ExecuteScalar() function which returns multiple rows and columns. The answer is the output would be the first column of the first row. All other rows and columns will be ignored.

Conclusion

If your query returns multiple rows and columns then you can use ExecuteReader() or Fill() method of data adapter depends upon the requirement but if the query returns a single value then ExecuteScalar is a good option.

Posted By -	Karan Gupta

Posted On -	Wednesday, November 30, 2011

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**