[C#][EF]呼叫SP並輸出參數

[C#][EF]呼叫SP並輸出參數

工作上某個專案使用EF,並且所有資料修改都使用SP來處理,

因個人對TSQL較熟悉,所以自己簡單紀錄使用TSQL方式。

 

SP邏輯很簡單(當然用Function會比較適合)

image

返回中文姓名。

 

 

我要在Action中取得返回的中文姓名和輸出參數,為了處理欄位型別對應問題,

這裡直接套用Reflection Emit ,達到動態建立 assembly、modules or types。


 TypeBuilder builder = CreateTypeBuilder(
                   "MyEMPLOYEEAssembly", "MyEMPLOYEEModule", "MyEMPLOYEE");
            CreateAutoImplementedProperty(builder, "EMPID", typeof(string));
            CreateAutoImplementedProperty(builder, "ENGNAME", typeof(string));
            CreateAutoImplementedProperty(builder, "NAME", typeof(string));
            Type resultType = builder.CreateType(); //runtime 建立 Type

 string empid = "";           
            var engParam = new SqlParameter
            {
                ParameterName = "ENGNAME",
                Value = "Rico Chen"
            };
            dynamic queryResult = db.Database.SqlQuery(
                   resultType, "SELECT EMPID,ENGNAME FROM EMPLOYEE WHERE ENGNAME=@ENGNAME", engParam);

//string empid = db.Database.SqlQuery<string>("SELECT EMPID FROM EMPLOYEE WHERE ENGNAME=@ENGNAME", engParam) 
            //    .FirstOrDefault<string>();
            foreach (dynamic item in queryResult)
            {
                empid = item.EMPID;
                //engname = item.ENGNAME;
            }

            var empidParam = new SqlParameter
            {
                ParameterName = "empid",
                Value = empid,
                Direction = ParameterDirection.Input
            };

            var messageParam = new SqlParameter
            {
                ParameterName = "message",  
                Value="",
                Size=4000,
                Direction = ParameterDirection.Output
            };         

            string empname = "";
            string message = "";         
            empname = db.Database.SqlQuery<string>("exec USP_GetEmpName @empid,@message out", empidParam, messageParam)
                .FirstOrDefault();
            message = (string)messageParam.Value;

image

取得動態查詢的型別屬性。

 

image

取得SP的輸出參數和結果。

 

 

 

參考

Use dynamic type in Entity Framework 4.1 SqlQuery() method
Stored procedures with output parameters using SqlQuery in the DbContext API

Writing SQL queries for entities

ObjectQuery.Parameters Property

ObjectQuery<T> Class

ObjectQuery<T>.GroupBy Method