How do I use ExecuteScalar in C#?

Table of Contents

1. Introduction

In this C# Ado.net example, we will see how one can use the

SQLCommand

to fetch a single value from the SQL Server database. Here, we will get the total number of records in the Employees table of the

NotrhWnd

database using the

ExecuteScalar

method of ‘ADO.Net’. Since Ado.Net designed this method to fetch only one value from the database, it works faster. Have a look at the below picture:

C# ADO.NET’s Execute Scalar Block Diagram

The SQL Connection Object through Connection String knows where the database resides and how to connect to it. The SQL Command object knows what needs to be executed on the database. SQL Command object uses the SQL Connection to execute the command stored in the

CommandText

property. The method

ExecuteScalar

returns the retrieved value as the Object.

2. Create C# Console Application

First, we need to create a C# Console application. Then, we need a table from a database. In this example, we will use Employees table from the NorthWnd database. But, you can use any table with some sample data in it. The Picture shows where to pick console application in the “New Project” window:

Create C# Console Application

3. C# Code Example for ExecuteScalar of ADO.NET

3.1 Required NameSpace

As we will work on accessing the database, we need to have the above using statement for ease of use.

//Sample 01: Required Name Space
using System.Data;
using System.Data.SqlClient;

//Sample 01: Required Name Space

using System.Data;

using System.Data.SqlClient;

3.2 Open Connection to SQL Server

Inside the main function, we form a connection string. Once the connection string is in place, it is easy to create the

SqlConnection

object. The code snippet below uses the Open method of the

SqlConnection

object to establish the connection to SQL server database. Below is the code that opens the SQL Server database connection:

//Sample 02: Form the SQL Connection String and Open Connection Object.
string connection_string = "Data Source=(Local);Initial Catalog=Northwind;" +
"Persist Security Info=True;User ID=sa;Password=type_your_password_here";
SqlConnection con = new SqlConnection(connection_string);
con.Open();

//Sample 02: Form the SQL Connection String and Open Connection Object.

string connection_string = "Data Source=(Local);Initial Catalog=Northwind;" +

"Persist Security Info=True;User ID=sa;Password=type_your_password_here";

SqlConnection con = new SqlConnection(connection_string);

con.Open();

Forming the Connection String is the Key to connect to a database. In the above connection string, (Local) represents that the database is running on the same system in which the program is executing. NorthWnd is the database name. Also, note that you should provide user id and password as part of the connection string. The below picture shows parts of the connection string:

Parts of Connection String

3.3 Setup SQLCommand

Now

SqlConnection

object is ready. Once it is ready, we will prepare a command object to issue the Database command through the

SqlConnection

object. In the below code, once the

SqlCommand

object ‘

cmd

‘ is created, the

CommandText

property is set with the SQL statement. This will retrieve a single value from the database. Note, the

SqlConnection

object also given to this

cmd

object.

//Sample 03: Create the SQL Command Object
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select Count(*) as Cnt from Employees";
cmd.Connection = con;

//Sample 03: Create the SQL Command Object

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Select Count(*) as Cnt from Employees";

cmd.Connection = con;

3.4 Call ExecuteScalar and Output the Result

Now we have a valid

SqlCommand

, which knows what to execute through

CommandText

property and where to execute through the Connection property. Making a call to the function

ExecuteScalar

will return an object. As

Count(*)

returns an integer, we can typecast the object to

Int32

using the

System.Convert.ToInt32

. Below is the code that fetches total number of records from the Employees table. Finally, we print the output in the console window.

//Sample 04: Execute the Query and Get the Count of Emplyees
object count = cmd.ExecuteScalar();
Int32 Total_Records = System.Convert.ToInt32(count);

//Sample 05: Print the Number of Records
System.Console.WriteLine("");
System.Console.WriteLine("Total Number of Records in Employees Table: {0}", Total_Records);
System.Console.WriteLine("");

//Sample 04: Execute the Query and Get the Count of Emplyees

object count = cmd.ExecuteScalar();

Int32 Total_Records = System.Convert.ToInt32(count);

//Sample 05: Print the Number of Records

System.Console.WriteLine("");

System.Console.WriteLine("Total Number of Records in Employees Table: {0}", Total_Records);

System.Console.WriteLine("");

4. Complete code and Output of ExecuteScalar Example

using System;
using System.Collections.Generic;
using System.Text;

//Sample 01: Required Name Space
using System.Data;
using System.Data.SqlClient;


namespace ExecuteScalarDemo
{
  class Program
  {
    static void Main(string[] args)
    {
      //Sample 02: Form the SQL Connection String and Open Connection Object.
      string connection_string = "Data Source=(Local);Initial Catalog=Northwind;" +
      "Persist Security Info=True;User ID=sa;Password=rohith";
      SqlConnection con = new SqlConnection(connection_string);
      con.Open();

      //Sample 03: Create the SQL Command Object
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "Select Count(*) as Cnt from Employees";
      cmd.Connection = con;

      //Sample 04: Execute the Query and Get the Count of Emplyees
      object count = cmd.ExecuteScalar();
      Int32 Total_Records = System.Convert.ToInt32(count);

      //Sample 05: Print the Number of Records
      System.Console.WriteLine("");
      System.Console.WriteLine("Total Number of Records in Employees Table: {0}", Total_Records);
      System.Console.WriteLine("");
    }
  }
}

using System;

using System.Collections.Generic;

using System.Text;

//Sample 01: Required Name Space

using System.Data;

using System.Data.SqlClient;

namespace ExecuteScalarDemo

{

class Program

{

static void Main(string[] args)

{

//Sample 02: Form the SQL Connection String and Open Connection Object.

string connection_string = "Data Source=(Local);Initial Catalog=Northwind;" +

"Persist Security Info=True;User ID=sa;Password=rohith";

SqlConnection con = new SqlConnection(connection_string);

con.Open();

//Sample 03: Create the SQL Command Object

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Select Count(*) as Cnt from Employees";

cmd.Connection = con;

//Sample 04: Execute the Query and Get the Count of Emplyees

object count = cmd.ExecuteScalar();

Int32 Total_Records = System.Convert.ToInt32(count);

//Sample 05: Print the Number of Records

System.Console.WriteLine("");

System.Console.WriteLine("Total Number of Records in Employees Table: {0}", Total_Records);

System.Console.WriteLine("");

}

Output

Program Output

Categories: C#

Tags: ADO.Net, CommandText, ExecuteScalar, SqlCommand

Do you like this Example? Please comment about it for others!!Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

1. Introduction

2. Create C# Console Application

3. C# Code Example for ExecuteScalar of ADO.NET

3.1 Required NameSpace

3.2 Open Connection to SQL Server

3.3 Setup SQLCommand

3.4 Call ExecuteScalar and Output the Result

4. Complete code and Output of ExecuteScalar Example

Output

Like this:

Related

Do you like this Example? Please comment about it for others!!Cancel reply

Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

How do I use ExecuteScalar in C#?

1. Introduction

2. Create C# Console Application

3. C# Code Example for ExecuteScalar of ADO.NET

3.1 Required NameSpace

3.2 Open Connection to SQL Server

3.3 Setup SQLCommand

3.4 Call ExecuteScalar and Output the Result

4. Complete code and Output of ExecuteScalar Example

Output

Share this:

Like this:

Related

Do you like this Example? Please comment about it for others!!Cancel reply