[EF] ExecuteSqlCommand 呼叫 Store Procedure - Output 參數

下列筆記

• [EF] Store Procedure
• [EF] Store Procedure - Output 參數
• [EF] Store Procedure - identity

都是把 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());
        }
    }
}