ExecuteReader, ExecuteNonQuery and Executescalar in ADO.NET

 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 storeprocedure 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:

1. public class Sample  
2. {  
3. public void Test(int Id, string Name)  
4. {  
5. SqlConnection sqlCon = null;  
6. String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;  
7. using (sqlCon = new SqlConnection(SqlconString))  
8. {  
9. sqlCon.Open();  
10. SqlCommand Cmnd = new SqlCommand("PROC_NAME", sqlCon);  
11. Cmnd.CommandType = CommandType.StoredProcedure;  
12. Cmnd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = Id;  
13. Cmnd.Parameters.AddWithValue("@NAME", SqlDbType.NVarChar).Value = Name;  
14. int result= Cmnd.ExecuteNonQuery();  
15. sqlCon.Close();  
16. }  
17. }  
18.   
19.   
20. }  

ExecuteReader:

ExecuteReader method is used to execute a SQL Command or storedprocedure returns a set of rows from the database.

Example:

1. public class Sample  
2. {  
3. public string Test(int Id)  
4. {  
5. SqlConnection sqlCon = null;  
6. String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;  
7. using (sqlCon = new SqlConnection(SqlconString))  
8. {  
9. sqlCon.Open();  
10. SqlCommand Cmnd = new SqlCommand("SELECT *FROM TABLE_NAME WHERE ID=@ID", sqlCon);  
11. Cmnd.Parameters.AddWithValue("@ID", Id);  
12. SqlDataReader rdr = Cmnd.ExecuteReader();  
13. while (rdr.Read())  
14. {  
15. string name = rdr["Name"].ToString();  
16. string age = rdr["Age"].ToString();  
17. string city = rdr["City"].ToString();  
18. }  
19. sqlCon.Close();  
20. }  
21. return "";  
22. }  
23.   
24. }  

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:

1. public class Sample  
2. {  
3. public string Test(int Id)  
4. {  
5. SqlConnection sqlCon = null;  
6. String SqlconString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;  
7. using (sqlCon = new SqlConnection(SqlconString))  
8. {  
9. sqlCon.Open();  
10. SqlCommand Cmnd = new SqlCommand("SELECT NAME FROM TABLE_NAME WHERE ID=@ID", sqlCon);  
11. Cmnd.Parameters.AddWithValue("@ID", Id);  
12. object result = Cmnd.ExecuteScalar();  
13. if (result != null)  
14. {  
15. string name = result.ToString();  
16. }  
17. sqlCon.Close();  
18. }  
19. return "";  
20. }  
21.   
22. }