如何使用相同的SqlConnection对象在多个SqlCommands中声明和使用T-SQL变量来执行多个插入？

I want to load a list of records given a possibly lengthy list of usernames (anywhere from one to thousands of usernames). Disregard how the name(s) are chosen, and assume they cannot be determined from any existing data in the database. This applies to SQL Server 2005.

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

I specifically want to avoid using a single select statement with thousands of expressions in the where clause, which would result in an excessively lengthy command text for the SqlCommand object (e.g. ...where n='bob000001' or n='bob000002' or ... or n='bob003000'). Sounds reasonable?

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

I have decided to perform the select by populating a simple table variable with the usernames, then performing a select/join between the table variable and the table with the user data.

我决定通过使用用户名填充一个简单的表变量来执行select,然后使用用户数据在表变量和表之间执行select / join。

So, the first thing I need to do is populate the table variable. I have some problems here:

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

• T-SQL syntax prior to SQL Server 2008 is verbose for inserting multiple rows into a table in a single statement, requiring something like multiple selects and union alls.

SQL Server 2008之前的T-SQL语法很简单,只需在单个语句中将多行插入表中,就需要多次选择和联合all。

• Rather than use verbose syntax of SS2005, or even the terse syntax available in SQL Server 2008, I am avoiding lengthy command texts altogether and just using multiple commands over a single connection.

我没有使用SS2005的详细语法,甚至SQL Server 2008中提供的简洁语法,而是完全避免使用冗长的命令文本,只使用单个连接上的多个命令。

• Declaring a table variable in one SqlCommand, produces a "must declare the scalar variable" error when I try to use it in subsequent SqlCommands.

在一个SqlCommand中声明一个表变量,当我尝试在后续的SqlCommands中使用它时,会产生“必须声明标量变量”错误。

• Involving stored procedures in any way may still involve passing huge strings, or may prevent variables from persisting outside the scope of the stored procedure. Assume creating stored procedures is not an option.

以任何方式涉及存储过程可能仍然涉及传递大量字符串,或者可能阻止变量持久存在于存储过程的范围之外。假设创建存储过程不是一种选择。

That third point is really the problem I'm trying to solve now. I've seen examples where people (claim to) successfully declare and use a variable in a single SqlCommand without an error. How can this be achieved when using multiple SqlCommand instances? I read that variables will persist for a single connection across multiple commands. Might involving a transaction help in some way?

第三点是我现在要解决的问题。我已经看过人们(声称)成功声明并在单个SqlCommand中使用变量而没有错误的示例。使用多个SqlCommand实例时如何实现?我读到变量将持续存在于多个命令之间的单个连接。涉及交易可能会以某种方式帮助吗?

Finally, keep in mind that I don't want to use temporary tables; doing so would offer a simple solution, but it also avoids the question I'm asking concerning variables and multiple SqlCommands; however, if you truly think that's the best option, feel free to say so.

最后,请记住我不想使用临时表;这样做会提供一个简单的解决方案,但它也避免了我要问的有关变量和多个SqlCommands的问题;但是,如果你真的认为这是最好的选择,请随时说出来。

Here is a code snippet that shows what's happening:

这是一个代码片段,显示正在发生的事情:

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;
}

Note: I'm aware that an SqlCommand involving parameters internally calls a stored procedure, which would normally prevent persisting variables across multiple SqlCommands, but the first query that declares the table variable doesn't involve parameters (i.e. no reference to command.Parameters.AddWithValue is made), so it should be in scope for later commands that may call stored procedures.

