SQL Server - Como enviar avisos e mensagens de erro para o banco pelo CLR (C#)

Visualizações: 2.452 views

Tempo de Leitura: 3 minutos

Olá pessoal,
Bom dia!

Neste post eu gostaria de demonstrar para vocês, já que estou (e pretendo continuar) postando várias coisas legais sobre o CLR, como enviar avisos (PRINT) e mensagens de erro (RAISEERROR) para o SQL Server quando suas Stored Procedures compiladas no CLR são executadas.

Apesar do post ser pequeno, resolvi criar um post só com isso, porque utilizo muito essa classe nas SP’s que vou publicar aqui futuramente, então entendo que seja mais fácil criar essa referência do que repostar essa classe várias vezes.

Código fonte do arquivo Servidor.cs (pré-requisito)

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;

            }
        }

    }

}

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;

}

Código-fonte da classe Retorno:

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)
        {      
        }
    }
}

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)

{

}

Uma vez que essa classe criada no seu projeto CLR, basta importá-la na sua Stored Procedure e começar a enviar avisos e mensagens de erro, como vou demonstrar abaixo:
Simulação de um erro num método do CLR:

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);
        }

    }

}

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);

}

Exemplo de utilização:

Consultando o histórico de erros:
Como vocês devem ter reparado, no método de erro eu coloquei uma instrução SQL para gravar o histórico de quando esse método é chamado, fazendo com que se tenha um log de erros do CLR, facilitando a localização de possíveis problemas nas suas procedures CLR.

É isso aí, pessoal!
Qualquer dúvida, deixem aqui nos comentários.

Abraço.

sql server clr c# csharp enviar avisos mensagens de erro warnings send text print error messages

Denis Willian Oliveira disse:

2 de dezembro de 2019 às 11:47

Bom dia, não reconhece o SqlProcedure do [Microsoft.SqlServer.Server.SqlProcedure]. Sabe o que poderia ser ?

Responder
- Dirceu Resende disse:
  
  2 de dezembro de 2019 às 12:10
  
  Oi Denis,
  Beleza ?
  
  Você tem que adicionar referências a System e System.Data no seu projeto.
  Abraço!
  
  Responder
Leonardo disse:

13 de agosto de 2019 às 15:29

No arquivo Servidor.cs, devemos informar o usuario e senha, correto ?

Mesmo informando recebo o erro abaixo:

Erro do .NET Framework durante a execução de rotina definida pelo usuário ou agregação “stpTeste”:
System.Data.SqlClient.SqlException: Falha de logon do usuário ‘leonardo’.
System.Data.SqlClient.SqlException:
em System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
em System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
em System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
em System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
em System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
em System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
em System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
em System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
em System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
em System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
em System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
em System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject…

Responder
- Dirceu Resende disse:
  
  18 de setembro de 2019 às 23:17
  
  Me chama no privado pra tentar entender o que está acontecendo
  
  Responder
Gustavo disse:

12 de setembro de 2016 às 08:45

bom dia Estou tentano executar o codigo acima porem estou com um dificuldade poderia me ajudar por favor

ao tentar executar esta informando o seguinte erro na Linha 29 do Codigo

Severity Code Description
Error CS0103 The name ‘Servidor’ does not exist in the current context

Responder
- Dirceu Resende disse:
  
  12 de setembro de 2016 às 10:18
  
  Gustavo, bom dia.
  
  Analisei o que você me falou, e realmente eu esqueci de colocar o código do arquivo Servidor.cs, que você deve importar para o seu projeto, uma vez que a classe Retorno o referencia. Já editei o post e adicionei o código-fonte desse arquivo lá.
  
  Obrigado!
  
  Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#)

sql server clr c# csharp enviar avisos mensagens de erro warnings send text print error messages

You may also like...

6 Responses

Deixe uma respostaCancelar resposta

sql server clr c# csharp enviar avisos mensagens de erro warnings send text print error messages

You may also like...

SQL Server – Porque NÃO utilizar SET ANSI_WARNINGS OFF

2º Encontro do Chapter SQL Server ES – 10/06/2017

Importando arquivos CSV para o banco de dados SQL Server

6 Responses

Deixe uma respostaCancelar resposta