Managed Code, Server-Side Cursors, and Result Sets





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.

Always bear in mind the usual caveat that applies to using cursors. They are resource intensive and are certainly not the most efficient technique for client-side data access unless you need to hit only a few rows. The Result Sets implementation in ADO.NET 2.0 uses system stored procedures within SQL Server that access data on a row-by-row basis, which means repeated requests are made to the database as you navigate the rowset.

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:

  • To implement the new classes and extensions as a thin layer over the top of the existing infrastructure so as to expose a server-side cursor, but to maintain the simplicity of use found in other data access classes in the Framework.

  • To avoid caching any rows on the client and instead use the concept of a "current row." Only one row at a time can be processed.

  • To allow rows to be read in any order and to skip to specific rows, using only the cursor positioning features built into SQL Server.

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 Methods of the SqlCommand Class to Support Result Sets

Method

Description

ExecuteResultSet

Returns a reference to a SqlResultSet instance through which the data can be accessed and updated. There are two overloads of this method:

results-set = command.ExecuteResultSet(options) Accepts as the single parameter a value from the ResultSetOptions enumeration, which specifies the type of results set to create.

results-set = command.ExecuteResultSet (options, cursor-name)

As above, plus accepts a String value that is used as the name for the cursor.

ExecuteRow

Returns a SqlRecord instance containing the single row returned by the command query.

record = command.ExecuteRow()

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.

The ResultSetOptions Enumeration

Member

Description

Sensitive

The SqlResultSet contents are sensitive to changes made to the source data and will change in line with changes made to the rows in the database by other applications. This is the default.

Insensitive

The SqlResultSet contents are not affected by changes made to the source data in the database by other applications.

Scrollable

Backward scrolling and relative and absolute positioning within the SqlResultSet are permitted.

Updatable

Update, insert, and delete operations are permitted against the SqlResultSet.

None

None of the above options are applied.

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 Properties of the SqlResultSet Class

Property

Description

Item(index)

Provides access to the value of a column in the current row as its native data type, using the zero-based index of the column within the row. The column value can be read and can be updated if the SqlResultSet is updatable.

Item(name)

Provides access to the value of a column in the current row as its native data type, using the column name. The column value can be read and can be updated if the SqlResultSet is updatable.

Depth

Returns an Integer value that indicates the nesting level of the current row. The main (outermost) table is at depth 0. Read-only.

HasRows

Returns True if there are any rows in the SqlResultSet, or returns False if not. Read-only.

FieldCount

Returns an Integer value that indicates the number of columns in each row. Read-only.

HiddenFieldCount

Returns an Integer value that indicates the number of columns in the table that are not in the SELECT statement used to create the SqlResultSet. Read-only.

IsClosed

Returns True if the SqlResultSet is closed, or returns False if it is open. Read-only.

RecordsAffected

Returns an Integer value that indicates the number of rows affected by the last call to the Update method. Read-only.

Scrollable

Returns True if the SqlResultSet is scrollable, or returns False if not. Read-only.

Updatable

Returns True if the SqlResultSet is updatable, or returns False if not. Read-only.

Sensitivity

Returns a value from the ResultSetSensitivity enumeration that indicates whether rows change when the underlying data in the database changes. Read-only. The possible values are:

Insensitive: The SqlResultSet contents are not affected by changes made to the data source by other applications.

Sensitive: The SqlResultSet contents are sensitive to changes made to the data source by other applications and will change when it does.

Asensitive: The sensitivity of the SqlResultSet cannot be determined.

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 Column Value Access Methods of the SqlResultSet Class

Method

Description

Getxxx(index)

Returns the value of the column as a native .NET data type. The available methods are:

GetBoolean, GetByte, GetBytes, GetChar, GetChars,
graphics/ccc.gif GetDateTime, GetDecimal, GetDouble, GetFloat, Get
graphics/ccc.gif Guid, GetInt16, GetInt32, GetInt64, GetObjectRef,
graphics/ccc.gif GetString

The GetValue method returns the value as the data type defined for the column.

The GetValues method returns an array of Object types containing all the column values.

GetSqlxxx(index)

Returns the value of the column as the corresponding SqlType data type. The available methods are:

GetSqlBinary, GetSqlBoolean, GetSqlByte,
graphics/ccc.gif GetSqlBytes, GetSqlBytesRef, GetSqlChars,
graphics/ccc.gif GetSqlCharsRef, GetSqlDate, GetSqlDateTime,
graphics/ccc.gif GetSqlDecimal, GetSqlDouble, GetSqlGuid,
graphics/ccc.gif GetSqlInt16, GetSqlInt32, GetSqlInt64,
graphics/ccc.gif GetSqlMetaData, GetSqlMoney, GetSqlSingle,
graphics/ccc.gif GetSqlString, GetSqlTime, GetSqlUtcDateTime

The GetSqlValue method returns the value as the SqlType defined for the column.

The GetSqlValues method returns an array of Object types containing all the column values.

