[原创]sql server 读取存储过程中所有结果集比较靠谱的

读取存储过程的所有结果集以及任何返回参数或输出参数 并 读取所有结果集 selet存档到datatable中
首先是别人的回答

using System;
using System.Data;
using System.Data.SqlClient;
 
class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        int departmentId = 1; // 示例部门ID
 
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            using (SqlCommand command = new SqlCommand("usp_GetEmployeeData", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
 
                // 添加输入参数
                command.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int)).Value = departmentId;
 
                // 添加输出参数
                SqlParameter totalEmployeesParameter = new SqlParameter("@TotalEmployees", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(totalEmployeesParameter);
 
                // 执行存储过程并读取结果集
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    do
                    {
                        while (reader.Read())
                        {
                            // 第一个结果集
                            if (reader.FieldCount == 3)
                            {
                                int employeeId = reader.GetInt32(0);
                                string employeeName = reader.GetString(1);
                                string department = reader.GetString(2);
                                Console.WriteLine($"EmployeeID: {employeeId}, Name: {employeeName}, Department: {department}");
                            }
                            // 第二个结果集
                            else if (reader.FieldCount == 2)
                            {
                                int deptId = reader.GetInt32(0);
                                string deptName = reader.GetString(1);
                                Console.WriteLine($"DepartmentID: {deptId}, Name: {deptName}");
                            }
                        }
                    } while (reader.NextResult());
                }
 
                // 获取输出参数的值
                int totalEmployees = (int)totalEmployeesParameter.Value;
                Console.WriteLine($"Total Employees in Department {departmentId}: {totalEmployees}");
            }
        }
    }
}

完整代码2

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
 
class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        int departmentId = 1; // 示例输入参数
 
        List<DataTable> dataTables = new List<DataTable>();
        int? returnValue = null; // 用于存储存储过程的返回值（如果有的话）
        int? outputParameter = null; // 用于存储输出参数（示例）
 
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            using (SqlCommand command = new SqlCommand("usp_GetEmployeeData", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
 
                // 添加输入参数
                command.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int)).Value = departmentId;
 
                // 添加输出参数（示例）
                SqlParameter outputParam = new SqlParameter("@OutputParam", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(outputParam);
 
                // 执行存储过程并获取返回值（如果存储过程有返回值）
                returnValue = (int?)command.ExecuteNonQuery(); // 注意：不是所有存储过程都有返回值，这取决于存储过程的定义
 
                // 执行存储过程并读取结果集
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
                {
                    do
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        dataTables.Add(dataTable);
                    } while (reader.NextResult());
                }
 
                // 获取输出参数的值
                outputParameter = (int?)outputParam.Value;
            }
        }
 
        // 处理返回值和输出参数
        Console.WriteLine($"Return Value: {returnValue}");
        Console.WriteLine($"Output Parameter: {outputParameter}");
 
        // 处理DataTable列表
        foreach (DataTable table in dataTables)
        {
            // 输出DataTable内容到控制台（或进行其他处理）
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.Write($"{row[column]} ");
                }
                Console.WriteLine();
            }
            Console.WriteLine(); // 分隔不同的DataTable
        }
    }
}

但是 这是 某智的回答，我.net core 7.0 是不对的。下面是我的解决代码