PetaPoco Varbinary(max) and IdentityColumn

Question

I have problem to get Identity value after inserting row with varbinary(max) column type. Here is the code which I use (according to PetaPoco Page):

 using (var uow = UnitOfWorkFactory.Create())
 {
       var result = uow.Db.Execute(@"INSERT INTO LOG_EXPORT (DateTime, FileName, FileSize, FormatID, Lines, Login, UPO, XMLFile) 
            VALUES (@0, @1, @2, @3, @4, @5, @6, @7)", logExport.DateTime, logExport.FileName, logExport.FileSize, logExport.FormatID, logExport.Lines, logExport.LogExportId, new SqlParameter() { SqlDbType = SqlDbType.VarBinary, Value = DBNull.Value }, new SqlParameter() { SqlDbType = SqlDbType.VarBinary, Value = DBNull.Value });
       uow.Commit();             
       return result;
 }

It returns the number of returned rows. I would like to have the same result as when I use:

var result = uow.Db.Insert(logExport);
uow.Commit(); 
return result;

I have also tried to use:

   var result = uow.Db.ExecuteScalar<int>("SELECT SCOPE_IDENTITY()");
   uow.Commit();  
   return result;

But I got an error. I don't wan't to use "SELECT max()". What is the best solution to do this?

Thanks, Nabu

Answer 1

Are you sure that the number of rows being returned is not the Id of the last inserted record?

You can modify you insert statement to include OUTPUT Inserted.Id as follows:

  var result = db.ExecuteScalar<int>(@"INSERT INTO ... UPO, XMLFile) OUTPUT Inserted.Id  VALUES (@0 ...