I need a mechanism for locating SQL server instances

Question

Hi

I have an idea and I want to apply it to my Application (C# .NET).

When we connect to a DB (MS SQL Server 2008) in VS 2008, the ConnectionString saved in the Application Setting and it's a static varriable (no one can edit it unless you edit it inside VS 2008).

I want a way to let my Application search for MS SQL Server and save it to Application Setting and use it to connect to my DB Programmatically.

When my application start, the first thing to do is checking the ConnectionString if vaild, NOT Empty and test connection to MS SQL Server Successfully so if there is a proplem I think to show a window form to let the user enter some data like username and password for MS SQL Server 2008

Is there any way to do it?

Answer 1

You can have settings that can be programatically changed in App.Settings... Something like:

Properties.Settings.Default.yourVariable=x;
Properties.Settings.Default.Save();

Answer 2

For instance you have a global variable called dbname, dbtype, uid, pwd; and assuming that you have a method HitTest(strConnectionString):bool which 'tries' to SELECT a table, and returns true if it finds valid number of rows; false for otherwise.

When you start, the app should do following.

1. Join dbname, uid and pwd, based upon dbtype and prepare a strConnectionString.
2. Call HitTest(strConnectionString);
3. If true, continue loading the app, else read more.
4. Popup a dialog box asking for dbname, dbtype, uid, pwd.
5. Goto 1.

--EDIT--

See Enumerating Instances of SQL Server, and Enumerate SQL Server Instances in C#, Using ODBC

Answer 3

http://support.microsoft.com/kb/q287737/

Answer 4

Maybe this CodeProject article is what you need.

Answer 5

You can use SMO (Server Management Objects), works with SQL 2005/2008 only.

SMO samples

SMO SqlServerList Sample