- Statements that Select Data
- Statements that Do Not Select Data
- Other Methods for Selecting Data
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Statements that Do Not Select Data
Statements that do not return data are easier to handle than those that select records because they don't require as much formatting. Subroutine ExecuteNonQuery shown in Listing 3 executes a SQL statement that doesn't select records. It begins by creating an OleDbConnection object attached to an Access database. As before, you can easily modify the code to use a SQL Server database. After it has created the database connection, the function calls the connection object's Open method to open the database connection.
Next, ExecuteNonQuery creates an OleDbCommand object to execute the SQL statement. Again, you can easily change the code to use SQL Server instead of an Access database. Here, you would use a SqlCommand object instead of an OleDbCommand object.
The function calls the command object's ExecuteNonQuery method to execute the SQL statement. The ExecuteNonQuery method returns the number of rows affected by the statement, if that makes sense. For example, if the SQL statement is an INSERT statement that adds one record to a table, the ExecuteNonQuery method returns 1. If the statement is a DELETE statement that removes 100 records, ExecuteNonQuery returns 100. If the statement is a CREATE TABLE statement, it doesn't really affect rows directly, so the value ExecuteNonQuery returns isn't meaningful.
The ExecuteNonQuery function uses the result returned by the command object's ExecuteNonQuery method to build a success string and returns it.
Executing the SQL statement is where the program is most likely to fail, particularly because the user is entering the query string. If the SQL statement is malformed, uses a table that doesn't exist, or is otherwise invalid, the call to the command object's ExecuteNonQuery method will raise an error. To protect itself, the function calls this statement inside a Try Catch Finally block.
The Catch block traps all exceptions, and raises them again so the main program can take action.
The Finally block closes the database connection. This is very important! ADO .NET uses a connectionless database strategy with connection pooling. That means the program should keep database connections open for the shortest amount of time possible. When the program closes a connection, the computer actually saves the connection in a pool. If that program or another one needs the same connection again later, the system can quickly reuse the one in the pool instead of creating a new one.
On the other hand, if the program doesn't close the database connection, its resources are unavailable for reuse. In Listing 3, the Finally block ensures that the connection is closed whether the command object's ExecuteNonQuery method succeeds or fails. Either way, the function closes the connection.
But wait! If you go back and look at the ExecuteQuery function described in the previous section, you'll notice that it never explicitly creates, opens, or closes a database connection. In that case, the OleDbDataAdapter object implicitly opens, uses, and closes the connection automatically. When the code creates the adapter object, it uses a constructor that gives the adapter a database connect string so it knows how to connect to the database when necessary.
The adapter opens, uses, and closes the connection very quickly so the connection's resources are released to the pool promptly. This technique is also handy because you cannot forget to close the connection when you are done with it.
Listing 3—Function ExecuteNonQuery executes a SQL statement that does not select records
' Execute the non-query statement. Return a success
' or failure message.
Private Function ExecuteNonQuery(ByVal sql As String) As String
' Connect to the database.
' (To use SQL Server, use a SqlConnection object.)
Dim db_connection As New OleDbConnection(ConnectString())
db_connection.Open()
Try
' Create an OleDbCommand object using the
' SQL statement.
' (To use SQL Server, use a SqlCommand object.)
Dim oldeb_command As New OleDbCommand( _
sql, db_connection)
' Execute the command.
Dim rows_affected As Long
rows_affected = oldeb_command.ExecuteNonQuery()
' Return a success message.
If rows_affected <= 1 Then
Return "Ok." & vbCrLf
Else
Return "Ok. " & rows_affected & _
" rows affected" & vbCrLf
End If
Catch exc As Exception
' Reraise the exception.
Throw exc
Finally
' Close the database.
db_connection.Close()
End Try
End Function