Data Operations That Do Not Return Rows

Priya Dhawan
Microsoft Developer Network

Revised February 2002

Summary: This article covers data operations that do not return rows using ADO .NET. (4 printed pages)

Download Bdadotnet_beta2.msi.

Contents

Introduction
Execute/Write
   Using the ADO .NET Command Object
Conclusion

Introduction

Many data operations do not return data other than an indication of success or failure and the number of rows affected. This article discusses how to use ADO .NET to execute these operations with SQL and ADO managed providers.

Execute/Write

Using the ADO .NET Command Object

The ADO .NET Command object provides the ExecuteNonQuery method for executing queries that do not return rows. Although it does not return rows, any output parameters or return values mapped to parameters of the Command object get populated with data.

The ExecuteNonQuery method returns the number of rows affected by Insert, Update, and Delete operations. For all other queries –1 is returned.

Error handling

When a query fails to execute—for example when an attempted action violates a database constraint—the managed provider throws an exception, which you can catch in your code.

The ADO .NET managed providers have a member class that handles exceptions. This exception class is created and thrown when an error is encountered.

With SQL expression as command text

In the following example, an SQL INSERT statement is executed using the ExecuteNonQuery method of the ADO .NET Command object. Structured error handling handles exceptions and closes the database connection regardless of the outcome:

Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim rowsAffected As Integer
Try 
' Create a new connection object
sqlConn = New SQLConnection(myConnString)
' Create a new command object
sqlCmd = New SQLCommand()

   ' Specify the SQL expression and connection
   With sqlCmd
      .CommandType = CommandType.Text
      .CommandText = "Insert Customers (Alias, CustomerName) _
         Values ('myAlias','myName')"
      . Connection = sqlConn
      End With
   ' Open the connection
   sqlConn.Open()
   ' Execute the command. This returns rows affected.
   rowsAffected = sqlCmd.ExecuteNonQuery()
Catch e As Exception
   ' Handle the exception 
   …
Finally
' Close connection regardless of outcome
   sqlConn.Close()
End Try

Note   See Example 1 in the BDAdotNetData1.vb sample code (see top of article for download).

With the Parameters collection

The Parameters collection of the Command object is used to define the arguments of parameterized stored procedures or parameter queries. Although you can specify a stored procedure and Input parameters as command text, using the Parameters collection simplifies getting the parameters packed into the query in the right places and in the right format. String parameters containing quote characters, for example, are automatically formatted.

The following code builds a command that executes a stored procedure named InsertCustomer:

Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim param As SQLParameter
Dim rowsAffected As Integer
Try
' Create a new connection object
sqlConn = New SQLConnection(myConnString)
' Create a new command object
sqlCmd = New SQLCommand()
   ' Specify the stored procedure and connection
   With sqlCmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "InsertCustomer"
      . Connection = sqlConn
   End With
   ' Define and add a parameter to the parameters collection
   param = sqlCmd.Parameters.Add(New _
   SQLParameter("@Alias", SQLDBType.NVarChar, 100))
   ' Set the parameter direction
   With param
      .Direction = ParameterDirection.Input
      ' Set the parameter value
      .Value = "myAliasEx2"
   End With
   ' Add remaining parameters
   …
   ' Open the connection
   sqlConn.Open()
   ' Execute the command
   rowsAffected = sqlCmd.ExecuteNonQuery()
Catch e As Exception
   ' Handle the exception 
   …
Finally
   ' Close connection regardless of outcome 
   sqlConn.Close()
End Try 

Note   See Example 2 in the BDAdotNetData1.vb sample code (see top of article for download).

Conclusion

ADO .NET facilitates data operations that do not return rows through the ExecuteNonQuery method of the Command object. When an indication of the number of rows affected is the only return data required this is the most efficient way to execute commands against the database.