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.

Also check out:

- Bind dropdownlist from enum in Asp.Net

- Common Language Runtime in C#.Net

- RangeValidator example 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();