Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Difference between ExecuteQuery And Execute NonQuery

Posted by Viral Sarvaiya on December 1, 2009

ExecuteReader expects to run a query command or a stored procedure that selects records. It expects to have one or more resultsets to return.
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
// process the resultset(s) here
cmd.Connection.Close();

You access the selected records using the SqlDataReader object and use the method Read to loop through them. You move to the next resultset using the NextResults method.

ExecuteNonQuery expects to run a command, or a stored procedure, that affects the state of the specified table. This means anything but a query command. You normally use this method to issue an INSERT, UPDATE, DELETE, CREATE, and SET statement.

ExecuteNonQuery returns only the number of rows affected by the command execution, or –1 should this information be unavailable. It doesn’t give you a chance to access any result set generated by the statement or the stored procedure. Actually, there’s really nothing to prevent you from using this method for a query command, but in this case you get neither the resultset nor the number of the affected rows.
cmd.Connection.Open();
nRecsAffected = cmd.ExecuteNonQuery();
cmd.Connection.Close();
// check the record(s) affected here

The number of affected rows is also made available through the RecordsAffected property of the SqlCommand object. This property equals –1 in case of errors or if a query command is executed.

ExecuteScalar expects to run a query command, or more likely a stored procedure, that returns data. However, this method is different from ExecuteReader in that it just makes available, as a scalar value, the first column on the first row of the selected resultset.
cmd.Connection.Open();
Object o = cmd.ExecuteScalar(); cmd.Connection.Close();
// work on the scalar here

The method returns the value as a boxed object. It’s then up to you to unbox or cast that value to the proper, expected type.

ExecuteScalar turns out to be particularly useful when you have statistical or aggregate operations to accomplish on a certain amount of data. In these and similar circumstances, there is just one value that you might want to return back to the caller. Because of its use cases, you normally use this method on more or less complex stored procedures rather than on single SQL statements.

ExecuteXmlReader builds up and returns an XmlReader object after a SELECT command that exploits XML features in SQL Server 2000 has been issued.

in short :

Execute NonQuery..
1.It will not return any data.
2.It is used with insert and update.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.

Execute Query..
1.Its for command objects.
2.It returns the value given by database through select statement.

About these ads

4 Responses to “Difference between ExecuteQuery And Execute NonQuery”

  1. Chandra Prakash Nagar said

    Excellent

  2. koti said

    very nice hint (in short)…good …keep on updating ur website

  3. Imam said

    nice1

  4. dreamU said

    thanks.. very helpful article

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 135 other followers

%d bloggers like this: