Assigning Text to a Command in VB.NET

Maker Data Matrix 2d barcode in VB.NET Assigning Text to a Command

Every command has a property, CommandText, that holds the SQL to execute. You can assign to this property directly or specify it when constructing the command. Let s look at these alternatives.

To set the CommandText property, follow these steps: 1. Modify the try block with the following bold code: Try 'Open connection conn.Open() 'connect command to connection cmd.Connection = conn Console.WriteLine("Connnected command to this connection.") ' associate SQL with command cmd.CommandText = "select count(*)from employees" Console.WriteLine("Ready to execute SQL:" & _ cmd.CommandText) End Try 2. Run the code by pressing Ctrl+F5. You should see the result in Figure 11-3.

CommandText is just a string, so you can print it with Console.WriteLine() just like any other string. The SQL will return the number of employees in the Northwind Employees table when you eventually execute it.

You can set both of these properties when you create the command with yet another variation of its constructor, as shown here:

'create command (with both text and connection) Dim sql As String = "select count(*) from employees" Dim cmd As SqlCommand = New SqlCommand(sql, conn) This is equivalent to the previous code that assigns each property explicitly. This is the most commonly used variation of the SqlCommand constructor, and you ll use it for the rest of the chapter.

Commands aren t much use unless you can execute them, so let s look at that now. Commands have several different methods for executing SQL. The differences between these methods depend on the results you expect from the SQL. Queries return rows of data (result sets), but the INSERT, UPDATE, and DELETE statements don t. You determine which method to use by considering what you expect to be returned (see Table 11-1). Table 11-1. Command Execution Methods

The SQL you just used in the example should return one value, the number of employees. Looking at Table 11-1, you can see that you should use the ExecuteScalar method of SqlCommand to return this one result. Let s try it.

To use the ExecuteScalar method, follow these steps: 1. Add a new Visual Basic Console Application project named CommandScalar to your 11 solution. Rename Module1.vb to CommandScalar.vb. 2. Replace the code in CommandScalar.vb with the code in Listing 11-2. Listing 11-2. CommandScalar.vb Imports System Imports System.Data Imports System.Data.SqlClient Module CommandScalar Sub Main()

'create connection Dim conn As SqlConnection = New SqlConnection _ ("Data Source=.\sqlexpress;" & _ "Integrated Security=True;" & _ "database=northwind") 'create command (with both text and connection) Dim sql As String = "select count(*) from employees" Dim cmd As SqlCommand = New SqlCommand(sql, conn) Console.WriteLine("Command created and connected.") Try 'Open connection conn.Open() 'execute query Console.WriteLine("Number of Employees is {0}", _ cmd.ExecuteScalar()) Catch ex As SqlException Console.WriteLine(ex) Finally ' Close connection conn.Close() Console.WriteLine("Connection closed.") End Try End Sub End Module 3. Make CommandScalar the startup project, and then run it by pressing Ctrl+F5. You should see the results in Figure 11-4.

All you do is add a call to ExecuteScalar() within a call to WriteLine(): 'execute query Console.WriteLine("Number of Employees is {0}", _ cmd.ExecuteScalar()) ExecuteScalar() takes the CommandText property and sends it to the database using the command s Connection property. It returns the result (9) as a single object, which you display with Console.WriteLine(). This is pretty simple to follow, but it s worth noting this is simpler than usual because Console.WriteLine() takes any kind of object as its input. In fact, ExecuteScalar() s return type is object, the base class of all types in the .NET Framework, which makes perfect sense when you remember that a database can hold any type of data. So, if you want to assign the returned object to a variable of a specific type (Integer, for example), you must cast the object to the specific type. If the types aren t compatible, the system will generate a runtime error that indicates an invalid cast. The following is an example that demonstrates this idea. In it, you store the result from ExecuteScalar() in the variable count, casting it to the specific type Integer: Dim count As Integer = cmd.ExecuteScalar() Console.WriteLine("Number of Employees is: {0}", count) If you re sure the type of the result will always be an Integer (a safe bet with COUNT(*)), the previous code is safe. However, if you left the cast to Integer in place and changed the CommandText of the command to the following: select firstname from employees where lastname = 'Davolio' ExecuteScalar() would return the string Nancy instead of an integer, and you d get this exception: Unhandled Exception: System.InvalidCastException: Specified cast is not valid. because you can t cast a string to an Integer. Another problem may occur if a query actually returns multiple rows where you thought it would return only one; for example, what if there were multiple employees with the last name Davolio In this case, ExecuteScalar() just returns the first row of the result and ignores the rest. If you use ExecuteScalar(), make sure you not only expect but actually get a single value returned.