CODEWITHSUNDEEP
c#sqldapper
Ismail Yilmaz
Ismail Yilmaz

Reputation: 239

Dapper's execute method returns two different results for insert and delete

When i use the Execute method of Dapper for a INSERT stored procedure, if query works successfully, it returns 1 as expected , but when i use it for a DELETE stored procedure, it returns -1 even query works successfully. In this case, success for DELETE stored procedure is "-1"?

 public override int Insert(Language entity)
        {
            var parameters = new DynamicParameters();
            parameters.Add("Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
            parameters.Add("Name", entity.Name, DbType.String);
            parameters.Add("Culture", entity.Culture, DbType.String);
            parameters.Add("Published", entity.Published, DbType.Boolean);
            parameters.Add("DisplayOrder", entity.DisplayOrder, DbType.Int32);
            int numberOfRowsAffected = Connection.Execute("Language_Insert", 
                parameters, commandType: CommandType.StoredProcedure);
            // numberOfRowsAffected = 1
            return parameters.Get<int>("Id");
        }

 public override bool Delete(int primaryKey)
        {
            var parameters = new DynamicParameters();
            parameters.Add("Id", primaryKey, DbType.Int32);
            int numberOfRowsAffected = Connection.Execute("Language_Delete",
                parameters, commandType: CommandType.StoredProcedure);
            // numberOfRowsAffected = -1
            return numberOfRowsAffected > 0;
        }

Upvotes: 2

Views: 2246

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1064114

Dapper can't see inside your stored procedure; anything that is different in these two cases is specific to the internals of the two commands. Ultimately, dapper is just returning the value from ExecuteCommand.

There are a few things that could cause the difference:

  • SET NOCOUNT ON
  • the presence of a TRIGGER (including CASCADE, SET NULL etc constraint / foreign key clauses)
  • additional operations after the INSERT/DELETE

If you want reliable values from this type of operation,you should use an OUTPUT or RETURN parameter instead, set via @@ROWCOUNT.

Upvotes: 2

Prince Jea
Prince Jea

Reputation: 5680

You can try structuring your code like this.

public override bool Delete(int primaryKey)
    {
        var parameters = new DynamicParameters();
        parameters.Add("Id", primaryKey, DbType.Int32);
        parameters.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
        Connection.Execute("Language_Delete",
            parameters, commandType: CommandType.StoredProcedure);

        int b = parameters.Get<int>("@b");
        // Output
        int c = parameters.Get<int>("@c");
        // ReturnValue



    }

Upvotes: 0

Related Questions