我有一个创建一个保存在指定的位置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;
}
}
谢谢大家了。
