StrataFrame Forum

How to tell if connecting to valid DB

http://forum.strataframe.net/Topic14530.aspx

By Crones - 2/22/2008

I am curious to know how different people handle issues when connecting to databases that don't match your designed schema...



The scenario would be an end user connecting to some other database thinking that it is the correct one associated with my program, but is really another database that was installed for some other application.



I'm handing this by creating a DatabaseSettings table with 2 fields... SettingKey & SettingValue.

The DatabaseSettings table has a known number of records with specific SettingKey values I query for.



When testing the connection, if I get an object not found SQL Exception when querying the DatabaseSettings table, I know it's not my DB.

If that passes, I then fill the DatabaseSettings BO and check the count of records. If the number of records returned matches my known count, then I am "ASSUMING" this is my DB and perform version checks and so forth.



Is there a better way to handle this type of scenario? My end users are typically PC illiterate and don't necessarily have somebody technically competent to install and connect to an SQL Server for them...



Any advice or other ways to accomplish this is greatly appreciated.
By Trent L. Taylor - 2/22/2008

Is there a better way to handle this type of scenario? My end users are typically PC illiterate and don't necessarily have somebody technically competent to install and connect to an SQL Server for them...

We never let them choose the database name.  In our medical application, we do not use the Connection String Wizard as most of the end-users are not able to cope with entering this information.  We took a more advanced approach here and we have a server that runs as a service on a single machine within the network.  When the client app starts, it first looks for the server.  If it cannot find it (in registry settings) then is sends out a UDP request looking for the server.  The server then responds with the location of the server.  Next, the client then asks the server for the connection string...so the client never directly has a connection string entered by the end-user.

During our installation on the server we ask for the connection information the first time (i.e. server, user name, and password).  But we do not let them enter the database or select a database.  We already know all of this information.  This prevents a LOT of downstream issues!

By Crones - 2/22/2008

I would love to be able to implement this type of feature, however I am dealing with small businesses with at most 15 people (so far) and no on-site IT staff that could setup the server and ports. As it is, we have about 1/2 of our clients using an XP workstation as a file server. I'm not looking forward to explaining to people how to install SQL Express and opening ports and starting services just to be able to connect in this environment. But, since I'm stuck having to let end users establish connection strings and connecting to the proper database themselves, I have to do what I can to make sure that my app tests for any misconfiguration I can think of.
By Trent L. Taylor - 2/22/2008

I would love to be able to implement this type of feature, however I am dealing with small businesses with at most 15 people (so far) and no on-site IT staff that could setup the server and ports. As it is, we have about 1/2 of our clients using an XP workstation as a file server. I'm not looking forward to explaining to people how to install SQL Express and opening ports and starting services just to be able to connect in this environment.

Part of this is related to the installation.  We have a VERY sophisticated install so that there doens't have to be IT people to install SQL Server, etc.  This is all done through the installation, including the installation of the service, ports, etc.  The more sophisticated you make the install, the less sophisticated the end-user has to be to install the product.  We have spent a TON of time here...but it has paid off in the end.

In this example, it doesn't have to be a server OS...we have users that use XP and peer to peer as well.  We just have our own Server service that runs (on any Windows platform).  Just some ideas Smile