How to get return value from query with Dapper?

Question

I'm trying to get a return value from an insert query using Dapper.

Here's how I try to make it work:

// the query with a "returning" statement
// note : I have a trigger that sets the Id to a new value using the generator IF Id is null...
string SQL = "UPDATE OR INSERT INTO \"MyTable\" (\"Id\", \"Name\") " + "VALUES (@Id, @Name) RETURNING \"Id\"";
using (var conn = new FbConnection(MyConnectionString)) {
    var parameters = new DynamicParameters();
    parameters.Add("Id", null, System.Data.DbType.Int32);
    parameters.Add("Name", "newName", System.Data.DbType.String);
    // --- also add the returned parameters
    parameters.Add("retval", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
    // execute the query with Dapper....
    conn.Execute(SQL, parameters);
    // expecting the new ID here but it is ALWAYS null....!!!
    var newId = parameters.Get<object>("retval"); 
}

Now to make sure my query is ok and not the source of the problem here, I implemented a similar code with my actual connector (Firebird in this case), as follows:

using (var conn = new FbConnection(MyConnectionString)) {
    FbCommand cmd = new FbCommand(SQL, conn);
    cmd.Parameters.Add("Id", null);
    cmd.Parameters.Add("Name", "newName");
    FbParameter pRet = cmd.Parameters.Add("retval", FbDbType.Integer);
    pRet.Direction = ParameterDirection.ReturnValue;
    conn.Open();
    cmd.ExecuteNonQuery();
    // => the new value is NOT null here, it returns the correct id!!
    var newId = Convert.ToInt32(pRet.Value);
    conn.Close();
}

What is my mistake in the Dapper code? Why is one version OK and NOT the other? I've read that Dapper executes ExecuteNonQuery() so I'm not expecting this to be the cause.

Answer 1

The returning clause acts like select, in that it returns data in a results grid. As such, your query should be executed as a query. This also has the advantage that it significantly simplifies the calling code:

var newId = conn.QuerySingle<int>(SQL, new { Id = (int?)null, Name = "newName" });

If you need additional fields, this can be extended to use a custom return type that matches the columns coming back, or a value-tuple. For example:

var row = conn.QuerySingle<MyTable>(SQL, new { Id = (int?)null, Name = "newName" });

or

var row = conn.QuerySingle<(int id, string name)>(SQL, new { Id = (int?)null, Name = "newName" });

-- edit You can access the returned values by

int iVal = row.Result.id;
string sVal = row.Result.name; 

Answer 2

The biggest drawback to Dapper's Execute() is that it returns "number of rows impacted" (by updates, deletes, etc)... even if all occurs in a transaction which, after an error occurred, was cancelled via ROLLBACK. The return-value still holds the impacted-row-number before Rollback, tho the transaction was not committed. Yikes!!

DynamicParameters() was more complex, but worked. But in Moq Tests, I encountered a number of exceptions that I couldn't easily resolve.

My solution (similar to Marc and neggenbe's) followed these steps:

1. In the SQL stored-procedure, return an integer-value via,

SELECT -1    -- 0 for success, -1 for error

note--> SQL-Returns (ie. RETURN(1)) are ignored for some reason.

2. Use Dapper as such,

int result = conn.QueryFirst<int>(SProcName, new { id = req.Id, value = req.Value }, commandType: CommandType.StoredProcedure);

note--> Other commands work as well with differing return types:
           QueryFirst:       result = key/value where value=[return value]
           QueryFirst<int>:  result = integer
           QuerySingle:      Detailed by Marc and neggenbe's answer.

3. Check result appropriately, as the above examples.