ExecuteNonQuery

[mlagasio]

Hi,

is it possible to return the number of affected rows calling

aUniCommand.ExecuteNonQuery

where aUniCommand is associated to an Oracle Stored procedure (containing only an update statement), without defining an out parameter to return this value, at least on c# side?
With SqlCommand it's possible.
We have see that UniCommand handle implicitly refcursor parameter in Oracle Store Provedure with executescalar and executeReader

Best regard Marco

[Shalex]

dotConnect Universal returns the number of rows affected when working with SQL Server as well.
Could you please give us an example of provider which can do that with Oracle? I have tried dotConnect for Oracle, System.Data.OracleClient, and ODP.NET - they do not return the number of rows affected for stored procedures.

[mlagasio]

Hi,

I agree with you:

when working with SQL Server as well dotConnect Universal returns the number of rows affected

My question is:

when working with Oracle, is there a modality to return the number of rows affected?

From your answer, I could understand that the answer is no.

In this scenario, the c# code to call the SQL stored procedure and the Oracle stored procedure differs for the need to define one more out parameter in the second case. Is it correct?

We have see that UniCommand (or wrapped providers) handle implicitly refcursor parameter in Oracle Store Provedure with executescalar and executeReader. Is there an implicit handling of row affected parameter as well?

Best Regard

[Shalex]

Here are possible workarounds to get the number of rows affected by stored procedure:

• using a PL/SQL block in UniCommand.CommandText -
  DDL

  Code: Select all

  CREATE OR REPLACE PROCEDURE UpdateDept
  IS
  BEGIN
  UPDATE Dept SET loc='asdf' WHERE deptno < 50;
  END;

  C#

  Code: Select all

      using (UniConnection conn = new UniConnection("Provider=Oracle;Data Source=orcl1120;user id=***;password=***;")) {
          conn.Open();
          UniCommand cmd = conn.CreateCommand();
          cmd.CommandText = "begin   updatedept(); :n := SQL%ROWCOUNT;  end;";
          cmd.Parameters.Add("n", UniDbType.Int).Direction = System.Data.ParameterDirection.Output;
          cmd.ExecuteNonQuery();
          int rowsAffected = Convert.ToInt32(cmd.Parameters["n"].Value);
      }

  But the current (3.20.47) build of dotConnect Universal throws "Invalid parameter count." with this code. We have fixed the issue. I will post here when the corresponding build is available for download.
• using a procedure-wrapper -
  DDL

  Code: Select all

  create or replace PROCEDURE wrapper (n out number)
  AS
  BEGIN
    UPDATEDEPT();
    n := SQL%ROWCOUNT;
  END;

  C#

  Code: Select all

      using (OracleConnection conn = new OracleConnection("Data Source=orcl1120;user id=AlexSh;password=AlexSh;")) {
          conn.Open();
          OracleCommand cmd = conn.CreateCommand();
          cmd.CommandText =  "wrapper";
          cmd.CommandType = System.Data.CommandType.StoredProcedure;
          cmd.Parameters.Add("n", OracleDbType.Number).Direction = System.Data.ParameterDirection.Output;
          cmd.ExecuteNonQuery();
          int rowsAffected = Convert.ToInt32(cmd.Parameters["n"].Value);
      }

[mlagasio]

Hi, thanks to you.
I'd want to clarify my scenario.

We have in

-Oracle db a procedure defined by

create or replace
PROCEDURE "INSERT_ORDER"
(
v_SURNAME IN NVARCHAR2 DEFAULT NULL ,
v_NAME IN NVARCHAR2 DEFAULT NULL ,
v_cur OUT Types.Cursor_Type
)
AS
v_orderCode nvarchar2(10);
BEGIN
INSERT INTO ORDERS
( ORDERCODE, SURNAME, NAME)
VALUES ( GENERATEKEYORDER(), v_SURNAME, v_NAME)
returning ORDERCODE into v_orderCode;

OPEN v_cur FOR
SELECT v_orderCode from DUAL;

END;

-in SQL Server db a procedure defined by

CREATE PROCEDURE [Insert_Order]
@v_SURNAME varchar (255),
@v_NAME varchar (255),
AS
BEGIN
insert into ORDER
(ORDERCODE , SURNAME, NAME)
values (GENERATEKEYORDER() ,@v_SURNAME ,@v_NAME)
END

with the following trigger

CREATE TRIGGER [ORDER_INSERT] ON [ORDERS] FOR INSERT
AS
BEGIN
SELECT ORDERCODE FROM INSERTED
END


The following c# code call both sps, without define in c# the cursor parameter.


UniConnection conn = new UniConnection("Data Source=MLAGASIO;Initial Catalog=MyDB;User ID=ML;Password=ML");
conn.Open();

UniCommand cmd = new UniCommand();
cmd.Connection = conn;
cmd.CommandText = "Insert_Order";
cmd.CommandType = CommandType.StoredProcedure;

UniParameter param;

param = cmd.Parameters.Add("@v_SURNAME", UniDbType.VarChar);
param.Direction = ParameterDirection.Input;
param = cmd.Parameters.Add("@v_NAME", UniDbType.VarChar);
param.Direction = ParameterDirection.Input;

int ordercode = (int)cmd.ExecuteScalar();

conn.Close();

conn.Dispose();

In the same way with ExecuteScalar I could obtain the number of affected rows for updating stored procedure (in the ref cursor param I could return as I want).

But the same (that is the handle of a refcursor) perhaps it's not possible with ExecuteNonQuery (in your example the Oracle Stored procedure have one more parameter of integer type defined explicitly
in c# code)?

Best Regard
Marco

[Shalex]

But the same (that is the handle of a refcursor) perhaps it's not possible with ExecuteNonQuery (in your example the Oracle Stored procedure have one more parameter of integer type defined explicitly
in c# code)?

You are right.

[Shalex]

New build of dotConnect Universal 3.20.50 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/univer ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21035 .