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

How do I return multiple result sets with SqlCommand?

提问by Doug

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

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

采纳答案by Dave Zych

See SqlDataReader.NextResult(an SqlDataReader is returned from calling SqlCommand.ExecuteReader):

请参阅SqlDataReader.NextResult（从调用SqlCommand.ExecuteReader返回一个 SqlDataReader ）：

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

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

Example:

例子：

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

Other examples:

其他例子：

• C# Multiple Result Sets
• Executing a Query That Returns Multiple Result Sets with SqlDataReader : SqlCommand Select???ADO.Net???C# / CSharp Tutorial

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

回答by Dave Zych

Create a Stored Procedurethat has multiple selects, and fill the DataSet.

创建一个Stored Procedure有多个选择的，并填充DataSet.

using (SqlConnection conn = new SqlConnection(connection))
{
    DataSet dataset = new DataSet();
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand("MyProcedure", conn);
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapter.Fill(dataset);
    return dataset;
}

The returned dataset will have a DataTablein it's Tables array for each select in the stored procedure.

对于DataTable存储过程中的每个选择，返回的数据集将在它的 Tables 数组中包含一个。

回答by Marc Gravell

Tools like "dapper" make this very easy, regardless of whether you use ad-hoc text queries or stored procedures; for example:

无论您是使用即席文本查询还是存储过程，诸如“dapper”之类的工具都使这变得非常容易；例如：

using(var multi = conn.QueryMultiple(sql, args))
{
    var customers = multi.Read<Customer>().AsList(); // first grid
    var regionName = multi.ReadFirstOrDefault<string>(); // second grid
    var addresses = multi.Read<Address>().AsList(); // third grid
    // todo: use those things
}

Individual grids can also be read withoutbuffering (as an open IEnumerable<T>over the reader itself) via the optional parameters to Read[<T>].

也可以通过可选参数 to读取单个网格而无需缓冲（作为IEnumerable<T>读取器本身的打开）Read[<T>]。

回答by Pete Koelbl

This is what i have been using for returning multiple result sets.

这是我一直用于返回多个结果集的方法。

public abstract class BaseRepo
{
    private string _connectionString;

    protected BaseRepo(string connectionString)
    {
        _connectionString = connectionString;
    }

    private SqlConnection GetSqlConnection(int commandTimeout, CommandType commandType, ref SqlCommand sqlCmd)
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
        sqlCmd.Connection = connection;
        sqlCmd.CommandTimeout = commandTimeout;
        sqlCmd.CommandType = commandType;
        return connection;
    }

    protected int ExecuteSql(SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            return sqlCmd.ExecuteNonQuery();
        }
    }

    protected IEnumerable<T> ExecuteSqlReader<T>(Func<IDataRecord, T> CreateObject, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
                return ExecuteReader(CreateObject, reader);
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>> ExecuteSqlReader<T1,T2>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2,  SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>>(result1, result2);
            }
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> ExecuteSqlReader<T1, T2, T3>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2, Func<IDataRecord, T3> CreateObject3, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                var result3 = ExecuteReader(CreateObject3, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>>(result1, result2, result3);
            }
        }
    }

    protected Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>> ExecuteSqlReader<T1, T2, T3, T4>(Func<IDataRecord, T1> CreateObject1, Func<IDataRecord, T2> CreateObject2, Func<IDataRecord, T3> CreateObject3, Func<IDataRecord, T4> CreateObject4, SqlCommand sqlCmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
    {
        using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
        {
            using (var reader = sqlCmd.ExecuteReader())
            {
                var result1 = ExecuteReader(CreateObject1, reader).ToList();
                var result2 = ExecuteReader(CreateObject2, reader).ToList();
                var result3 = ExecuteReader(CreateObject3, reader).ToList();
                var result4 = ExecuteReader(CreateObject4, reader).ToList();
                return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>>(result1, result2, result3, result4);
            }
        }
    }

    private IEnumerable<T> ExecuteReader<T>(Func<IDataRecord, T> CreateObject, SqlDataReader reader)
    {
        while (reader.Read())
        {
            yield return CreateObject(reader);
        }
        reader.NextResult();
    }
}

Then I just inherit it like so:

然后我就这样继承它：

public class ReviewRepo : BaseRepo
{
    public ReviewRepo(string connectionString) : base(connectionString) { }

    public ReviewPageableResult GetAllReviews(string productType, string serviceType, int pageNumber, int itemsPerPage, string sortBy, string sortDirection)
    {
        var parameters = new List<SqlParameter>
        {
            new SqlParameter("ProductRefDescription", productType),
            new SqlParameter("ServiceRefDescription", serviceType),
            new SqlParameter("ZipCodes", "NULL"),
            new SqlParameter("PageNumber", pageNumber),
            new SqlParameter("ItemsPerPage", itemsPerPage),
            new SqlParameter("SortBy", sortBy),
            new SqlParameter("SortDirection", sortDirection)
        };
        var cmd = new SqlCommand("dbo.GetReviews");
        cmd.Parameters.AddRange(parameters.ToArray());
        var results = ExecuteSqlReader(CreateReview, CreateReviewPageableResult, cmd, commandType: CommandType.StoredProcedure);
        var reviewResult = results.Item2.Single();
        reviewResult.Items = results.Item1;
        return reviewResult;
    }

    public ReviewPageableResult GetReviewsByZip(string productType, string serviceType, string zipCodes, int pageNumber, int itemsPerPage, string sortBy, string sortDirection)
    {
        var parameters = new List<SqlParameter>
        {
            new SqlParameter("ProductRefDescription", productType),
            new SqlParameter("ServiceRefDescription", serviceType),
            new SqlParameter("ZipCodes", zipCodes),
            new SqlParameter("PageNumber", pageNumber),
            new SqlParameter("ItemsPerPage", itemsPerPage),
            new SqlParameter("SortBy", sortBy),
            new SqlParameter("SortDirection", sortDirection)
        };
        var cmd = new SqlCommand("dbo.GetReviewsByZipCodes");
        cmd.Parameters.AddRange(parameters.ToArray());
        var results = ExecuteSqlReader(CreateReview, CreateReviewPageableResult, cmd, commandType: CommandType.StoredProcedure);
        var reviewResult = results.Item2.Single();
        reviewResult.Items = results.Item1;
        return reviewResult;
    }

    private Review CreateReview(IDataRecord record)
    {
        return new Review
        {
            PageReviewId = (int)record["PageReviewId"],
            ProductRefId = (Guid)record["ProductRefId"],
            ServiceTypeRefId = Convert.IsDBNull(record["ServiceTypeRefId"]) ? Guid.Empty : (Guid)record["ServiceTypeRefId"],
            TerritoryId = Convert.IsDBNull(record["TerritoryId"]) ? Guid.Empty : (Guid)record["TerritoryId"],
            FirstName = $"{record["FirstName"]}",
            LastName = $"{record["LastName"]}",
            City = $"{record["City"]}",
            State = $"{record["State"]}",
            Answer = $"{record["Answer"]}",
            Rating =(double)record["Rating"],
            SurveyDate = (DateTime)record["SurveyDate"]
        };
    }

    private ReviewPageableResult CreateReviewPageableResult(IDataRecord record)
    {
        return new ReviewPageableResult
        {
            AverageRating = (double)record["AverageRating"],
            Count1Stars = (int)record["Count1Stars"],
            Count2Stars = (int)record["Count2Stars"],
            Count3Stars = (int)record["Count3Stars"],
            Count4Stars = (int)record["Count4Stars"],
            Count5Stars = (int)record["Count5Stars"],
            ItemsPerPage = (int)record["ItemsPerPage"],
            PageNumber = (int)record["PageNumber"],
            TotalCount = (int)record["TotalCount"],
        };
    }
}

回答by Stephen Himes

I call a sproc and get multiple result sets with object, so you end up with a

我调用一个 sproc 并用对象获得多个结果集，所以你最终得到一个

