NHibernate2.1.1 can finally call the Oracle stored procedure. However, you must use the nhib.pdf. Driver. OracleDataClientDriver Driver (Oracle. DataAc
NHibernate2.1.1 can finally call the Oracle stored procedure. However, you must use the nhib.pdf. Driver. OracleDataClientDriver Driver (Oracle. DataAc
Summary
NHibernate2.1.1 can finally call the stored procedure of Oracle, but you must use nhib.pdf. driver. oracleDataClientDriver Driver (Oracle. dataAccess. dll), but this will cause hbm2ddl. keywords throws an exception, so it is not perfect.
How does nhib.pdf call Oracle stored procedures?
First, create a simple stored procedure in Oracle10g. Its first parameter must be of the SYS_REFCURSOR type.
Stored procedure or replace procedure spCustomer_get_by_name (cur_OUT out SYS_REFCURSOR, customer_name VARCHAR2) is
Begin
Open cur_OUT
Select *
FROM Customer c
WHERE c. firstname LIKE '%' | customer_name;
End spCustomer_get_by_name; the structure of the Customer table is as follows.
Customer table structure table CUSTOMER
(
CUSTOMERID VARCHAR2 (36) not null,
VERSION VARCHAR2 (200 ),
FIRSTNAME VARCHAR2 (200 ),
LASTNAME VARCHAR2 (200 ),
Isdelete number (1 ),
Createtime date,
ESTABLISH_DATE DATE,
Age number (10 ),
Income number (19,4 ),
CUSTOMER_TYPE NUMBER (10)
In Customer. hbm. xml, add the configuration for calling the stored procedure.
Code highlighting produced by Actipro CodeHighlighter (freeware)> SQL-query>
Return alias = "cus">
Return>
{Call spCustomer_get_by_name (: customer_name )}
SQL-query> the C # code for calling the stored procedure is:
Code highlighting produced by Actipro CodeHighlighter (freeware)> ISession session =
IQuery q = session. GetNamedQuery ("SelectCustomerByName ");
Q. SetString ("customer_name", "");
IListCustomer> cList = q. ListCustomer> ();
You can run it now, but it is likely to throw "OracleClientDriver does not support CallableStatement syntax (stored procedures ). consider using OracleDataClientDriver instead. "This exception, if you use" nhib.pdf. driver. oracleClientDriver. The solution is to put"
Nhib.pdf. driver. change OracleClientDriver to "nhib.pdf. driver. oracleDataClientDriver. dataAccess. dll (which can be found in the Oracle client installation directory) is copied to the bin directory of the website.
When you run the program again, the System. Data. Common. DbConnection object of the Oracle. DataAccess. Client. OracleConnection type cannot be forcibly converted to the System. Data. DbConnection type ". "Exception. The exception is "NHibernate \ Tool \ hbm2ddl \ SuppliedConnectionProviderConnectionHelper. cs ". Row 25th: "connection = (DbConnection) provider. GetConnection ();". There is a way to bypass the execution of this line of code, that is, add a line in hibernate. cfg. xml"
None ". But in fact, "hbm2ddl. keywords is a very good feature (if you are interested, you can Google this keyword). You cannot use it as a pity, and you do not know whether there are other functions that will be affected in addition to this, in my heart.
In addition to the above demo, you can also call Oracle Function to obtain data, you can also call the stored procedure to add, delete, modify the operation, please refer to "NHibernate-2.1.1.GA-src \ src \ nhib.pdf. source code under the "Test \ SqlTest \ Custom \ Oracle" directory.