From the author of

From the author of

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