The Command Object

for RuBoard

The Command Object

The Command object enables you to execute queries against your data source. However, in order to retrieve data, you must know the schema of your database as well as how to build a valid SQL query. In Hour 3, you learned how to build SQL queries to retrieve and modify data. You will now have the opportunity to apply that knowledge as you use the Command object.

The SqlCommand object must be used in conjunction with the SqlConnection object. If you attempt to attach a SqlCommand to an OleDbConnection , you will get an error from the compiler. The converse is also true: OleDbCommands cannot be used with the SqlConnection .

Associating Connection Objects with Command Objects

Before creating a Command object, you should create a Connection object. Remember that a Command object is useless without a Connection object to provide communication to the database. Recall from Hour 5 that to create an instance of the Connection object, you use the following code:

 Dim myConnection as new SqlConnection("  Connection string  ") 

or in C#:

 SqlConnection myConnection = new SqlConnection("  Connection string  "); 

Similarly, to create a new Command object, you can simply use the new keyword and pass no arguments:

 Dim myCommand as new SqlCommand() 

or in C#:

 SqlCommand myCommand = new SqlCommand(); 

However, there's one additional step if you use this method to create a new command. You must associate the newly created Command object with the Connection object. You can do this by using the Connection property of the Command object. Listing 6.1 shows how this is done.

Most of the examples in this hour use the Northwind database. Therefore, to follow along, simply provide a connection string to the Northwind database in your datasource, if it is present. If you are using SQL Server, your connection string will be similar to the ones used in the examples of this hour.

Listing 6.1 Instantiating the Command and Connection Objects

 Dim myConnection as new SqlConnection("Initial Catalog=Northwind;                                        Server=(local);UID=sa;PWD=") Dim myCommand as new SqlCommand() myCommand.Connection = myConnection myCommand.CommandText = "SELECT * FROM Employees" 

The last line of Listing 6.1 specifies the command text that contains a query to pass to the database. Note that this line only specifies the query; it does not execute the query. We're almost ready to query the database and retrieve some data! But first, you'll have an opportunity to optimize the code in Listing 6.1 to make your life easier.

Overloaded Constructors Save Time

Figure 6.1 shows the Microsoft Class Browser entry for the SqlCommand object. Notice the very first section entitled 'Constructors.' This section shows you the various ways you can instantiate the Command object. Notice that you can specify the command text and Connection object when you instantiate the new Command object, rather than doing it line by line later. The code in Listing 6.1 can be reduced to the code in Listing 6.2.

Figure 6.1. The .NET Framework Class Browser entry for the SqlCommand object.

Listing 6.2 Using Overloaded Constructors to Reduce Code Size

 Dim myConnection as New SqlConnection("Initial Catalog=Northwind;                                        Server=(local);UID=sa;PWD=") Dim myCommand as New SqlCommand("SELECT * FROM Employees", myConnection) 

for RuBoard