My database is being used by another process, but what process ?!?

See more:

C#

SQL-Server

Visual-Studio

, +

OK, after working on my code regarding my original question (below in italics), I think I have a (slightly !) better understanding of how this works and I think I have better code ... however, it is still not working & I am confused !!!

I have amended my code to use the "USING" statement as I am led to believe from what I have read that it will release resources when I have finished with them, even if their is a program failure, but it doesn't seem to be doing that for me ...

Here is the timeline leading to my problem :

1) Open the Project within VS2010
2) Add a new Item / Data / Service Based Database / DataSet & then create a single Table, so the Database is called MyTestDB, the Table is MyTestTbl, & the fields are MyTestID (Int, Primary Key, IsIdentity True, 1,1) & MyTestNum (Int)
3) Build Succeeded
4) Run the Program in Debug mode, Form displays, Click on Insert
5) MyCount = 1, MessageBox says myDB.State = Open, process data, MessageBox says myDB.State =Closed, Form re-displays.
6) Repeat, repeat, repeat, etc. ... MyCount progresses as expected, no problems, Quit & program ends.
7) Now we have a problem ... Firstly, there are still no records in the database !!! If I right-click on the MyTestTbl Table in Server Explorer and "Show Table Data" it just shows the Headings & Null, Null. Question 1 : Why ? Do I have to add another line of code to "commit" the update or something ?!?
8) If I now run the Program in Debug mode again, it will not run because I have 2 Errors, similar to my original problem, something to do with files in the \bin\debug Folder already existing ... "Unable to copy file <path> \MyTestDB.mdf to bin\Debug\MyTestDB.mdf. The process cannot access the file bin\Debug\MyTestDB.mdf because it is being used by another process. (the same error message for the file MyTestDB_log.ldf). Question 2 : Again, why ?!? Why are these files getting locked, or blocked ?!?

This is my code, surely it shouldn't be this difficult !!!

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|\MyTestDB.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();
                MessageBox.Show("State = " + myDB.State);
                mySqlCmd.CommandText = "INSERT INTO MyTestTbl(MyTestNum) VALUES(@MyTestNumValue)";
                mySqlCmd.Parameters.AddWithValue("@MyTestNumValue", myCount);
                mySqlCmd.ExecuteNonQuery();
                myDB.Close();
                MessageBox.Show("State = " + myDB.State);
            }
            return;
        }

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

    }
}

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 these constant problems !!!

    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
        {
            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();
            }
        }
    }
[/code]