ASP.NET: Get a Single Value From a Stored Procedure Part 7

Sometimes you need to call a stored procedure that only returns one value. It would be overkill to use the SqlDataReader to store just one value. You can use the SqlCommand.ExecuteScalar() method instead to retrieve just one value from the database.
Here is how you would call the "GetProductsAvgPrice" stored procedure in the Northwind database.

1.  First you need create a stored procedure in the SQL Server that will return just one value the average price of products in Products table in the Northwind database. Run this code in the SQL Server query editor window

USE Northwind;
GO
CREATE PROCEDURE GetProductsAvgPrice
AS
    SELECT AVG(UnitPrice)
    FROM Products;
GO

2.  In your C# code file you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure using the ExecuteScalar() method

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
      conn.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "GetProductsAvgPrice";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = conn;
      decimal avgPrice = Convert.ToDecimal(cmd.ExecuteScalar());

    }

In the above code the way you call a stored procedure is very similar to the way you query the database. The only different is instead of specifying the SQL statement in the SqlCommand.CommandText property you specify the stored procedure name instead. Also you have set the SqlCommand.CommandType to CommandType.StoredProcedure type to let the reader know that it is executing a stored procedure.




Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8