ExecuteNonQuery and Return Value from SQL Server, C#

Question

I want to provide a web service for users to insert or update some values in a table. The user enters an ID and three parameters. If the ID does not exist in the database I want to return 0, fail or similiar. When I test the code below and provide an ID that doesn't exist the stored procedure return 1 cell (Return Value) with 0 but the status gets set to 1. I guess it is because I use ToString() when I execute the query and it returns 1 cell. So how should I improve the code below?

I have this code in a method:

string status = "";

    SqlParameter[] param = { 
        new SqlParameter("@ID", ID),
        new SqlParameter("@Flag", Flag),
        new SqlParameter("@C_Interval", C_Interval),
        new SqlParameter("@D_Interval", D_Interval)
    };

    status = DatabaseHelper.ExecuteNonQuery("sp_InsertInstruction", param);

return status;

In ExecuteNonQuery I pass on the stored procedure command to the database

// skipped some of the code this example
    status = cmd.ExecuteNonQuery().ToString();
    return status;

My stored procedure looks like:

ALTER PROCEDURE dbo.sp_InsertInstruction
@Flag char(1) = null,
@C_Interval int=null,
@D_Interval int=null,
@ID char(20),

AS

DECLARE @Entity_ID int
SET @Entity_ID = (SELECT ID FROM Entity WHERE ID = @ID)

INSERT INTO Instructions(Flag, C_Interval, D_Interval, Entity_ID)
VALUES (@Flag, @C_Interval, @D_Interval, @Entity_ID)

Thanks in advance.

tobias86 · Accepted Answer · 2011-03-24 06:59:21Z

1

ExecuteNonQuery returns the number of rows affected. In this case, you're always inserting one row.

Change your SP to read something like this:

ALTER PROCEDURE dbo.sp_InsertInstruction
    @Flag char(1) = null,
    @C_Interval int=null,
    @D_Interval int=null,
    @ID char(20),

AS

DECLARE @Entity_ID int
SET @Entity_ID = (SELECT ID FROM Entity WHERE ID = @ID)

IF (@Entity_ID IS NOT NULL)
BEGIN
    INSERT INTO Instructions(Flag, C_Interval, D_Interval, Entity_ID)
    VALUES (@Flag, @C_Interval, @D_Interval, @Entity_ID)
END

I.e. Only insert if the Entity Id indeed exists.

answered Mar 24, 2011 at 6:59

tobias86

5,0291 gold badge23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naveed Butt · Accepted Answer · 2011-03-24 06:35:12Z

0

place this code on the top of the stored procedure

ALTER PROCEDURE dbo.sp_InsertInstruction
@Flag char(1) = null,
@C_Interval int=null,
@D_Interval int=null,
@ID char(20),

AS

    IF NOT EXISTS
    (
        SELECT ID 
        FROM Entity 
        WHERE ID = @ID
    )
    BEGIN
        RETURN 0;
    END

-- Your remaining SQL Code here....

answered Mar 24, 2011 at 6:35

Naveed Butt

2,9177 gold badges33 silver badges58 bronze badges

1 Comment

Nick Over a year ago

Well the return value is 0 but when I use status = cmd.ExecuteNonQuery().ToString(); it get set to 1

Ian Jasper · Accepted Answer · 2011-03-24 07:34:28Z

The returned valued being displayed as "1" is the total rows returned after the execution of your query.

Here is a draft of what you might need to do, this is just an idea on how the data from your procedure is being treated on the code. And I am revising your sql procedure as to return the value that you are expecting in return.

For the Code Behind:

using System.Data.SqlClient;
using System.Data;

public class DBAccess{
    private string strCon = "Data Source=YourServer;Initial Catalog=YourDBName;etc";
    public string GetResult(){

        string strQuery = "Exec YourProcedure Param1";
        SqlCommand cmdSql = new SqlCommand(strQuery);
        SqlConnection conSql = new SqlConnection(strCon);
        conSql.Open();
        cmdSql.Connection=conSql;
        SqlDataReader dreSql = cmdSql.ExecuteReader();
        dreSql.Read();
        // here I'm trying to read the item of the row on the column named Result
        return dreSql["Result"].ToString();

    }
}

Your Procedure:

ALTER PROCEDURE dbo.sp_InsertInstruction   
    @Flag char(1) = null,   
    @C_Interval int=null,   
    @D_Interval int=null,   
    @ID char(20)      AS      

    DECLARE @Entity_ID int   
    SET @Entity_ID = (SELECT ID FROM Entity WHERE ID = @ID)      

    if(@Entity_ID is not null)
        begin
            INSERT INTO Instructions(Flag, C_Interval, D_Interval, Entity_ID)   
            VALUES (@Flag, @C_Interval, @D_Interval, @Entity_ID)  

            -- this will return as a table with the result of 1
            select Result='1'
        end
    else
        begin
            -- this will return as a table with the result of 0
            select Result='0'
        end

Collectives™ on Stack Overflow

ExecuteNonQuery and Return Value from SQL Server, C#

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related