c# - 在不同的命令中创建和填充临时表

Question

我遇到了一个问题，我在一个 SqlCommand 中创建了一个临时表 #muMapping，并用另一个 SqlCommand 填充它。第二个命令在 cmd.ExecutNonQuery() 上抛出异常，消息为:

Invalid object name '#muMapping'.
Statement(s) could not be prepared.

这两个命令使用相同的 SqlConnection，我开始并指定要使用的事务，这样 SqlCommand 就不会隐式开始并提交事务。

代码:

using (var conn = new SqlConnection(connString))
{
    await OpenConnection(conn);
    var trans = conn.BeginTransaction();

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "CREATE TABLE #muMapping ( mu_id INT NOT NULL PRIMARY KEY, facility NVARCHAR(100) NOT NULL, team NVARCHAR(100) NOT NULL );";
        cmd.Transaction = trans;

        await cmd.ExecuteNonQueryAsync();
    }

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO #muMapping VALUES (@mu, @facility, @team);";
        cmd.Parameters.Add("@mu", SqlDbType.Int);
        cmd.Parameters.Add("@facility", SqlDbType.NVarChar, 100);
        cmd.Parameters.Add("@team", SqlDbType.NVarChar, 100);
        cmd.Transaction = trans;

        cmd.Prepare();

        foreach (var mu in loadData)
        {
            try
            {
                cmd.Parameters[0].Value = mu.Mu;
                cmd.Parameters[1].Value = mu.Facility;
                cmd.Parameters[2].Value = mu.Team;
                await cmd.ExecuteNonQueryAsync(); //Error thrown here
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }
    }
    //Other statements
}

据我所知，#muMapping 表应该在第二个命令中可见。

Answer 1

来自 Create Table

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

因此，创建一个全局临时表(##table_name)

更新

忽略我之前的声明，我在 sql server 2014 上运行了您的代码(几乎没有修改)，它执行得很好!

public static void Bar()
    {

        Task.Run(async () =>
        {

            using (var conn = new SqlConnection("Server=.;Database=Test;Trusted_Connection=True;"))
            {
                await conn.OpenAsync();
                var trans = conn.BeginTransaction();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
                        "CREATE TABLE #muMapping ( mu_id INT NOT NULL PRIMARY KEY, facility NVARCHAR(100) NOT NULL, team NVARCHAR(100) NOT NULL );";
                    cmd.Transaction = trans;

                    await cmd.ExecuteNonQueryAsync();
                }

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO #muMapping VALUES (@mu, @facility, @team);";
                    cmd.Parameters.Add("@mu", SqlDbType.Int);
                    cmd.Parameters.Add("@facility", SqlDbType.NVarChar, 100);
                    cmd.Parameters.Add("@team", SqlDbType.NVarChar, 100);
                    cmd.Transaction = trans;

                    cmd.Prepare();


                    foreach (var i in new[] {1, 2, 3})
                    {
                        try
                        {
                            cmd.Parameters[0].Value = i;
                            cmd.Parameters[1].Value = "f";
                            cmd.Parameters[2].Value = "t";
                            cmd.ExecuteNonQuery(); //Error thrown here
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                            throw;
                        }

                    }
                }
            }
        }).Wait();

        //Other statements
    }

看起来你可能有一个语法错误: Statement(s) Could Not Be Prepared. (SQL Error 37000)