Get Message Info Of SQL Server In C#

Today I am going to explain you how to get message information of SQL Server in C#.You must have a question,Why this would be helpful to us? Most of us, used to create Stored Procedure in Sql Server database to achieve security, execution plans, reusability and decoupling. But sometime we want to capture some of information at time of exceptions like what was last statement ran, what was the final inputs in stored procedure was etc.. To achieve this things we could capture logs by PRINTING statement in SQL Server and can get whole message in your log file to dig into.

Below is the Database of EmployeeDB, in which there is a stored procedure which will print only messages.

USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDB')
DROP DATABASE [EmployeeDB]
GO

USE [master]
GO

/****** Object:  Database [EmployeeDB]
CREATE DATABASE [EmployeeDB]
GO

USE [EmployeeDB]
GO

CREATE PROCEDURE PrintMessagesToCapture

AS
BEGIN
    SET NOCOUNT ON;
    PRINT 'Message has been captured.'
    PRINT 'Congratulations...'
END
GO

 

Lets execute PrintMessagesToCapture SP and whose output will be as shown below:

InfoMessage is helpful to capture message from Sql Server

cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)

{ Console.WriteLine(e.Message); };

Below is the code snippet in C#.

using System;
using System.Management;
using System.Data;
using System.Data.SqlClient;

namespace GetMessageInfoOfSQLServerInCsharp
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Get Message Information of SQL Server using C#";
            Console.WriteLine("Get Message Information of SQL Server using C#");

            string strConnectionString = "Data Source=DHRUV-PC;Initial Catalog=EmployeeDB;Persist Security Info=True;User ID=sa;Password=sa123";
            using (SqlConnection cn = new SqlConnection(strConnectionString))
            {
                cn.Open();
                cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
                {
                    Console.WriteLine(e.Message);
                };

                SqlCommand cmd = new SqlCommand("PrintMessagesToCapture", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = cmd.ExecuteReader();
            }
            Console.ReadKey();
        }
    }
}

OUTPUT: