none
C# 不能分离数据库 RRS feed

  • 问题

  • 我有一个创建一个保存在指定的位置mdf文件数据库中的类。然后将其复制从现有的数据库表。然后创建从SQL文件的存储过程。然后,一旦这个过程完成分离从一开始就创建的数据库。我的问题是,我的分离方法不起作用抛出一个异常 “数据库正在使用中”。

    下面是我用的代码。

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

    谢谢大家了。

    2016年2月2日 12:54

答案

  • 你好,

    ado.net 链接数据库基本上都是通过连接池来连接的,大多数情况下这种方式可以快速地重新连接数据库我猜测应该是连接池阻止你分离数据库

    你可以修改配置文件,关掉连接池,看看能不能解决你的问题。

    uid=...; pwd=...; pooling=false;

    Best regards,

    Cole Wu

    2016年2月3日 5:09
    版主