SqlCommand.Cancel and Transactions
- From: "draggin dragon" <james.tsao@xxxxxxxxx>
- Date: 27 Feb 2007 18:50:13 -0800
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
.
- Prev by Date: Re: Reference for Columns returned by GetSchema for Sql Server
- Next by Date: Re: Need Expert Help and Advice. Thank You.
- Previous by thread: Re: Joining tables from two databases
- Next by thread: Re: SqlCommand.Cancel and Transactions
- Index(es):
Relevant Pages
|
