By Bill Cunnien - 2/2/2010
Good morning,
I am revisiting a topic that may have been touched on before (I did search for it, but could not find any direct forum entries). I apologize if I am re-walking on some old ground.
I'd like to have a mechanism to alert the user that a database connection has been lost or regained. Is it possible to get this information from the DataLayer? Or, do I need to create my own test (perhaps in its own thread?) and bubble up the response to the currently running application thread? Ideally, I prefer a graceful message to the end-user that the connection to the database is down and all data access functionality has been temporarily stopped. After the connection is reestablished, the system would generate a message to the end-user that the connection is back online and work can continue normally. For a similar functionality, consider the message Outlook provides when the Exchange server cannot be communicated with.
Thanks!
Bill
|
By Trent L. Taylor - 2/2/2010
I am not sure what you are trying to accomplish. First, the connections are never left open. You never want to leave a connection open to a server longer than necessary. So the connection to a server is only opened for any interactions (i.e. SELECT, INSERT, UPDATE, DELETE, etc.) then closed. If you leave a connection open to a server, you will experience massive performance issues which also leads to other problems. So I guess I am not sure exactly what you might be trying to determine here. Are you just trying to determine if the SQL Server goes down and isn't available for use anymore? If that is the case, then you could create a threaded class that periodically (once a minute or something) attempts to connect to the server. If so, then you are good, otherwise you would create an alert.
|
By Bill Cunnien - 2/2/2010
Granted, before anything can be done with a database, a connection must be opened. A threaded class sounds like what I am looking for. A user may be looking at some data which has been cached locally. He can sort and filter to his heart's content, but without knowing it, the database server cannot be reached anymore. The user finishes reviewing the cached data and wants to retrieve additional data or persist additions/changes he made. Still unaware, he initiates the appropriate command. The application errors because the database cannot be reached. Rather than that happening, I want to be proactive and alert the user that the database has gone offline. He would be provided a choice...wait for the database to come back online or close the application losing potential changes. If he chooses to wait, then the application would disallow other activity until the threaded class reports back that the server is back online. Once the server is back online, the threaded class detects this and resets the application so that he can proceed like normal.
In addition, if the threaded class has not performed the check, yet, and the server is unreachable, then the DbDataSourceItem class will fail when a connection is opened. If that class could somehow tie into that threaded class and either use the connection test data (fire a connection test of some kind from that thread) or somehow tell the threaded class that a problem exists, then the threaded class would take over and alert the user as outlined above.
I am probably not being clear...holler if this doesn't make sense.
|
By Trent L. Taylor - 2/2/2010
Just curious...why are you expceting your database to go offline? Also, if this is a big issue for you, you already have the data source collections you can reference, so in you base BO (if you have one) you could override the FillDataTable method or the appropriate OnBeforeXXXX methods and just ping the server, thus eliminating an error. As I mentioned, the connection is gone and is never left open, so unless you execute a test with a 1-2 second timeout, then you are never going to know anyway unless you open a connection.SqlConnection conn = new SqlConnection(MicroFour.StrataFrame.Data.DataBasics.DataSources[""].ConnectionString); bool r = true; try
{
conn.Timeout = (really small number here  );
conn.Open();
conn.Close();
}
catch
{
r = false;
} return r; Again, I am not sure why your database (server) is just going away, but maybe this is an idea.
|
By Greg McGuffey - 2/2/2010
A couple of thoughts here. First, as to the db going away, I can easily imagine this. Most of my users are remote. I.e. they are connecting from hotels, and hot spots at starbucks, from all over the US and occasionally Europe, or using broadband cards. Lots of times these connections are flaky (to put it mildly). Thus, it isn't the db that is going away, but them. They loose connection to, well, just about everything. So I totally see were this would be nice, especially if they are in the middle of editing a record (which in my app's case can take many minutes) when they loose the connection. It would be nice if they were informed earlier rather than later.
If I were to implement this, based on what's been discussed so far, I think I'd have a class to handle the check. I'd run an asynch thread to check every minute or two. But I'd also be able to call the method synchronously from my BaseBO (as Trent suggested). That way if they are in a long edit, they'd get informed before a save failed, but I'd also be checking before access in order to gracefully inform the user of the issue. I'm just throwing out ideas here. Figure I might have to do this one day myself!
Also, note that if you using Enterprise Server, the Connection test won't work (last time I tried, I couldn't get a connection from an ES DbDataSourceItem). You'll need to run a real fast query instead. Could be something as simple as Select 1. This would just make sure you can connect (with a real small timeout). I'm not sure how to manipulate the ES timeout at runtime though...
I might also consider making this check configurable, so if the user knows they have high availability, they could turn it off. Finally, you might also consider handling the errors that come up when the connection is lost, and taking the action you were considering taking (user can hang out and try again (with data related functionality turned off) or just close and loose any changes. Upon further thought, I'd likely start with handling exceptions gracefully, and only add in the other strategies (threading and checks before access via BO) if it seem like I still needed it (I.e. lots of lost edits, connection going south frequently). Threading can be hard to get right, performance issues are likely too.
Just some ideas....
|
By Bill Cunnien - 2/3/2010
Thanks Trent and Greg,
Our company has two locations with a dedicated T1 line between them. Over-utilization and other external factors bring down that line a little too often for comfort's sake. Remote users experience an annoying disconnect from the data server. Sometimes users will connect via a VPN. Occasionally, these types of connections have problems, too, for a myriad of reasons.
I have steered away from a base BO. The purist in me wants to utilize the framework "as is" as much as I can; however, I can see where the code outlined would be a real help. I'll look into this.
Sometimes, I pull data without using a BO, though (reporting, specialized grids, etc.). That's where I really need to try to incorporate a more global solution. The asynchronous class may provide the functionality that I am looking for. I have my homework to do.
Bill
|
By Bill Cunnien - 2/3/2010
If I create a BaseBO class from which all of my BOs inherit, does it need to have the "Serializable()" attribute?
|
By Trent L. Taylor - 2/3/2010
Creating a Base BO is still using the framework as it is designed. If you download the StrataFlix sample you will see a Base BO there. But yes, include the serializable tag.
|
|