Sometimes it is useful to be able to execute commands while reading through results of a query without opening a new connection and without reading all the results into memory. The Multiple Active Result Set (MARS) feature in ADO .NET 2.0 is a technology which can help you achieve that. Unfortunately, MARS is not implemented for the in-process provider used for server-side programming. To work around this limitation, server-side cursors can be used. This sample demonstrates how to use server-side cursors to get around absence of MARS support for server side programming.

Note:
Using server side cursors is expensive in terms of server resources and can sometimes prevent the query optimizer in SQL Server from enhancing the performance of your queries. Therefore, you might consider rewriting your code to use JOINs whenever possible.

The API for this class is similar to a data reader except that you can move forward or backward throughout the result set, and other commands can be issued on the connection even while the result set is open.

This implementation is highly simplified to make the sample easy to understand. A more efficient implementation would fetch multiple rows to avoid a database turnaround per row fetched.

Using this class can have a significantly smaller memory footprint than filling a dataset with all the results of a query which is very important for server side programming.

Install directory:

drive:\Program Files\Microsoft SQL Server\90\Samples\

Engine\Programmability\CLR\ResultSet\

Scenario

Jane is a developer for Adventure Works Cycles. She has to write software that reads data and performs other actions before all the data is returned from her server-side query.

Languages

Transact-SQL, Visual C# and Visual Basic.

Features

The ResultSet sample uses the following features of SQL Server.

Application Area Features

Overall

CLR, server-side cursors, Transact-SQL 

Prerequisites

Before running this sample, make sure the following software is installed:

  • Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express Edition (SQL Server Express). You can obtain SQL Server Express free of charge from the SQL Server 2005 Express Edition Documentation and Samples Web site.

  • The AdventureWorks database, which is included with SQL Server 2005 from the SQL Server 2005 Express Edition Documentation and Samples Web site.

  • The SQL Server 2005 Database Engine samples. These samples are included with SQL Server 2005 and are also included with the Microsoft .NET Framework version 2.0 SDK 2.0.

  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework SDK.

Building the Sample

To build the ResultSet sample

  1. CD to the install directory and execute the following at a .NET Framework or Microsoft Visual Studio 2005 command prompt:

    sn -k keypair.snk

  2. Compile the sample by using Visual Studio 2005 and the provided Visual Studio solution, or by using , which is included in the .NET Framework SDK 2.0, by executing commands similar to the following at the command prompt:

    msbuild /nologo /verbosity:quiet /property:Configuration=Debug CS\ ResultSet.sln

  3. Make sure that the AdventureWorks database is installed.

  4. If you did not install the SQL Server engine samples in the default location, modify the path in the CREATE ASSEMBLY part of the script in Scripts\InstallCS.sql to refer to the location where the samples were installed.

  5. If you are not an administrator for the SQL Server instance you are using, you must have an administrator grant you CreateAssembly permission to complete the installation.

  6. Open the scripts\installCS.sql or scripts\installVB.sql file (depending on whether you compiled the csprcs project or the Visual Basic project) in and execute the script that is contained in the file or execute a command similar to the following in a command prompt window:

    sqlcmd -E -I -i Scripts\InstallCS.sql

Running the Sample

To run the ResultSet sample

  1. Open the scripts\test.sql file in SQL Server Management Studio and execute the script contained in the file, or execute the following command in a command prompt window:

    sqlcmd -E -I -i Scripts\test.sql

Removing the Sample

To remove the ResultSet sample

  1. Open the scripts\cleanup.sql file in SQL Server Management Studio and execute the script contained in the file, or execute the following command in a command prompt window:

    sqlcmd -E -I -i Scripts\cleanup.sql

Comments

The CLR for SQL Server 2005 or SQL Server Express must be enabled for this sample to work correctly.

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications and assemblies should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.