executenonquery in C #

0

Greetings, I would like to know what value returns me executenonquery in case I do not affect any field, example

        string respuesta = "";
        SqlConnection SqlConexion = new SqlConnection();
        try
        {
            SqlConexion.ConnectionString = Conexion.ConexionPalmComSync;
            SqlConexion.Open();
            SqlCommand SqlComando = new SqlCommand();
            SqlComando.Connection = SqlConexion;
            SqlComando.CommandText = "SP_INGRESAR_CONDUCE";
            SqlComando.CommandType = CommandType.StoredProcedure;

            SqlParameter ParDate = new SqlParameter();
            ParDate.ParameterName = "@date";
            ParDate.SqlDbType = SqlDbType.DateTime;
            ParDate.Value = Conduce.Date;
            SqlComando.Parameters.Add(ParDate);

            SqlParameter ParVendedor = new SqlParameter();
            ParVendedor.ParameterName = "@vendedor";
            ParVendedor.SqlDbType = SqlDbType.VarChar;
            ParVendedor.Size = 10;
            ParVendedor.Value = Conduce.Vendedor;
            SqlComando.Parameters.Add(ParVendedor);

            respuesta = SqlComando.ExecuteNonQuery() == 0 ? "Ya existe en el sistema" : "OK";

        }
        catch (Exception ex)
        {
            respuesta = ex.Message;
        }
        finally
        {
            if (SqlConexion.State == ConnectionState.Open) SqlConexion.Close();
        }
        return respuesta;

I want to know what value it returns to me in case I'm not affected by any row

This is the stored procedure I think it's the procedure

