CODEWITHSUNDEEP
c#sqlsql-serverlinqstored-procedures
Cynewulf
Cynewulf

Reputation: 65

Extract SQL 'print' messages from LINQ ExecuteMethodCall

I'm calling a stored procedure via LINQ in a fairly simple manner:

    [Function(Name = "dbo.add_second_override")]
    public int AddSecondOverride(
        [Parameter(DbType = "numeric(10)")] decimal account_id,
        [Parameter(DbType = "numeric(10)")] decimal security_id,
        [Parameter(DbType = "varchar(255)")] string reason,
        [Parameter(DbType = "numeric(10)")] decimal? order_id,
        [Parameter(DbType = "numeric(10)")] decimal current_user)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), new object[] { account_id, security_id, reason, order_id, current_user });

        if ((int)result.ReturnValue != 0)
        {
            string errorDescription = Sysmessages.FirstOrDefault(x => x.Error == (int)result.ReturnValue).Description;
            throw new Exception(errorDescription);
        }

        return (int)result.ReturnValue;
    }

This works fine, but if the stored procedure has a SQL print statement within it, how do I extract this information? E.g.

create procedure dbo.add_second_override
(   
    @account_id numeric(10), 
    @security_id numeric(10), 
    @reason varchar(255) = null output,
    @order_id numeric(10) = null,
    @current_user numeric(10)               
) 
as
begin
    /* Do some other stuff */
    print 'This is a SQL message'
    return 0
end

There used to be a way of retrieving this message with SQLClient but I can't find anything related to this for LINQ.

Note that I am unable to throw an exception within the stored procedure instead of using 'print'. It must pick up the print statements somehow.

Upvotes: 5

Views: 1989

Answers (3)

รัฐพล.James
รัฐพล.James

Reputation: 99

I using ToQueryString() for test my Linq script.

var query = appDbContext.SomeTable.Where(x=>x.someFeild==anyData);
System.Diagnostics.Debug.WriteLine(query.ToQueryString());

Upvotes: 0

Anatoli Klamer
Anatoli Klamer

Reputation: 2638

It took me some time, so just in case someone come across it.

I have my stored procedure that executes ~30minutes. I would like to pass PRINT messages to the user during execution. The issue is MS SQL Servcer has buffer for output messages and to flush this buffer you need to use RAISEERROR('',0,1) - 0,1 is important here. TRY/CATCH is handling messages above 10.

Also i had to set FireInfoMessageEventOnUserErrors to true:

When you set FireInfoMessageEventOnUserErrors to true, errors that were previously treated as exceptions are now handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure.

            var ctx = new EFContext();
            var sqlConnection = (SqlConnection)_ctx.Database.Connection;

            sqlConnection.Open();
            sqlConnection.InfoMessage += (s, a) =>
            {
                Clients.All.notifyAll("MSG from server:" + a.Message);
            };
            sqlConnection.FireInfoMessageEventOnUserErrors = true;//this line will fire InfoMessage on every print or RAISERROR line
            var b = ctx.Database.ExecuteSqlCommand(@"
DECLARE @startTime datetime2(0) = GETDATE();

WHILE (GETDATE() < DATEADD(SECOND, 10, @startTime))
BEGIN

    PRINT 'HELLO FROM SP:'+CONVERT(nvarchar(max), DATEDIFF(SECOND, @startTime, GETDATE()))

    RAISERROR( 'This message will show up right away...',0,1) WITH NOWAIT -- this will flush MS SQL buffer and you can get messages immediately when they occurs
    WAITFOR DELAY '00:00:03';
END");


            Clients.All.notifyAll("Execution finished with result:" + b);

Upvotes: 1

Sean Hosey
Sean Hosey

Reputation: 323

I didn't find anything Linq specific. But taking a queue from this question

Capture Stored Procedure print output in .NET (Different model!)

If you can hook into SqlConnection you can react to the InfoMessage event.

If you are using Entity Framework and DbContext - you can do something like this.

SqlConnection conn = (SqlConnection)context.Database.Connection;
conn.Open();
conn.InfoMessage += (s, e) => Console.WriteLine(e.Message);

As I said, I realize this isn't a Linq specific method - but at least you may be able to achieve your end goal with something like this.

Upvotes: 6

Related Questions