Return Single Value with ExecuteScalar, Stored Procedures and C#

December 3rd, 2008 | Posted in ASP.NET, SQL Server

A common query is to return a single value from a SQL query such as an aggregate to get the total number of rows in a table. I will show you how to do just that with SQL Server Stored Procedures and C#.

Preparation

You will need some table data to query against. I am using the Northwind sample database. You can get links to download a copy of the sample database and instructions how to install from here.

I will be using the following technologies for this demonstration.

ASP.NET 2.0
SQL Server Express 2005 – Code should work with SQL Server versions 2000 and higher

For the impatient you can download the entire project here.

SQL Server

Stored Procedure

Copy the following code into a new stored procecure with SQL Server Management Studio or directly within the Visual Studio Server Explorer.

Stored Procedure: CountCustomers.sql

CREATE PROCEDURE dbo.CountCustomers
AS
SELECT COUNT(CustomerID) AS TotalCustomers
FROM Customers

ASP.NET

web.config

Add the following SQL connection string to your web.config if you already do not have a connection to your database.

Web Form

Create a new web site project or a new web form page to an existing web site. Add a label control to display the total number of customers. Web Form: ExecuteScalar.aspx

Total Number of Customers:

Code Behind

Add the following to your using statements.

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

Place the following within your Page_Load or event you want the count to be displayed.

///
/// SQL
///

// Open SQL connection
SqlConnection myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString());
myConnection.Open();

// Create command
SqlCommand myCommand = new SqlCommand("CountCustomers", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

// Execute
int totalCustomerCount = Convert.ToInt32(myCommand.ExecuteScalar());

// Close SQL connection
myConnection.Dispose();
myConnection.Close();

///
/// Set control values
///

Label1.Text = totalCustomerCount.ToString();

Jiv Studio