Differeces Between ExecuteReader,ExecuteScalar,ExecuteNonQuery

ExecuteReader

ExecuteReader will be used to return the set of Rows,on execution of Sql query or Stored Procedure using command object.This  one is the forward only retrieval of records and it is used to read the table value from first to last.

ExecuteScalar

Execute Scalar will return single row single column value ( i.e single value ) on execution of Sql query or Stored Procedure using command object.It is very fast to retrieve single value form database.

example :

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select Student_Name,Roll from student", con);

string result = (string)cmd.ExecuteScalar();

if (!string.IsNullOrEmpty(result))

{

lblDetails.Text = result;

}

else

{

lblDetails.Text =  "No value Selected" ;

}

con.Close();

}

}

ExecuteNonQuery

ExecuteNonQuery will return number of rows effected with Insert,Update,Delete operations.This method used only for Insert,Update,Delete,Create and Set Statement.

example

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

using (SqlConnection con=new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("insert into student(Student_Name,Roll) values(@Name,@Roll)", con);

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

cmd.Parameters.AddWithValue("@Roll", "12");

int count= cmd.ExecuteNonQuery();

if(count>=1)

{

lblDetails.Text =  count.ToString();

}

else

{

lblDetails.Text = "0" ;

}

con.Close();

}

}