0

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

Kamil
  • 149
  • 1
  • 2
  • 10
  • 1
    Use the `OUTPUT` clause when you do your `INSERT` to select what you want. See http://msdn.microsoft.com/en-us/library/ms177564.aspx – Zer0 Sep 03 '14 at 20:12
  • Thanks for answer! Unfortunatelly neither PetaPoco Db.Execute or Db.Query can handle with OUTPUT clause... – Kamil Sep 03 '14 at 20:41

1 Answers1

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 ...
kagundajm
  • 1,152
  • 1
  • 15
  • 26
  • Yeah! Works perfectly!! I have tried using OUTPUT with @Table variable . That is why it didn't work correctly with PetaPoco. – Kamil Sep 04 '14 at 09:07