Introduction
In this blog, I will explain the ExecuteReader, ExecuteNonQuery, and Executescalar in ADO.Net.
ExecuteNonQuery
ExecuteNonQuery method is used to execute SQL Command or the stored procedure performs INSERT, UPDATE, or Delete operations. It doesn't return any data from the database. Instead, it returns an integer specifying the number of rows inserted, updated or deleted.
Example:
public class Sample {
public void Test(int Id, string Name) {
SqlConnection sqlCon = null;
String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
using(sqlCon = new SqlConnection(SqlconString)) {
sqlCon.Open();
SqlCommand Cmnd = new SqlCommand("PROC_NAME", sqlCon);
Cmnd.CommandType = CommandType.StoredProcedure;
Cmnd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = Id;
Cmnd.Parameters.AddWithValue("@NAME", SqlDbType.NVarChar).Value = Name;
int result = Cmnd.ExecuteNonQuery();
sqlCon.Close();
}
}
}
ExecuteReader
ExecuteReader method is used to execute a SQL Command or storedprocedure returns a set of rows from the database.
Example
public class Sample {
public string Test(int Id) {
SqlConnection sqlCon = null;
String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
using(sqlCon = new SqlConnection(SqlconString)) {
sqlCon.Open();
SqlCommand Cmnd = new SqlCommand("SELECT *FROM TABLE_NAME WHERE ID=@ID", sqlCon);
Cmnd.Parameters.AddWithValue("@ID", Id);
SqlDataReader rdr = Cmnd.ExecuteReader();
while (rdr.Read()) {
string name = rdr["Name"].ToString();
string age = rdr["Age"].ToString();
string city = rdr["City"].ToString();
}
sqlCon.Close();
}
return "";
}
}
Executescalar
ExecuteScalar method is used to execute SQL Commands or storeprocedure, after executing return a single value from the database. It also returns the first column of the first row in the result set from a database.
Example
public class Sample {
public string Test(int Id) {
SqlConnection sqlCon = null;
String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
using(sqlCon = new SqlConnection(SqlconString)) {
sqlCon.Open();
SqlCommand Cmnd = new SqlCommand("SELECT NAME FROM TABLE_NAME WHERE ID=@ID", sqlCon);
Cmnd.Parameters.AddWithValue("@ID", Id);
object result = Cmnd.ExecuteScalar();
if (result != null) {
string name = result.ToString();
}
sqlCon.Close();
}
return "";
}
}