Service based Database not closed

0
votes

I have problem when I do connection to Service based Database, to mdf file after I opened the connection I cant closed it.

That is my Code: Create 2 buttons and 1 OpenDialogFile

private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectionString(db.mdf));
conn.Open();
conn.Close();
}


private void button2_Click(object sender, EventArgs e)        {
openFileDialog1.Filter = "Database File|*.mdf|All files |*.*";
openFileDialog1.DefaultExt = "mdf";
openFileDialog1.FileName = "db.mdf";
if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
{
return;
}
textbox1.Text = openFileDialog1.FileName;
}

After click on the first button the file in used and cannot do the second button

My Problem: i got connection string i can get the DataBased connected; but after i do it such as: Conn.Open(); no metter what i do Conn.close(); or anything else the file, the DB.MDF file is in used until i closed the whole project... it mean if i wanna open it and closed it i need open and close the project...

Edit: Try even that:

 using (SqlConnection connection = new SqlConnection(ConnectString))
            {
                connection.Open();
            }

still not worked

3
c#database
Use "using" when opening your connection string, e.g. using (SqlConnection conn... This should dispose of the connection correctly when the button1_Click method exits. – Richard Hansell
"Close and Dispose are functionally equivalent." msdn.microsoft.com/en-us/library/… – Steve Wellens
Still not working not using like that: using (SqlConnection conn = new SqlConnection(connectionString(db.mdf)); { conn.Open(); conn.Dispose(); conn.Close(); } – Asaf Shazar

3 Answers

2
votes

Try clearing the connection pools:

using(var conn = new SqlConnection(connectionString(db.mdf))
{
    conn.Open();
    conn.Close();
}
SqlConnection.ClearAllPools();
1
votes

You never directly use a *.mdf file. Instead, what happens is that Sql Server is already running somewhere in the background, and you tell Sql Server to attach to that file for you. Then you talk to Sql Server and open a connection to the database that Sql Server just attached.

When you close the connection, you are no longer connected to the database, but Sql Server is still running and still using the file. When you later go to try to open that file, it's still locked in use, and so you see the error from your question. Now you ask, "How can I tell Sql Server to release the file?" There are ways, but that's really the wrong question.

Instead, you need to ask if what you're doing fits with how Sql Server works. You see, Sql Server is not intended to work as a local data store for desktop applications. It really isn't. Sql Server is intended to live on a dedicated server somewhere, providing access to databases that have many many people connecting and making changes all at once. It's connection and operating methods reflect this, and therefore it's just not a good choice as a simple local data store used for desktop applications.

I love Sql Server, but if all you want to do is open a file and read or save a few records on your local machine from a local application, there are much better options available: Sql Server Compact (not Express) Edition, Sqlite, and even MS Access are all better choices for this.

While I'm here, never ever ever call .Close() or .Dispose() yourself. You see, Sql Server supports a finite number of connections. If your code calls .Close() or .Dispose() to end those connections manually, you're likely leaving a chance that an exception will occur in the method before reaching that function call, and the connection will never be closed. Do this enough, and suddenly you've locked yourself out of the database server. The best way to close the connection is not call those methods yourself, but instead let the framework do it for you, through the using mechanism:

using (var cn = new SqlConnection("connection string here"))
{
    cn.Open();
    //do stuff with cn

}  // cn WILL be closed at the end of this brace, **even if an exception is thrown**
// At no point did I ever use cn.Close(); or cn.Dispose(); the framework made sure it happened for me
0
votes

Thank you everyone for the help after using that

SqlConnection.ClearAllPools();

the file stop to be used and i can use it in other project/Form in my Full Project so i dont need to close the Whole Project