C# 无法分离数据库

Question

我有一个创建数据库的类，该数据库将 mdf 文件保存在指定位置。然后它从现有数据库中复制表。然后从 sql 文件创建存储过程。然后在过程完成后分离从一开始创建的数据库。我的问题是我的 detach 方法无法工作，会抛出一个异常，说明数据库正在使用中。我已经妥善处理了我的连接。

这符合我的previous question .

这是我的类(class):

Event
    private void btnFullBackup_Click(object sender, EventArgs e)
            {
                progressBar.Value = 0;   
                lblStatus.Text = "Starting full backup...";
                CreateDB("FULL");
                progressBar.Value = 20;      

                lblStatus.Text = "Copying tables...";
                CopyTables("FULL");
                progressBar.Value = 60;

                lblStatus.Text = "Creating stored procedures...";
                CreateStoredProcedures("FULL");
                progressBar.Value = 70;

                progressBar.Value = 80;

                DetachBackup("FULL");

                lblStatus.Text = "Done";
                progressBar.Value = 100;

                MessageBox.Show("Backup was created successfully", "",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);  
            }

使用的方法:

void CreateDB(string type)
        {
            //define and browse location to save mdf
            lblStatus.Text = "Creating pysical database...";
            FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();            
            folderBrowserDialog.ShowDialog();
            lblStatus.Text = "Checking folder permission...";
            string selectedFolder = folderBrowserDialog.SelectedPath + "\\";
            newBackupLocation = selectedFolder;
            //check permission
            if (WriteAccessToFolder(selectedFolder) == false)
            {
                MessageBox.Show("The folder you have chosen does not have write permission", "Monytron",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                folderBrowserDialog.ShowDialog();
                return;
            }

            //create DB
            lblStatus.Text = "Creating database...";
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var query = GetDbCreationQuery(selectedFolder, type);
            using (var conn = new SqlConnection(connectionString))
            using (var command = new SqlCommand(query, conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    folderBrowserDialog.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    if ((conn.State == ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }
            }
        }
void CopyTables(string backupDBName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var query = CopyQuery(backupDBName + DateTime.Now.ToString("yyyyMMdd"));
            using (var conn = new SqlConnection(connectionString))
            using (var command = new SqlCommand(query, conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    if ((conn.State == ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }
            }            
        }

void CreateStoredProcedures(string type)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd")))
            {
                string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
                Server server = new Server(new ServerConnection(conn));
                try
                {
                    server.ConnectionContext.ExecuteNonQuery(spLocation);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

bool DetachBackup(string backupDBName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var builder = new SqlConnectionStringBuilder(connectionString);
            string serverName = builder.DataSource; 
            string dbName = builder.InitialCatalog;
            try
            {
                Server smoServer = new Server(serverName);
                smoServer.DetachDatabase(backupDBName + DateTime.Now.ToString("yyyyMMdd"), false);
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        }

Answer 1

数据库的连接在大多数情况下在使用后会放在一个池中。这样您就可以使用相同的连接字符串快速重新连接，但另一方面，我怀疑这个连接池会阻止您分离数据库。

你或许可以这样做:

• 在关闭连接之前将use master 作为对数据库的每个查询的最后一条语句，或者
• 修改连接字符串，使其不使用池化 (uid=...; pwd=...; pooling=false;)

希望对您有所帮助。