1

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.

Improve this question
7
  • You have to read from database to get updated value. You need to execute a Select Query. Commented Mar 28, 2020 at 12:56
  • 1
    @jdweng not necessarily; the direct ado.net code uses ExecuteNonQuery so this should work similarly Commented Mar 28, 2020 at 13:21
  • However, I do agree with @jdweng that - reading the documentation on returning - this probably should be a Query<T> usage. It is very unclear to me why the cmd.ExecuteNonQuery() version works, frankly. Commented Mar 28, 2020 at 13:25
  • Actually, I use this to get SEVERAL values back (new ID and some timestamp for sync stuff) - the goal is not to insert/update data and then make a second query to fetch updated data, which is precisely what RETURNING should be used for, as far as I know... Commented Mar 28, 2020 at 13:47
  • @neggenbe yes, but the question is how it comes back; if this comes back as a results grid, then it should be executed with Query<T> - exact same command, though - not two queries; Commented Mar 28, 2020 at 15:02

2 Answers 2

Reset to default
6

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Side note: I haven't managed to get FbConnection connecting locally to fully look at the differences, but I have got the ISQL tool working to verify the behavior of the returning clause.
I'll give it a shot and let you know if it is working the same. Thx for the valuable feedback anyway !
ok updated your answer to make sure it contains also the code to actually READ the returned result. That did it, thanks a million !
Internally in Firebird, a statement with a RETURNING clause doesn't work like a select (unfortunately). It is a singleton result (row) that is returned immediately after the execute (so there is no cursor you need to fetch from). That probably also accounts for why ExecuteNonQuery works for the direct usage of the Firebird .net provider. Technically it behaves as an (executable) stored procedure with OUT columns.
@neggenbe not currently; you could use Select or SelectMany to shim it, though: var results = source.Select(x => conn.QuerySingle<ReturnType>(sql, new {...x...}).ToList(); (SelectMany if using Query instead of QuerySingle)
|
0

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.
  1. 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.
  1. Check result appropriately, as the above examples.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.