What is the difference between ExecuteNonQuery(), ExecuteReader() and ExecuteScalar()?

ExecuteNonQuery(): It retuns onbly number of affected rows. Suppose you added anew row and deleted one row and updated 4 rows, so its output will be 6.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    var result = cmd.ExecuteReader();
}

ExecuteScalar(): It retuns first row’s first column’s value. So suppose friom Database you return list of employees and number of rows are 10 and fields are 5. And first field is EmployeeName, so ExecuteScalar wil retuns first row’s first column’s value.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    var result = cmd.ExecuteScalar();
}

ExecuteReader():  It works only in connected mode, that means your connection to Database is still open. Also it can have multiple result sets but you can read one at a time and only in forward direction. Once you pass a set you can’t go back and read it again. This is readonly.

It returns IDataReader by which you can read data. For current set you can user READ() method and for moving to next set you can use NextResult() methods.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    using (var reader = cmd.ExecuteReader())
    {
        while(reader.Read()) // Current set, reading set 1
        {
            learerLabel.Text = reader.GetString(reader.GetOrdinal("yourcolumn"))
        }
        reader.NextResult(); // Next set, load set 2
        while(reader.Read()) // reading set 2
        {
          // code to read set 2
        }
    }
}
Advertisements

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