Where To Use Sql Command ExecuteScalar


Introduction


In my previous article I have explained a bit about how and where to use ExecuteNonQuery. In this article we will see how and where to use the Sql Command ExecuteScalar in ADO.NET.

 

Behind The Scene


When your query returns a single value, that is an aggregate value from the database then its better to use ExecuteScalar.

This needs a minimum of one select query in your procedure that returns a single value. The returned value may be of any type so you need to typecast the ExecuteScalar with the DataType you want.

Lets say your stored procedure is written to select a particular user name from the database according to the user id, so in this case you don't need a DataSet to hold the returned record. Instead you need only a string variable to hold the user name  that is retrieved from the database.

Below is a simple example of this that retrieves the user name from the database according to the user id.
 public static string GetUserNameByUserID(string userID, string connString)
 {
     string userName = string.Empty();
     string sql =
         "SELECT UserName FROM [User] WHERE UserId = "+ userID;
     using (SqlConnection conn = new SqlConnection(connString))
     {
         SqlCommand cmd = new SqlCommand(sql, conn);
         try
         {
             conn.Open();
             userName = (string)cmd.ExecuteScalar();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
         }
     }
     return userName ;
 }
** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...

No comments:

Post a Comment