| If you read Chapter 20, "Database Basics," you already know that a SQL statement is a type of database command. By using SQL you can query a database, update records, and perform other activities.  For more on SQL, p.535
In this section, we'll describe how to execute SQL commands using ADO.NET's connection and command classes. Connecting to the Database To get or update data in a database, you have to first connect to the database. Connecting to a database is similar to logging in to your favorite Web site. In ADO.NET, you establish connections using the appropriate connection class. The Open method establishes the connection, and the Close method terminates the connection. While the connection is in the open state, you use other ADO.NET objects to do something to the database such as execute a query. The following lines of code show how to connect to a Microsoft SQL server with the SQLConnection object: Dim strInfo As String Dim cn As SqlConnection strInfo = "server=bsw2k;uid=hruser;pwd=elvis;database=BrianTest" cn = New SqlConnection() cn.ConnectionString = strInfo cn.Open() ' PERFORM OPERATIONS ON DATABASE HERE cn.Close() Even more important than the Open and Close methods is the connection string, shown in the third line of the previous code. The connection string contains whatever information is necessary to successfully establish a connection to your database, such as a user name and password. In the last chapter, we discussed connection strings in detail and provided several examples. Note Different types of databases require different connection strings. For example, when using the OleDbConnection object, you'll need to supply the name of an OLE DB provider or ODBC driver. However, because a SQLConnection only connects to one type of database, you just specify the name of the desired SQL server. One easy way to determine a valid connection string for the OleDbConnection object is to create the connection in the Server Explorer, then copy the connection string into your code.
For more on connection strings, p.575 As you can see, opening and closing a database connection is very simple. One important point is to make sure you close a database connection as soon as you are through with it. Each connection to a database represents a certain amount of system and network resources, which are limited. It is easy enough to remember to call the Close method in your code, but what happens if an exception occurs? As you may recall from Chapter 5, "Visual Basic Building Blocks," when an exception is thrown the normal flow of code is interrupted. In the previous code example, an exception during a database query operation might cause the call to the Close method to be skipped. For more on exceptions, p.113 Note Closed connections are placed in a "pool" of available connections for a period of time. If another connection request is made using the same connection string, ADO.NET can use the pooled connection, which is quicker than establishing a new one. For more information on connection pooling, see the help topic "Connection Pooling for the SQL Client .NET Provider."
Fortunately, Visual Basic's structured error handling makes it easy to ensure a connection is always closed: Dim strInfo As String Dim cn As SqlConnection strInfo = "server=bsw2k;uid=hruser;pwd=elvis;database=BrianTest" Try cn = New SqlConnection() cn.ConnectionString = strInfo cn.Open() ' PERFORM OPERATIONS ON DATABASE HERE Catch exc As SqlException ' HANDLE EXCEPTION HERE Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try In the preceding code, there are basically two operations during which an exception may occur: when establishing the connection or when performing operations on the database. As these statements are all enclosed in the same Try block, the connection might be open or closed after an exception occurs. Because attempting to close a connection that is already closed would throw another exception, we first need to check the State property and only call the Close method if necessary. Setting Up a Command Object Now that you know how to establish a connection, you can execute database commands over it. The SQLCommand and OleDbCommand command classes encapsulate the functionality of a SQL statement in ADO.NET. By setting properties of a command object, you determine the SQL statement you want to execute. Then, you simply associate it with a valid connection object and call a method to execute the command. Determining the Command Text The SQL statement itself is stored in the CommandText property, as shown in the following lines of code: Dim cmd AS SqlCommand cmd = New SqlCommand() cmd.CommandText = "SELECT LastName, FirstName FROM Person" Setting up the SQLCommand object in the previous example is very straightforward just set the CommandText property to the actual SQL statement. However, as you may recall from Chapter 20, SQL statements can also be compiled in the database as stored procedures for greater efficiency. Executing a stored procedure not only requires setting the CommandText property to the stored procedure name, but also setting the CommandType property appropriately: Dim cmd AS SqlCommand cmd = New SqlCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "spGetEmployeeBySSN" CommandType has three possible values, each of which influences ADO.NET's interpretation of the CommandText property: Text The default setting, used for any type of SQL statement other than a stored procedure, such as a SELECT or INSERT statement. StoredProcedure If you choose this option, ADO.NET expects the CommandText property to contain the name of the stored procedure you want to execute. TableDirect Equivalent to executing the SQL query SELECT * FROM tablename where the CommandText property contains the name of the table. This option is only available for the OleDbCommand object and, in the author's opinion, should be used rarely if at all.
Setting the Connection Property Before you can execute a command, you also need to assign the Connection property of the command to an active connection object, as in the following example: Dim cmd As New SqlCommand("SELECT * From Employee") Dim cn As New SqlConnection("server=bsw2k;uid=hruser;pwd=elvis;database=Testdb") cn.Open() cmd.Connection = cn Although the previous code example opens the connection before assigning cn to the cmd.Connection property, it is only necessary that the connection be open before the command is executed. Creating Command Parameters Most stored procedures(and even some SQL statements) have one or more parameters. A parameter is just a value that is substituted for a token in the SQL statement before it is executed. Parameters are especially useful in stored procedures, because the stored procedure works just like a VB function. For example, in Chapter 20, we define a stored procedure that requires an SSN parameter, spGetEmployeeBySSN. All the calling program needs to know is the name of the stored procedure and the parameter; the actual SQL query is stored in the database (and can easily be modified without recompiling your VB program). In ADO.NET, each stored procedure parameter is represented in the Parameters collection of a command object. Table 22.2 lists the important properties of a parameter object: Table 22.2. Setting Up a Command Parameter| Property | Description | Example |
|---|
| Name | Parameter name in procedure | @strSSN | | Type | The parameter's data type | String | | Length | Size of the data type | 11 | | Direction | Use of the parameter | Input | | Value | Value to use in the query | 111-22-3456 |
You add parameters by using the Add method of the Parameters collection in a command object, as shown in the following example: cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "spGetEmployeeBySSN" Dim pmTemp AS SqlParameter pmTemp = cmd.Parameters.Add(New SqlParameter("@strSSN", SqlDbType.Char, 11)) pmTemp.Direction = ParameterDirection.Input pmTemp.Value = "284-73-7769" In the code example, the Name, Type, and Length properties of pmTemp were set using an object constructor. The Direction property indicates the parameter is used for input to the stored procedure. The Value property contains the SSN value for which we want to search. Although we set a lot of properties in the previous code example, ADO.NET is actually smart enough to interpret most parameters with only the Name and Value properties. For example, the last four lines in the previous code example could be replaced with the following single line of code: cmd.Parameters.Add(New SqlParameter("@strSSN", "284-73-7769")) Each parameter object you set up in VB must match the name and data type of a corresponding parameter in the stored procedure. If you have a different number of parameters or an incorrect data type, an exception may occur. Note SQL Server stored procedures can have optional parameters, which are indicated by providing a default value for the parameter in the CREATE PROCEDURE statement. When setting up a SQLCommand object's Parameters collection, you can omit default parameters if desired.
The majority of stored procedure parameters are input parameters, but a parameter can also be used to return data to the calling program. These types of parameters are called output parameters, and are useful when you only want to return one or two pieces of data, rather than an entire record. The following stored procedure shows an example use of an output parameter: CREATE PROCEDURE spGetMaxAgeByState @strState char(2), @intResult int OUTPUT AS SELECT @intResult = Max(Age) FROM Person WHERE State = @strState The example stored procedure, spGetMaxAgeByState, uses an output parameter called @intResult to store the maximum age. Note the keyword OUTPUT, which indicates an output parameter. An alternative approach would be to just let the results of the SELECT query be returned as a record. However, returning an integer value requires less overhead than returning a record. The following two lines of code set up a Parameter object for the @intResult parameter: pmTemp = cmd.Parameters.Add(New SqlParameter("@intResult", sqldbtype.Int)) pmTemp.Direction = ParameterDirection.Output As you can see from the example, to specify an output parameter in an ADO.NET command, you set the Direction property to ParameterDirection.Output. After executing the command (described in the next section), simply examine the Value property of an output parameter to determine the resulting value: Dim intMaxAge As Integer intMaxAge = Convert.ToInt32(cmd.Parameters("@intResult").Value) Messagebox.Show("The maximum age in " & strStateAbbreviation & " is " & intMaxAge) Because the Value property stores a value of type Object, the sample code converts the parameter value into an integer so it can be stored in a variable of type Integer. Note One of the settings for a parameter's Direction property is ReturnValue, which can be used to retrieve the result of a RETURN statement from a stored procedure.
Choosing an Execute Method After you have set the properties of a SQLCommandor OleDbCommand, including any necessary parameters, you can execute the command by calling one of the command's execute methods, as listed here: ExecuteNonQuery Executes a SQL statement that does not return records; that is, a DELETE, UPDATE, or INSERT statement. Also can be used if your stored procedure only returns output via parameters. ExecuteReader Executes a SQL statement and returns a SqlDataReader object that contains any resulting records. ExecuteScalar Used when you need to return only a single value (column) of data from a SQL SELECT statement. ExecuteXMLReader Available only for the SQLCommand Similar to ExecuteReader, but returns an XMLReader.
The reason there are so many different types of execute methods is that each one is optimized for a particular type of SQL statement. Although the selection of an execute method is somewhat flexible, for efficiency's sake you should try to pick the method that most closely matches the data returned by the SQL statement. Note The CommandTimeout property can be used to specify a maximum time to execute in seconds, after which a timeout exception will be thrown.
Executing Non-Query SQL Statements Because a DELETE statement does not actually return rows of data, it would be wasteful to declare a variable to contain a result set. Instead, a more appropriate choice would be the ExecuteNonQuery method, shown in Listing 22.1. Listing 22.1 COMMANDEXAMPLES.ZIP Using the ExecuteNonQuery Method Private Function DeleteEmployee(ByVal strSSN As String,_ ByVal strDept As String) As Integer Dim intRowsDeleted As Integer Dim strDeleteSQL As String Dim cmd As New SqlCommand() Dim cn As New SqlConnection() 'BUILD THE SQL STRING strDeleteSQL = "DELETE FROM Employee WHERE SSN='" & strSSN & "'" strDeleteSQL &= " AND Dept = '" & strDept & "'" 'SET UP COMMAND AND CONNECTION cn.ConnectionString = "server=CSQL1;uid=hruser;pwd=elvis;database=DevHR" cn.Open() cmd.Connection = cn cmd.CommandText = strDeleteSQL 'EXECUTE COMMAND intRowsDeleted = cmd.ExecuteNonQuery() 'CLOSE CONNECTION, RETURN NUMBER OF ROWS cn.Close() Return intRowsDeleted End Function Listing 22.1 uses the ExecuteNonQueryMethod to delete a record from the Employee table. The return value of the ExecuteNonQuery method is the number of rows affected by the command. In the case of a DELETE statement, this value contains the number of rows deleted. Another example would be the spGetMaxAgeByState procedure defined in the last section. Because this procedure does not return records, but instead uses an output parameter, ExecuteNonQuery would be an appropriate choice to execute it. Using a Data Reader When you execute a SELECT query or stored procedure that returns records, you need to use an execute method that allows you to access those records. We'll start with an easy one: the ExecuteReader method. The ExecuteReader method returns a DataReader object, which can be loosely compared to the read-only, forward-only Recordset object in previous ADO versions. (One important difference, however, is that a DataReader cannot be used in a disconnected fashion.) The SQLDataReader and OleDbDataReader classes are designed to get records out of a database as quickly as possible to be processed sequentially. Therefore, navigation capability is limited to moving forward through the records. Listing 22.2 shows a typical use of a data reader with a while loop: Listing 22.2 COMMANDEXAMPLES.ZIP Using the SQLDataReader Dim rdrPeople As SqlDataReader Dim cn As New SqlConnection("server=CSQL1;uid=hruser;pwd=elvis;database=DevHR") Dim cmd As New SqlCommand("Select LastName, FirstName, Age From Person", cn) Dim strName As String cn.Open() rdrPeople = cmd.ExecuteReader() While rdrPeople.Read() 'BUILD STRING CONTAINING LAST AND FIRST NAME strName = rdrPeople.GetString(1).Trim & " " & rdrPeople.GetString(0).Trim 'DISPLAY NAME AND AGE IN A MESSAGE BOX Messagebox.Show("Hello, " & strName & ". You are " &_ rdrPeople.Item("Age").ToString & " years old!") End While cn.Close() The code in Listing 22.2 queries the Person table and returns a SQLDataReader object. Each call to the rdrPeople.Read method positions a pointer in the data reader to the next record. Therefore, if your query returns five records, the loop will execute five times. The Read method returns False after all records have been processed, exiting the While loop. The code in Listing 22.2 demonstrates a couple of ways to access column values in the current row of a data reader. One method is to use the Item property and supply the name or index of the column. Another is to use one of the SQLDataReader's many Get methods to return a column of information in a particular data type. In the example, we know that the LastName and FirstName fields contain string data, so we used the GetString method. Note The purpose of the Trim method in Listing 22.2 is to remove trailing spaces from the data field. When working with string comparisons to data fields, remember the length of a Char will field always be constant, while a VarChar field can have a varying number of characters.
Some other useful methods and properties of the data reader object are listed next: GetName method Returns the name of a data column. In the example in Listing 22.2, GetName(0) returns LastName. FieldCount property Returns the number of data columns. The example in Listing 22.2 has three columns. IsDBNull method Used to check for a null value in a database column.
In addition to working with multiple records in a set, the DataReader can also be used to retrieve multiple result sets in a sequential manner. One example of this would be a stored procedure that contains several SELECT queries. To access additional result sets in a DataReader, use the NextResult method. Retrieving Single Values Sometimes, you may want to just return a single value from a stored procedure, rather than rows of data. We have already covered how to do this using an output parameter. However, you can also retrieve a value that results from a SELECT query. One typical example is when you are using an identity column. An identity column is like an autonumber field in Microsoft Access it is a number generated automatically by the database. To understand when you need an identity column, consider our sample Person table, which uses the social security number as the primary key. If you were not able to obtain a person's SSN or another identifying number, you could create a primary key using the identity column. One example might be logins and passwords for a Web site. The following CREATE TABLE statement defines a table called WebUsers with an identity column: CREATE TABLE WebUsers ( UserNumber int IDENTITY (1, 1) NOT NULL PRIMARY KEY, UserID char(10) NOT NULL, Password char (10) NOT NULL, UserDescription varchar (50) NULL, AccessLevel tinyint NOT NULL ) ALTER TABLE WebUsers ADD CONSTRAINT UniqueUserID UNIQUE NONCLUSTERED (UserID) The UserNumber field in the WebUsers table is both a primary key and an identity column. When records are inserted into the table, the database assigns this field an integer starting at the number 1 and increments with each new record. You can then use this identity column as a foreign key in other tables. The advantage of having the database create the identity column rather than attempting to generate a unique number yourself is that the database handles concurrent user requests while keeping the number unique. Note Identity columns are great for numbers that are keys throughout your database. However, if you need just a temporary string that is unique, you can also store a global-unique identifier (GUID) in the database.
If you need to update several tables at the same time that you insert a record in the WebUsers table, you need to know what UserNumber was assigned. The following stored procedure, spWebUserInsert, adds a record to the table and then returns the value of the UserNumber field for that record: CREATE PROCEDURE spWebUserInsert @strUserID char(10), @strPassword char(10), @strDescription varchar(50), @intAccessLevel tinyint As INSERT INTO WebUsers VALUES (@strUserId, @strPassword,@strDescription,@intAccessLevel) SELECT @@IDENTITY The spWebUserInsert procedure first executes an INSERT statement using the parameter values, then selects the system variable @@IDENTITY, which contains the identity value most recently inserted. The spWebUserInsert stored procedure returns a record with one column containing the identity value. This, of course, could be retrieved using the ExecuteReader method. However, in order to avoid declaring a SQLDataReader object, you can use the SQLCommand object's ExecuteScalar method, which returns a single object variable. Listing 22.3 shows an example of using ExecuteScalar: Listing 22.3 COMMANDEXAMPLES.ZIP Using ExecuteScalar Private Function InsertWebUser(ByVal strUserId As String,_ ByVal strPassword As String, ByVal strName As String,_ ByVal intAccessLevel As Short) As Integer Dim cn As New SqlConnection("server=corpwww;uid=admin;pwd=xyz;database=security") Dim cmd As SqlCommand Dim intNewUserNumber As Integer cn.Open() cmd = New SqlCommand() cmd.Connection = cn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "spWebUserInsert" cmd.Parameters.Add(New SqlParameter("@strUserID", strUserId)) cmd.Parameters.Add(New SqlParameter("@strPassword", strPassword)) cmd.Parameters.Add(New SqlParameter("@strDescription", strName)) cmd.Parameters.Add(New SqlParameter("@intAccessLevel", intAccessLevel)) intNewUserNumber = Convert.ToInt32(cmd.ExecuteScalar()) cn.Close() Return intNewUserNumber End Function The InsertWebUser function in Listing 22.3 uses the ExecuteScalar method in conjunction with the stored procedure to retrieve the user number. The following lines of code show how this function might be called: intUserNumber = InsertWebUser("bsiler", "secret", "Brian Siler", 5) Call SetupUserColors(intUserNumber, "Default") Call SetupUserWeatherInfo(intUserNumber, strZipcode) This example includes additional function calls that make use of the new user number returned by the InsertWebUser function. Presumably in addition to the WebUsers table, the database contains tables for Web site preferences that use UserNumber as a key. Retrieving XML from SQL Server As part of Microsoft's ubiquitous XML initiative, support for XML processing is built into SQL Server 2000. By adding special clauses to your SQL statement, you can cause SQL Server to format the results of a query as XML: Select * from Person FOR XML AUTO, XMLDATA Notice the FOR XML AUTO clause in the previous SQL query, which tells SQL Server to return records in XML format. The XMLDATA argument lets SQL Server know you want to include field definitions in XML as well. If you use these options when executing the previous SQL query interactively, the output records are represented in XML and may look similar to this: <PERSON SSN="684-99-0012" LastName="Vandelay" FirstName="Art" Age="47" Address="123 Fourth Street" City="Paramus" State="NJ" Zipcode="12345"/> In order to make processing XML results easier, the SQLCommand class contains a method called ExecuteXMLReader, which can parse the XML as it is returned to your program. The XMLReader class returned by this method contains several methods that allow you to read XML attributes and values: cmd = New SqlCommand("Select * from Person FOR XML AUTO, XMLDATA", cn) Employees = cmd.ExecuteXmlReader() While Employees.Read() If Employees.NodeType = Xml.XmlNodeType.Element Then Employees.MoveToAttribute("LastName") If Employees.HasValue Then Debug.WriteLine("Current Employee=" & Employees.Value) End If End If End While The previous code sample writes all of the last names in the Person table in the output window. The XMLReader class is designed for forward-only processing of XML data. In a later chapter, we'll introduce the XMLDocument class, which can be used to represent an entire XML document in memory. For more on XML, p.667 To learn other means of accessing data as XML, see the help topic "Reading XML Data Using XMLReader." | 
