SQL Connection form - how to? 1

[fweeee]

Hello,

I'm writing an application which connects to a database (SQL Server 2005). I want to make this flexible enough so it can connect to the server/database that the user specifies (so a form pops up, that they can select the server/database from the list).

I can work out just about everything, except how to give the user a list of available SQL Servers, and a list of the databases on it. Can someone point me to an example of this somewhere (I have looked and looked).

Thanks.

 

[AlexCuse]

Well, you would need to already be connected. YOu then could put together a union query like this to show all databases.

select name
from Server1.master.dbo.sysdatabases

union all select name
from Server2.master.dbo.sysdatabases

---and so on...

Servers I *think* you will have to hard-code values for, or set up a table somewhere to use to populate your drop down list. There is a sysservers table, but I am not so sure it will help you.

Be VERY careful issuing queries against master. Try to erase the words 'UPDATE', 'DELETE' and 'INSERT' while you are in there ;-)


Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[jmeckley]

You could also enter a list of avialable databases in the Connection string section of the app.config file.
then read the connection names into a list of strings. bind the list to a drop down.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JurkMonkey]

Be aware of letting users connect to multiple databases. Users are dumb - very very dumb. They can end up on the wrong database and entering data for an entire day. Then you have to clean up their mess which requires time and money.

You may want to consider other options like not showing a database at all but have a configuration section which allows you to select a database (not on the login form). This would avoid the stupid users from clicking on that server list drop down and using the scroll wheel by "accident"

No... I'm not bitter....

 

[jmeckley]

Users are dumb - very very dumb

a universal truth.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AlexCuse]

What's the saying? By the time you make something idiot-proof, the universe will have created a better idiot?

(my apologies for butchering whoever's quote this is ;-) )

Ignorance of certain subjects is a great part of wisdom

 

[fweeee]

Thanks for your replies - I'll give them a go soon.

The reason why I want this, is because this application is going to be distributed to various locations, and each location will have its own database. Thus, I need the means to allow the user to select their own server/database easily.

 

[jmeckley]

Isa user associated with exactly 1 location? if so then just link the login names to a role, and a role to a database. then the users do not have to select databases.

Jason Meckley
Programmer
Specialty Bakers, Inc.