C# Programming 23 - ExecuteNonQuery vs ExecuteReader vs ExecuteScalar

Hello All,

Welcome to C# Programming article, in this article we will learn about ExecuteNonQuery vs ExecuteReader vs ExecuteScalar.

Lets Begin...

Basically command objects are used to perform the required operation on the database objects(Tables,Stored Procedure, Views etc...)

Also command objects will work with connection oriented architecture.

There are three Execution methods available in Command class.

1. ExecuteNonQuery()
2. ExecuteReader()
3. ExecuteScalar()

ExecuteNonQuery:

• It will work with Action queries only like Create, Alter, Drop, Insert, Update and Delete.
• It returns the count of rows which is affected the by the given SQL Query.
• Its return type in integer.
• We should know that Return value is optional and can be assigned to an integer variable.

    SqlConnection cn = new SqlConnection("CONNECTION_STRING");

    SqlCommand cmd = new SqlCommand("INSERT INTO Employee(EmpId,FullName)

    VALUES (@EmpId,@FullName)", cn);

    cmd.Parameters.Add("@EmpId", SqlDbType.VarChar, 50).Value = empId;

    cmd.Parameters.Add("@FullName", SqlDbType.VarChar, 50).Value = fullName;

    cn.Open();

    cmd.ExecuteNonQuery();

    cn.Close();

ExecuteReader:

• It will work with both Action and Non Action queries(Select)
• It will returns the collection of rows selected by the query
• Its return type is DataReader.
• We should know that return value is compulsary and should be assigned to an another object DataReader.

    

    SqlConnection cn = new SqlConnection("CONNECTION_STRING");

    SqlCommand cmd = new SqlCommand("select * from employee", cn);

    cn.Open();

    SqlDataReader dr = cmd.ExecuteReader();

    if(dr.Read())

    {

       //Load the data to list, collection or datatable

    }

    cn.Close();

Execute Scalar:

• It Only works with Non Action Queries that contain aggregate functions like, sum, max, count etc..
• It only returns the first row and first column value of the query result.
• Return type is object.
• Return value is compulsary and should be assigned to a varible of required type.

    SqlConnection cn = new SqlConnection("CONNECTION_STRING");

    SqlCommand cmd = new SqlCommand("select count(1) from employee", cn);

    cn.Open();

    int totalRecords = cmd.ExecuteScalar();

    cn.Close();