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



3 comments

santhosh kumar July 28, 2017 at 11:37 PM

The Information shared was very much useful My sincere thanks for sharing this post Please Continue to share this kind of post
Dot Net Training in Chennai

Logavani G July 31, 2017 at 3:39 AM

really nice blog has been shared by you. before i read this blog i didn't have any knowledge about this. but now i got some knowledge. so keep on sharing such kind of an interesting blogs.
dotnet training in chennai

abril joseph August 18, 2017 at 10:27 PM

Pearson Vue certifications are one of the most sought after certifications in the IT certification industry. They identify talented IT Professionals, who can plan, design, implement, and operate small to medium enterprise networks. They provide the opportunity to enhance your ability in the world of IT industry. Pearson Vue Exam Center in Bangalore |
Pearson Vue Exam Centers in Bangalore |

Post a Comment