CODEWITHSUNDEEP
c#.netsql-serverdatabasesql-server-express
user3103111
user3103111

Reputation: 3

Unable to copy or use my SQL Server database while my C# app is running

I am new to SQL Server and stuck with very basic (appears so!) sort of issue. I am accessing my local SQL Server database file through my C# app.

What I want is to be able to create a backup of my local database on ftp server through my C# app whenever I want. But whenever I try to upload the database file (or even copy using windows explorer) I get an error

Action cannot be completed because file is open in SQL Server

Every time form in my app is loaded I get list of products by calling this function:

private void populate_products()
{
    SqlConnection con = new SqlConnection();

    con.ConnectionString = @"Data Source=.\SQLEXPRESS;"+
                          "AttachDbFilename=" + AppDomain.CurrentDomain.BaseDirectory + "myDB.mdf;" +
                          "Integrated Security=True;Connect Timeout=30;User Instance=True";
    con.Open();

    try
    {
        SqlCommand cmd;

        string cmd1 = "SELECT Name,Cost FROM Product";

        cmd = new SqlCommand(cmd1, con);            
        SqlDataReader read = cmd.ExecuteReader();

        bool ch = true;

        while (read.Read())
        {
            list_name.Items.Add(read[0].ToString());
            costs.Add(read[1].ToString());
        }

        read.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    con.Close();
    con.Dispose();    
}

As soon as this function is called file can not be copied. Since con.Close() is called I wonder why file is still in use.

Even detaching database from SQL Server Management Studio doesn't work. Can anyone please help?

Upvotes: 0

Views: 118

Answers (2)

Andy-Delosdos
Andy-Delosdos

Reputation: 3720

An alternative would be to create a backup, then upload that rather than your mounted database files.

You can use this SQL command to create a backup: 

BACKUP DATABASE [DATABASENAME] TO DISK = 'c:\path\backup.bak' WITH FORMAT, INIT, STATS = 25

Change DATABASENAME and c:\path\backup.bak as necessary. You can then pick-up the bak file, and upload from there.

Upvotes: 1

gilmishal
gilmishal

Reputation: 2012

You need to clear connection pools.

Try adding 

SqlConnection.ClearAllPools();

at the end of the method.

Upvotes: 0

Related Questions