如何使用 SqlCommand 返回多个结果集? [英] How do I return multiple result sets with SqlCommand?

问题描述

我可以执行多个查询并只返回一次执行 SqlCommand 的结果吗?

Can I execute multiple queries and return their results executing a SqlCommand just once?

推荐答案

参见SqlDataReader.NextResult (调用 SqlCommand.ExecuteReader):

在读取批处理 Transact-SQL 语句的结果时，将数据读取器推进到下一个结果 [集].

Advances the data reader to the next result [set], when reading the results of batch Transact-SQL statements.

例子:

string commandText = @"SELECT Id, ContactId
FROM dbo.Subscriptions;

SELECT Id, [Name]
FROM dbo.Contacts;";


List<Subscription> subscriptions = new List<Subscription>();
List<Contact> contacts = new List<Contact>();

using (SqlConnection dbConnection = new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;"))
{
    dbConnection.Open();
    using (SqlCommand dbCommand = dbConnection.CreateCommand())
    {
        dbCommand.CommandText = commandText;
        using(SqlDataReader reader = dbCommand.ExecuteReader())
        {
            while(reader.Read())
            {
                subscriptions.Add(new Subscription()
                {
                    Id = (int)reader["Id"],
                    ContactId = (int)reader["ContactId"]
                });
            }

            // this advances to the next resultset 
            reader.NextResult();

            while(reader.Read())
            {
                contacts.Add(new Contact()
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"]
                });
            }
        }
    }
}

其他例子:

• C# 多结果集
• 使用 SqlDataReader 执行返回多个结果集的查询:SqlCommand Select «ADO.Net « C#/CSharp 教程

这篇关于如何使用 SqlCommand 返回多个结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！