Capture Stored Procedure Print Output in .Net

Capture Stored Procedure print output in .NET

You can do this by adding an event handler to the InfoMessage event on the connection.

 myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);

    void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
       Console.WriteLine(e.Message);
    }

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

Actually, it does, but since the EF is not SQL Server specific, you have to cast it:

var sqlConn = (SqlConnection)Context.Connection.StoreConnection;

Capture SQL PRINT output in .NET while the SQL is running

It's not that the InfoMessage event is only fired once, but that SQL Server doesn't send PRINT output straight away. You could try using RAISERROR with the NOWAIT option instead;

RAISERROR ('My message', 0, 1) WITH NOWAIT

Using RAISERROR with a severity lower than 11 is not treated as an error condition.

In the .Net you need to set SqlConnection.FireInfoMessageEventOnUserErrors = true to receive the messages straight away. A consequence of this setting is that SQL errors will no longer raise exceptions, they will be info messages instead, so you have to check for real errors in the InfoMessage event handler.

Edit: Complete working example

I've put together a complete working example. I'm a C# guy not a VB guy, sorry about that. Make sure you read up on the FireInfoMessageEventOnUserErrors property of the connection. Here's my simple SP;

Edit 2: Enhanced working example

I've added a check for real errors in the InfoMessage event handler and added a SqlCommand.StatementCompleted event handler to show how 'X rows affected' messages fit in. I've added a lot more messages in the demo SP so it's easier to see which messages are delayed and which are received promptly.

create proc dbo.MyProc as
begin
    declare @t table (i int)
    set nocount off
    print 'This is the 1st PRINT message.'
    waitfor delay '00:00:01'
    -- generate a 'rows affected' info message.
    insert @t values (1)
    waitfor delay '00:00:01'
    print 'This is the 2nd PRINT message.'
    waitfor delay '00:00:01'
    insert @t values (1)
    waitfor delay '00:00:01'
    raiserror('This is the 1st RAISERROR NOWAIT message, severity 1.', 1, 1) with nowait
    waitfor delay '00:00:01'
    insert @t values (1)
    waitfor delay '00:00:01'
    print 'This is the 3rd PRINT message.'
    waitfor delay '00:00:01'
    raiserror('This is the 2nd RAISERROR NOWAIT message, severity 10.', 10, 1) with nowait
    waitfor delay '00:00:01'
    -- generate an error message.
    raiserror('This is the 3rd RAISERROR NOWAIT message, severity 11.', 11, 1) with nowait
    waitfor delay '00:00:01'
    print 'This is the 4th PRINT message.'
    waitfor delay '00:00:01'
    -- generate a single real error.
    declare @i int
    select @i = 1 / 0
    waitfor delay '00:00:01'
    print 'This is the 5th PRINT message.'
    waitfor delay '00:00:01'
    insert @t values (1)
    waitfor delay '00:00:01'
    -- generate multiple real errors from one statement.
    backup log fakedb to disk = 'xxx'
    waitfor delay '00:00:01'
    print 'This is the 6th PRINT message.'
    waitfor delay '00:00:01'
    raiserror('This is the final RAISERROR NOWAIT message, severity 10.', 10, 1) with nowait
    waitfor delay '00:00:01'
    print 'This is the final PRINT message.'
    waitfor delay '00:00:01'
end

Here's my simple console app;

namespace RaiserrorNoWait
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    static class Program
    {
        static void Main()
        {
            using (SqlConnection connection = new SqlConnection(@"Integrated Security=SSPI;Initial Catalog=Scratch;Data Source=DESKTOP-DIR91LS\MSSQL2016"))
            {
                connection.InfoMessage += ConnectionInfoMessage;
                connection.FireInfoMessageEventOnUserErrors = true;
                connection.Open();

                using (SqlCommand command = new SqlCommand("dbo.MyProc", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    Console.WriteLine("{0:T} - Execute query...", DateTime.Now);
                    command.StatementCompleted += CommandStatementCompleted;
                    command.ExecuteNonQuery();
                    Console.WriteLine("{0:T} - Query finished.", DateTime.Now);
                }
                connection.Close();
            }
            Console.WriteLine("Press Enter to Exit.");
            Console.ReadLine();
        }

        // StatementCompleted events and InfoMessage events might not fire in the expected order although 
        // in my very limited testing with SQL Server 2016 and .Net Framework 4.8 they appear to.
        // See https://dba.stackexchange.com/questions/119334/how-can-i-get-individual-rowcounts-like-ssms

        private static void CommandStatementCompleted(object sender, StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine("{0:T} - StatementCompleted: {1} rows affected", DateTime.Now, e.RecordCount);
        }

        private static void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            // The documentation states that e.Errors will always have at least one SqlError.
            // See https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlerrorcollection?view=dotnet-plat-ext-3.1

            // We could assume that when FireInfoMessageEventOnUserErrors is true then the event will be raised once per error
            // but the documentation does not clearly state this so we have to check each SqlError to see if it is a real error. 

            bool isRealError = false;
            byte maxSeverity = 0;
            foreach (SqlError sqlError in e.Errors)
            {
                isRealError = isRealError || sqlError.Class > 10;
                maxSeverity = Math.Max(maxSeverity, sqlError.Class);
            }

            // Here we display errors in red but we could throw an exception or perform some error handling.

            string messageType = isRealError ? "Error" : "Info";
            Console.ForegroundColor = isRealError ? ConsoleColor.Red : ConsoleColor.White;
            Console.WriteLine("{0:T} - {1}: \"{2}\" - max severity: {3}", DateTime.Now, messageType, e.Message, maxSeverity);
        }
    }
}

