RiCo技術農場

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

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

 

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

返回中文姓名。

 

 

我要在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;

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

 

取得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