asked on

Question about 3 tier app and db connections

We have an in house VB app that allows a user to enter one or more parameters and click a find button.
The code builds a SQL query and runs it.
The users enter 1 of 3 types of parameter sets.
1. detailed - the query runs in a few seconds
2. general - the query runs in a minute or 3
3. accident - the query might return in 10--20 minutes

If the user enters type 3, they usually realize it and close the app.
That kills the SQL connection and the query dies.

We are rewriting the app in VB.net as a 3-tier architecture.
We have concerns about case 3 above.
We have been told that due to the middle tier, IIS, and connection pooling, it is not possible for the client to end a SQL connection when a user closes the app.
The client tells the middle tier to do the find with a certain set of parameters.
The client does not get any connection info back from the middle tier, so there is no way for the client to abort the find.

I am looking for any documentation or sample code that would prove or disprove whether the client can track the SQL connection.
Any articles or advice on the best way to do things would be greatly appreciated.

Thanks

sarabande

We have been told that due to the middle tier, IIS, and connection pooling, it is not possible for the client to end a SQL connection when a user closes the app.

I think that the assertment is wrong.

I found the following in the IIS docs at https://msdn.microsoft.com/en-us/library/ms525295(v=vs.90).aspx

Exiting

Under normal circumstances you will want your ASP application to complete each line of script in the page. There may be some circumstances, however, where you will need to simply end the response. For example, if you have detected (by using the Response.IsClientConnected method) that the client is no longer waiting for a response, you will want to terminate the ASP application.

You can accomplish exiting the ASP by using the Response.End method.

Sara

Pavel Celba

The client can still click Cancel button which tells to server "Cancel the query" or "Cancel the connection". The Click event processing code on the server then kills the connection invoked by the client's previous request.

BUT remember one thing: The connection between client and server can be dropped for whatever reason (we are on the internet) and the server cannot know about it so the query continues the run which consumes server resources. The client can execute several such queries which results in the server overloading...

The easiest thing you can do is to define some query timeout which avoids long queries. Other possibility is to remember all unfinished client's requests and inform the client about their existence after the re-connection etc. etc. Of course, the connection needs some client authentication in this case.

You may also display a dialog window saying "Waiting for the query result" which informs the server about the client waiting state periodically. Once the server looses track about this informational message the query and connection can be cancelled.

Code samples are out of scope of this post but you may find many books and web posts about 3-tier architecture in .NET etc. Also many ways to achieve your goal exist and you have to find the one which will fit to all your needs...

You may start here: https://msdn.microsoft.com/en-us/library/ms973279.aspx
And look at this book: http://www.amazon.com/Beginning-ASP-NET-4-5-1-Wrox-Programmer/dp/111884677X/ref=pd_sim_sbs_b_2?ie=UTF8&refRID=1AKW24YS9TW1B6VWCRV1
plus many others.

Remember one fact: The .NET learning curve is really flat and long.

Pavel Celba

Wow. Response.IsClientConnected - I still have to learn a lot! Thanks.

David Johnson, CD

accident - the query might return in 10--20 minutes Why so long?

AEPRTD

ASKER

Sorry about the delay - I am busy with other things and doing more research into this issue.

I am not expecting anyone to post sample code here.
I was hoping someone had done what I wanted and had a sample on the internet somewhere.
Just to clarify things a little, this is an in house VB.net application hitting an in house web server and an in house sql server.

The story I get from developers is they were planning on doing a synchronous web service call.
The client app calls a web service and waits for it to return the sql data.
I've been looking into asynchronous web service calls.
That would allow the client to tell the app server to perform a find function.
Then control would return to the client app while the app server did the find.
My ideal solution would be like this
client calls a web service that tells the app server to do a sql query to find some data
app server acknowledges the request and returns an id to the client
if the find finishes, the app server sends the data to the client
if the user clicks an abort button on the form before the sql query finishes, the client app calls a web service and passes the id it got earlier
the app server would get the request with the id, look up the id, and kill the associated sql query

I don't know if any of that is possible.
Thoughts?

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

jknj72

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial