ExecuteNonQuery alway return -1 when update data

[bungphe]

When i using .net core
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();

retval alway received -1 when update successfully or false

[alexkeh]

I tried reproducing this issue with Beta 2, but did not observe -1 being returned when performing an update. Can you post more of a test case? There may be some particular aspect in your test case in which a bug manifests itself that my sample doesn't?

Here's a snippet of the sample test case I used. I'm connecting to the Oracle HR sample schema.

---

        using (OracleConnection con = new OracleConnection(conString))
        {
            using (OracleCommand cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();

                    //Use the command to display employee names from 
                    // the EMPLOYEES table
                    cmd.CommandText = "update employees set salary=salary+10 where department_id=50";

                    //Execute the command and use DataReader to display the data
                    int rowsUpdated = cmd.ExecuteNonQuery();
                    Console.WriteLine("Number: " + rowsUpdated.ToString());

---

[alexkeh]

Another item to check is what DB server you are using. ODP.NET Core beta supports 11.2 and higher DB versions. I've noticed some customers encountering bugs when using older DB versions, such as 10.2

[alexkeh]

Doing some more research into this issue, there's a known issues with some DBs earlier than 12.2 in which the number of rows affected was not returned. Perhaps you're hitting this problem. It affects ODP.NET Core and managed ODP.NET since the bug lies with the DB, not the provider itself.

[bungphe]

Dear alexkeh,

i used stored procedure to update and delete data. Ti alway received -1 when update,delete successfully

[bungphe]

I post more examples with differents way:
Example 1, not return -1. It return true row number to be delete:

using (OracleConnection con = DataConnection.GetConnection(strConn))
{
using (OracleCommand cmd = con.CreateCommand())
{

                con.Open();

                //Use the command to display employee names from  
                cmd.CommandText = " delete from mis_daily_policy_table where policy_table_id = " + policy_id;
                //Execute the command and use DataReader to display the data
                int rowsUpdated = cmd.ExecuteNonQuery();
                Console.WriteLine("Number: " + rowsUpdated.ToString());
                if (rowsUpdated > 0)
                {
                    con.Close();
                    return true;
                }
                else
                {
                    con.Close();
                    return false;
                }

Example 2, it always return -1 :

using (OracleConnection con = DataConnection.GetConnection(strConn))
            {
               if (OracleHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "PKG_MIS_DAILY_POLICY_TABLE.del_policy",
                         new OracleParameter("v_policy_id", policy_id)
                         ) > 0)
                    {
                        con.Close();
                        return true;
                    }
                    else
                    {
                        con.Close();
                        return false;
                    }

[kiquenet]

Full source Code of your stored procedure? Returns values? El mar, 3 de abril de 2018 17:15, bungphe <notifications@github.com> escribió:

…

I post more examples with differents way: Example 1, not return -1. It return true row number to be delete: using (OracleConnection con = DataConnection.GetConnection(strConn)) { using (OracleCommand cmd = con.CreateCommand()) { con.Open(); //Use the command to display employee names from cmd.CommandText = " delete from mis_daily_policy_table where policy_table_id = " + policy_id; //Execute the command and use DataReader to display the data int rowsUpdated = cmd.ExecuteNonQuery(); Console.WriteLine("Number: " + rowsUpdated.ToString()); if (rowsUpdated > 0) { con.Close(); return true; } else { con.Close(); return false; } Example 2, it always return -1 : using (OracleConnection con = DataConnection.GetConnection(strConn)) { if (OracleHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "PKG_MIS_DAILY_POLICY_TABLE.del_policy", new OracleParameter("v_policy_id", policy_id) ) > 0) { con.Close(); return true; } else { con.Close(); return false; } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#7 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABJYDW8UqbwQ6Is-S5-WB1Auk8UCxGh9ks5tk5IQgaJpZM4Srggt> .

[alexkeh]

@bungphe This is not a bug. What you describe is the documented behavior.

ExecuteNonQuery returns the number of rows affected, for the following:

If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.

If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.

For all other types of statements, the return value is -1.

From the ODP.NET doc: ExecuteNonQuery link.