Tuesday, February 17, 2015

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 "Top Ten Most Expensive Products" 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;
SqlDataReader dataReader = cmd.ExecuteReader();

}

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:

No comments:

Post a Comment