Running this I get this output, note that RAISERROR NOWAIT and real errors flush any pending info messages through.

02:38:03 - Execute query...
02:38:07 - Info: "This is the 1st PRINT message." - max severity: 0
02:38:07 - StatementCompleted: 1 rows affected
02:38:07 - Info: "This is the 2nd PRINT message." - max severity: 0
02:38:07 - StatementCompleted: 1 rows affected
02:38:07 - Info: "This is the 1st RAISERROR NOWAIT message, severity 1." - max severity: 1
02:38:10 - StatementCompleted: 1 rows affected
02:38:10 - Info: "This is the 3rd PRINT message." - max severity: 0
02:38:10 - Info: "This is the 2nd RAISERROR NOWAIT message, severity 10." - max severity: 0
02:38:11 - Error: "This is the 3rd RAISERROR NOWAIT message, severity 11." - max severity: 11
02:38:18 - Info: "This is the 4th PRINT message." - max severity: 0
02:38:18 - Error: "Divide by zero error encountered." - max severity: 16
02:38:18 - Info: "This is the 5th PRINT message." - max severity: 0
02:38:18 - StatementCompleted: 1 rows affected
02:38:18 - Error: "Database 'fakedb' does not exist. Make sure that the name is entered correctly." - max severity: 16
02:38:18 - Error: "BACKUP LOG is terminating abnormally." - max severity: 16
02:38:18 - Info: "This is the 6th PRINT message." - max severity: 0
02:38:18 - Info: "This is the final RAISERROR NOWAIT message, severity 10." - max severity: 0
02:38:20 - Info: "This is the final PRINT message." - max severity: 0
02:38:20 - Query finished.
Press Enter to Exit.

How to get OUTPUT value from stored procedure in asp.net

string connectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string insertStoredProcName = "dbo.InsertNode";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(insertStoredProcName, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    -- set up paramters - DO NOT USE AddWithValue !!        
    cmd.Parameters.Add("@FName", SqlDbType.VarChar, 100).Value = FName;
    cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 100).Value = LName;
    cmd.Parameters.Add("@CDesc", SqlDbType.VarChar, 100).Value = cDesc;
    cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = pid;

    conn.Open();

    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            -- read all returned values - if you only ever insert
            -- one row at a time, there will only be one value to read
           int insertedId = rdr.GetInt32(0);
        }

        rdr.Close();
    }

    conn.Close();
}

Extract SQL 'print' messages from LINQ ExecuteMethodCall

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.

How to execute stored procedure with Output parameters using FromSqlInterpolated/Database.ExecuteSqlInterpolated in Dot Net Core 3.1?

I have found other way which is working for me

1. Add Nuget package Microsoft.Data.SqlClient

2. Use ExecuteSqlRaw method instead

Below is the code

    int? employeeCount = null;
    string deptName="IT";

    // Use Microsoft.Data.SqlClient namespace for SqlParameter.Visual studio will suggest  "system.data.sqlclient" which does not work
    var deptNameSQLParam = new Microsoft.Data.SqlClient.SqlParameter("@Dept", deptName);
    var employeeCountSQLParam = new Microsoft.Data.SqlClient.SqlParameter("@EmpCount", SqlDbType.Int) { Direction = ParameterDirection.Output }; 
    Database.ExecuteSqlRaw("exec dbo.usp_GetEmpCountByDept @Dept={0}, @EmpCount={1} out", deptNameSQLParam, employeeCountSQLParam);

     if (employeeCountSQLParam.Value != DBNull.Value)
     {
        employeeCount = (int)employeeCountSQLParam.Value;
     }

---

Related Topics