I have a Data Access Layer that clients use to interact with the
database.  The layer takes in a SqlConnection and optionally a
SqlTransaction.  Clients are responsible for the upkeep of the
connection/transaction.

The layer allows clients to limit the # of records (maxsize)
returned.  Suppose we keep a table of Books containing 20000 book
records.

The implementation runs the query and processes the records until
there are no more records to process OR the maxsize is reached.  We
close our SqlDataReader and return the results.  I realize we can use
TOP in the actual sql itself to restrict the # of records but that has
performance and query complexity issues.

One drawback about calling SqlDataReader.Close is it tries to process
the remaining records even tho I'm done with the query and the results
(see the remarks section of:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close.aspx).

So I call Cancel on the SqlCommand object before closing it out.
However, things start breaking if I try to reuse the Transaction.  The
frustrating part is that it works fine most of the time.  I can
continue using the transaction after Cancel/Close as if nothing's
happened. However I'd occasionally get the following exception:
System.Data.SqlClient.SqlException: Operation cancelled by user.

This happens on an irregular basis.  It works the majority of the
time.  It's almost as if there's a timebomb tied to the Transaction
after the Cancel method of a related SqlCommand is called.


The following code is a distilled example of the problem (.Net
Framework 1.1, SQL Server 2000):

using System;
using System.Data;
using System.Data.SqlClient;

public class CancelTest
{
    public static void Main(string[] args)
    {
        int maxsize = 10;
        int runcount = 0;

        using(SqlConnection conn = new SqlConnection("Data
Source=dvdb01a;Integrated Security=SSPI;Initial
Catalog=OperationsL1;Connect Timeout=2"))
        {
            conn.Open();
            /// Run the main "meat" of the example 1000 times.
Eventually, we'll get...
            /// System.Data.SqlClient.SqlException: Operation
cancelled by user.
            for(int i=0; i<1000; i++)
            {
                /// The following works most of the time.  But
eventually, we'll get the SqlException.
                SqlTransaction trans = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand("Select * from
Books",conn,trans);
                SqlDataReader reader = cmd.ExecuteReader();

                int recordcount = 0;
                while(reader.Read() && (recordcount < maxsize))
                {
                    Console.Write("{0}\r",++recordcount);
                }
                Console.WriteLine("Select completed {0} times.", +
+runcount);
                cmd.Cancel();
                reader.Close();
                trans.Commit();
            }
        }
    }
}

I'm at my wit's end.  Any help/insight is much appreciated.

Thanks.
JT

Seems like you have the worst case problem. I guess you have two options
here:
- Call PSS
- use TOP statement
I guess the later is easier. BTW why are you opposed to using TOP
statements - you mentioned performance and complexity but I don't relly see
any of the two or at least it shouldn't be a huge problem.

Thanks for the response.

1. What's PSS?
2. Can you provide some insight on what SqlCommand.Cancel does?  Why
is it that my connection/transaction sometimes unusable, albeit
infrequently.
3. TOP is undesirable because of our given architecture.  There are
issues when used in conjunction with orderby and other more esoteric
issues.

thanks again.
JT

Show quoteHide quote

MS' Product Support Service.

Not sure what it does.

Well, just wrap entire your SQL command into SELECT TOP xx FROM (your select
here)

we may try PSS at this point.  TOP works fine for straightforward
queries.  issues arise when the queries get too complex (joins,
unions, nested selects, etc...).

JT

Show quoteHide quote

Hi JT,

I don't think so. Just wrap an entire query into SELECT TOP FROM (complex
query).

query question
autocomplete textbox like google
VS2003 versus VS2005 data adapters: Auto-generated update commands generate parameter errors?
How to cancel deleting a row in an event
Computed Columns