Solved

Question about 3 tier app and db connections

Posted on 2015-02-12
7
106 Views
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.

Thanks
0
Comment
Question by:AEPRTD
7 Comments
 
LVL 33

Expert Comment

by:sarabande
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

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
0
 
LVL 41

Expert Comment

by:pcelba
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40607609
Wow.  Response.IsClientConnected - I still have to learn a lot!  Thanks.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 79

Expert Comment

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

Author Comment

by:AEPRTD
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.
Thoughts?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 250 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.
0
 

Accepted Solution

by:
jknj72 earned 250 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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
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 …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now