Data Operations That Do Not Return Rows
Microsoft Developer Network
Revised February 2002
Summary: This article covers data operations that do not return rows using ADO .NET. (4 printed pages)
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.
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.
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).
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.