How do you check for existance of local instance of database from application code in a fast and safe way?

Question

What is the way to find out if,

there is a locally running instance of SQL Server
if so, does database X exist?
if so, can databse X be connected to using integrated security?

I want to accomplish the above at application startup in the cleanest possible way. Specficially, I'm worried about introducing the kind of delay I commonly see when I click on a dropdown to browse for databses in a connection wizard of various dev tools.

I can make the assumption of the instance name being either . or .\sqlexpress.

The end users are mostly running Windows XP.

The actual context of this is I want to automatically have my application sense a local database that developers can have installed.

If it's as simple as creating a a SqlConnection with the apprpriate values in the connection string, and that is what is safe and the recommended way, then I'm good with that. I'm asking this because I'm trying to be safe about not introducing a bad end user experience.

score 1 · Answer 1 · edited May 23 '17 at 11:48

1

If you know to look at either . or .\sqlexpress simply attempt to connect in a try-catch and perform a select from master.sysdatabases.

If you don't know the server, then you can use SQL Server Management Object libraries to list all instances / databases that are discoverable. This question details how:

How to list available instances of SQL Servers using SMO in C#?

The SMO Server class then has a Databases property to iterate.

edited May 23 '17 at 11:48

Community

1
1

answered Jun 27 '12 at 19:27

Adam Houldsworth

63,413
11
150
187

i've always had this feeling that SMO was fairly heavyweight, but if it turns out to be nothing more than some assemblies I can deploy with my application, this might be a good solution, thanks! – Aaron Anodide Jun 27 '12 at 19:32
@AaronAnodide It'll be slower because it enumerates all discoverable servers. If you know the servers, try to connect and manually query master.sysdatabases. – Adam Houldsworth Jun 27 '12 at 19:34

score 1 · Answer 2 · answered Jun 27 '12 at 19:28

1

Connect to (local) server. If good, then switch to master database. Then run this SQL statement:

SELECT * FROM sysdatabases WHERE name = 'YOUR DB NAME HERE'

answered Jun 27 '12 at 19:28

bluevector

3,485
1
15
18

1

(local) will only find the default instance, not any named instances running locally (ie, `.\sqlexpress`). – Adam Houldsworth Jun 27 '12 at 19:32

score 0 · Answer 3 · answered Jun 27 '12 at 21:56

Use the property SqlConnection.ConnectionString and set the value for Connection Timeout. The default value is 15 seconds, which is a long time. So if you enumerate all the DB servers, as in Adam’s idea, and decrease the timeout value, then perhaps the user experience won’t be so bad.

More details are at SqlConnection Class .

I don't want to make a SQL query because that may take a long time before it fails.

@Aaron, Let us know what you decide.

How do you check for existance of local instance of database from application code in a fast and safe way?

3 Answers3