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

InfoPath alternatives for form designing SharePoint

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 and WCF interview questions and answers in C#.Net.

Similar SharePoint 2013 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 →

Leave a Reply