(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

VB6 	Classic ADO's Behavior Can Be Confusing
Listing 1	VB6 and ADO expose the data in a resultset in three ways. These three Recordset.Open statements look almost identical, but their behavior-on both the client and in SQL Server-is radically different.

' identical connection string
Dim connstr as String
connstr = "provider=sqloledb;database=pubs;" & _
	"server=.;integrated security=sspi"

' identical command text
Dim cmdtext as String
cmdtext = "select * from authors"

Dim rs As New Recordset

' 1. Cursorless resultset (default)
rs.Open cmdtext, connstr

' 2. Server cursor (static cursor type)
rs.Open cmdtext, connstr, adOpenStatic

' 3. Client cursor (rows all on client)
' Uses cursorless resultset to populate
rs.CursorLocation = adUseClient
rs.Open cmdtext, connstr, adOpenStatic


Transact-SQL	Declare and Populate a Table.
Listing 2	You can see how SQL Server's cursorless mode resultset reads through a table by using a table that has a primary key and enough data to fill more than two buffers of data. This code creates such a table and populates it with 1,000 rows of data.

-- drop table, if it exists
DROP TABLE bigtable
GO

CREATE TABLE bigtable (
	id INT PRIMARY KEY, 
	name CHAR(8000))
GO

-- populate 1000 rows
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
	INSERT INTO bigtable VALUES(@i, 
	'vrvnreifnfaknnfd' + CAST(@i AS VARCHAR(5)))
	SET @i = @i + 1
END
GO

SELECT COUNT(*) FROM bigtable
GO


C#	Use ADO.NET's Cursorless Resultset
Listing 3	This program does a simple SELECT from a table with more data than two buffers can hold. It reads the rows from the database directly and can manipulate rows in the table after the second buffer's-worth while it reads the resultset. The client can also insert and delete rows at the end of the table, regardless of transaction isolation level.


// simple SELECT command

string s = 
	"server=.;integratedsecurity=sspi;database=pubs"
SqlConnection conn = new SqlConnection(s);
SqlCommand cmd = new SqlCommand(
  "select * from bigtable", conn);

try
{
	conn.Open();
	SqlDataReader rdr = cmd.ExecuteReader();

	int counter =0;
	// stop in this while loop 
	// after reading a few rows
	// login to Query Analyzer
	// and insert/change/delete from bigtable
	while (rdr.Read())
		counter++;

	Console.WriteLine(
		"number of rows = {0}", counter);
	rdr.Close();
}
catch (Exception e)
{
	Console.WriteLine(e.Message);
}
finally
{
	cmd.Dispose();
	conn.Dispose();
}

C#	You Can't Open Multiple Cursorless Resultsets
Listing 4	This program attempts to read two cursorless-mode resultsets at a time using two SqlDataReaders. The second SqlDataReader statement will fail, because you haven't closed the first one.

string s = 
	"server=.;integratedsecurity=sspi; "
	"database=pubs"
SqlConnection conn = new SqlConnection(s);
SqlCommand cmd1 = new SqlCommand(
	"select * from bigtable", conn);
// second Command, same Connection
SqlCommand cmd2 = new SqlCommand(
	"select * from authors", conn);

	conn.Open();
	SqlDataReader rdr1 = cmd1.ExecuteReader();

	// this statement will fail
	SqlDataReader rdr2 = cmd2.ExecuteReader();

