CODEWITHSUNDEEP
c#.netsql-serverado.netasync-await
Karsten
Karsten

Reputation: 1866

How to query a database asynchronously in .NET

I tried to run a query asynchronously in .NET so that I can read the first few rows while the others are still being transmitted, but I had no luck so far.

The reason I want to to this is that we often need to fetch large tables from the database and while it is possible to not freeze the UI completely with asnyc variants of ExecuteReader() it doesn't seem to be possible to fetch data row by row so that the user can see the progress and maybe even work with the first portion of data.

What I tried is the following query:

SELECT 'Hello '
WAITFOR DELAY '0:0:10'
SELECT 'World!'

When I run this query with SqlCommand.BeginExecuteReader(callback) the callback function is called after approximately 10 seconds so it obviously waited for the whole query to finish. I also tried SqlCommand.ExecuteReaderAsync() with the same results.

My question is the following: Is this even possible in .NET? Or doesn't it work because of my query and would work better if tested with a real query (i.e. large table)?

Upvotes: 4

Views: 520

Answers (2)

lloyd
lloyd

Reputation: 1811

Get the first 10 rows in the table.

SELECT TOP 10 * FROM table ORDER BY ID DESC

Then run a second query. 

SELECT * FROM table ORDER BY ID DESC

Do a check if the first 10 changed ( Create, Update, Delete )

Update: Alternatively adding TOP then using OFFSET such as 

SELECT TOP 10 * FROM table

then 

SELECT * FROM table OFFSET 10 LIMIT 50

or pre SQL Server 2012 see Dave Ballantyne's solution

Upvotes: 2

Ali Adravi
Ali Adravi

Reputation: 22723

Not sure whether you want to use, in web application or in windows

If web, you can try this

  • On page load we can load a blank control
  • then we can fetch page by page records say 10
  • Append the fetched records to the control

Hope it will help you

Upvotes: 0

Related Questions