ExecuteScalar in ADO.Net - ProgramIdea

ExecuteScalar In ADO.NET

ExecuteScalar executes the command specified and returns first column of the first row. Its return type is object that containing first column of the first row. It's very useful when you are working with retrieving single value, count or aggregate function like sum, avg.

ExecuteScalar is method of Command class. For using this you have to specify below namespaces so that you can connect sql server.

// Namespace

using System.Data;

using System.Data.SqlClient;

After specify namespaces, create SqlConnection object, specify connection string and open the connection. Create SqlCommand object and set connection string, command type and command text. After that use ExecuteScalar method to execute the query and after this close the connection. Make sure you open the connection before using ExecuteScalar.

Below example demonstrate that steps to use of ExecuteScalar:

// Create SqlConnection object

SqlConnection con = new SqlConnection();

// Specify connection string

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

// Open Connection

con.Open();

// Create SqlCommand object

SqlCommand cmd = new SqlCommand();

// Specify command connection

cmd.Connection = con;

// Set commant type means query, stored procedure or table direct

cmd.CommandType = CommandType.Text;

// Sql query specify

cmd.CommandText = "Select Name from tblTest where Id=1";

// Execute command

object obj = cmd.ExecuteScalar();

// Close connection

con.Close();

Get Return value from ExecuteScalar

ExecuteScalar returns object that contains a single value or first column of first row of specified command. You can convert return type of ExecuteScalar according to your requirement. Suppose you want to retrieve a single value in form of integer or string or boolean or data time. Below examples demonstrate that how to retrieve return type according to your requirement:

ExecuteScalar's return value as string

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select Name from tblTest where Id=1";

string retval = Convert.ToString(cmd.ExecuteScalar());

con.Close();

ExecuteScalar's return value as integer

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select count(*) from tblTest";

int retval = Convert.ToInt32(cmd.ExecuteScalar());

con.Close();

ExecuteScalar's return value as boolean

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select IsActive from tblTest where Id=1";

bool retval = Convert.ToBoolean(cmd.ExecuteScalar());

con.Close();

ExecuteScalar's return value as date time

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select DOJ from tblTest where Id=1";

DateTime retval = Convert.ToDateTime(cmd.ExecuteScalar());

con.Close();

ExecuteScalar for OleDb

In above examples we seen sql server database command execution, now we are using ExecuteScalar for OleDb database command. For OleDb connection, you have to specify below namespaces:

// Namespace

using System.Data;

using System.Data.OleDb;

After specify namespaces, all the steps are same as sql command object, just difference is to use OleDb command object instead of Sql.

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dataaccess.mdb;Persist Security Info=False");

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Insert into tblTest(Name,Points) Values('Jitesh', 100)";

object obj = cmd.ExecuteScalar();

con.Close();

ExecuteScalar for Stored Procedure

You can use ExecuteScalar for executing stored procedure. For stored procedure execution, you have to specify command type and pass stored procedure name in command text. If you want to pass parameter then use Parameters.AddWithValue that holds parameter name and that value. Below example demonstrate that use of ExecuteScalar for executing stored procedure and that returns a string value:

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_test";

cmd.Parameters.AddWithValue("@Name", "Jitesh");

cmd.Parameters.AddWithValue("@Ponits", 100);

string retval = Convert.ToString(cmd.ExecuteScalar());       

con.Close();

Standard ExecuteScalar Functions

If you are using ADO.Net frequently in your project then create a class for ADO.Net that contains all functions that executing commands. Below is example of that class:

using System;

using System.Data;

using System.Data.SqlClient;

 

public class SqlDb

{

    SqlConnection con = new SqlConnection( "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;" );

 

 

    // Use for return type as String

    public string ExecuteScalarString(string sqlQuery)

    {       

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        string retval = Convert.ToString(cmd.ExecuteScalar());

        con.Close();

        return retval;

    }

 

    // Use for return type as Integer

    public int ExecuteScalarInt(string sqlQuery)

    {       

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        int retval = Convert.ToInt32(cmd.ExecuteScalar());

        con.Close();

        return retval;

    }

    // Use for return type as Boolean

    public bool ExecuteScalarBool(string sqlQuery)

    {

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        bool retval = Convert.ToBoolean(cmd.ExecuteScalar());

        con.Close();

        return retval;

    }

 

    // Use for return type as DateTime

    public DateTime ExecuteScalarDateTime(string sqlQuery)

    {

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        DateTime retval = Convert.ToDateTime(cmd.ExecuteScalar());

        con.Close();

        return retval;

    }

}

Once you created above class, then it's very easy to execute sql query from anywhere in your application. Best things about this is to no need of write to much of codes in page behind. Below example demonstrate that, how to use above class functions:

SqlDb objDb = new SqlDb();

       

int count = objDb.ExecuteScalarInt("select count(*) from tblTest");

 

string name = objDb.ExecuteScalarString("select Name from tblTest where Id=1");

 

bool isActive = objDb.ExecuteScalarBool("select IsActive from tblTest where Id=1");

 

DateTime datetime = objDb.ExecuteScalarDateTime( "select DOJ from tblTest where Id=1" );