MT Data Retrieval Question

[Angelo]

Hello,

I am currently working on a small project which performs user defined
queries on an MS-SQL DB. When the user starts the query, I start a
temporary thread (thread pool) which performs the query and contacts
the UI thread when the data is ready, which is then displayed.

I would like to allow the user to abort a query while its processing,
but I seem to be having trouble on how to implement the abort
mechanism. This is due to the fact that most of the time spent in the
query thread is waiting for the database connection to retrieve all of
the row data.

Currently, I have thought of three solutions:
1. Create a flag in the query thread which tells it to cancel the
query. The thread would only check this flag directly before the
query is executed and directly after. If the user decides to cancel
the query while the thread is waiting on the database connection, then
the thread will continue until all data is retrieved, but the data
will not be displayed.
2. Similar to 1, create a flag in the query thread which tells it to
cancel the query. In the query thread, create a loop which only
queries part of the data each time, checking the flag on each
iteration (for example, 10 iterations of 3000 rows per iteration).
3. Abort the thread (which I have read countless number of time is
evil).

I believe that 2 is the best solution, however I have not come up with
a sufficient implementation. Currently, this project is using Linq to
SQL / Entity Classes to obtain the data from the database. Is there a
'simple' way to splice a query into a defined number of chunks for
retrieval?

For simplicity's sake, lets say the table I am querying from is
defined as such:

Table Name: Process_Output
Columns -
ID <pk> long
Name NText
Value int
Timestamp Date

My inexperience in the area is definitely a hindrance, therefore I am
looking to a few experts for some helpful advice. If you need more
information, I will gladly provide.

Thanks
AJ

 

[Nicholas Paldino [.NET/C# MVP]]

Angelo,

I'm slightly concerned about you using a thread from the thread pool to
do this. You should take your Linq-to-SQL queries (your IQueryable<T>
implementations which are generated as a result of "from i in items...", if
you aren't using these, then there is no reason for you to be using
Linq-to-SQL) and pass them to the GetCommand method on the DataContext you
will use.

You can then execute this command asycnchronously. It won't waste a
thread from the thread pool (it shouldn't, it should be an I/O completion
method and use a different thread). When you get the data reader back from
the command in your callback (which you passed when you executed the
command) you would pass the reader to the Translate method on the
DataContext to translate back into object instances.

I would recommend creating a class which you initialize with the
DataContext and the DbCommand returned from the call to GetCommand (and
maybe the original query, i.e. IQueryable<T>). Then, it would expose the
method that you pass as the callback, and you hold onto that object. It
would also expose an Abort method and contain a private flag (which you
synchronize access to, since it will be called from different threads) which
you set when your callback is called.

In the abort method, you check the flag. If it is set, you do nothing,
and exit. If the flag is not set, then you set it, and then proceed to call
Dispose on the command and the DataContext.

In the callback, if the flag is set, you exit the callback. If it is
not set, then set the flag, and process the result.

You would also, of course, have some way of exposing the result on this
object.

 

[Angelo]

Angelo,

    I'm slightly concerned about you using a thread from the thread pool to
do this.  You should take your Linq-to-SQL queries (your IQueryable<T>
implementations which are generated as a result of "from i in items...", if
you aren't using these, then there is no reason for you to be using
Linq-to-SQL) and pass them to the GetCommand method on the DataContext you
will use.

    You can then execute this command asycnchronously.  It won't waste a
thread from the thread pool (it shouldn't, it should be an I/O completion
method and use a different thread).  When you get the data reader back from
the command in your callback (which you passed when you executed the
command) you would pass the reader to the Translate method on the
DataContext to translate back into object instances.

    I would recommend creating a class which you initialize with the
DataContext and the DbCommand returned from the call to GetCommand (and
maybe the original query, i.e. IQueryable<T>).  Then, it would expose the
method that you pass as the callback, and you hold onto that object.  It
would also expose an Abort method and contain a private flag (which you
synchronize access to, since it will be called from different threads) which
you set when your callback is called.

    In the abort method, you check the flag.  If it is set, you do nothing,
and exit.  If the flag is not set, then you set it, and then proceed tocall
Dispose on the command and the DataContext.

    In the callback, if the flag is set, you exit the callback.  Ifit is
not set, then set the flag, and process the result.

    You would also, of course, have some way of exposing the result on this
object.

--
          - Nicholas Paldino [.NET/C# MVP]
          - (e-mail address removed)

Hello,

I am currently working on a small project which performs user defined
queries on an MS-SQL DB.  When the user starts the query, I start a
temporary thread (thread pool) which performs the query and contacts
the UI thread when the data is ready, which is then displayed.

I would like to allow the user to abort a query while its processing,
but I seem to be having trouble on how to implement the abort
mechanism.  This is due to the fact that most of the time spent in the
query thread is waiting for the database connection to retrieve all of
the row data.

Currently, I have thought of three solutions:
1.  Create a flag in the query thread which tells it to cancel the
query.  The thread would only check this flag directly before the
query is executed and directly after.  If the user decides to cancel
the query while the thread is waiting on the database connection, then
the thread will continue until all data is retrieved, but the data
will not be displayed.
2. Similar to 1, create a flag in the query thread which tells it to
cancel the query.  In the query thread, create a loop which only
queries part of the data each time, checking the flag on each
iteration (for example, 10 iterations of 3000 rows per iteration).
3.  Abort the thread (which I have read countless number of time is
evil).

I believe that 2 is the best solution, however I have not come up with
a sufficient implementation.  Currently, this project is using Linq to
SQL / Entity Classes to obtain the data from the database.  Is there a
'simple' way to splice a query into a defined number of chunks for
retrieval?

For simplicity's sake, lets say the table I am querying from is
defined as such:

Table Name: Process_Output
Columns -
ID <pk> long
Name NText
Value int
Timestamp Date

My inexperience in the area is definitely a hindrance, therefore I am
looking to a few experts for some helpful advice.  If you need more
information, I will gladly provide.

Thanks
AJ- Hide quoted text -

- Show quoted text -

Thank you for your response,

After looking up GetCommand in the datacontext, I believe it is eactly
what I need (Convert it to a DbCommand and perform an asynch query).

AJ