How And Where To Use SQL Command ExecuteNonQuey

Introduction to SQL Command ExecuteNonQuery in SQL Server


Its a very simple and common thing if you are well known to ADO.NET.  But if you are new to ADO.NET then this will help you a lot.

There are mostly 3 types of return types you can expect from a stored procedure like;
  •  A set of rows ( DataSet/Datatable )
  •  A single value ( may be integer, string )
  •  No return value ( only number of rows affected  ie. row count)
So, My point is, its not required to hold the data a stored procedure returns in a DataSet/DataTable always.
ADO.NET provide 3 types of SQL Commands for achieving the above said purposes.

Where and How to Use SQL Command ExecuteNonQuery in ASP.NET(C#)


Here in this article we will see the use of the SQL Command ExecuteNonQuery.

When your stored procedure is used to update, insert or delete records in the database then its better to use ExecuteNonQuery. although it returns nothing, you can use a return parameter to return values.

While using ExecuteNonQuery  for Insert, Update and Delete statements, it returns an integer value that hold the number of rows affected by the procedure.

If your procedure successfully updated or  deleted or inserted some data in the database then it returns the number of rows affected by it. If nothing happens in the database or a rollback occurs or an exception occurs then it returns -1. 
 private static void CreateCommand(string queryString, string connectionString)
 {
     using (SqlConnection connection = new SqlConnection(
                connectionString))
     {
         SqlCommand command = new SqlCommand(queryString, connection);
         command.Connection.Open();
         command.ExecuteNonQuery();
     }
 }
Why to use  ExecuteNonQuery  instead of using ExecuteReader and ExecuteScalar.

ExecuteReader needs a DataSet or a List to hold the return values, so it is unnecessary to declare a DataSet for updating/inserting/deleting something in the database. So its best practice to use ExecuteNonQuery  instead that need no DataSet to instantiate.

ExecuteScalar holds a single value that is returned from the stored procedure. This can be used in case of ExecutenonQuery where you are passing some return value from database by a select statement in the same procedure.

** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...

No comments:

Post a Comment