读取存储过程的所有结果集以及任何返回参数或输出参数 并 读取所有结果集 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 是不对的。下面是我的解决代码
