Use ExecuteNonQuery for Commands That Do Not Return Data

If you want to run commands that do not retrieve data, use the ExecuteNonQuery method. For example, you would use ExecuteNonQuery for the following types of commands:

  • Data Definition Language commands such as CREATE and ALTER
  • Data Modification Language commands such as INSERT, UPDATE, and DELETE
  • Data Control Language commands such as GRANT and REVOKE.

The following code fragment shows an update to the customer table that uses ExecuteNonQuery.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(
"UPDATE Customer SET Freight = 45.44 WHERE CustomerID = 10248", conn);
cmd.ExecuteNonQuery();

Use ExecuteScalar to Return Single Values

If you want to retrieve a single value from your query by using a function such as COUNT(*) or SUM(Price), you can use a stored procedure output parameter, and then use the Command.ExecuteNonQuery method. This eliminates the overhead that is associated with creating a result set.
The following stored procedure returns the number of rows in a Customers table.
CREATE PROCEDURE GetNumberOfCustomers(
@CustomerCount int OUTPUT)
AS
SELECT @CustomerCount = COUNT(*)
FROM Customers

To call the stored procedure, use the following code.
static int GetCustomerCount()
{
int customerCount = 0;
SqlConnection conn = new SqlConnection("server=(local);" +
"Integrated Security=SSPI;database=northwind");
SqlCommand cmd = new SqlCommand("GetNumberOfCustomers", conn );
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramCustCount =
cmd.Parameters.Add("@CustomerCount", SqlDbType.Int );
paramCustCount.Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
customerCount = (int)paramCustCount.Value;
}
finally
{
if(null!=conn)
conn.Close();
}
return customerCount;
}

If you do not have control over the stored procedure, and if the stored procedure returns the number of rows as a return value, then you can use Command.ExecuteScalar as shown in the following code fragment. The ExecuteScalar method returns the value of the first column of the first row of the result set.
static int GetCustomerCountWithScalar()
{
int customerCount = 0;
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
SqlCommand cmd = new SqlCommand("GetCustomerCountWithScalar", conn );
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
customerCount = (int)cmd.ExecuteScalar();
}
finally
{
if(null!=conn)
conn.Close();
}
return customerCount;
}

The previous code fragment requires the following stored procedure.
CREATE PROCEDURE GetCustomerCountWithScalar
AS
SELECT COUNT(*) FROM Customers

 

Use CommandBehavior.SequentialAccess for Very Wide Rows or for Rows with BLOBs

Use the CommandBehavior.SequentialAccess enumeration for very wide rows or for rows that contain binary large object (BLOB) data. This permits you to return specific bytes from the retrieved row instead of returning the entire row. Returning the entire row may consume large amounts of memory because of the BLOB data.

When you use CommandBehavior.SequentialAccess, the BLOB data is retrieved only when you reference it. For example, you can call the GetBytes method. The GetBytes method permits you to control the precise number of bytes that are read. The following code fragment shows how to use CommandBehavior.SequentialAccess.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

Also, if you are performing optimistic locking against a table with very wide rows or against rows that contain BLOB data, use timestamps. Use timestamps instead of comparing all the fields in the table to the original versions. Using time stamps reduces the number of arguments by a value that is equal to n/2+1.

More Information

For a complete sample, see "Obtaining BLOB Values from a Database" in .NET Framework Developer's Guide on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp.

Use CommandType.StoredProcedure with SqlCommand

If you are using the SqlCommand object, use CommandType.StoredProcedure when you call stored procedures. Do not use CommandType.Text because it requires extra parsing. The following code fragment shows how to set the CommandType property to avoid extra parsing on the server.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("UpdateCustomerProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(...
Using the Prepare method does not yield a benefit if you are only going to run the statement one or two times. The next version of SQL Server will better leverage how plans are cached, so using it would not make a difference. You should only use the Prepare method for those statements that you run multiple times.

Consider Using Command.Prepare


If your application runs the same set of SQL queries multiple times, preparing those queries by using the Command.Prepare method may give you better performance. In ADO.NET, the SqlCommand.Prepare method calls the sp_prepare stored procedure for SQL Server 7. The SqlCommand.Prepare method calls sp_prepexec for SQL Server 2000 and later. SqlCommand.Prepare makes these calls instead of running a regular batch remote procedure call (RPC). The following code fragment shows how to use Command.Prepare.


cmd.CommandText =
"insert into Region (RegionID, RegionDescription) values (@id, @desc)";
cmd.Parameters.Add ( "@id", SqlDbType.Int, 4, "RegionID") ;
cmd.Parameters.Add ( "@desc", SqlDbType.NChar, 50, "RegionDescription") ;
cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = "North West";
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = 6;
cmd.Parameters[1].Value = "North East";
cmd.ExecuteNonQuery();



Using the Prepare method does not yield a benefit if you are only going to run the statement one or two times. The next version of SQL Server will better leverage how plans are cached, so using it would not make a difference. You should only use the Prepare method for those statements that you run multiple times.