ALTER PROCEDURE [dbo]. [SP_INGRESAR_CONDUCE] @date datetime, @ varchar seller (10) ACE     BEGIN         SET NOCOUNT OFF;

    DECLARE @PALMID VARCHAR(10)
    DECLARE @TRANSNUM VARCHAR(16)
    DECLARE @CODPROD VARCHAR(16)
    DECLARE @DESCRIP VARCHAR(45)
    DECLARE @CANTIDAD INT
    DECLARE @ALMORIGEN VARCHAR(10)
    DECLARE @ALMDESTIONO VARCHAR(10)
    DECLARE @FECTRANS DATETIME
    DECLARE @IDVENDEDOR VARCHAR(10)
    DECLARE @IDAYUDANTE VARCHAR(10)
    DECLARE @IDAYUDANTE2 VARCHAR(10)
    DECLARE @TIPODOCUMENTO VARCHAR(14)
    DECLARE @CODRUTA VARCHAR(14)
    DECLARE @RECIBIDOEN DATETIME
    DECLARE @IPADDRESS VARCHAR(20)
    DECLARE @PROCESADO INT
    DECLARE @ROWID INT
    DECLARE @COD VARCHAR(16)
    DECLARE @VENDEDOR_DEVOLUCION VARCHAR(10)

    DECLARE CUR_INGRESAR_CONDUCE CURSOR
    FOR SELECT * FROM IN_TRANSINV WHERE FECTRANS = @date and IDVENDEDOR = @vendedor

    OPEN CUR_INGRESAR_CONDUCE
    FETCH NEXT FROM CUR_INGRESAR_CONDUCE INTO @PALMID, @TRANSNUM, @CODPROD, @DESCRIP,
    @CANTIDAD, @ALMORIGEN, @ALMDESTIONO, @FECTRANS, @IDVENDEDOR, @IDAYUDANTE, @IDAYUDANTE2,
    @TIPODOCUMENTO, @CODRUTA, @RECIBIDOEN, @IPADDRESS, @PROCESADO, @ROWID

    WHILE(@@FETCH_STATUS = 0)
        BEGIN
                SET @COD = DBO.FT_COD_DEVOLUCION('D0000000')
                SET @VENDEDOR_DEVOLUCION = (SELECT IDVENDEDOR from OUT_CONFIG where PALMID = @PALMID)

                IF NOT EXISTS(SELECT * FROM SITGPIntegration.DBO.RelacionConduce
                                      WHERE TipoDocumento = 'Conduce' and NoDocumento = @TRANSNUM)
                    BEGIN
                        IF NOT EXISTS(SELECT ORDDOCID FROM GPHN.DBO.SVC00700 WHERE ORDDOCID = @TRANSNUM)
                            BEGIN
                                EXEC SP_INSERTAR_CABECERA @TRANSNUM,'','','',1,@FECTRANS,'1900-01-01 00:00:00.000','','','','','','','',@ALMORIGEN,'',@ALMDESTIONO,306130.00000,0,'','','','sa'
                            END
                        ELSE
                            BEGIN
                                EXEC SP_INSERTAR_ERROR_CABEZA  @ALMORIGEN,@TRANSNUM,'Violation of PRIMARY KEY constraint ''PKSVC00700''. Cannot insert duplicate key in object ''dbo.SVC00700''.  The statement has been terminated.',@FECTRANS
                            END
                        IF NOT EXISTS(SELECT ORDDOCID FROM GPHN.DBO.SVC00701 WHERE ORDDOCID = @TRANSNUM)
                            BEGIN                       
                                EXEC SP_INSERTAR_DETALLE  @TRANSNUM,16384,1,@CODPROD,@DESCRIP,'1X1UND',@CANTIDAD,@CANTIDAD,0,0,0,0,0,0,0,'',0,0,'',0,'',0,@ALMORIGEN,'',@ALMDESTIONO,1,1,0,0,''
                            END
                        EXEC SP_INSERTAR_CONDUCE @TRANSNUM,'Conduce',@IDVENDEDOR,@CODRUTA,@FECTRANS,@IDAYUDANTE,@IDAYUDANTE2,'sa',''                        

                        /*DEVOLUCION*/

                        IF NOT EXISTS(SELECT ORDDOCID FROM GPHN.DBO.SVC00700 WHERE ORDDOCID = @COD)
                            BEGIN
                                EXEC SP_INSERTAR_CABECERA @COD ,'','','',1,@FECTRANS,'1900-01-01 00:00:00.000','','','','','','','',@ALMORIGEN,'','SAN ISIDRO',306130.00000,0,'','','','sa'
                            END

                        IF NOT EXISTS(SELECT ORDDOCID FROM GPHN.DBO.SVC00701 WHERE ORDDOCID = @COD)
                            BEGIN
                                EXEC SP_INSERTAR_DETALLE  @COD,16384,1,@CODPROD,@DESCRIP,'1X1UND',@CANTIDAD,@CANTIDAD,0,0,0,0,0,0,0,'',0,0,'',0,'',0,@ALMORIGEN,'','SAN ISIDRO',1,1,0,0,''
                            END 
                        IF NOT EXISTS(SELECT NoDocumento FROM SITGPIntegration.dbo.RelacionConduce WHERE NoDocumento = @COD)
                            BEGIN
                                EXEC SP_INSERTAR_CONDUCE  @COD,'Devolucion',@VENDEDOR_DEVOLUCION,@CODRUTA,@FECTRANS,@IDAYUDANTE,@IDAYUDANTE2,'sa',''                        
                            END
                        FETCH NEXT FROM CUR_INGRESAR_CONDUCE INTO @PALMID, @TRANSNUM, @CODPROD, @DESCRIP,
                        @CANTIDAD, @ALMORIGEN, @ALMDESTIONO, @FECTRANS, @IDVENDEDOR, @IDAYUDANTE, @IDAYUDANTE2,
                        @TIPODOCUMENTO, @CODRUTA, @RECIBIDOEN, @IPADDRESS, @PROCESADO, @ROWID
                    END
                ELSE
                    BEGIN
                        BREAK
                    END
        END
        CLOSE CUR_INGRESAR_CONDUCE
        DEALLOCATE CUR_INGRESAR_CONDUCE
END

GO

    
asked by 31.10.2017 в 13:54
source

2 answers

1
  

I would like to know what value executenonquery returns to me in case I am not affected by any field

As indicated by the SqlCommand page. ExecuteNonQuery () , ExecuteNonQuery returns the number of affected records if the executed statement is a INSERT , UPDATE or DELETE . In the case of all other types of sentences, it returns -1 .

According to that, since what you execute is a stored procedure, it will always return -1 , which has affected records or not.

    
answered by 31.10.2017 в 14:46
0

If you want to know the answer message one of the options is with a variable output:

c #:

string msg = string.empty;
SqlCommand cmd = new SqlCommand(micadena, sqlConn);
cmd.Parameters.AddWithValue("@miparametro", id);
cmd.Parameters.Add("@msg", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output; 

int res = Convert.ToInt32(cmd.ExecuteNonQuery()); 
msg = cmd.Parameters["@MSG"].Value.ToString(); //aqui obtienes el msg devuelto

sql:

,@miparametro int
,@msg As Varchar(300) OUTPUT

--un poco de sql por aqui... bla bla...
SET @msg = 'tu mensaje personalizado desde la query'

in synthesis (in your code):

msg = 'tu mensaje personalizado desde la query'
    
answered by 07.11.2017 в 19:06