.net - 如何使用同一个 SqlConnection 对象在多个 SqlCommand 中声明和使用 T-SQL 变量来执行多次插入？

Question

我想加载一个记录列表，给定可能很长的用户名列表(从一个到数千个用户名)。忽略名称的选择方式，并假设无法从数据库中的任何现有数据确定它们。这适用于 SQL Server 2005。

我特别想避免使用在where子句中包含数千个表达式的单个选择语句，这会导致过长SqlCommand 对象的命令文本(例如 ...其中 n='bob000001' 或 n='bob000002' 或 ... 或 n='bob003000')。听起来有道理吗？

我决定通过用用户名填充一个简单的表变量来执行选择，然后在表变量和带有用户数据的表之间执行选择/连接。

所以，我需要做的第一件事是填充表变量。我这里有一些问题:

• SQL Server 2008 之前的 T-SQL 语法对于在单个语句中向表中插入多行而言非常冗长，需要类似于 multiple selects and union alls 的内容。 .
• 我没有使用 SS2005 的详细语法，甚至没有使用 SQL Server 2008 中的简洁语法，而是完全避免使用冗长的命令文本，而只是在单个连接上使用多个命令。
• 在一个 SqlCommand 中声明表变量，当我尝试在后续 SqlCommand 中使用它时，会产生“必须声明标量变量”错误。
• 以任何方式涉及存储过程仍可能涉及传递巨大的字符串，或者可能会阻止变量在存储过程的范围之外持续存在。假设创建存储过程不是一个选项。

第三点确实是我现在正在努力解决的问题。我见过一些例子，人们(声称)在单个 SqlCommand 中成功声明和使用变量而没有错误。使用多个 SqlCommand 实例时如何实现这一点？我读到变量将在多个命令的单个连接中持续存在。涉及交易可能会以某种方式有所帮助吗？

最后，请记住，我不想使用临时表；这样做将提供一个简单的解决方案，但它也避免了我提出的有关变量和多个 SqlCommand 的问题；但是，如果您确实认为这是最好的选择，请随意说出来。

以下代码片段显示了正在发生的情况:

public static List<Student> Load( SqlConnection conn, List<StudentID> usernames )
{
    //Create table variable
    SqlCommand  command = new SqlCommand( "declare @s table (id varchar(30))", conn );
    command.ExecuteNonQuery();

    //Populate a table variable with the usernames to load
    command = new SqlCommand( "insert into @s (id) values (@p)", conn );
    command.Parameters.Add( "@p", SqlDbType.VarChar );
    int len = usernames.Count;
    for (int i = 0; i < len; i++)
    {
        command.Parameters["@p"].Value = usernames[i].ToString();
        command.ExecuteNonQuery(); //ERROR: must declare scalar variable @s
    }

    //Select all students listed in the table variable
    command = new SqlCommand( "select StudentID, FName, LName, [etc.] from Student inner join @s on StudentID = @s.id order by StudentID", conn );

    //Execute the query to get the student info from the database.
    List<Student> students = new List<Student>()
    using(SqlDataReader reader = command.ExecuteReader())
    {
        //code to load results and populate students list
    }
    return students;
}

注意:我知道涉及参数的 SqlCommand 在内部调用存储过程，这通常会阻止跨多个 SqlCommand 持久保存变量，但声明表变量的第一个查询不涉及参数(即不引用命令) .Parameters.AddWithValue 已创建)，因此它应该在可能调用存储过程的后续命令的范围内。

编辑:要使用临时表，只需将 @ 更改为 # 并将 declare @s table"更改为 create table #s，这很好。人们可能还想在最后删除临时表，但这不是必需的。

Answer 1

使用在 session /连接(多次调用)期间持续存在的临时表。表变量的作用域仅适用于批处理，这基本上是一次调用。