March 2, 2011, 3:54 a.m.
posted by pitbull
Managed Code, Server-Side Cursors, and Result Sets
One of the new features of SQL Server "Yukon" is the ability to write stored procedures in managed code, using any of the supported .NET languages. This presents new opportunities for building more complex stored procedures that are not ideally suited to ordinary Transact SQL (T-SQL) programming techniques, as well as providing a level of portability for your code. For example, existing logic can more easily be moved into or out of the database as required.
Of particular importance is the fact that moving data manipulation code into the database can provide improved performance because the data does not have to be moved over the wire for processing—only the results are exposed by the database server. Of course, this approach may not directly match your own application design methodology, but it can be used judiciously in many scenarios to maximize performance.
The code you write in a stored procedure can use the same ADO.NET logic and programming techniques (the same classes and methods) as your data layer or business layer code. However, one technique that disappeared from ADO in the move to the .NET Framework (and has been requested by users) has been reintroduced primarily for use within the managed code stored procedure model. This is support for server-side cursors.
Using Server-Side Cursors to Access Data Rows Directly
ADO.NET version 1.0 pretty much did away with database cursors as far as developers were concerned. The data access classes DataSet, DataReader, and DataTable are fundamentally aimed at handling rowsets of data that are constructed using SQL statements or stored procedures. Data within the DataSet and DataTable can be accessed and modified directly by finding and selecting rows or by using the row index. In a DataReader, all you can do is read the rows sequentially as a forward-only "firehose" cursor-style rowset.
Microsoft moved away from database cursors and the ability to scroll through the rows directly (as you can with the ADO Recordset) on the grounds that it almost always introduces performance penalties. Modern disconnected applications should connect to the database, extract the rows they need, and then disconnect. Updates in a DataTable are flushed back into the database using the Update method, not through some server-side cursor that points to rows in the target table of the database.
However, to provide better support for code running inside the database, server-side cursors have resurfaced in ADO.NET 2.0 under the guise of the SqlResultSet class. This feature is enabled only for SQL Server at present, through new classes and methods in the SqlClient namespace. They also work client-side with all versions of SQL Server from 7.0 onward, as we demonstrate in the examples later in this chapter, but they are not generally recommended for use in this way because of the inherent inefficiency in this scenario.
Server-Side Cursors and Result Sets
ADO.NET 2.0 adds a new feature that allows developers to work with data in a SQL Server database in a similar way to an ADO.NET Recordset object, using a cursor to access specific rows and read or update the data in these rows. Using the classes from the SqlClient namespace, you can open, scroll through, and update rows in a database table.
The technique makes use of the new concept of a result set and is implemented through extensions to the SqlCommand and the new SqlResultSet and SqlRecord classes. The general goals of the technology are:
As you'll see here, the Result Sets feature is an exciting new addition to ADO.NET that enables some of the techniques you can use with a Recordset to be accomplished in managed code against SQL Server.
The Classes and Methods That Support Result Sets
The interface changes required to the existing ADO.NET classes to support result sets are minimal, with two new methods added to the SqlCommand class to create a result set and an individual Record, and a couple of new classes to interface with the content of the result set.
The New SqlCommand Methods
The two new SqlCommand methods are listed in Figure.
The ResultSetOptions Enumeration of the ExecuteResultSet Method
The ExecuteResultSet method takes a value from the ResultSet Options enumeration to define the options that will be available for the result set. The members of the enumeration are shown in Figure on the next page.
The SqlResultSet Class
The ExecuteResultSet method of a Command object returns a reference to a SqlResultSet instance that is used to manipulate the data rows specified by the Command. One common way that humans describe something new is by comparing it to things that are already familiar. We can take this same approach to understanding broadly what a SqlResultSet looks like and what it does.
In some respects the SqlResultSet looks like the existing ADO.NET DataReader class because it exposes the current row as a collection of columns so that you can access and update values in that row. It also exposes a series of Getxxx methods (such as GetDouble, GetString, and GetValues) that return individual column values as specific data types or as an array of values. And the SqlResultSet provides information about the current row with the same methods as the DataReader, such as GetName, GetOrdinal, IsDbNull, and so on. Finally, it also exposes navigation methods, such as Read and NextResult.
However, the SqlResultSet also looks somewhat like a "classic" ADO Recordset object, in that it contains methods and properties that allow you to find out about the complete rowset, navigate from row to row (including backward if you specify this when you create it), and insert and delete rows. And, possibly one of the most important aspects in the SqlResultSet, it exposes the Update method so that you can update values in the source database table.
Other new features do not mirror existing classes or techniques. There is a series of methods that extract column values as their native SQL Server data type (such as GetSqlByte, GetSqlChars, GetSqlMoney, Get SqlValues, and so on). Plus, there are methods to set the values of columns using .NET or SQL Server data types (such as SetDouble, SetString, Set Values, SetSqlByte, SetSqlChars, and SetSqlValues).
SQL Server "Yukon" supports new "types" of columns, which can be user-defined data types (such as collections) or XML documents. To support this, you can now access columns through a SqlResultSet by using a Reader instance. The GetData method returns a DataReader that allows you to stream the value of a column to another object or (for example) use it with data binding. The GetSqlXmlReader method returns the column value as XML elements via an XmlReader, and you can even set the column value using an XmlReader with the SetSqlXmlReader method.
The SqlResultSet Properties
The SqlResultSet class exposes several properties that are the same as the DataReader class, such as Depth, FieldCount, IsClosed, and so on. However, it also has properties that are specific to the requirements of the SqlResultSet. The full list of properties is shown in Figure.
The SqlResultSet Column Value Access Methods
The SqlResultSet class exposes a large number of methods. To make it easier to grasp these and to understand what they do, we have divided them into several separate tables.
The methods that are used to access column values are shown in Figure. Each method accepts an Integer value that is the zero-based index of the column within the row. The exceptions are the GetValues, SetValues, GetSqlValues, and SetSqlValues methods, which use an array of the Object type to get or set all the columns in the row.
The SqlResultSet Methods for Getting Column Properties
Several methods that expose information about a column in the DataReader are implemented in the SqlResultSet class (see Figure).
The SqlResultSet Navigation Methods
The SqlResultSet allows navigation forward, just like a DataReader, using the Read method. And, if the Scrollable option was specified when it was created, it can also implement direct access to a specific row and support navigation backward through the rows. The methods that implement navigation are shown in Figure.
The SqlResultSet Row Manipulation Methods
Provided that the SqlResultSet instance was created with the Updatable option set, you can insert rows into and delete rows from the results it references. You can also push changes to the current row back into the database (see Figure on the next page).
The SqlRecord and SqlUpdatableRecord Classes
A SqlResultSet instance can contain either instances of the SqlRecord class or instances of the SqlUpdatableRecord class (but not both in the same SqlResultSet). The SqlRecord class provides a lighter-weight object that is useful when you only want to read the data in the rows and expose it as data types from the SqlTypes namespace. The SqlUpdatableRecord class is the generic class used when you want to be able to update the rows and access the data using native .NET data types.
The interface exposed by the SqlRecord class is relatively small. There are four properties—FieldCount, HiddenFieldCount, Item(name), and Item(index)—as described earlier in Figure for the SqlResultSet class. And the only methods it exposes are the GetSqlxxx methods listed earlier in Figure for the SqlResultSet.
The SqlUpdatableRecord has a more complete interface. As well as the same four properties as the SqlRecord class, it exposes the same Updatable property as the SqlResultSet class. It also exposes a full set of methods for accessing and changing column values, which includes all the Getxxx, Setxxx, GetSqlxxx, and SetSqlxxx variations listed for the SqlResultSet in Figure.
Using the SqlResultSet Class
To demonstrate how the Result Sets feature can be used, this section contains a couple of examples. We've used ASP.NET code here so that you can more easily see what's going on. Just bear in mind the caveat about the general use of result sets and server-side cursors and their adverse affect on performance when used outside of stored procedures.
Navigating through a SqlResultSet
The first example (Listing 4.12) creates a SqlResultSet that is both scrollable and updatable and then displays the values of its properties.
' get connection string and create connection Dim sConnectString As String = "your-connection-string" Dim oConn As New SqlConnection(sConnectString) 'create Command using SQL statement Dim sQueryText As String = "SELECT * FROM [Order Details]" Dim oCmd As New SqlCommand(sQueryText, oConn) ' open the connection oConn.Open() ' declare a SqlResultSet variable and create an updatable ' and scrollable SqlResultSet Dim oRS As SqlResultSet oRS = oCmd.ExecuteResultSet(ResultSetOptions.Scrollable _ Or ResultSetOptions.Updatable) ' display some properties lblProperties.Text = "Depth:" & oRs.Depth.ToString() & "<br />" _ & "HasRows:" & oRs.HasRows.ToString() & "<br />" _ & "FieldCount:" & oRs. FieldCount.ToString() & "<br />" _ & "HiddenFieldCount:" & oRs.HiddenFieldCount.ToString() & "<br />" _ & "IsClosed:" & oRs.IsClosed.ToString() & "<br />" _ & "Scrollable:" & oRs.Scrollable.ToString() & "<br />" _ & "Updatable:" & oRs.Updatable.ToString() & "<br />" _ & "Sensitivity:" & oRs.Sensitivity.ToString() & "<br />" ...
To demonstrate navigation through the SqlResultSet, the code next uses the ReadAbsolute method to go to the row at index 23, displays the values of the columns in that row, and then deletes the row (see Listing 4.13). An error message is displayed if the ReadAbsolute method cannot move to the specified row.
... ' move to the row at index 23 If oRS.ReadAbsolute(23) Then lblResult.Text = "ReadAbsolute(23) succeeded<br />" ' iterate through the columns displaying the values For iCol As Integer = 0 To oRS.FieldCount - 1 lblResult.Text &= "Column: " & oRS.GetName(iCol) _ & " = " & oRS.GetValue(iCol) & "<br />" Next ' delete this row oRS.Delete() lblResult.Text &= "Deleted the row at index 23" Else lblResult.Text &= "No row at index 23" End If ' remember to close the SqlResultSet after use oRS.Close() oConn.Close()
At the end of the process, the SqlResultSet and connection are closed. The screenshot in Figure shows the results.
Inserting a New Row
Like the previous example, the example in Listings 4.14 through 4.17 is not a real-world scenario and is not a recommended approach for working with data outside of a stored procedure. However, it does neatly demonstrate some of the other techniques available for working with the SqlResultSet and SqlUpdatableRecord classes and makes it easy to see what's going on.
The code starts in Listing 4.14 by creating a SqlResultSet in the same way as the previous example, then checks to see whether there are any rows in it by querying the HasRows property. Then it uses the ReadRelative method to skip forward five rows and display the value of the CompanyName column in that row.
' create and open connection and create command as before but ' this time using the Customer table from Northwind database '... ' create an updatable and scrollable SqlResultSet Dim oRS As SqlResultSet oRS = oCmd.ExecuteResultSet(ResultSetOptions.Updatable _ Or ResultSetOptions.Scrollable) If Not oRS.HasRows Then lblResult.Text = "Result Set is empty" Exit Sub End If ' skip forward five rows and display CompanyName column value If oRs.ReadRelative(5) Then lblResult.Text = " Executed ReadRelative(5) to " & oRS.GetName(1) _ & " = " & oRS.GetValue(1) & "<br />" Else lblResult.Text &= "Cannot move relative +5 rows" End If ...
Now the code declares an Object array to hold the values from a row, dimensioning it using the FieldCount property minus 1, and then moves to the last row in the SqlResultSet. After displaying the CompanyName column value, it collects all the values into the array using the GetValues method (see Listing 4.15).
... ' declare array to hold row values Dim aVals(oRS.FieldCount - 1) As Object ' go to last row If oRS.ReadLast() Then lblResult.Text &= "Executed ReadLast() to " & oRS.GetName(1) _ & " = " & oRS.GetValue(1) & "<br />" ' get all values into an array oRS.GetValues(aVals) Else lblResult.Text &= "Cannot move to last row " End If ...
Next, in Listing 4.16, a new SqlUpdatableRecord is created and filled with values using the same Object array. However, the table uses the first column as a primary key, so its value must be changed before the row can be inserted into the table. The value is set to ZZZZZ, using the SetString method, to ensure it appears at the end of the table and can therefore easily be located again. Then the row is inserted into the SqlResultSet and pushed back into the database using the Update method.
... ' create a new row and fill it with saved values Dim oRR As SqlUpdatableRecord = oRS.CreateRecord() oRR.SetValues(aVals) ' have to use different value for Primary Key column ' make it last row oRR.SetString(0, "ZZZZZ") ' insert new row into SqlResultSet oRS.Insert(oRR) ' push update into database oRS.Update() ...
Reading the Row Values
To prove that it worked, the code now moves to the last row again (the one just inserted) and gets the values in the database using the GetValues method (Listing 4.17). After displaying them, it deletes the new row so that the code can be executed again without causing a duplicate primary key error.
... ' go to last row (the one just inserted) oRS.ReadLast() ' display values just inserted oRS.GetValues(aVals) lblResult.Text &= "Inserted new row - values are:<br />" For Each oVal As Object In aVals lblResult.Text &= " " & oVal & "<br />" Next ' then delete this row so code can run again oRS.Delete() lblResult.Text &= "Deleted the new row" ' remember to close the SqlResultSet after use oRS.Close()
The result is shown in the screenshot in Figure. You can see the company names from the rows at index 5 and at the end of the table. You can also see the values of the new row that was inserted and then deleted.