GetData(index)

Returns a DataReader instance that references the specified column.

GetSqlXmlReader (index)

Returns an xmlReader instance that references the specified column. The column must be defined as the new xml data type.

Setxxx (index, value)

Sets the value of the column (when the SqlResultSet is updatable) using a native .NET data type. The available methods are:

SetBoolean, SetByte, SetBytes, SetChar, SetChars,
graphics/ccc.gif SetDateTime, SetDecimal, SetDouble, SetFloat,
graphics/ccc.gif SetGuid, SetInt16, SetInt32, SetInt64,
graphics/ccc.gif SetObjectRef, SetString

The SetValue method takes an Object data type.

The SetValues method takes an array of Object types containing all the column values.

SetSqlxxx (index, value)

Sets the value of the column (when the SqlResultSet is updatable) using the corresponding SqlType data type. The available methods are:

SetSqlBinary, SetSqlBoolean, SetSqlByte,
graphics/ccc.gif SetSqlBytes, SetSqlBytesRef, SetSqlChars,
graphics/ccc.gif SetSqlCharsRef, SetSqlDate, SetSqlDateTime,
graphics/ccc.gif SetSqlDecimal, SetSqlDouble, SetSqlGuid,
graphics/ccc.gif SetSqlInt16, SetSqlInt32, SetSqlInt64, SetSqlMoney
graphics/ccc.gif, SetSqlSingle, SetSqlString, SetSqlTime,
graphics/ccc.gif SetSqlUtcDateTime

SetSqlXmlReader (index, reader)

Takes an xmlReader reference and sets the value of the column to the contents of the reader. The column must be defined as the new xml data type.

SetDefault(index)

Sets the column to the default value if one is defined for this column.

IsSetAsDefault(index)

Returns True if the column value is the same as the default for the column if one is defined for this column. In all other cases, returns False.

IsDBNull(index)

Returns True if the column contains null, or returns False otherwise.

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 Column Property Access Methods of the SqlResultSet Class

Method

Description

GetDataTypeName(index)

Returns the data type name of the specified column as a String.

GetFieldType(index)

Returns the data type of the specified column as a Type instance.

GetName(index)

Returns the name of the specified column as a String.

GetOrdinal(name)

Returns the zero-based Integer index of the column with the specified name.

GetSchemaTable()

Returns a reference to the DataTable that contains metadata about the columns in the SqlResultSet. Unlike a DataReader, it returns information only on the columns included in the SELECT statement that was used to create the SqlResultSet. This includes the data type, name, unaliased base name, and ordinal position. It does not include key or constraint information.

The Navigation Methods of the SqlResultSet Class

Method

Description

Read()

Reads the next row in the SqlResultSet. Returns True on success, or returns False if there is no row to read. This is the only navigation method that can be used if the SqlResultSet is not opened with the Scrollable option specified.

ReadAbsolute(index)

Reads the row at the specified zero-based index within the SqlResultSet. Returns True on success, or returns False if there is no row at this position.

ReadFirst()

Reads the first row in the SqlResultSet. Returns True on success, or returns False if there are no rows in the SqlResultSet.

ReadLast()

Reads the last row in the SqlResultSet. Returns True on success, or returns False if there are no rows in the SqlResultSet.

ReadPrevious()

Reads the previous row in the SqlResultSet. Returns True on success, or returns False if there is no previous row.

ReadRelative(skip)

Skips the specified number of rows and reads the next one. Returns True on success, or returns False if there is no row at this position. The skip value can be positive or negative.

NextResult()

Moves to the next rowset in the SqlResultSet. Returns True on success, or returns False if there is no following rowset.

Close()

Closes the SqlResultSet.

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 Row Manipulation Methods of the SqlResultSet Class

Method

Description

CreateRecord()

Returns an instance of a SqlUpdatableRecord that matches the current rowset schema in the SqlResultSet. No return value.

Insert(sql-record)

Inserts the specified SqlUpdatableRecord instance at the current position in the SqlResultSet. No return value.

Delete()

Deletes the row at the current position from the SqlResultSet. No return value.

Update()

Pushes the values in the current row in the SqlResultSet into the database. No return value.

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.

Creating and Displaying Details of a SqlResultSet Instance
' 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.

Navigating through a SqlResultSet
...
' 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.

1. Simple use of the SqlResultSet class

graphics/04fig01.gif

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.

Creating the SqlResultSet and Checking for Rows
' 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).

Retrieving a New Row in a SqlResultSet
...
' 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.

Inserting a New Row into a SqlResultSet
...
' 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()
...

Remember that the SqlResultSet doesn't actually hold a copy of the rows. It uses stored procedures to access the table within the database each time. So the row will appear in the table in the location appropriate to the value of its primary key (or the sorting order specified by any table index).

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.

Reading the New Row Values
...

' 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 &= "&nbsp; " & 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.

2. Inserting a row into a SqlResultSet

graphics/04fig02.gif


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows