c#如何检查数据库是否不忙
本文关键字:数据库 是否 检查 何检查 | 更新日期: 2023-09-27 18:16:03
我的类有几个方法正在进行。第一个是创建数据库,完成了。然后,创建从sql文件读取的存储过程。然后分离该DB。现在看来,我的存储过程查询需要一段时间才能完成,而我的分离方法正在数据库繁忙时被调用。那么如何判断数据库是否空闲呢?异常为"无法分离数据库,因为它当前正在使用"
方法:
void CreateStoredProcedures(string type)
{
string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd"));
try
{
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(spLocation);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
bool DetachBackup(string type)
{
var conn = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("", conn);
command.CommandText = @"sys.sp_detach_db '" + type + DateTime.Now.ToString("yyyyMMdd") + "'";
try
{
conn.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
finally
{
if ((conn.State == ConnectionState.Open))
{
conn.Close();
}
}
return true;
}
点击事件:
private void btnFullBackup_Click(object sender, EventArgs e)
{
lblStatus.Text = "Starting full backup...";
Execute("FULL");
progressBar.Value = 20;
lblStatus.Text = "Copying tables...";
progressBar.Value = 60;
CopyTables("FULL");
progressBar.Value = 70;
lblStatus.Text = "Creating stored procedures...";
CreateStoredProcedures("FULL");
progressBar.Value = 80;
CheckDBSize(newBackupLocation, "FULL");
progressBar.Value = 100;
MessageBox.Show("Backup was created successfully", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
lblStatus.Text = "Done";
progressBar.Value = 0;
if (DetachBackup("FULL") == false)
{
DetachBackup("FULL");
}
}
很可能它在自己的连接上挂起了。sp_detach_db的MSDN https://msdn.microsoft.com/en-CA/library/ms188031.aspx在获取独占访问一节中有以下建议:
USE master;
ALTER DATABASE [DBName] SET SINGLE_USER;
您的DetachBackup方法将连接到master,运行ALTER和sp_detach_db过程。
您没有在CreateStoredProcedures方法中关闭连接。把using语句像我在这里展示的那样放进去,它应该能解决问题。(使用微软的语句说明)
为你的方法试试下面的代码:
void CreateStoredProcedures(string type)
{
string spLocation = File.ReadAllText("CreateStoredProcedures.sql");
using (var conn = new SqlConnection(connectionString + ";database=" + type + DateTime.Now.ToString("yyyyMMdd")))
{
try
{
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(spLocation);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
} // End of using, connection will always close when you reach this point.
}
bool DetachBackup(string type)
{
using (var conn = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(@"sys.sp_detach_db '" + type + DateTime.Now.ToString("yyyyMMdd") + "'", conn);
try
{
conn.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
} // End of using, connection will always close when you reach this point.
return true;
}
您不应该认为它是数据库"繁忙",但是错误消息使用了很好的语言:in use。要确定数据库当前是否正在使用,最准确的方法是通过查询sys.dm_tran_locks
来确定是否有任何会话在特定数据库中有任何锁。下面是一个辅助函数,无论数据库是否正在使用,它都会返回一个bool
:
bool IsDatabaseInUse(string databaseName)
{
using (SqlConnection sqlConnection = new SqlConnection("... your connection string ..."))
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConnection;
sqlCmd.CommandText =
@"select count(*)
from sys.dm_tran_locks
where resource_database_id = db_id(@database_name);";
sqlCmd.Parameters.Add(new SqlParameter("@database_name", SqlDbType.NVarChar, 128)
{
Value = databaseName
});
sqlConnection.Open();
int sessionCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
if (sessionCount > 0)
return true;
else
return false;
}
}
注意:确保您的连接字符串中的初始目录不是您试图使"未使用"的数据库,因为这会将您的当前会话置于数据库的上下文中,不允许该操作完成