SQL Server - How to send warnings and error messages to the database using CLR C#

Hey guys,
Good morning!

In this post I would like to demonstrate to you, since I am (and intend to continue) posting several cool things about the CLR, how to send warnings (PRINT) and error messages (RAISEERROR) to SQL Server when your Stored Procedures compiled in the CLR are executed.

Although the post is small, I decided to create a post just with this, because I use this class a lot in the SP's that I will publish here in the future, so I understand that it is easier to create this reference than to repost this class several times.

Server.cs file source code (prerequisite)

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Bibliotecas.Model
{

    public class ServidorAtual
    {

        public string NomeServidor { get; set; }

        public ServidorAtual()
        {

            try
            {

                using (var conn = new SqlConnection(Servidor.Context))
                {

                    conn.Open();

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT @@SERVERNAME AS InstanceName";
                        NomeServidor = (string) cmd.ExecuteScalar();
                    }

                    var partes = NomeServidor.Split('\\');

                    if (partes.Length <= 1) return;
                    if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase))
                        NomeServidor = partes[0];
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }


    public static class Servidor
    {

        public static string Ds_Usuario => "Usuario";
        public static string Ds_Senha => "Senha";

        public static string PRODUCAO => "data source=PRODUCAO;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";
        public static string Context => "context connection=true";
        public static string Localhost => "data source=LOCALHOST;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";

        public static string getLocalhost()
        {

            var servidorAtual = new ServidorAtual().NomeServidor;
            return "data source=" + servidorAtual + ";initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";

        }

        public static List<string> Servidores
        {
            get
            {
                var servidores = new List<string>
                {
                    PRODUCAO,
                    Localhost
                };

                return servidores;

            }
        }

    }

}

Return class source code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SqlServer.Server;

namespace Bibliotecas.Model
{

    public static class Retorno
    {

        public static void Erro(string erro)
        {

            /*

            IF (OBJECT_ID('CLR.dbo.Log_Erro') IS NOT NULL) DROP TABLE CLR.dbo.Log_Erro
            CREATE TABLE CLR.dbo.Log_Erro (
	            Id_Erro INT IDENTITY(1,1),
	            Dt_Erro DATETIME DEFAULT GETDATE(),
	            Nm_Objeto VARCHAR(100),
	            Ds_Erro VARCHAR(MAX),
	            CONSTRAINT [PK_Log_Erro] PRIMARY KEY CLUSTERED (Id_Erro)
            )

            */

            using (var conexao = new SqlConnection(Servidor.getLocalhost()))
            {

                var comando = new SqlCommand("INSERT INTO dbo.Log_Erro (Nm_Objeto, Ds_Erro) VALUES (@Nm_Objeto, @Ds_Erro)", conexao);

                var stackTrace = new StackTrace();
                var objeto = stackTrace.GetFrame(1).GetMethod().Name;

                comando.Parameters.Add(new SqlParameter("@Nm_Objeto", SqlDbType.VarChar, 100)).Value = objeto;
                comando.Parameters.Add(new SqlParameter("@Ds_Erro", SqlDbType.VarChar, 8000)).Value = erro;
                conexao.Open();

                comando.ExecuteNonQuery();
            }
            

            throw new ApplicationException(erro);
        }


        public static void Mensagem(string mensagem)
        {

            using (var conexao = new SqlConnection(Servidor.Context))
            {

                var Comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", conexao);
                conexao.Open();

                if ((int) Comando.ExecuteScalar() != 0) return;

                var retorno = SqlContext.Pipe;
                retorno?.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem);
            }

        }

        public static void RetornaReader(SqlDataReader dataReader)
        {
            var retorno = SqlContext.Pipe;
            retorno?.Send(dataReader);
        }
    }

    public class Ret : Exception
    {
        public Ret(string str) : base(str)
        {      
        }
    }
}

Once this class is created in your CLR project, simply import it into your Stored Procedure and start sending warnings and error messages, as I will demonstrate below:
Simulation of an error in a CLR method:

using Bibliotecas.Model;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpTeste(SqlString Ds_String)
    {

        try
        {

            if (Ds_String.Value == "ERRO")
            {
                // Vou forçar um erro ao tentar inserir dados em uma tabela que não existe, causando uma Exception
                using (var Conexao = new SqlConnection(Servidor.Localhost))
                {

                    var Comando = new SqlCommand("INSERT INTO dbo.Erro (Nm_Objeto, Ds_Erro) VALUES (@Nm_Objeto, @Ds_Erro)", Conexao);

                    Comando.Parameters.Add(new SqlParameter("@Nm_Objeto", SqlDbType.VarChar, 100)).Value = "Vai dar erro";
                    Comando.Parameters.Add(new SqlParameter("@Ds_Erro", SqlDbType.VarChar, 8000)).Value = "Descrição do Erro";
                    Conexao.Open();

                    Comando.ExecuteNonQuery();
                }
            }

            Retorno.Mensagem("Alerta enviado para o banco (PRINT)");

        }
        catch(Exception e)
        {
            Retorno.Erro("Mensagem de erro (RAISEERROR) gravada. Descrição do erro: " + e.Message);
        }

    }

}

Example of use:

Consulting the error history:
As you may have noticed, in the error method I placed an SQL statement to record the history of when this method is called, creating a CLR error log, making it easier to find possible problems in your CLR procedures.

That's it, folks!
If you have any questions, leave them here in the comments.

Hug.

sql server clr c# csharp send warnings error messages warnings send text print error messages

sql server clr c# csharp send warnings error messages warnings send text print error messages