如何使用SqlCommand返回多个结果集？

Z时代
2024-01-10
分类：问答

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

回答：

请参见SqlDataReader.NextResult（通过调用SqlCommand.ExecuteReader返回SqlDataReader ）：

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

例：

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"]
                });
            }
        }
    }
}