如何将 SQL 输出参数与 FromSqlInterpolated 一起使用?或替代 [英] How to use a SQL output parameter with FromSqlInterpolated? Or alternative

问题描述

使用 ASP.Net 核心 3.0.我的数据库查询有效 - 我可以获得结果集(记录)，但是如何传递/获取输出参数?也想知道有没有办法获取返回值.

Using ASP.Net core 3.0. my Database query works - I can get the result set (records), but how to pass/get the output parameter? Also would like to know if there's a way to get the return value.

我尝试使用不同的方法来调用 SQL 查询，唯一能够工作的是 FromSqlInterpolated，但对不同的方法开放.

I tried using different ways to call the SQL query and the only one I was able to get working was FromSqlInterpolated, but open to different methods.

这段代码对我有用，但我想传递一个可以作为输出参数填充的附加参数(当我通过从 SQL Server 中调用存储过程来测试它时，它可以工作).

This code works for me, but I want to pass an additional parameter that can get populated as an output parameter (which is working when I test it by calling the stored proc from within SQL Server).

var result = _context.Users
        .FromSqlInterpolated($"EXEC mystoredproc  {user.Username} ").AsEnumerable().FirstOrDefault();

我尝试在调用前创建一个变量

I tried creating a variable before the call

string out1 = null;

然后在调用中包含它，但我无法弄清楚语法，我不确定此方法是否支持它.

And then including that in the call but I can't figure out the syntax and I'm not sure if it's supported with this method.

var result = _context.Users
        .FromSqlInterpolated($"EXEC mystoredproc  {user.Username},  OUTPUT {out1}").AsEnumerable().FirstOrDefault();
Console.WriteLine(out1);

希望有人能指出我正确的方向 - 想知道如何使用输出参数以及如何获取返回值，而不仅仅是记录集.谢谢.

Hoping someone can point me in the right direction - would like to know both how to use the output parameter and how to get the return value, not just the record set. Thank you.

推荐答案

接受的答案似乎对我不起作用.但是我终于设法通过使用以下代码使 OUTPUT 参数工作

The answer accepted does not seem to work for me. However I finally manage to have the OUTPUT parameter work by using the following code

 var p0 = new SqlParameter("@userName", {user.Username});
 var p1 = new SqlParameter("@out1", System.Data.SqlDbType.NVarChar, 20) { Direction = System.Data.ParameterDirection.Output };

 string out1 = null;
 var result = _context.Users
    .FromSqlRaw($"EXEC mystoredproc  @userName, @out1 OUTPUT ", p0, p1).AsEnumerable().FirstOrDefault();

 Console.WriteLine(p1.Value); // Contains the new value of the p1 parameter

只是一个小警告，p1 值属性只会在请求被执行时改变(通过 AsEnumerable、Load() 或任何强制 Sql执行)

Just a litte warning, the p1 value property will be changed only when the request will be executed (by an AsEnumerable, Load() or anything that forces the Sql execution)

这篇关于如何将 SQL 输出参数与 FromSqlInterpolated 一起使用?或替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！