Methods of querying multiple tables with stored procedures

Before using the Enterprise Library to read SQL Server returns the dataset without any problems, you can return 1 or more, and you can return to a dataset when you read Oracle, but there was a problem with Oracle returning multiple datasets recently, and after a few turns, we finally found a solution Write it down. Be sure to write it down. Never forget .... The following code is tested under VSS2005+ORACLE10G. To use the Enterprise Library, you must first refer to the: CS file add three references: Code
Using Microsoft.Practices.EnterpriseLibrary.Data;
Using Microsoft.Practices.EnterpriseLibrary.Common;
Using Microsoft.Practices.ObjectBuilder;
1 method to return a result set: Oracle stored procedure: Pl/sql
Create or Replace procedure P_sel_topcount (in_top in number,
Out_count out number,
Cur_out out Sys_refcursor) is
--Query for data that specifies the number of record bars and returns the total number of records
Begin
SELECT COUNT (*) into the out_count from UserInfo; Open Cur_out for
SELECT * from UserInfo where ID < in_top;
End P_sel_topcount; Note: The cursor cur_out in the procedure, legend is that the Enterprise Library in the call to Oracle when returning a result set of the time to know this name cur_out, if the change into something else is not (I tried, changed to another name is really wrong, the Enterprise Library does not know), specifically why, I do not know. C # Calls://CREATE Database objects
Database db = Databasefactory.createdatabase ("Oracle");
Construct cmd
DbCommand dbcmd = db. Getstoredproccommand ("P_sel_topcount");
Adding parameters, and without setting the cursor parameters in the process, the Enterprise Library will automatically find the cursor named "Cur_out" and only recognize the cursor parameter named "Cur_out"
Db.  Addinparameter (dbcmd, "In_top", Dbtype.int32, 10); Input parameters
Db.  Addoutparameter (dbcmd, "Out_count", Dbtype.int32, 4); Output parameters
Get the data here, there's only one DataTable in the DS.
DataSet ds=db. ExecuteDataset (dbcmd);
This.dataGridView1.DataSource = ds. Tables[0];
The above is to return a dataset, and then see how to return more than one dataset, here only to return 2 data sets of code, of course, can also add multiple. (2) methods to return multiple result sets: Oracle stored procedures: Create or Replace procedure P_sel_topcount2 (in_top in number,
Out_count out number,
Cur_out_1 out Sys_refcursor,
Cur_out_2 out Sys_refcursor) is
--Query for data that specifies the number of record bars, and returns a total number of records, returning multiple datasets
Begin
SELECT COUNT (*) into the out_count from UserInfo;
Open cur_out_1 for
SELECT * from UserInfo where ID < in_top;
Open Cur_out_2 for
SELECT * from UserInfo where ID < 5;
End P_sel_topcount2;
C # Call://Construct database
Database db = Databasefactory.createdatabase ("Oracle");
Construct cmd
DbCommand dbcmd = db. Getstoredproccommand ("P_sel_topcount2");
Add parameters
Db. Addinparameter (dbcmd, "In_top", Dbtype.int32, 10);
Db. Addoutparameter (dbcmd, "Out_count", Dbtype.int32, 4);
Note: Here is the key, you must explicitly add two Oracle cursor parameters and indicate output, and then add it to the cmd command object.
OracleParameter oraPara1 = new OracleParameter ("Cur_out_1", oracletype.cursor);
OracleParameter OraPara2 = new OracleParameter ("Cur_out_2", oracletype.cursor); indicated as an output parameter
Orapara1.direction = ParameterDirection.Output;
Orapara2.direction = ParameterDirection.Output; Add parameters to cmd
dbcmd. Parameters.Add (ORAPARA1);
dbcmd. Parameters.Add (ORAPARA2);
To get the data, there are two DataTable in the DS.
DataSet ds=db. ExecuteDataset (dbcmd);
This.dataGridView1.DataSource = ds.   TABLES[1]; Summary: If you want to return multiple results, you must add the parameters of the OracleParameter type.