Question: It is possible with Code First approach to call any Oracle Procedure without result value(s)? All Examples I found was with Functions with result data.
My Example:
Used NuGet packages:
EntityFramework v6.1.1
EntityFramework – Support for store functions for Entity Framework 6.1.1+ code First
Used dotConnect from Oracle v8.4.264.6
Code: Select all
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using CodeFirstStoreFunctions;
using System.Data.Entity.Core.Objects;
public class DatabaseModelContainer : DbContext
{
static DatabaseModelContainer()
{
Database.SetInitializer<DatabaseModelContainer>(null);
}
public DatabaseModelContainer()
: base("Name=DatabaseModelContainer")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
…
modelBuilder.Conventions.Add(new FunctionsConvention<DatabaseModelContainer>("dbo"));
}
[DbFunction("DatabaseModelContainer", "SET_PROD_QUANTITY")]
public ObjectResult<int> SET_PROD_QUANTITY(Nullable<decimal> i_ID, Nullable<decimal> i_POS)
{
var i_IDParameter = i_ID.HasValue ?
new ObjectParameter("I_ID", i_ID) :
new ObjectParameter("I_ID", typeof(decimal));
var i_POSParameter = i_POS.HasValue ?
new ObjectParameter("I_POS", i_POS) :
new ObjectParameter("I_POS", typeof(decimal));
return (((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>(" SET_PROD_QUANTITY", i_IDParameter, i_POSParameter));
}
Example procedure:
Code: Select all
CREATE OR REPLACE PROCEDURE dbo.SET_PROD_QUANTITY (I_ID IN NUMBER, I_POS IN NUMBER) IS
BEGIN
BEGIN
DECLARE
D_PROD_ID VARCHAR2 (15);
D_QUANTITY NUMBER (8, 3);
D_CNT NUMBER (5);
BEGIN
SELECT COUNT (*) INTO D_ANZ FROM PROD_DATA
WHERE ID = I_ID AND POS = I_POS;
IF D_CNT = 1 THEN
SELECT PROD_ID, QUANTITY INTO D_PROD_ID, D_QUANTITY
FROM PROD_DATA
WHERE ID = I_ID AND POS = I_POS;
UPDATE PRODUCT
SET QUANTITY_IN = NVL (QUANTITY_IN, 0) - D_ QUANTITY,
QUANTITY_OUT = NVL (QUANTITY_OUT, 0) + D_QUANTITY
WHERE ID = D_PROD_ID;
END IF;
END;
END;
END;
Regards
Jaroslav