SqlConnection/SqlCommand在关闭和处置后保持数据库在使用中

c#

我正在尝试创建一个临时数据库以在 Xunit 中进行集成测试，但是当我尝试删除临时数据库时，出现错误：

无法删除数据库“TempDatabase_[numbers]”，因为它当前正在使用中。

简单地关闭和处理命令和连接似乎并不能切断它。

这是我失败的测试的精简版：

using System;
using System.Data.SqlClient;
using Xunit;

namespace Test
{
    public class Test_Raw_Spec
    {
        [Fact]
        public void PerformWorkInTemporaryDatabase()
        {
            string connectionStringTemplate = "Data Source=SQLEXPRESS;Initial Catalog={0};Integrated Security=SSPI;Connection Timeout=10";
            int dbNum = (new Random()).Next() % 1000000;
            int tblNum = (new Random()).Next() % 1000000;

            string nameTempDb = $"TempDatabase_{dbNum}";
            string nameTempTable = $"TempTable_{tblNum}";

            var sqlConnection1 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand1 = new SqlCommand($"CREATE DATABASE {nameTempDb}", sqlConnection1);
            sqlConnection1.Open();
            sqlCommand1.ExecuteNonQuery();
            sqlCommand1.Dispose();
            sqlConnection1.Close();
            sqlConnection1.Dispose();

            var sqlConnection2 = new SqlConnection(string.Format(connectionStringTemplate, nameTempDb));
            var sqlCommand2 = new SqlCommand($"CREATE TABLE {nameTempTable}(id int)", sqlConnection2);
            sqlConnection2.Open();
            sqlCommand2.ExecuteNonQuery();
            sqlCommand2.Dispose();
            sqlConnection2.Close();
            sqlConnection2.Dispose();

            var sqlConnection3 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand3 = new SqlCommand($"DROP DATABASE {nameTempDb}", sqlConnection3);
            sqlConnection3.Open();
            sqlCommand3.ExecuteNonQuery();
            sqlCommand3.Dispose();
            sqlConnection3.Close();
            sqlConnection3.Dispose();
        }
    }
}

回答

private void Execute()
{
    using (var connection = new SqlConnection("."))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "CREATE DATABASE [test]";
            command.ExecuteNonQuery();

            connection.ChangeDatabase("test");
            command.CommandText = "CREATE TABLE [dbo].[MyTable] (id int)";
            command.ExecuteNonQuery();
            
            // you must change your db context to drop the database
            connection.ChangeDatabase("master");
            command.CommandText = "DROP DATABASE [test]";
            command.ExecuteNonQuery();
        }
    }
}