Now that you've established a connection to a database through a provider, you're ready to issue SQL commands. But how?
Use a Command object to issue SQL commands directly to your database through the provider connection.
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:
There are also asynchronous versions of these methods (except for ExecuteScalar()).
Other recipes in this chapter use additional features of command objects. For instance, Recipe 13.5 uses a command object to access a stored procedure.