Issuing SQL Commands

Problem

Now that you've established a connection to a database through a provider, you're ready to issue SQL commands. But how?

Solution
Use a Command object to issue SQL commands directly to your database through the provider connection.

Discussion
The following code updates a SQL Server table named Table1, changing every Column2 field to 25 whenever Column1 has a value of 0:

' ----- Connect to the database.
Dim connectionString As String = _
"Data Source=MySystem\SQLEXPRESS;" & _
"Initial Catalog=MyDatabase;Integrated Security=true"
Dim theDatabase As New SqlClient.SqlConnection(connectionString)
theDatabase.Open()

' ----- Prepare the SQL statement for use.
Dim sqlStatement As New SqlClient.SqlCommand( _
"UPDATE Table1 SET Column2 = 25 WHERE Column1 = 0", _
theDatabase)
sqlStatement.ExecuteNonQuery()

' ----- Clean up.
theDatabase.Close()
theDatabase.Dispose()

Just like connections, command objects are provider-specific. When using the SQL Server provider, the System.Data.SqlClient. SqlCommand class wraps a SQL statement string and prepares it for use by the database. You must supply a valid SQL statement that is recognizable by the database.







The SQL statement you provide to the command can include the standard Data Manipulation Language (DML) SQL statements (SELECT, INSERT, UPDATE, DELETE), or any of the platform-specific Data Definition Language (DDL) statements (such as CREATE TABLE). Do not include a terminating semicolon in the statement.

Instead of including the SQL statement and connection object in the command's constructor, you can assign these values to the command object's CommandText and Connection properties, respectively.

The command object includes several methods that send the command to the database for processing:

ExecuteReader()
Issues a command, and returns the data results in the form of a DataReader object. See Recipe 13.3 for additional information on data readers.

ExecuteNonQuery()
Issues a command, expecting no results. This method is generally used for INSERT, UPDATE, and DELETE commands.

ExecuteScalar()
Issues a command, expecting a single row and column of data in response. The data is returned as a generic System.Object instance, which you can convert to the appropriate data type.

ExecuteXmlReader()
Issues a command, and returns the data results as an XmlReader object.

There are also asynchronous versions of these methods (except for ExecuteScalar()).