下列筆記
都是把 Store Procedure 匯進 Entity 內去使用，該筆記紀錄直接使用 ExecuteSqlCommand 呼叫 Store Procedure 並透過 output 回傳資料
TSQL - Store Procedure
在 AdventureWorks 上建立 SP 來存取 Person.Person Table 並回傳指定資料的全名 (LastName + FirstName)
use AdventureWorks2022
go
CREATE OR ALTER PROCEDURE uspEF_ExecuteSqlCommand_Output
(
@BusinessEntityID int ,
@FullName varchar(100) OUTPUT
)
AS
BEGIN
SELECT
@FullName = TRIM(LastName) + '-' + TRIM(FirstName)
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID
END
C# EF edmx 呼叫 SP
namespace EFSPOutput_ExecuteSqlCommand
{
internal class Program
{
static void Main(string[] args)
{
AdventureWorks2022Entities dbContext = new AdventureWorks2022Entities();
string TSQL = @"EXEC uspEF_ExecuteSqlCommand_Output @BusinessEntityID , @FullName OUTPUT";
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@BusinessEntityID", SqlDbType.Int) { Value = 1 });
var outputPara = new SqlParameter("@FullName", SqlDbType.VarChar, 100)
{
Direction = ParameterDirection.Output
};
parameters.Add(outputPara);
dbContext.Database.ExecuteSqlCommand(TSQL, parameters.ToArray());
Console.WriteLine(outputPara.Value.ToString());
}
}
}
