在使用 EF 的 ASP.NET MVC 專案中需要跑一段預存程序然後回傳一組不具名型別的資料,
類似
SELECT Student.Name, Class.Name FROM Student INNER JOIN Class ON Student.ClassId = Class.Id
這個 Query 出來的結果就是一個新的型別(既不屬於 Student 也不是 Class),偏偏 EF 的設計就是希望你弄出來的東西是強型別的,而不是像 DataTable 這類的東西(我本來一直天真的以為一定會有可以 Query Result 是 DataSet 或 DataTable)。
感謝 stankovski(連結最下方的回覆) 提供的擴充方法,完成擴充後我們就可以利用 dynamic 型別去存取它動態產生出來的不具名型別物件。
如果不知道擴充方法(Extension method)怎麼實作,可以參考 這篇。
- public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
- {
- TypeBuilder builder = _CreateTypeBuilder(
- "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
- using (System.Data.IDbCommand command = database.Connection.CreateCommand())
- {
- try
- {
- if (database.Connection.State == ConnectionState.Closed)
- database.Connection.Open();
- command.CommandText = sql;
- command.CommandTimeout = command.Connection.ConnectionTimeout;
- foreach (var param in parameters)
- {
- command.Parameters.Add(param);
- }
- using (System.Data.IDataReader reader = command.ExecuteReader())
- {
- var schema = reader.GetSchemaTable();
- foreach (System.Data.DataRow row in schema.Rows)
- {
- string name = (string)row["ColumnName"];
- Type type = (Type)row["DataType"];
- _CreateAutoImplementedProperty(builder, name, type);
- }
- }
- }
- finally
- {
- database.Connection.Close();
- command.Parameters.Clear();
- }
- }
- Type resultType = builder.CreateType();
- return database.SqlQuery(resultType, sql, parameters);
- }
- #region Inner method of DynamicSqlQuery(...)
- private static TypeBuilder _CreateTypeBuilder(
- string assemblyName, string moduleName, string typeName)
- {
- TypeBuilder typeBuilder = AppDomain
- .CurrentDomain
- .DefineDynamicAssembly(new AssemblyName(assemblyName),
- AssemblyBuilderAccess.Run)
- .DefineDynamicModule(moduleName)
- .DefineType(typeName, TypeAttributes.Public);
- typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
- return typeBuilder;
- }
- private static void _CreateAutoImplementedProperty(
- TypeBuilder builder, string propertyName, Type propertyType)
- {
- const string PrivateFieldPrefix = "m_";
- const string GetterPrefix = "get_";
- const string SetterPrefix = "set_";
- // Generate the field.
- FieldBuilder fieldBuilder = builder.DefineField(
- string.Concat(PrivateFieldPrefix, propertyName),
- propertyType, FieldAttributes.Private);
- // Generate the property
- PropertyBuilder propertyBuilder = builder.DefineProperty(
- propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);
- // Property getter and setter attributes.
- MethodAttributes propertyMethodAttributes =
- MethodAttributes.Public | MethodAttributes.SpecialName |
- MethodAttributes.HideBySig;
- // Define the getter method.
- MethodBuilder getterMethod = builder.DefineMethod(
- string.Concat(GetterPrefix, propertyName),
- propertyMethodAttributes, propertyType, Type.EmptyTypes);
- // Emit the IL code.
- // ldarg.0
- // ldfld,_field
- // ret
- ILGenerator getterILCode = getterMethod.GetILGenerator();
- getterILCode.Emit(OpCodes.Ldarg_0);
- getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
- getterILCode.Emit(OpCodes.Ret);
- // Define the setter method.
- MethodBuilder setterMethod = builder.DefineMethod(
- string.Concat(SetterPrefix, propertyName),
- propertyMethodAttributes, null, new Type[] { propertyType });
- // Emit the IL code.
- // ldarg.0
- // ldarg.1
- // stfld,_field
- // ret
- ILGenerator setterILCode = setterMethod.GetILGenerator();
- setterILCode.Emit(OpCodes.Ldarg_0);
- setterILCode.Emit(OpCodes.Ldarg_1);
- setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
- setterILCode.Emit(OpCodes.Ret);
- propertyBuilder.SetGetMethod(getterMethod);
- propertyBuilder.SetSetMethod(setterMethod);
- }
- #endregion
結果取用範例 (隨手打的,Copy Paste 的話要自己檢查一下!)
- string sqlCmd = "sp_myProc @p1, @p2";
- List<SqlParameter> lstOfPars = new List<SqlParameter>() {
- new SqlParameter() {
- Direction = ParameterDirection.Input,
- ParameterName = "p1",
- SqlDbType = SqlDbType.DateTime,
- Value = someDay
- },
- new SqlParameter() {
- Direction = ParameterDirection.Input,
- ParameterName = "p2",
- SqlDbType = SqlDbType.NVarChar,
- Value = someStr
- }
- };
- var myObjLst = new List<MyObj>();
- // 重點
- var lst = _entity.Database.DynamicSqlQuery(sqlCmd, lstOfPars.ToArray());
- foreach (dynamic item in lst)
- {
- MyObj obj = new MyObj();
- obj.Caption = item.Caption;
- obj.Num = Convert.ToInt32(item.Num);
- myObjLst.Add(obj);
- }
ref: stackoverflow
補充
其實你也可以手工建個複雜型別,然後用 entities.Database.SqlQuery
沒有留言:
張貼留言