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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
neggenbe
  • 1,697
  • 2
  • 24
  • 62
  • You have to read from database to get updated value. You need to execute a Select Query. – jdweng Mar 28 '20 at 12:56
  • 1
    @jdweng not necessarily; the direct ado.net code uses ExecuteNonQuery so this should work similarly – Marc Gravell Mar 28 '20 at 13:21
  • However, I do agree with @jdweng that - reading the documentation on `returning` - this probably *should* be a `Query` usage. It is very unclear to me why the `cmd.ExecuteNonQuery()` version works, frankly. – Marc Gravell Mar 28 '20 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... – neggenbe Mar 28 '20 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` - exact same command, though - not two queries; – Marc Gravell Mar 28 '20 at 15:02
  • @Marc Gravell : The ExecuteNonQuery works. Trying to read the parameter after the query completes is the issue. To read results you need to do a Select Query. – jdweng Mar 28 '20 at 15:46
  • @jdweng no, you don't; `returning` *acts like* `select` (like the `output` clause on SQL Server) - I've installed Firebird to verify this – Marc Gravell Mar 28 '20 at 16:26

2 Answers2

4

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;
neggenbe
  • 1,697
  • 2
  • 24
  • 62
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 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. – Marc Gravell Mar 28 '20 at 16:49
  • I'll give it a shot and let you know if it is working the same. Thx for the valuable feedback anyway ! – neggenbe Mar 28 '20 at 18:50
  • ok updated your answer to make sure it contains also the code to actually READ the returned result. That did it, thanks a million ! – neggenbe Mar 28 '20 at 19:57
  • @neggenbe I still have no idea why the raw ado.net version works; from what I can see: it simply shouldn't. Are you sure it is 100% the same SQL code? There isn't an extra "return" in it? – Marc Gravell Mar 28 '20 at 21:10
  • Absolutely yes. But maybe (that's just a guess here) it has to do with the fact I'm using a command??? – neggenbe Mar 29 '20 at 08:16
  • 1
    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. – Mark Rotteveel Mar 29 '20 at 08:27
  • @MarcGravell : is there a way to extend this if using a grouped insert or update (i.e. when passing a List of items to Dapper's execute method) ? – neggenbe Apr 11 '20 at 11:10
  • 3
    @neggenbe not currently; you could use Select or SelectMany to shim it, though: `var results = source.Select(x => conn.QuerySingle(sql, new {...x...}).ToList();` (SelectMany if using Query instead of QuerySingle) – Marc Gravell Apr 11 '20 at 11:48
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.
Kurt S
  • 21
  • 3