Article Source: Wei.com Motivation (http://www.weiw.com) What can be visible, you can come to this forum http://www.weiw.com/bbs/ Repost Please indicate the source, thank you. I have recently been a project. In order to improve database security, customers require all database operations to be placed in the stored procedure. If the general update operation, it is easier to implement, it is easy to implement. And I want to return to records, I have trouble, I have checked online, and there are not many articles in this regard. Summary here for your reference. There is no introduction to how to access the Oracle database .NET. There are many related materials, interested friends can see below. How to access the Oracle database using ASP.NET http://www.weiw.com/Article/List.asp?id=649
Oracle's stored procedure returns a recordset, the key is to use the cursor. Regarding the database of the database, everyone is certainly exposed to many, we can make a variety of convenient operations through the Open, Fetch, and Close operation control cursors, I will not be repeated. We now want to introduce the cursor variable. Similar cursors, the cursor variable also points to the current line of a query result set. Different, the cursor variable can be opened for any type of similarity-compatible, not binding to a particular query. With a cursor variable, you can get more convenience in the data extraction of the database.
The first is to establish a table.
CREATE TABLE LIHUAN.BILL_POINTS (POINTS_ID NUMBER (10,0) NOT NULL, CUSTOMER_ID NUMBER (10,0) NOT NULL, BILL_POINT_NO NUMBER (2,0) DEFAULT 1 NOT NULL, CONSTRAINT PK_BILL_POINTS PRIMARY KEY (POINTS_ID)) /
Second, build package create or replace package lihuan.yy_pkg_bill_point_no / * acquire all of the user's billboard * / iStype T_cursor is Ref cursor;
Procedure bill_point_no (p_customer_id bill_points.customer_id% type, re_cursor out t_cursor); end; /
Again, build package bodycreate or replace package body lihuan.yy_pkg_bill_point_no / * acquired all of the user's billboard * / IS
PROCEDURE BILL_POINT_NO (P_CUSTOMER_ID BILL_POINTS.CUSTOMER_ID% TYPE, Re_CURSOR OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN OPEN V_CURSOR FOR select Bill_Point_No from BILL_POINTS where CUSTOMER_ID = P_CUSTOMER_ID; Re_CURSOR: = V_CURSOR; END; END; /
Finally, program calls in .NET. public DataSet Bill_Point_No (string CUSTOMER_ID) // ok {DataSet dataset = new DataSet (); Hashtable HT = new Hashtable (); HT.Add ( "P_CUSTOMER_ID", CUSTOMER_ID); if (RunProcedure ( "Re_CURSOR", OracleType.Cursor, ref dataset, HT, bmsOracleUser "YY_PKG_BILL_POINT_NO.BILL_POINT_NO.", bmsOracleConnectionString)) {;} else {dataset = null;} return dataset;} public bool RunProcedure (string returnParameter, OracleType ParamType, ref DataSet Dataset, Hashtable HT, string ProcedureName, string OracleConnection) {System.Data.OracleClient.OracleConnection dsconnection = new System.Data.OracleClient.OracleConnection (OracleConnection); System.Data.OracleClient.OracleCommand dacommand = new System.Data.OracleClient.OracleCommand (ProcedureName, dsconnection); dsconnection. Open (); dacommand.commandtype = commandtype.storedProcedure; iDictionaryEnumerator Enumerator; enumerator = ht.geeerator (); object value = null; OracleParameter OracleParam; OracleParam = Dacommand.p arameters.Add (new OracleParameter (ReturnParameter, ParamType)); OracleParam.Direction = ParameterDirection.Output; while (Enumerator.MoveNext ()) {Value = Enumerator.Value; OracleParam = dacommand.Parameters.Add (new OracleParameter (Enumerator.Key .ToString (), Value));} OracleDataAdapter ODAdapter = new OracleDataAdapter (dacommand); try {ODAdapter.Fill (Dataset); return true;} catch (System.Exception e) {e.ToString (); return false;} Finally {ht.clear (); dacommand.parameters.clear (); dsconnection.close ();}}
