存儲過程(sp)在EDM中的使用,相比於sp在EDM中的functionImport,似乎我更關心sp在EDMX文件中的表現.
當我將存儲過程sp GetOrder添加到EDM並完成FunctionImport之後,我就可以打開EDMX文件,查看關於GetOrder在ssdl(),csdl,c-s mapping中的實現:
sdl: <Function Name="GetOrder" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <Parameter Name="OrderID" Type="int" Mode="In" /> </Function>
關於function中的參數:對於IsComposable,BuiltIn這2個參數非常重要,用於指定返回結果將由存儲過程的查詢來返回,不需要生成其它的sql(針對linq,及時不需要被解釋),因為均設為false,其它的沒什麼好說的。
csdl: <FunctionImport Name="GetOrders" EntitySet="Orders" ReturnType="Collection(NorthwindEFModel.Orders)"> <Parameter Name="OrderID" Mode="In" Type="Int32" /> </FunctionImport>
ReturnType是一個Collection,我曾嘗試將其修改為單一實體(讓其返回一個非集合類型),不過在EF v1中並不支持.
如果sp在edmx文件中就僅僅如此,那真的沒什麼可寫了.不過並不僅僅如此,因為除了Return Type之外還有CommandText,out put 參數.
.CommandText
在Function的xml節點下面有CommandText這個標簽,CommandText可以讓我們在ssdl層的function中自定義sql,而非僅僅局限於sp,在某些特定的場景中將sql嵌入ssdl層中要好過sp,畢竟sp是存在數據庫上,這使得不僅要管理應用程序還需要管理數據,function中的CommandText屬性可以2者達到某種平衡.下面是使用CommandText的例子
ssdl:<Function Name="CustomerWithHighestSales" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <!-- cannot use command text with functions. allows to execute multiple statement.--> <CommandText> select * from Customers where CustomerID = ( select top 1 o.CustomerID from Orders o join OrderDetails od on o.OrderID = od.OrderID group by o.CustomerID order by SUM(od.UnitPrice * od.Quantity) desc ) </CommandText> </Function> ssdl中的CommandText內容是找出HighestSales的Customer Csdl: <FunctionImport Name="CustomerWithHighestSales" EntitySet="Customers" ReturnType="Collection(NorthwindEFModel.Customers)"> </FunctionImport> msdl: <FunctionImportMapping FunctionImportName="CustomerWithHighestSales" FunctionName="NorthwindEFModel.Store.CustomerWithHighestSales" />
而使用CustomerWithHighestSales這個方法,和之前的並沒有任何不同.
using (NorthwindEFEntities context = new NorthwindEFEntities()) { var customer = context.CustomerWithHighestSales().First(); Console.WriteLine("{0}", customer.CustomerID); }
.out put 參數,當我一直在調用sp相應的方法是,總是傳入參數,所以我很想嘗試對於帶有out put參數的sp的處理,不過很遺憾的是在EF v1中不直接支持帶有out put參數的sp.當我在數據庫創建一個帶參數的sp getTotolOrdersForCust,在模型浏覽器進行FunctionImport後,我查看edmx文件沒有看到1任何相關getTotolOrdersForCust的內容,後來在EF社區得到了EF v1不支持帶out put參數的sp.當然只是說不直接支持,但 可以通過自定義來解決
Ssdl: <Function Name="getTotolOrdersForCust" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <Parameter Name="CustomerID" Type="nchar" Mode="In" /> <Parameter Name="Count" Type="int" Mode="Inout" /> </Function> Csdl: <FunctionImport Name=" getTotolOrdersForCust "> <Parameter Name="CustomerID" Type="nchar" Mode="In" /> <Parameter Name="Count" Type="int" Mode=="InOut" /></FunctionImport> Msdl: <FunctionImportMapping FunctionImportName="getTotolOrdersForCust" FunctionName=" NorthwindEFModel.Store.getTotolOrdersForCust" />
而剩下的代碼則可以寫成這樣:
public void CustomerWithHighestSales(string customerID, ref int totalOrders) { var dbparams = new DbParameter[] { new EntityParameter { ParameterName = "CustomerID", DbType = DbType.String, Value = customerID }, new EntityParameter { ParameterName = "Count", DbType = System.Data.DbType.Int32, Direction = ParameterDirection.Output } }; DbCommand cmd = this.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddRange(dbparams); cmd.CommandText = this.DefaultContainerName + "." + "getTotolOrdersForCust"; if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open(); } cmd.ExecuteNonQuery(); totalOrders = Convert.ToInt32(dbparams[1].Value); }