Powered by Blogger.

Sunday, February 23, 2014

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



0 comments

Post a Comment