Geek Answers Handbook

The database is being used by another process ... but which process?

I wrote a very small C # program that uses a very small SQL Server database, solely for some training and testing purposes. The database is used in this new project and nowhere else. However, I am having problems starting Debugs, where the program will not start because the database is "being used by another process."

If I reboot my machine, it will work again, and then after several test starts, I will get the same problem again.

I have found many, many similar problems reported all over the Internet, but I cannot find a definitive answer to the question of how to solve this problem. Firstly, how do I know that a “different process” uses my .mdf and .ldf files? Then, how can I get these files released and not held in order to stop this from time to time after time ?!

I am new to VS2010, SQL Server and C #, so please be descriptive enough in any answers you give me !!!

This is my code, as you can see, you cannot get anything more basic, of course, I should not face 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();
        }
    }
}
+5
source share
10 answers

The most likely options:

  • Previous (broken) instance of your program
  • Visual Studio ( - )

1) TaskManager 2), Server Explorer. db , "".

, . try/finally using(){ }.

+6

, "". " SQL", , myCount = 1 ( 100%, , , "" ?!?) .

" SQL", :

SqlException

. . 'MEDESKTOP\Gary'.

( , "" , , , ...

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;
    }

}
}

, , ?!?

+1

sp_who2, .

FYI: , SQL Server

0

IIRC, AttachDbFilename, SqlServr.exe, , , SqlServer, ( MsSQLIerver ). . , db.

0

Process Explorer, , Microsoft. Windows Admins/Developers. , .

Process Explorer, :

  • (, ).
  • Process Explorer ( , ).
  • "" .mdf .ldf.

/ , .

0

VS 2010 Entity Framework SQL Server . , , , Server Explorer. , .

VS2010 ( .MDF .LDF), Server Explorer, . , . , MDF Copy to Output Directory , Copy always. , , , , .

- SQL Server. VS2010 SQL Server, . Entity Framework XML- App.Config.

SQL Server, , . , .

SQL .

SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances

- :

   |owning_principle_name | instance_pipe_name                      | heart_beat
--------------------------------------------------------------------------------
1  | MyServer\Admin       | \\.\pipe\91B3063E-CD0F-4B\tsql\query\   | dead
--------------------------------------------------------------------------------
2  | MyServer\Rich        | \\.\pipe\B04A1D3B-6268-49\tsql\query\   | alive

, , , .

, Tasks > Detach..., Detach Database, Drop Connections OK.

, .

0

. . " ".

, .

0

, SqlTestDB.mdf. , Ms Sql , Server Explorer Visual Studio.

0

, . , , , , , .

, " ".

, , , , .

, , , :

, .

 S_UpdateSingleRecord(...)
 M_UpdateManyRecords(...)

, , , . , - .

I use "use" in S_SingleTransaction () because it clears it for me. But I leave the open multiple entry point open and close it when my call function ends. I have never had this problem with locking with one mdf and one application. It will return to the game with one mdf and several applications, in these cases you need to use MSSQL as a dbase service, instead you can easily configure it in your Visual Studio, and then other applications already have this database in the list on the toolbar .

0
source

More articles:


All Articles