Database is being used by another process ... but what process?

Question

I have written a very small C# program, that uses a very small SQL Server database, purely for some learning & testing purposes. The database is used in this one new project and nowhere else. However, I am getting problems whilst running Debugs where the program will not run, because the database "is being used by another process".

If I reboot my machine, it will work again, and then after a few test runs I will get the same problem again.

I have found many, many similar problems reported all over the Internet, but can find no definitive answer as to how to resolve this problem. Firstly, how do I find out what "other process" is using my .mdf & .ldf files ? Then, how do I get these files released & not held in order to stop this happening time after time after time ?!?

I am new to VS2010, SQL Server & C#, so please be quite descriptive in any replies you give me !!!

This is my code, as you can see, you couldn't get anything much more basic, I certainly shouldn't be running into so many problems !!!

namespace MySqlTest
{
    public partial class Form1 : Form
    {
        SqlConnection myDB = new SqlConnection(@"Data Source=MEDESKTOP;AttachDbFilename=|DataDirectory|\SqlTestDB.mdf;Initial Catalog=MySqlDB;Integrated Security=True");
        SqlDataAdapter myDA = new SqlDataAdapter();
        SqlCommand mySqlCmd = new SqlCommand();

        string mySQLcmd;
        int myCount;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("myDB state = " + myDB.State.ToString());
            //Open SQL File
            myDB.Open();
            MessageBox.Show("myDB state = " + myDB.State.ToString());
        }

        private void button2_Click(object sender, EventArgs e)
        {
            myCount++;
            MessageBox.Show("myCount = " + myCount.ToString());
            //Insert Record Into  SQL File
            mySqlCmd.Connection = myDB;
            mySqlCmd.CommandText = "INSERT INTO Parent(ParentName) Values(myCount)";
            myDA = new SqlDataAdapter(mySqlCmd);
            mySqlCmd.ExecuteNonQuery();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //Read Record From SQL File
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //Read All Records From SQL File
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //Delete Record From DQL File
        }

        private void button6_Click(object sender, EventArgs e)
        {
            MessageBox.Show("myDB state = " + myDB.State.ToString());
            //Close SQL File
            myDB.Close();
            MessageBox.Show("myDB state = " + myDB.State.ToString());
        }

        private void button7_Click(object sender, EventArgs e)
        {
            //Quit
            this.Close();
        }
    }
}

Answer 1

The most likely options:

1. A previous (crashed) instance of your program
2. Visual Studio (with a Table designer open or something similar)

You can check 1) with TaskManager and 2) by looking in Server Explorer. Your db should show a small red cross meaning 'closed'.

And you should rewrite your code to close connections ASAP. Use try/finally or using(){ } blocks.

Answer 2

This is the rewritten code, using the "Using" statements. When I execute the program and click on Insert Record Into SQL File, off it goes, completes the process with myCount = 1 (though I'm not 100% sure that it is actually doing a physical Write, am I missing a command that actually "commits" the update ?!?) and re-displays the Form.

If I then click on Insert Record Into SQL File again, I get an error as follows :

SqlException was unhandled

Cannot open user default database. Login failed. Login failed for user 'MEDESKTOP\Gary'.

This is the program (I am the only user on this PC and have full Admin rights, the "State" of the database at this point is, according to Properties, Closed, so it looks like the first pass through the code did as was expected ...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MySqlTest
{
public partial class Form1 : Form
{
    int myCount;
    string myDBlocation = @"Data Source=MEDESKTOP;AttachDbFilename=|DataDirectory|\mySQLtest.mdf;Integrated Security=True;User Instance=False";

    public Form1()
    {
        InitializeComponent();

    }

    private void button2_Click(object sender, EventArgs e)
    {
        myCount++;
        MessageBox.Show("myCount = " + myCount.ToString());
        //Insert Record Into  SQL File
        myDB_Insert();
    }

    private void button3_Click(object sender, EventArgs e)
    {
        //Read Record From SQL File

    }

    private void button4_Click(object sender, EventArgs e)
    {
        //Read All Records From SQL File

    }

    private void button5_Click(object sender, EventArgs e)
    {
        //Delete Record From SQL File
    }

    private void button7_Click(object sender, EventArgs e)
    {
        //Quit
        myDB_Close();
        this.Close();
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void Form1_Close(object sender, EventArgs e)
    {

    }

    void myDB_Insert()
    {
        using (SqlConnection myDB = new SqlConnection(myDBlocation))
        using (SqlCommand mySqlCmd = myDB.CreateCommand())
        {
            myDB.Open(); **<<< Program fails here, 2nd time through**
            mySqlCmd.CommandText = "INSERT INTO Parent (ParentName) VALUES(@ParentNameValue)";
            mySqlCmd.Parameters.AddWithValue("@ParentNameValue", myCount);
            mySqlCmd.ExecuteNonQuery();
            myDB.Close();
        }
        return;
    }

    void myDB_Close()
    {
        using (SqlConnection myDB = new SqlConnection(myDBlocation))
        using (SqlCommand mySqlCmd = new SqlCommand())
        {
            myDB.Close();
        }
        return;
    }

}
}

I don't understand why I am suddenly losing access to my own file that I am already using ?!?