[Webinar] Learn how to a build a cloud-first strategyRegister Now


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
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 43

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 43

Expert Comment

ID: 40607609
Wow.  Response.IsClientConnected - I still have to learn a lot!  Thanks.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 84

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 43

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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

864 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