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

About these ads

3 thoughts on “ASP.NET – Example Connection Returning a Single Value

  1. Pingback: ASP.NET - Example Connection Returning a Single Value | Web App Dev | Scoop.it

  2. Scalar query is when query returns single value, not single row. In your case you actually might get several rows and if you require to get result, you need to use DataSet/DataTable or DataReader.

    • Thanks for your feedback. Yes, scalar returns a single value, not a row. Perhaps I was not clear enough in my introductory sentence. My example, however is quite on par with existing examples by Microsoft where cmd.ExecuteScalar() is set to populate a variable, so I don’t see a need to use a DataSet/DataRow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s