CommonMethods.ExecuteSql always return 1

[Dee]

The intension is If it returns 1, means inserted. If returns 0, means updated. When I execute the sp in SSMS, it returned 1 or 0 correctly, but C# returnNum always got 1. If I set nocount on in stored procedure, it always got -1. Can someone please help? Thanks.

In stored procedure

stored procedure

if (not exist)
begin
    insert
    return @@ROWCOUNT
end
else
begin
    update
    return 0
end

In C# code

int returnNum = CommonMethods.ExecuteSql('EXEC Procedure1')

 

[Skydiver]

Can you show us the code behind CommonMethods.ExecuteSql()? I've never heard of a class named CommonMethods before.

 

[Dee]

It was user defined.

public class CommonMethods
    {
        internal static Int32 ExecuteSql(string SQL, Dictionary<String, Object> data, string company)
        {          
            using (SqlConnection connString = GetConnectionString(company))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = connString;
                    cmd.CommandText = SQL;
                    cmd.CommandType = CommandType.Text;
                    foreach (var key in data.Keys)
                    {
                        cmd.Parameters.Add(new SqlParameter(key, data[key]));
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
}

 

[Skydiver]

Looks like you just gave us pseudo code in your original post because there is no way that would have compiled if the method takes 3 parameters, but you are passing only one.

Anyway, if you are always getting a 1, then that means your stored procedure is always inserting into the database.

Are you using a local .mdf file? Are you sure that you are not accidentally starting from a fresh database each time you run your code? A lot of beginners are caught by this feature in Visual Studio where its default setting is to copy the template database file into the build output directory everytime the code is compiled.

 

[Skydiver]

Ignore my post #4. I was wrong.

ExecuteNonQuery() should return -1 for stored procedure calls. I'm not sure why it is returning a 1 for you.

SqlCommand.ExecuteNonQuery Method (System.Data.SqlClient)

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

learn.microsoft.com

 

[Skydiver]

Assuming the documentation is wrong, if the ExecuteNonQuery() also returns the number of rows affected even when calling a stored procedure, then you are getting expected results. When you do an insert, 1 row was affected. If you do an update, 1 row is affected. When NO COUNT is set, no count of rows affected is returned and therefore you get -1.

As an aside, I've been seeing some SO responses to question similar to yourself. You need to check the out parameters of the command object after the call to see the return value of stored procedures, apparently. I don't know. I avoid SQL like the plague.

 

[Dee]

I changed my sp to

if (not exist)
begin
    insert
    select @@ROWCOUNT ReturnValue
end
else
begin
    update
    select 0 ReturnValue
end

and C# code to read the 1 or 0 from and dataset, like if (ds.Tables[0].Rows[0]["ReturnValue"].ToString() == "1") .... It served the purples.

Thanks for the responds.

 

[Skydiver]

Seems like a using a sledgehammer trying to kill a fly by using a whole DataSet just to capture one integer value.

 

[Dee]

It may be overkilled. I understand insert or update @@ROWCOUNT is 1, but in the SQL is as below. It returned 0 from SSMS, if update.

else
begin
    update
   return 0
end

 

[Skydiver]

See:

[Solved] Why can't I get output parameter value back from executenonquery? - CodeProject

www.codeproject.com

 

[Dee]

I did the out parament. It was always 1. Again, it was correct in SSMS.

 

[Skydiver]

Are you looking at cmd.Parameters["ReturnValue"].Value or are you looking at the return value of cmd.ExecuteNonQuery()?

 

[Dee]

The return value of cmd.ExecuteNonQuery()

 

[Skydiver]

If you look at the CodeProject link, it's not just a matter of setting the direction of the command paramter, but also reading the command parameter afterwards.