In this article we will learn about Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery. Please read all .NET post in this link.
ExecuteNonQuery
ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations.This ExecuteNonQuery method will be used only for INSERT,UPDATE and DELETE statements.
Example :
- Declare the Connection string in App.config in like below, if the app.config is not available then add the app.config like below and if you are using web application the add connection string in Web.config File.
Add the connection like below Format.
Create a table like below script in SQL Server
CREATE TABLE [dbo].[ContanctInfo]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [Address] [varchar](50) NULL, CONSTRAINT [PK_ContanctInfo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
INSERT
- Then add the ExecuteNon-Query method to add the data like below
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ADO.NETConsoleApp
{
class Program
{
static void Main(string[] args)
{
// Execute Non Query
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string FirstName = "Jayant";
string LastName = "Tripathy";
string Address = "New Delhi";
int rowsAffected = 0;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO ContanctInfo (FirstName, LastName,Address) VALUES (@FirstName,@LastName, @Address)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Address", Address);
con.Open();
rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ExecuteNonQuery no. od rows Affected is "+ rowsAffected + "");
Console.ReadLine();
}
}
}When run the application the Final output should like below
UPDATE
- The update statement should like this
static void Main(string[] args)
{
// Execute Non Query
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string FirstName = "Jayant";
string LastName = "Tripathy123";
string Address = "Noida";
int rowsAffected = 0;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("UPDATE ContanctInfo SET LastName =@LastName,Address=@Address WHERE FirstName=@FirstName)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Address", Address);
con.Open();
rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ExecuteNonQuery no. od rows updated is "+ rowsAffected + "");
Console.ReadLine();
}DELETE
static void Main(string[] args)
{
// Execute Non Query
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string FirstName = "Jayant";
string LastName = "Tripathy";
string Address = "New Delhi";
int rowsAffected = 0;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM ContanctInfo WHERE FirstName = @FirstName", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FirstName", FirstName);
con.Open();
rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ExecuteNonQuery no. od rows deleted is "+ rowsAffected + "");
Console.ReadLine();
}Note : ExecuteNonQuery will work too for to Fetch the records ( Select Query ) only difference is that it return no. of rows affected is -1.
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’s very fast to retrieve single values from database.
string FirstName = "Jayant";
string ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Address FROM ContanctInfo WHERE FirstName=@FirstName", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FirstName", FirstName);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string city = o.ToString();
}
con.Close();
}
}ExecuteReader
Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last.
static void Main(string[] args)
{
// Execute Non Query
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT FirstName, LastName,Address FROM ContanctInfo", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string FirstName = dr["FirstName"].ToString();
string LastName = dr["LastName"].ToString();
string Address = dr["Address"].ToString();
Console.WriteLine("First Name is : "+ FirstName + "");
Console.WriteLine("Last Name is :" + LastName + "");
Console.WriteLine("Address is :" + Address + "");
}
con.Close();
}
Console.ReadLine();
}
}The Final output should like of this for ExecuteReader is
Conclusion
So far in this article we will learnt about Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery.