c# - 如何在 Dot Net Core 3.1 中使用 FromSqlInterpolated/Database.ExecuteSqlInterpolated 执行带有输出参数的存储过程？

Question

我想在 Dot Net core 3.1 中执行带有输出参数的存储过程。我正在使用 DatabaseFacade 类的 ExecuteSqlInterpolated 扩展方法。

获取员 worker 数的 C# 代码。

string deptName="IT";
int? employeeCount = null;
Database.ExecuteSqlInterpolated($"exec dbo.usp_GetEmpCountByDept {deptName}, {employeeCount} out");

执行后employeeCount为null，-1为返回值。由于有些人请求存储过程代码来重现该问题，我已存储过程如下

CREATE PROCEDURE usp_GetEmpCountByDept
@Dept nvarchar(20),
@EmpCount int Output
AS
BEGIN
SELECT @EmpCount = COUNT(Id)
FROM [dbo].[Employees] 
WHERE Department = @Dept
END

Answer 1

我找到了其他对我有用的方法

1. 添加 Nuget 包 Microsoft.Data.SqlClient

2. 改用 ExecuteSqlRaw 方法

下面是代码

    int? employeeCount = null;
    string deptName="IT";

    // Use Microsoft.Data.SqlClient namespace for SqlParameter.Visual studio will suggest  "system.data.sqlclient" which does not work
    var deptNameSQLParam = new Microsoft.Data.SqlClient.SqlParameter("@Dept", deptName);
    var employeeCountSQLParam = new Microsoft.Data.SqlClient.SqlParameter("@EmpCount", SqlDbType.Int) { Direction = ParameterDirection.Output }; 
    Database.ExecuteSqlRaw("exec dbo.usp_GetEmpCountByDept @Dept={0}, @EmpCount={1} out", deptNameSQLParam, employeeCountSQLParam);

     if (employeeCountSQLParam.Value != DBNull.Value)
     {
        employeeCount = (int)employeeCountSQLParam.Value;
     }