none
c# stored procedure ExecuteNonQuery() return code

    Question

  •  

    Hi,

    I have a stored procedure written in C# that updates entries in the database.
    I am using sql2005 Express version.




    Partial code block:

    SqlCommand.CommandText =
    "UPDATE " + acPointType + " SET " + FieldDef.sFldName +
    " = @data_params WHERE CTRL = '" + acCTRLName +
    "' and name = '" + acPointName + "';";

    ----

    SqlCommand.Connection = conn;
    conn.Open();
    iRet = SqlCommand.ExecuteNonQuery();
    conn.Close();
    -----


    Anyway it all works in a fashion.



    My issue is that whether the db entry exists or not, the return code is zero (0).


    I would like to be able to efficiently determine if the entry needs to be created.

    In the application, the entry would only ever be created once, then updated as often as every second forever ( or the electricity failsSmile)

    Blindly doing an UPDATE evertime sounds like a pain!!!!

    Wednesday, September 10, 2008 8:46 PM

All replies

  • Is this a SQLCLR stored procedure, or standard CLR method in a .NET application?  Either way, I hope that you are doing validation of the data inputs to avoid SQL Injection, and you should also change

     

    WHERE CTRL = '" + acCTRLName +
    "' and name = '" + acPointName + "';";

     

    to use parameters for optimization and plan caching to occur.  The reason I ask if this is a SQLCLR procedure is that you are not using the context connection, so it would seem to me that this is not a SQLCLR stored procedure in SQL Server.

    Wednesday, September 10, 2008 10:39 PM
  •  

    Do not use unparameterized queries at all ! For the sake of dynamic Sql as you are using there might be no other way around (depends on your architecture), but the rest should be parametrized:

     

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = string.Format("UPDATE {0} SET {1} = @data_params WHERE CTRL = @acCTRLName and name = @acPointName", acPointType,FieldDef.sFldName);

                SqlParameter paramacCTRLName = new SqlParameter("@acCTRLName", acCTRLName);
                cmd.Parameters.Add(paramacCTRLName);

                SqlParameter paramacPointName = new SqlParameter("@acPointName", acPointName);
                cmd.Parameters.Add(paramacPointName);

     

    -Jens

    Thursday, September 11, 2008 7:35 AM
  • This is a partial code block ( because the whole thing is pages long ) written in c# and running as a stored procedure in SQL Express.  It is built using Visual Studio 2005 C# and DEPLOYed to SQL Express.

     

    As suggesed could use parameters as opposed to simply concatenating a string and there may be some benefit to that, but this is a separate issue. 

     

    Not included in this partial code snippet is a prior definition declaring a "context connection = true"

     

    The question remains that when doing an UPDATE on an entry in the database table, the retun value from ExecuteNonQuery() is zero when the action succeeds as expected.  If the entry does not exist in the database table, the ExecuteNonQuery() also returns zero and the entry continues to not exist.

     

    What I need is to UPDATE an entry in the database about once every second for years.  I also need to be able to create an entry that does not yet exist.

     

    The only approach that seems to work is to ALWAYS use an INSERT then use an  UPDATE but that is very wasteful since the INSERT is truely only needed once, but I have no prior knowledge method of knowing when that once will occur.

     

    It would be best if the ExecuteNonQuery() would return a failure status if the entry it is trying to UPDATE doesn't exist!

    Thursday, September 11, 2008 1:28 PM
  • ExecuteNonQuery() returns the row count affected, and I just tested this to validate it, for Inserts Updates, and Delete commands against the database. 

     

     

     

    Code Snippet

    using (SqlConnection connection = new SqlConnection("Server=devdb1;Database=dbtest;Trusted_Connection=yes;" ))

    {

    SqlCommand command = new SqlCommand("update test set exists = 0 where email like 'Jon@test.com'", connection);

    command.Connection.Open();

    int i = command.ExecuteNonQuery();

    MessageBox.Show(i.ToString());

    }

     

     

    The above returns 0 since there are no rows that have that email.  Set up with

     

    Code Snippet
    SqlCommand command = new SqlCommand("update test set exists = 0 where email like '%@test.com'", connection);

     

     

    The return is 5 since I have 5 rows in that table with test@test.com as the email. What is the iRet variable's datatype in your C#? 

    Thursday, September 11, 2008 1:48 PM
  • Ok, the entire stored procedure running under SQL 2005 Express.

     

    ExecuteNonQuery() returns zero if the UPDATE succeeds and zero if the entry being UPDATED does not exist!

     

     

     

     

    Code Snippet

     

       [Microsoft.SqlServer.Server.SqlProcedure]
        public static int vciWriteDataCS
            (
      String acPointType,
            String acPointName,
      String acVECName,
      byte[] acDataBuf,
      int iLen,
      int uiOffset   
            )
        {

            int  ioffset;
            int iRet = 1;
            pt_type_table_struct FieldDef;

           
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                using (BinaryReader rdr = new BinaryReader(new MemoryStream( acDataBuf) ) )
                {

                    ioffset = uiOffset;
                    int prevIndex = 0;

                                                          
                        while ((iRet == 1) &&
                                (ioffset < (uiOffset + iLen)))
                        {
                            int iLength_Remaining = iLen - (ioffset - uiOffset);

                            FieldDef = GetObjByOffset(ioffset, acPointType, ref prevIndex, ref iRet);
                                                   
                            if (iRet != 1)
                            {// do nothing
                            }
                            else if (FieldDef.iLen > iLength_Remaining)
                            {// not a complete field at end of buffer
                                iRet = -3;
                            }
                            else if (FieldDef.iLen != 0)
                            {
                                SqlCommand vciSqlCommand = new SqlCommand();

                                if (acPointType.Equals("USs"))
                                {
                                    vciSqlCommand.CommandText =
                                    "UPDATE " + acPointType + " SET " + FieldDef.sFldName +
                                    " = @data_params WHERE name = '" + acPointName + "';";

                                }

                                else
                                {

                                    vciSqlCommand.CommandText =
                                        "UPDATE " + acPointType + " SET " + FieldDef.sFldName +
                                        " = @data_params WHERE VEC = '" + acVECName +
                                        "' and name = '" + acPointName + "';";
                                }

                                switch ((vcidatatype)FieldDef.ivcidatatype)
                                { // read in the binary data!!!!!!
                                    case vcidatatype.eLABEL:
                                    case vcidatatype.eENGUNITS:
                                    case vcidatatype.eFILENAMESIZE:
                                        {
                                            //Encoding ASCIIcode = Encoding.ASCII;

                                            byte[] bbuff = new byte[512];
                                            bbuff = rdr.ReadBytes(FieldDef.iLen);
                                            int i;
                                            int iNullFound = 0;
                                            for (i = 0; i < FieldDef.iLen; i++)
                                            { // data from legacy can have *** after end of string
                                                if (iNullFound == 1)
                                                {
                                                    bbuff[i] = 0; //fill
                                                }
                                                else if (bbuff[i] == '\0')
                                                    iNullFound = 1;
                                            }
                                            //bbuff = rdr.ReadBytes(FieldDef.iLen);
                                            if ((vcidatatype)FieldDef.ivcidatatype != vcidatatype.eFILENAMESIZE )
                                            {
                                                rdr.ReadByte(); // eat the null terminator!!!!
                                                ioffset += 1;// account for the null terminator!!!!
                                            }

                                            //char[] asciiChars = new char[ASCIIcode.GetCharCount(bbuff, 0, bbuff.Length)];
                                            //ASCIIcode.GetChars(bbuff, 0, bbuff.Length, asciiChars, 0);
                                            //string asciiString = new string(asciiChars);
                                                                                   
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.VarBinary));
                                            vciSqlCommand.Parameters["@data_params"].Value = bbuff;
                                            break;
                                        }

                                    case vcidatatype.eINITIALS_SIZE:
                                        {
                                            byte[] bbuff = new byte[512];
                                            bbuff = rdr.ReadBytes(FieldDef.iLen);
                                            rdr.ReadByte(); // eat the null terminator!!!!
                                            ioffset += 1;// account for the null terminator!!!!

                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.NVarChar));
                                            vciSqlCommand.Parameters["@data_params"].Value = bbuff;
                                            break;
                                        }

                                    case vcidatatype.ePW_SIZE:
                                        {
                                            byte[] bbuff = new byte[512];
                                            bbuff = rdr.ReadBytes(FieldDef.iLen);
                                            rdr.ReadByte(); // eat the null terminator!!!!
                                            ioffset += 1;// account for the null terminator!!!!

                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.NVarChar));
                                            vciSqlCommand.Parameters["@data_params"].Value = bbuff;
                                            break;
                                        }

                                    case vcidatatype.eINT16SIZE:
                                    case vcidatatype.eALSET:
                                        {
                                            Int16 ival;
                                            ival = rdr.ReadInt16();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.SmallInt));
                                            vciSqlCommand.Parameters["@data_params"].Value = ival;
                                            break;

                                        }

                                    case vcidatatype.eUINT16SIZE:
                                        {
                                            UInt16 ival;
                                            ival = rdr.ReadUInt16();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.Int));
                                            vciSqlCommand.Parameters["@data_params"].Value = ival;
                                            break;

                                        }

                                    case vcidatatype.ebacnet_MACADRSize:
                                       /*
                                          {
                                            UInt64 ival;
                                            ival = rdr.ReadUInt64();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.BigInt));
                                            vciSqlCommand.Parameters["@data_params"].Value = ival;
                                            break;

                                        }
                                        */

                                    case vcidatatype.eFIDADR:
                                    case vcidatatype.eLANADR:
                                        {
                                            byte[] bbuf = new byte[512];
                                            bbuf = rdr.ReadBytes(FieldDef.iLen);
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.Binary, FieldDef.iLen));
                                            //vciSqlCommand.Parameters["@data_params"]. = bbuf;
                                            vciSqlCommand.Parameters["@data_params"].Value = bbuf;
                                            break;
                                        }

                                    case vcidatatype.eBYTESIZE:
                                        {
                                            byte bval;
                                            bval = rdr.ReadByte();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.TinyInt));
                                            vciSqlCommand.Parameters["@data_params"].Value = bval;
                                            break;
                                        }
                                    case vcidatatype.eFLOATSIZE:
                                        {
                                            float fval = rdr.ReadSingle();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.Real));
                                            vciSqlCommand.Parameters["@data_params"].Value = fval;
                                            break;
                                        }
                                    case vcidatatype.eINT32SIZE:
                                        {
                                            int ival = rdr.ReadInt32();
                                            vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.Int));
                                            vciSqlCommand.Parameters["@data_params"].Value = ival;
                                            break;
                                        }
                                    case vcidatatype.eFILLERBYTES:
                                        {
                                            rdr.ReadBytes(FieldDef.iLen);
                                            break; // eat up empty bytes
                                        }
                                    default: break;
                                }

                                if (FieldDef.ivcidatatype != (Int32)vcidatatype.eFILLERBYTES)
                                { // all but filler
                                    vciSqlCommand.Connection = conn;
                                    conn.Open();
                                    iRet = vciSqlCommand.ExecuteNonQuery();
                                    conn.Close();
                                }
                                // on to next field in binary block of data
                                ioffset += FieldDef.iLen;
                            }
                            else
                            {
                                // didn't find one this time around?
                                // as in someone didn't define a field between 2 good ones?  recurse until we find one!
                                rdr.ReadBytes(1);
                                ioffset++;
                            }
                        } //end while
                   
                }
            }
            //if ( iRet == 0 )
            //    iRet = 1;
            return (iRet);
         }

     

     

    Thursday, September 11, 2008 2:06 PM
  • Ok, my bad, the ExecuteNonQuery() is returning other than 0.

     

    And in the above code, since fixed, the while runs until the return value becomes 0.

     

    Doh!

     

    Using SQL Server Management Studio Express, I now confirm the value from EXecuteNonQuery is being returned from the return() function!

     

     

    But my calling .NET application still gets zero back in the RETURN_VALUE parameter::

     

    (otherwise works as expected)

     

     

    Code Snippet

     

     

    extern int UpdatePoint( ANYTYPE *pt_ripper, ANYTYPE *sy_rec, int itypesize )
    {
     int iret = 1;
     char acType[16];
     char *s = acType;
     
     SqlConnection ^myConnection = ConnectToSQLServer();

     String ^myUpdatePointQuery = "vciWriteDataCS";
     SqlCommand  ^myC = gcnew SqlCommand(myUpdatePointQuery );
     myC->CommandType = CommandType::StoredProcedure;
     myC->Connection = myConnection;

     strcpy_s( acType, DB_bintostr( pt_ripper->id.type ) );
     strcat_s( acType, "s" );

     String ^sType = gcnew String(acType );
     String ^sPtName = gcnew String(pt_ripper->id.label );
     String ^sVECName = gcnew String(sy_rec->id.label );
     
     int iBinaryLength = itypesize - sizeof( RECID );
     Byte ^binarydata = gcnew  Byte[iBinaryLength];
     

        MemoryStream^ memStream = gcnew MemoryStream( iBinaryLength );
     

     unsigned char *ac = (unsigned char *)pt_ripper;
     ac += sizeof( RECID );

     int i;
     for ( i = 0; i < iBinaryLength; i++ )
      memStream->WriteByte(*ac++);
     memStream->Seek(0,SeekOrigin::Begin);

     BinaryReader ^rdr = gcnew BinaryReader(  memStream );

     myC->Parameters->AddWithValue( "@acPointType", sType );
     myC->Parameters->AddWithValue( "@acPointName", sPtName );
     myC->Parameters->AddWithValue( "@acVECName",   sVECName );

     myC->Parameters->Add("@acDataBuf", SqlDbType::Binary, iBinaryLength);
     myC->Parameters["@acDataBuf"]->Value =  rdr->ReadBytes(iBinaryLength);

     myC->Parameters->Add( "@iLen",   SqlDbType::Int );
     i =  iBinaryLength; //DB_pcutypsiz(pt_ripper->id.type);
     myC->Parameters["@iLen"]->Value =   i;

     myC->Parameters->Add( "@uiOffset",   SqlDbType::Int );
     myC->Parameters["@uiOffset"]->Value =    sizeof( RECID );

     int RETURN_VALUE = 0;
     myC->Parameters->Add("@RETURN_VALUE", SqlDbType::Int);
     myC->Parameters["@RETURN_VALUE"]->Direction =  ParameterDirection::ReturnValue;
        myC->Parameters["@RETURN_VALUE"]->Value = RETURN_VALUE;
      
     try
     {
      myC->Connection->Open();
      myC->ExecuteNonQuery();
            iret = ((int)(myC->Parameters["@RETURN_VALUE"]->Value));
      myC->Connection->Close();
     }
     catch(SqlException ^mySqlEx)
     { 
      iret = -1;
      for(int i=0;i<mySqlEx->Errors->Count;i++)
      { // here for debug purposes
      }

     

      myC->Connection->Close();
     }
     return( iret );
    }

     

     

    Thursday, September 11, 2008 3:29 PM
  •  Boyd564852 wrote:

    Ok, my bad, the ExecuteNonQuery() is returning other than 0.

     

    And in the above code, since fixed, the while runs until the return value becomes 0.

     

    Doh!

     

    Using SQL Server Management Studio Express, I now confirm the value from EXecuteNonQuery is being returned from the return() function!

     

     

    But my calling .NET application still gets zero back in the RETURN_VALUE parameter::

     

     

    You answered your problem above.  You are returning the iret, and iret is 0 because you are issuing the return outside the while, so it was last set at 0 since the while exits. The other possible issue would be that you aren't setting the @RETURN_VALUE parameter as an output parameter in your .NET code, you are passing a value of 0 to the parameter from it looks like by reading the code, but I am not completely familiar with the specific language you are using, so I could be wrong.

    Thursday, September 11, 2008 4:43 PM
  • Just run update if exists it updates else doesn't update any thing or create SP to meet your requirement:

    if exists(select stmt) then

    ........

    else

    ........

    Thursday, August 29, 2013 4:23 PM