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 ~~~