• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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.

2 Solutions
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


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.

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.
Wow.  Response.IsClientConnected - I still have to learn a lot!  Thanks.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

David Johnson, CD, MVPOwnerCommented:
accident - the query might return in 10--20 minutes Why so long?
AEPRTDAuthor Commented:
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.
Yes, this is possible. Each client session has unique ID and you may even assign some ID to the query request. The webpage code (e.g. JavaScript or Ajax) can check the query status at the server periodically and switch to result page when results are available, it can also ask the user if he wants to cancel the query when leaving the page and inform the server about cancellation etc. etc.

I just don't have appropriate code available now.
I have a feeling that if a user has the ability to cancel requests while waiting for data to be returned(or whatever process is being performed) that there is a problem with your queries or the problem may be the way the tables are structured or the way the data is stored, etc.... If I had a query that took over 1 minute, I would focus on the query and make sure you are doing this correctly and work your way up the ladder from there. If youd like to post a sample of your different types of queries,we can start by looking there first... I suspect this may be an issue.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now