Question about 3 tier app and db connections

Posted on 2015-02-12
Medium Priority
Last Modified: 2015-07-16
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.

Question by:AEPRTD
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 35

Expert Comment

ID: 40607578
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.

LVL 42

Expert Comment

ID: 40607605
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.
LVL 42

Expert Comment

ID: 40607609
Wow.  Response.IsClientConnected - I still have to learn a lot!  Thanks.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 82

Expert Comment

by:David Johnson, CD, MVP
ID: 40608289
accident - the query might return in 10--20 minutes Why so long?

Author Comment

ID: 40621297
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.
LVL 42

Assisted Solution

pcelba earned 1000 total points
ID: 40621361
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.

Accepted Solution

jknj72 earned 1000 total points
ID: 40805146
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.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question