ASP.NET – Example Connection Returning a Single Value

By definition, a scalar query will return the first column of the first row of a database query. In plain English this means it will return a single value.  This can come in handy at times.

In the next series of articles I will cover the basic data connections including basic examples in C# and VB.NET.

One thing to note from the examples below is that I am using a try – catch – finally syntax to handle the clean-up of the command and connection objects. As an alternate syntax, you can make use of the Using object to handle the cleanup.

Working Example of a Scalar Query in C#

For starters, don’t forget to import the System.Web.Configuration, System.Data, and System.Data.SqlClient Namespaces:

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

Here is the sample query of calling a stored procedure that returns a single String (VARCHAR) value:

String strConn = WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
 SqlConnection conn = null;
 SqlCommand cmd = null;
 String strResultId = null;
 try
 {
 conn = new SqlConnection(strConn);
 cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandText = "test";
 cmd.Parameters.AddWithValue("testval", "testParamValue");
 cmd.CommandTimeout = 30;
 cmd.CommandType = CommandType.StoredProcedure;
 conn.Open();
 strResultId = (String)cmd.ExecuteScalar();
 }
 catch (Exception ex) { }
 finally
 {
 if (cmd != null) cmd.Dispose();
 if (conn != null)
 {
 conn.Close();
 conn.Dispose();
 }
 }

Working Example of a Scalar Query in VB.NET

As with the C# example aboce, don’t forget to import the  System.Web.Configuration, System.Data, and System.Data.SqlClient Namespaces:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Here is the sample query of calling a stored procedure that returns a single String (VARCHAR) value:

Dim strConn = WebConfigurationManager.ConnectionStrings("MyConnection").ConnectionString
 Dim conn As SqlConnection = Nothing
 Dim cmd As SqlCommand = Nothing
 Dim strResultId As String = Nothing
 Try
 conn = New SqlConnection(strConn)
 cmd = New SqlCommand()
 cmd.Connection = conn
 cmd.CommandText = "test"
 cmd.Parameters.AddWithValue("testval", "testParamValue")
 cmd.CommandTimeout = 30
 cmd.CommandType = CommandType.StoredProcedure
 conn.Open()
 strResultId = CStr(cmd.ExecuteScalar)
 Catch ex As Exception
 Finally
 If Not cmd Is Nothing Then cmd.Dispose()
 If Not conn Is Nothing Then
 conn.Close()
 conn.Dispose()
 End If
 End Try

Other Resources

Microsoft has also published a great article about the ExecuteScalar function that gives example code in both C# and VB.NET