注意:我知道涉及参数的SqlCommand在内部调用存储过程,这通常会阻止跨多个SqlCommands的持久化变量,但是声明表变量的第一个查询不涉及参数(即没有引用command.Parameters。添加了AddWithValue,因此它应该在以后可以调用存储过程的命令的范围内。

Edit: To use a temp table, one just has to change @s to #s and declare @s table" to create table #s, which is nice. One may also want to drop the temp table at the end, but it's not necessary.

编辑:要使用临时表,只需将@s更改为#s并声明@s表“以创建表#s,这很好。有人可能还想在最后删除临时表,但它不是必要。

5 个解决方案

#1

Use a temporary table which persists for the duration of the session/connection (multiple calls). Table variables have scope only for the batch, which is basically one call.

使用临时表,该表在会话/连接期间持续存在(多次调用)。表变量只有批处理的范围,基本上是一个调用。

#2

What's wrong with lengthy command texts? I've executed several kilobyte big query's in one call. SQL2005 supports it, and I think it's better than round-tripping all the time.

冗长的命令文本有什么问题?我在一次通话中执行了几千字节的大查询。 SQL2005支持它,我认为它总是比往返更好。

Why not create the query like this?

为什么不这样创建查询?

select StudentID, FName, LName, [etc.] from Student 
where StudentID in ('bob000001', 'bob000002', [etc.])

#3

Erland's article should address your issues:

Erland的文章应该解决你的问题:

http://www.sommarskog.se/arrays-in-sql-2005.html

#4

Not sure if this is the best solution, but I believe you can batch multiple commands by wrapping them in a BEGIN/END block, and use a single call to run all of them.

不确定这是否是最佳解决方案,但我相信您可以通过将它们包装在BEGIN / END块中来批量处理多个命令,并使用单个调用来运行所有命令。

#5

I dont know how to solve your question, however my alternative approach to your problem would instead be to have a stored procedure that accepted a comma delimited list of ID's, inserted those ID's into a temporary table and then do a "SELECT WHERE IN", for example (altered from another blog):

我不知道如何解决你的问题,但是我对你的问题的另一种方法是让一个存储过程接受逗号分隔的ID列表,将这些ID插入临时表然后执行“SELECT WHERE IN”,例如(从另一个博客改变):

CREATE PROC dbo.SelectStudents
(
    @StudentIdList varchar(8000)
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #StudentIdList (
        StudentId int,
    )

    DECLARE @StudentId varchar(10), @Pos int

    SET @StudentIdList = LTRIM(RTRIM(@StudentIdList)) + ','
    SET @Pos = CHARINDEX(',', @StudentIdList, 1)

    IF REPLACE(@StudentIdList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @StudentId = LTRIM(RTRIM(LEFT(@StudentIdList, @Pos - 1)))
            IF @StudentId <> ''
            BEGIN
                INSERT INTO #StudentIdList (StudentId) VALUES (CAST(@StudentId AS int))
            END
            SET @StudentIdList = RIGHT(@StudentIdList, LEN(@StudentIdList) - @Pos)
            SET @Pos = CHARINDEX(',', @StudentIdList, 1)
        END
    END 

    SELECT  * 
    FROM    dbo.Students
    WHERE   StudentId IN
    (
        SELECT StudentId FROM #StudentIdList
    )
END

You can then call this stored procedure with a comma delimtied list of id's:

然后,您可以使用逗号分隔的id列表来调用此存储过程:

exec dbo.SelectStudents '1,2'

You will need to make sure that your formatted list of id's is no longer than 8000 characters (and make multiple calls to the database if it is) however this method will be far more efficient and requires only a large parameter, not a long command text - making large numbers of trips to the database to insert each of the Id's will be extremely slow.

您需要确保您的格式化id列表不超过8000个字符(如果是,则对数据库进行多次调用)但是这种方法效率更高,只需要一个大参数,而不是长命令文本 - 对数据库进行大量访问以插入每个Id将非常慢。

Similar approaches exist for passing in the list of Id's as Xml (although my preference would probably be to go for the simplicity of delimited strings).

存在类似的方法用于将Id的列表作为Xml传递(尽管我的偏好可能是为了简化分隔的字符串)。

Also, it shoud be possible to use the above technique even if for whatever reason you aren't able to create stored procedures on your database.

此外,即使出于任何原因您无法在数据库上创建存储过程,也可以使用上述技术。