ExecuteNonQuery(), ExecuteReader() and ExecuteScalar() in Ado.Net


In this post we will discuss about ExecuteNonQuery(), ExecuteReader() and ExecuteScalar() methods used in executing commands in Ado.Net in Asp.Net.


ExecuteNonQuery():

ExecuteNonQuery() method used for UPDATE, INSERT, or DELETE statements. And it returns an integer value for the number of rows affected.

Example:

string select = “Delete from UserMaster where UserID >100”;

SqlConnection conn = new SqlConnection(“Your connection string”);

conn.Open();

SqlCommand cmd = new SqlCommand(select, conn);

int rowsReturned = cmd.ExecuteNonQuery();

lblResult.Text = “Number of rows deleted ” + rowsReturned;

conn.Close();

It will show the number of rows deleted in the above query.

ExecuteReader():

The ExecuteReader method executes the command and returns a typed data reader object. Then you can use that data reader object to iterate through the records returned.

Example:

string select = “SELECT * from UserMaster”;

SqlConnection conn = new SqlConnection(“Your connection string”);

conn.Open();

SqlCommand cmd = new SqlCommand(select, conn);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
//Get the values
}

ExecuteScalar():

When we try to return a single result from a sql statement, then we can use ExecuteScalar() method. ExecuteScalar() method returns an object and you can type cast according to your requirement.

Example:

string select = “SELECT COUNT(*) FROM Customers”;

SqlConnection conn = new SqlConnection(“Your connection string”);

conn.Open();

SqlCommand cmd = new SqlCommand(select, conn);

object o = cmd.ExecuteScalar();

int number = Convert.ToInt32(o);

ExecuteXmlReader():

This is provided only with SqlClient Provider. ExecuteXmlReader method executes a SQL statement and returns an XmlReader object to the caller.

SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause.

This clause can include one of three options:
FOR XML AUTO — Builds a tree based on the tables in the FROM clause.

FOR XML RAW — Maps result set rows to elements with columns mapped to attributes.

FOR XML EXPLICIT — Requires that you specify the shape of the XML tree to be returned.

Example:

string select = “SELECT * from UserMaster FOR XML AUTO”;

SqlConnection conn = new SqlConnection(“Your connection string”);

conn.Open();

SqlCommand cmd = new SqlCommand(select, conn);

XmlReader xr = cmd.ExecuteXmlReader();

xr.Read();

string data;
do
{
data = xr.ReadOuterXml();

if (!string.IsNullOrEmpty(data))

//You can write data.

} while (!string.IsNullOrEmpty(data));

conn.Close();

You can also check out various articles on

Working with enterprise library for data access in asp.net Part-3,

Difference between primary key and foreign key in SQL server



WCF interview questions and answers in C#.Net.

Check out Best Alternative to InfoPath -> Try Now

You May Also like the Following SharePoint Online Tutorials:

About Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

View all posts by Bijay Kumar →