I have an application that is currently reading data from a table using the following stored procedure:
CREATE PROCEDURE [dbo].[GetBatchOfEmails] @BatchSize INT AS BEGIN SET NOCOUNT ON; WITH ResultSet AS ( SELECT TOP(@BatchSize) [To], [Subject], [MessageBodyText], [MessageBodyHtml], [From], [ReplyTo], [DateCreated], [EmailId] FROM SendMailQueue WITH (ROWLOCK, READPAST) ORDER BY TableId ) DELETE FROM ResultSet OUTPUT deleted.[To], deleted.[Subject], deleted.MessageBodyHtml, deleted.MessageBodyText, deleted.ReplyTo, deleted.[From], deleted.EmailId, deleted.DateCreated END
As you can see, the stored procedure uses ROWLOCK and READPAST, so the number of rows (controlled by @BatchSize) is safely read by only one connection. After reading the lines are deleted.
Can someone tell me how I can do the same with NHibernate?
source share