讀取存儲過程的所有結果集以及任何返回參數(shù)或輸出參數(shù) 并 讀取所有結果集 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;
 
                // 添加輸入?yún)?shù)
                command.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int)).Value = departmentId;
 
                // 添加輸出參數(shù)
                SqlParameter totalEmployeesParameter = new SqlParameter("@TotalEmployees", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(totalEmployeesParameter);
 
                // 執(zhí)行存儲過程并讀取結果集
                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());
                }
 
                // 獲取輸出參數(shù)的值
                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; // 示例輸入?yún)?shù)
 
        List<DataTable> dataTables = new List<DataTable>();
        int? returnValue = null; // 用于存儲存儲過程的返回值（如果有的話）
        int? outputParameter = null; // 用于存儲輸出參數(shù)（示例）
 
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            using (SqlCommand command = new SqlCommand("usp_GetEmployeeData", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
 
                // 添加輸入?yún)?shù)
                command.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int)).Value = departmentId;
 
                // 添加輸出參數(shù)（示例）
                SqlParameter outputParam = new SqlParameter("@OutputParam", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(outputParam);
 
                // 執(zhí)行存儲過程并獲取返回值（如果存儲過程有返回值）
                returnValue = (int?)command.ExecuteNonQuery(); // 注意：不是所有存儲過程都有返回值希停，這取決于存儲過程的定義
 
                // 執(zhí)行存儲過程并讀取結果集
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
                {
                    do
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        dataTables.Add(dataTable);
                    } while (reader.NextResult());
                }
 
                // 獲取輸出參數(shù)的值
                outputParameter = (int?)outputParam.Value;
            }
        }
 
        // 處理返回值和輸出參數(shù)
        Console.WriteLine($"Return Value: {returnValue}");
        Console.WriteLine($"Output Parameter: {outputParameter}");
 
        // 處理DataTable列表
        foreach (DataTable table in dataTables)
        {
            // 輸出DataTable內(nèi)容到控制臺（或進行其他處理）
            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 是不對的。下面是我的解決代碼