How to detach properly using SQL Server Express?

It’s easy to attach a database file in the connection string using AttachDbFilename. And that file gets closed when all connections to that database are closed (one might have to clear the connection pool, or set the connection to use master). The database file can now be moved or deleted. Sounds good...

...but that database still shows up in the attached databases! (select * from sysdatabases). So over time one could end up with hundreds of attached databases (especially if an application uses the attached databases like document files that users open and close frequently).

Question 1: Is there a performance hit having hundreds of attached databases?

OK, one can try to detach the database when the application is done with a specific file, using sp_detach_db after checking if other connections to that database are open (SELECT count(*) FROM master.dbo.sysprocesses WHERE dbid = DB_ID()). But that still does not detach if the application exists unexpectedly, or if one does not have a connection available anymore (e.g. during finalization when exiting the application). There should be a better way.

Question 2: Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

Question 3: Is there a connection string property that auto-detaches when the connection is closed?

Thanks for any tips

--Ralf

Is there a performance hit having hundreds of attached databases?

Depends on how hard this databases are being used. Perhaps, it would be hard a little to navigate through them in Management Studio or other management tool, but I think it's all.

Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

It looks strange that application attaches database everytime it connected to the server and detaches database everytime it disconneted. Imho, it's unnecessary cycle - just keep databases attached.

especially if an application uses the attached databases like document files that users open and close frequently

Sounds not good. What's the reason to have multiple databases instead of one?

|||

The reason for having multiple databases that are attached and detached frequently is that the user uses our app to open a database just like he would use Word to open a word file. We need to store data acquired from an instrument in easily movable files.

I thought this would be a typical use case for SQL Server Express.