List<List<Dictionary<string, object>>>

In the multiResultsSet each results set is then

在 multiResultsSet 中，每个结果集都是

List<Dictionary<string, object>>

They can be cast to their types and transformed into the models as needed.

它们可以转换为它们的类型并根据需要转换为模型。

After you set up the sproc command with everything needed, pass it to this:

使用所需的一切设置 sproc 命令后，将其传递给：

    private static List<List<Dictionary<string, object>>> ProcessReader(SqlCommand command)
    {
        var tables = new List<List<Dictionary<string, object>>>();
        using (var reader = command.ExecuteReader())
        {
            do
            {
                var table = new List<Dictionary<string, object>>();
                while (reader.Read())
                    table.Add(Read(reader));
                tables.Add(table);
            } while (reader.NextResult());
        }
        return tables;
    }

and Read() is fairly straight forward.

而 Read() 是相当直接的。

    private static Dictionary<string, object> Read(IDataRecord reader)
    {
        var row = new Dictionary<string, object>();
        for (var i = 0; i < reader.FieldCount; i++)
        {
            var val = reader[i];
            row[reader.GetName(i)] = val == DBNull.Value ? null : val;
        }
        return row;
    }

回答by Mohmd Nader

Try this

尝试这个

Dim dt1, dt2, dt3As New DataTable
    Dim command As SqlCommand
    Dim adapter As New SqlDataAdapter
    Dim ds As New DataSet
    Dim Sql1, Sql2, Sql3 As String

    Sql1 = "select id, CurName from Table1 where IS_Deleted=0 order by id"
    Sql2 = "select id ,Item from Table2 order by id"
    Sql3 = "select id ,SellPrice from Table3 order by id"

    Try

        conn1.Open()
        command = New SqlCommand(Sql1, conn1)
        command.CommandType = CommandType.Text
        adapter.SelectCommand = command
        adapter.Fill(ds, "dt1")

        adapter.SelectCommand.CommandText = Sql2
        adapter.Fill(ds, "dt2")

        adapter.SelectCommand.CommandText = Sql3
        adapter.Fill(ds, "dt3")


        adapter.Dispose()
        command.Dispose()
        conn1.Close()


        cmbCurrency.DataSource = ds.Tables("dt1") 
        cmbCurrency.DisplayMember = "CurName"
        cmbCurrency.ValueMember = "id"
        cmbCurrency.SelectedIndex = -1 
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

        cmbGroups.DataSource = ds.Tables("dt2") 
        cmbGroups.DisplayMember = "Item"                                    
        cmbGroups.ValueMember = "id"                                        
        cmbGroups.SelectedIndex = -1                                        
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

        cmbUnits.DataSource = ds.Tables("dt3")  
        cmbUnits.DisplayMember = "SellPrice" 
        cmbUnits.ValueMember = "id" 
        cmbUnits.SelectedIndex = -1                                         
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|

    Catch ex As Exception
       MessageBox.Show(ex.ToString())
    End Try

For More Help http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

如需更多帮助http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

回答by BobSpring

I am using a dictionary approach. You can use Newton Json to convert it to json. This way you are not tied to types and IDataRecord

我正在使用字典方法。您可以使用 Newton Json 将其转换为 json。这样你就不会被绑定到类型和 IDataRecord

public List<List<Dictionary<string, object>>> ExecuteSqlReader(string cmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
        {
           var sqlCmd = new SqlCommand(cmd);
           var allRecord = new List<List<Dictionary<string, object>>>();
            using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
            {
                using (var reader = sqlCmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        var result = new List<Dictionary<string, object>>();
                        while (reader.Read())
                        {
                            result = GetTableRowData(reader);
                        }

                        allRecord.Add(result);
                    }
                    while (reader.NextResult())
                    {
                        if (reader.HasRows)
                        {
                            var result = new List<Dictionary<string, object>>();
                            while (reader.Read())
                            {
                                result = GetTableRowData(reader);
                            }
                            allRecord.Add(result);
                        }
                    }
                }
            }
            return allRecord;
        }