Execute the stored procedure and return the cursor set to convert to list

Entity Framework 4.1 has been used in the project recently, and all statement queries are completed using LINQ and lambda expressions. However, several page queries are complex, when multiple condition summaries and column values are used for judgment, if writing statements using LINQ is rather complicated, it may not be able to achieve the desired results. In the end, you can only select the Oracle stored procedure to complete the process, because you need to input parameters to execute the query and return the result set.

After using EF to call and execute the stored procedure to obtain the cursor set, the experiment fails to be implemented after several days. net client driver or oledb connects to Oracle to call the stored procedure. The final collection can only be dataset or able, and must also be converted to the Object List <>.

Stored Procedure execution method:

View code

 1   ///   <Summary>    2           ///  Execute the Stored Procedure  3           ///   </Summary>   4           ///   <Param name = "storedprocname">  Stored Procedure name  </Param>    5           ///   <Param name = "Parameters">  Stored Procedure Parameters  </Param>    6           ///   <Param name = "tablename">  Name of the table in the dataset result  </Param>    7          ///   <Returns>  Dataset  </Returns>    8           Public   Static Dataset runprocedure ( String Storedprocname, idataparameter [] parameters, String  Tablename)  9   {  10               Using (Oracleconnection connection =New  Oracleconnection (connectionstring ))  11   {  12 Dataset dataset = New  Dataset ();  13   Connection. open ();  14 Oracledataadapter sqlda = New  Oracledataadapter ();  15 Sqlda. selectcommand =Buildquerycommand (connection, storedprocname, parameters );  16   Sqlda. Fill (dataset, tablename );  17   Connection. Close ();  18                   Return  Dataset;  19   }  20 }

Datatable conversion list method:

View code

 1  Class Converthelper <t> Where T: New  ()  2   {  3           ///   <Summary>    4           ///  Reflection and generics  5           ///   </Summary>    6          ///   <Param name = "DT"> </param>    7           ///   <Returns> </returns>    8           Public   Static List <t> Converttolist (datatable DT)  9   {  10     11               //  Define a set  12 List <t> TS = New List <t> ();  13     14               //  Obtain the model type  15 Type type = Typeof  (T );  16               //  Define a Temporary Variable  17               String Tempname = String . Empty;  18               //  Traverse all data rows in the datatable  19               Foreach (Datarow Dr In  DT. Rows)  20   {  21 T = New  T ();  22                   //  Obtain the public attributes of this model. 23 Propertyinfo [] propertys = T. GetType (). getproperties ();  24                   //  Traverse all attributes of the object  25                   Foreach (Propertyinfo pi In  Propertys)  26   {  27 Tempname = pi. Name; //  Assign attribute names to temporary variables 28                       //  Check whether the datatable contains this column (column name = Object attribute name)  29                       If  (Dt. Columns. Contains (tempname ))  30   {  31                           //  Determines whether this property has a setter.  32                           If (! Pi. canwrite) Continue ; // This attribute cannot be written and jumps out directly.  33                           //  Value  34                           Object Value = Dr [tempname];  35                           //  If it is not empty, the property assigned to the object  36                           If (Value! = Dbnull. value)  37 Pi. setvalue (T, value, Null );  38   }  39   }  40                   //  Add an object to a generic set  41   TS. Add (t );  42   }  43     44               Return  TS;  45    46   }  47 }

Call the stored procedure:

View code

 1   Public List <v_test> selsum ( String Startdate, String  Enddate)  2   {  3 Oracleparameter [] parms = {  4                                    New Oracleparameter ( "  P_ybqj_s  " , Oracletype. varchar, 6  ),  5                                     New Oracleparameter ( "  P_ybqj_e  " , Oracletype. varchar, 6  ),  6                                     New Oracleparameter ("  Result_cur  "  , Oracletype. cursor)  7   };  8 Parms [ 0 ]. Value = Startdate;  9 Parms [ 1 ]. Value = Enddate;  10 Parms [ 2 ]. Direction =Parameterdirection. output;  11 Dataset DS = orahelper. runprocedure ( "  Pro_ljhz  " , Parms, "  DS  "  );  12 List <v_test> list1 = converthelper <v_test>. converttolist (Ds. Tables [ 0  ]);  13       Return List1;  14 }

 

Do you know how EF calls the stored procedure!

You are welcome to leave your footprints here for further discussion and sharing ~~~