ExecuteNonQuery

ExecuteNonQuery

Postby mlagasio » Fri 15 Apr 2011 14:22

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
mlagasio
 
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Postby Shalex » Mon 18 Apr 2011 15:33

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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby mlagasio » Tue 19 Apr 2011 07:06

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
mlagasio
 
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Postby Shalex » Thu 21 Apr 2011 09:26

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);
        }
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby mlagasio » Thu 21 Apr 2011 13:19

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
mlagasio
 
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Postby Shalex » Fri 22 Apr 2011 12:30

mlagasio wrote: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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 19 May 2011 09:55

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 .
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect Universal