Difference between ExecuteNonQuery, ExecuteReader and ExecuteScalar Functions

In this article I will explain the differences and scenarios of  ExecuteNonQuery,
ExecuteReader and ExecuteScalar Functions of SqlCommand Class of ADO.NET.

ExecuteNonQuery :

ExecuteNonQuery executes the SQL statement and returns an integer value that represents 

the number of rows affected. It is basically used for operation that doesn't return value and

change the data in a database with INSERT, UPDATE or DELETE  Query. Even though not 

used widely, it is also used for catalog operations like creating database objects like table.

With any other SQL Queries other than INSERT,UPDATE or DELETE, return value is -1.
Also in case if rollback occurs, it returns -1.

If there is any trigger associated with INSERT or UPDATE query, it will return both the
number of rows affected by the INSERT or UPDATE and the number of rows affected
by the trigger.

For eg. there is an insert function to insert values to Agents Table as :

public int AddAgent(int AgnNo, string Name, int Tel)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int rowsAffected = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "insert into Agents(AGN_NO,AGN_NAME,AGN_TEL) values       (@AgnNo,@Name,@Tel)";
            cmd.Connection = conn;
            conn.Open();

            cmd.Parameters.Add(new SqlParameter("@AgnNo", AgnNo));
            cmd.Parameters.Add(new SqlParameter("@Name", Name));
            cmd.Parameters.Add(new SqlParameter("@Tel", Tel));
            rowsAffected = cmd.ExecuteNonQuery();
            conn.Close();

        }
        return rowsAffected;
    }

Here this function returns 1 if inserted successfully.

Next is the example for Update Query :

public int UpdateAgent(int AgnNo, string Name, int Tel)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int rowsAffected = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "Update Agents set AGN_NAME=@Name,AGN_TEL=@Tel where AGN_NO=@AgnNo";
            cmd.Connection = conn;
            conn.Open();

            cmd.Parameters.Add(new SqlParameter("@AgnNo", AgnNo));
            cmd.Parameters.Add(new SqlParameter("@Name", Name));
            cmd.Parameters.Add(new SqlParameter("@Tel", Tel));
            rowsAffected = cmd.ExecuteNonQuery();
            conn.Close();

        }
        return rowsAffected;
    }

Here also this function returns 1 if updated successfully.

Example for Delete Query is as following :

public int DeleteAgent(int AgnNo)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int rowsAffected = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "Delete From Agents where AGN_NO=@AgnNo";
            cmd.Connection = conn;
            conn.Open();

            cmd.Parameters.Add(new SqlParameter("@AgnNo", AgnNo));
            cmd.Parameters.Add(new SqlParameter("@Name", Name));
            cmd.Parameters.Add(new SqlParameter("@Tel", Tel));
            rowsAffected = cmd.ExecuteNonQuery();
            conn.Close();

        }
        return rowsAffected;
    }

Here also this function returns 1 if deleted successfully.

ExecuteReader :

This method is used for fetching records from database using SELECT statement

or Stored Procedure with SELECT Query.

ExecuteReader basically sends the CommandText to the Connection and builds a

SqlDataReader which provides a forward-only way of reading rows from the 

database.

For eg.

public SelectAgents()
{
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "Select * From Agents";
            cmd.Connection = conn;
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string Name = dr["AGN_NAME"].ToString();
                string Tel = dr["AGN_TEL"].ToString();

                Response.Write("Name is : "+ Name);
                Response.Write("Tel is : "+ Tel);
            }
            conn.Close();

        }
     
  }

ExecuteScalar :

ExecuteScalar method executes the query and returns the first column of the first row

of the result set. It is used in case of getting single value from the database.

Below is the example for Select statement :

public int GetAgentCount()

{

        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        using (SqlConnection conn= new SqlConnection(connectionString))

        using (SqlCommand cmd= new SqlCommand())

        {

            cmd.CommandText = "select count(*) from Agents";

            cmd.Connection = dbConnection;

            conn.Open();

            return (int) cmd.ExecuteScalar();

        }

}

It can also be used for getting the identity column value of the inserted row.

Below is the example:

public int AddAgent(int AgnNo, string Name, int Tel)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int id= 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "insert into Agents(AGN_NO,AGN_NAME,AGN_TEL) values       (@AgnNo,@Name,@Tel); Select CAST(scope_identity() as int)";
            cmd.Connection = conn;
            conn.Open();

            cmd.Parameters.Add(new SqlParameter("@AgnNo", AgnNo));
            cmd.Parameters.Add(new SqlParameter("@Name", Name));
            cmd.Parameters.Add(new SqlParameter("@Tel", Tel));
            id=  (int) cmd.ExecuteScalar();
            conn.Close();

        }
        return id;
    }