detach database file from local SQL Server Express instance by C#?

I am using the following C# code to establish a SQL connect to a SQL
database file:



// connection string

// attach a SQL database file to a local SQL server express instance

string _connectionString = @"Server=.\SQLExpress; AttachDbFilename=C:
\BalanceDatabase_1.mdf; Trusted_Connection=Yes; User Instance=True";



// using System.Data.SqlClient;

SqlConnection _sqlConnection = new SqlConnection(_connectionString);

// open the connection

_sqlConnection.Open();



// do something



// close the connection

_sqlConnection.Close();



So far, the connection works fine.



However, next, I want to copy the database file to another folder. So
the following codes:

// source database file name

string sourceDatabaseFileName = @"C:\BalanceDatabase_1.mdf";

// target database file name
string targetDatabaseFileName = @"D:\BalanceDatabase_1.mdf";

// copy database file

System.IO.File.Copy(sourceDatabaseFileName, targetDatabaseFileName,
true);



Then the program came with runtime exception: "IOException was
unhandled: The process cannot access the file 'C:
\BalanceDatabase_1.mdf' because it is being used by another process."



Is it because the database file was sill attached to the local SQL
Server express instance? What can I do to bypass this problem? Detach
the database file? or dispose the local SQL Server express instance?



Many thanks indeed!

Re: detach database file from local SQL Server Express instance by C#?

After the database is attached SQL Server Express opens database files with
exclusive access and you cannot do a copy. To release the exclusive access
you have to detach the database. This is done using the sp_detach_db system
stored procedure:
http://msdn2.microsoft.com/en-us/library/ms188031.aspx

Note that in order for detach to work there should be no user connections to
the database. You can obtain exclusive rights to the database when all users
disconnect with something like this:

ALTER DATABASE DatabaseName SET SINGLE_USER;

If you need to force users out of the database immediately, you can use
something like this (note, this will roll back incomplete user
transactions):

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
or
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK AFTER
[SECONDS];

The second statement will give you an option to roll back after the
specified number of seconds (substitute with seconds).

More for working with SQL Server Express instances here:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx

More on ALTER DATABASE:
http://msdn2.microsoft.com/en-us/library/ms174269.aspx


HTH,

Plamen Ratchev
http://www.SQLStudio.com