SQL Server - Como enviar Torpedos SMS utilizando o CLR (C#) e a API da Mais Resultado (PG Soluções)

Post Views 2,217 views

Neste artigo

Reading time 6 minutes

Olá pessoal,
Bom dia!

Neste post, vou demonstrar como enviar Torpedos SMS utilizando o CLR (C#) e a API da Mais Resultado (PG Soluções) pelo próprio banco de dados SQL Server. Isso é especialmente útil quando você precisa criar alertas e monitoramentos de suas rotinas críticas, que funcionam de madrugada ou finais de semana e requerem uma ação imediata.

Porque utilizar essa API? E a solução com o Pushbullet?

Eu já havia feito o post Como utilizar a API do Pushbullet para enviar torpedos SMS no C#, PHP, Java ou pelo SQL Server (com CLR), que permitia o envio de torpedos SMS pelo banco de dados SQL Server utilizando, mas utilizando a API do Pushbullet e um celular físico para realizar o envio, mas apesar de ser um método muito mais barato (vai utilizar os torpedos “ilimitados” do seu chip), existe a necessidade de ter um telefone sempre ligado e conectado a Internet.

Além disso, sabemos que os torpedos “ilimitado” das operadoras de celular nunca é realmente ILIMITADO, pois alegando prevenir spam, elas criam uma série de limitações sobre a quantidade mensal de SMS. Umas operadoras limitam em cerca de 10.000 torpedos SMS (no melhor dos casos e se eles não cancelarem seu contrato), outras exigem que a quantidade torpedos enviada seja no máximo 2.500 torpedos/mês e tenha que ser no máximo o dobro da quantidade de torpedos recebidos, etc.

Como eu precisava de uma solução que permitisse enviar mais de 10.000 torpedos por mês, essa solução não atendia essa necessidade. Você até poderia comprar vários chips e aparelhos celulares e fazer um “pool de celulares”, mas dependendo da criticidade das informações, talvez não valha a pena arriscar.

Diante dessa necessidade, apresento-lhes a PG Soluções, empresa do Paraná especializada no envio de torpedos SMS em massa, que disponibiliza uma API para integração com outras linguagens de programação e permitindo assim, o envio automatizado. O custo do SMS é relativamente baixo (Se não me engano, custa 5 centavos por torpedo – mas o valor pode variar conforme a negociação).

PS: Só para deixar claro, não faço parte da empresa e nem conheço ninguém lá. Apenas estou demonstrando essa solução porque foi a que achei mais viável para o envio dos SMS que utilizo na empresa onde eu trabalho.

Uma vez que você já tenha realizado seu contrato com a PG, você poderá acessar a área administrativa, onde é possível visualizar os torpedos enviados pela API e gerenciar o cadastro de clientes (Na API, você pode criar vários clientes, que podem ser setores da sua empresa ou clientes distintos mesmo).

Como enviar torpedos SMS pela API da Mais Resultado

Agora que você já está familiarizado com a ferramenta e já possui um contrato com a PG Soluções, você poderá começar a enviar seus torpedos SMS pelo seu sistema C# ou pelo seu banco de dados SQL Server utilizando o CLR (C#).

Antes de iniciar, você precisa ter em mãos, o token de autenticação disponibilizado pela PG. Esse token é o que permite identificar os torpedos enviados por você, como sendo os seus torpedos e por isso, nunca divulge ou compartilhe esse token.

Como pré-requisito para utilizar essa procedure, você precisará criar a classe Retorno, disponível no post SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#) para utilizar o método Retorno.Erro e assim, enviar mensagens de erro, caso ocorram. Você também pode optar por comentar o código e remover as chamadas para esse método (e comentar também o using Bibliotecas.Model), mas não aconselho, pois você não saberá quando ocorreu algum erro na sua chamada à SP de envio de torpedo SMS.

Código-fonte da Procedure:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
using System.Xml;
using Bibliotecas.Model;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpEnvia_Torpedo_PG(SqlString Nr_Numero, SqlString Ds_Mensagem, SqlDateTime Dt_Agendamento, SqlString Cd_Cliente)
    {


        var token = "meu_token";

        if (Nr_Numero.Value.Length <= 9)
            Retorno.Erro("Favor informar o número de telefone com DDD");

        if (Ds_Mensagem.Value.Length > 160)
            Retorno.Erro("O tamanho máximo da mensagem a ser enviada por SMS é de 160 caracteres");

        
        try
        {

            
            var request = (HttpWebRequest) WebRequest.Create("http://api.recuperemais.com.br/send");
            
            request.Method = "POST";
            request.UserAgent = "curl/7.45.0";
            request.ContentType = "application/x-www-form-urlencoded";

            var parametros = $"token={token}&sms_to={Nr_Numero.Value}&sms_msg={Ds_Mensagem.Value}&sms_cliente={Cd_Cliente.Value}" + ((!Dt_Agendamento.IsNull) ? "&sms_data=" + Dt_Agendamento.Value.ToString("yyyy-MM-dd hh:mm:ss") : "");

            var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);
            using (var reqstr = request.GetRequestStream())
            {
                reqstr.Write(buffer, 0, buffer.Length);

                using (var response = request.GetResponse())
                {

                    using (var dataStream = response.GetResponseStream())
                    {

                        if (dataStream == null) return;

                        using (var reader = new StreamReader(dataStream))
                        {

                            var responseFromServer = reader.ReadToEnd();

                            var xml = new XmlDocument();
                            xml.LoadXml(responseFromServer);

                            var retorno = xml.SelectSingleNode("//pgdigitalsms")?.FirstChild.Attributes?["id"].Value;
                            if (retorno == null)
                            {
                                Retorno.Erro("Erro desconhecido ao recuperar o id retornado");
                                return;
                            }

                            var codigoRetorno = int.Parse(retorno);


                            if (codigoRetorno > 5)
                            {

                                var pipe = SqlContext.Pipe;
                                
                                var colunas = new SqlMetaData[3];
                                colunas[0] = new SqlMetaData("Ds_Numero", SqlDbType.VarChar, 50);
                                colunas[1] = new SqlMetaData("Nr_Retorno", SqlDbType.BigInt);
                                colunas[2] = new SqlMetaData("Ds_Mensagem", SqlDbType.VarChar, 4000);

                                var linhaSql = new SqlDataRecord(colunas);
                                
                                if (pipe == null) return;

                                pipe.SendResultsStart(linhaSql);
                                
                                linhaSql.SetSqlString(0, new SqlString(Nr_Numero.Value));
                                linhaSql.SetSqlInt64(1, new SqlInt64(codigoRetorno));
                                linhaSql.SetSqlString(2, new SqlString("Torpedo enviado com sucesso"));

                                pipe.SendResultsRow(linhaSql);
                                pipe.SendResultsEnd();

                            }
                            else
                            {

                                string msgErro;

                                switch (codigoRetorno)
                                {
                                    case 1:
                                    {
                                        msgErro = "Erro de autenticação de sua conta PG SMS";
                                        break;
                                    }

                                    case 2:
                                    {
                                        msgErro = "Erro nos parâmetros informados";
                                        break;
                                    }

                                    case 3:
                                    {
                                        msgErro = "Registros não encontrados";
                                        break;
                                    }

                                    case 4:
                                    {
                                        msgErro = "Mensagem não encontrada no banco de dados";
                                        break;
                                    }

                                    case 5:
                                    default:
                                    {
                                        msgErro = "Telefone Inválido";
                                        break;
                                    }
                                }


                                var pipe = SqlContext.Pipe;

                                var colunas = new SqlMetaData[3];
                                colunas[0] = new SqlMetaData("Ds_Numero", SqlDbType.VarChar, 50);
                                colunas[1] = new SqlMetaData("Nr_Retorno", SqlDbType.BigInt);
                                colunas[2] = new SqlMetaData("Ds_Mensagem", SqlDbType.VarChar, 4000);

                                var linhaSql = new SqlDataRecord(colunas);

                                if (pipe == null) return;

                                pipe.SendResultsStart(linhaSql);

                                linhaSql.SetSqlString(0, new SqlString(Nr_Numero.Value));
                                linhaSql.SetSqlInt64(1, new SqlInt64(codigoRetorno));
                                linhaSql.SetSqlString(2, new SqlString(msgErro));

                                pipe.SendResultsRow(linhaSql);
                                pipe.SendResultsEnd();


                                Retorno.Erro(msgErro);

                            }

                        }
                    }

                }

            }

        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message + "\n\nInner exception: " + e.InnerException);
        }

    }

};

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

using System;

using System.Data;

using System.Data.SqlTypes;

using System.IO;

using System.Net;

using System.Text;

using System.Xml;

using Bibliotecas.Model;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpEnvia_Torpedo_PG(SqlString Nr_Numero, SqlString Ds_Mensagem, SqlDateTime Dt_Agendamento, SqlString Cd_Cliente)

{

var token = "meu_token";

if (Nr_Numero.Value.Length <= 9)

Retorno.Erro("Favor informar o número de telefone com DDD");

if (Ds_Mensagem.Value.Length > 160)

Retorno.Erro("O tamanho máximo da mensagem a ser enviada por SMS é de 160 caracteres");

try

{

var request = (HttpWebRequest) WebRequest.Create("http://api.recuperemais.com.br/send");

request.Method = "POST";

request.UserAgent = "curl/7.45.0";

request.ContentType = "application/x-www-form-urlencoded";

var parametros = $"token={token}&sms_to={Nr_Numero.Value}&sms_msg={Ds_Mensagem.Value}&sms_cliente={Cd_Cliente.Value}" + ((!Dt_Agendamento.IsNull) ? "&sms_data=" + Dt_Agendamento.Value.ToString("yyyy-MM-dd hh:mm:ss") : "");

var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);

using (var reqstr = request.GetRequestStream())

{

reqstr.Write(buffer, 0, buffer.Length);

using (var response = request.GetResponse())

{

using (var dataStream = response.GetResponseStream())

{

if (dataStream == null) return;

using (var reader = new StreamReader(dataStream))

{

var responseFromServer = reader.ReadToEnd();

var xml = new XmlDocument();

xml.LoadXml(responseFromServer);

var retorno = xml.SelectSingleNode("//pgdigitalsms")?.FirstChild.Attributes?["id"].Value;

if (retorno == null)

{

Retorno.Erro("Erro desconhecido ao recuperar o id retornado");

return;

}

var codigoRetorno = int.Parse(retorno);

if (codigoRetorno > 5)

{

var pipe = SqlContext.Pipe;

var colunas = new SqlMetaData[3];

colunas[0] = new SqlMetaData("Ds_Numero", SqlDbType.VarChar, 50);

colunas[1] = new SqlMetaData("Nr_Retorno", SqlDbType.BigInt);

colunas[2] = new SqlMetaData("Ds_Mensagem", SqlDbType.VarChar, 4000);

var linhaSql = new SqlDataRecord(colunas);

if (pipe == null) return;

pipe.SendResultsStart(linhaSql);

linhaSql.SetSqlString(0, new SqlString(Nr_Numero.Value));

linhaSql.SetSqlInt64(1, new SqlInt64(codigoRetorno));

linhaSql.SetSqlString(2, new SqlString("Torpedo enviado com sucesso"));

pipe.SendResultsRow(linhaSql);

pipe.SendResultsEnd();

}

else

{

string msgErro;

switch (codigoRetorno)

{

case 1:

{

msgErro = "Erro de autenticação de sua conta PG SMS";

break;

}

case 2:

{

msgErro = "Erro nos parâmetros informados";

break;

}

case 3:

{

msgErro = "Registros não encontrados";

break;

}

case 4:

{

msgErro = "Mensagem não encontrada no banco de dados";

break;

}

case 5:

default:

{

msgErro = "Telefone Inválido";

break;

}

var pipe = SqlContext.Pipe;

var colunas = new SqlMetaData[3];

colunas[0] = new SqlMetaData("Ds_Numero", SqlDbType.VarChar, 50);

colunas[1] = new SqlMetaData("Nr_Retorno", SqlDbType.BigInt);

colunas[2] = new SqlMetaData("Ds_Mensagem", SqlDbType.VarChar, 4000);

var linhaSql = new SqlDataRecord(colunas);

if (pipe == null) return;

pipe.SendResultsStart(linhaSql);

linhaSql.SetSqlString(0, new SqlString(Nr_Numero.Value));

linhaSql.SetSqlInt64(1, new SqlInt64(codigoRetorno));

linhaSql.SetSqlString(2, new SqlString(msgErro));

pipe.SendResultsRow(linhaSql);

pipe.SendResultsEnd();

Retorno.Erro(msgErro);

}

catch (Exception e)

{

Retorno.Erro("Erro : " + e.Message + "\n\nInner exception: " + e.InnerException);

}

};

Exemplos de uso:

-- Enviar torpedo agora
EXEC CLR.dbo.stpEnvia_Torpedo_PG 
    @Nr_Numero = N'27111111111', -- nvarchar(max)
    @Ds_Mensagem = N'Teste', -- nvarchar(max)
    @Dt_Agendamento = NULL, -- datetime
    @Cd_Cliente = N'Dirceu_Resende' -- nvarchar(max)


-- Enviar torpedo no meu aniversário
EXEC CLR.dbo.stpEnvia_Torpedo_PG 
    @Nr_Numero = N'27111111111', -- nvarchar(max)
    @Ds_Mensagem = N'Teste', -- nvarchar(max)
    @Dt_Agendamento = '2017-05-28 08:00:00', -- datetime
    @Cd_Cliente = N'Dirceu_Resende' -- nvarchar(max)

-- Enviar torpedo agora

EXEC CLR.dbo.stpEnvia_Torpedo_PG

@Nr_Numero = N'27111111111', -- nvarchar(max)

@Ds_Mensagem = N'Teste', -- nvarchar(max)

@Dt_Agendamento = NULL, -- datetime

@Cd_Cliente = N'Dirceu_Resende' -- nvarchar(max)

-- Enviar torpedo no meu aniversário

EXEC CLR.dbo.stpEnvia_Torpedo_PG

@Nr_Numero = N'27111111111', -- nvarchar(max)

@Ds_Mensagem = N'Teste', -- nvarchar(max)

@Dt_Agendamento = '2017-05-28 08:00:00', -- datetime

@Cd_Cliente = N'Dirceu_Resende' -- nvarchar(max)

Automatizando o envio dos torpedos

Uma dica de utilização para vocês, que utilizo no dia a dia, é criar uma tabela de torpedos, de modo que suas rotinas apenas insiram os dados do torpedo nessa tabela e criar um Job que leia os torpedos não enviados dessa tabela e faça o disparo dos torpedos utilizando o CLR.

Criação da tabela base:

CREATE TABLE dbo.Torpedo (
    Id_Torpedo INT NOT NULL IDENTITY(1, 1),
    Nr_Telefone varchar (11) NOT NULL,
    Ds_Mensagem varchar (200) NOT NULL,
    Dt_Cadastro datetime NOT NULL CONSTRAINT DF_Torpedo_Dt_Cadastro DEFAULT (getdate())
) WITH(DATA_COMPRESSION = PAGE)
GO

CREATE TABLE dbo.Torpedo (

Id_Torpedo INT NOT NULL IDENTITY(1, 1),

Nr_Telefone varchar (11) NOT NULL,

Ds_Mensagem varchar (200) NOT NULL,

Dt_Cadastro datetime NOT NULL CONSTRAINT DF_Torpedo_Dt_Cadastro DEFAULT (getdate())

) WITH(DATA_COMPRESSION = PAGE)

Stored Procedure para realizar o envio automatizado:

CREATE PROCEDURE [dbo].[stpRotina_Envia_Torpedo]
AS
BEGIN 

    IF (OBJECT_ID('dbo.Torpedos_Enviados') IS NULL)
    BEGIN
    
        CREATE TABLE dbo.Torpedos_Enviados (
            Id_Torpedo INT NOT NULL,
            Cd_Envio bigint
        ) WITH(DATA_COMPRESSION = PAGE)

    END


    IF (OBJECT_ID('tempdb..#Fila') IS NOT NULL) DROP TABLE #Fila
    SELECT 
        IDENTITY(INT, 1, 1) AS Ranking,
        A.Id_Torpedo,
        A.Nr_Telefone,
        A.Ds_Mensagem,
        A.Dt_Cadastro
    INTO 
        #Fila
    FROM 
        dbo.Torpedo			        A	WITH(NOLOCK)
        LEFT JOIN dbo.Torpedos_Enviados		B	WITH(NOLOCK) ON A.Id_Torpedo = B.Id_Torpedo
    WHERE 
        A.Dt_Cadastro >= DATEADD(MINUTE, -10, GETDATE())
        AND B.Id_Torpedo IS NULL


    IF (OBJECT_ID('tempdb..#Retorno') IS NOT NULL) DROP TABLE #Retorno
    CREATE TABLE #Retorno (
        Ds_Numero VARCHAR(50),
        Nr_Retorno BIGINT,
        Ds_Mensagem VARCHAR(MAX)
    )


    DECLARE
        @Contador INT = 1,
        @Total INT = (SELECT COUNT(*) FROM #Fila),
        @Nr_Telefone VARCHAR(30),
        @Ds_Mensagem VARCHAR(160),
        @Id_Torpedo INT,
        @Cd_Retorno_PG BIGINT


    WHILE(@Contador <= @Total)
    BEGIN


        SELECT
            @Id_Torpedo = Id_Torpedo,
            @Ds_Mensagem = REPLACE(Ds_Mensagem, '\', '/'),
            @Nr_Telefone = Nr_Telefone
        FROM
            #Fila
        WHERE
            Ranking = @Contador

    
        BEGIN TRY
            

            TRUNCATE TABLE #Retorno
            

            INSERT INTO #Retorno
            EXEC CLR.dbo.stpEnvia_Torpedo_PG
                @Nr_Numero = @Nr_Telefone, -- nvarchar(max)
                @Ds_Mensagem = @Ds_Mensagem, -- nvarchar(max)
                @Dt_Agendamento = NULL,
                @Cd_Cliente = 'Nome_Do_Cliente'


            SELECT TOP 1 @Cd_Retorno_PG = Nr_Retorno
            FROM #Retorno

            
            INSERT INTO dbo.[Torpedos_Enviados]
            VALUES(@Id_Torpedo, @Cd_Retorno_PG)


        END TRY
        BEGIN CATCH
            RAISERROR('Erro ao enviar SMS', 16, 1)
        END CATCH
    

        SET @Contador = @Contador + 1

    END

END

CREATE PROCEDURE [dbo].[stpRotina_Envia_Torpedo]

BEGIN

IF (OBJECT_ID('dbo.Torpedos_Enviados') IS NULL)

BEGIN

CREATE TABLE dbo.Torpedos_Enviados (

Id_Torpedo INT NOT NULL,

Cd_Envio bigint

) WITH(DATA_COMPRESSION = PAGE)

END

IF (OBJECT_ID('tempdb..#Fila') IS NOT NULL) DROP TABLE #Fila

SELECT

IDENTITY(INT, 1, 1) AS Ranking,

A.Id_Torpedo,

A.Nr_Telefone,

A.Ds_Mensagem,

A.Dt_Cadastro

INTO

#Fila

FROM

dbo.Torpedo A WITH(NOLOCK)

LEFT JOIN dbo.Torpedos_Enviados B WITH(NOLOCK) ON A.Id_Torpedo = B.Id_Torpedo

WHERE

A.Dt_Cadastro >= DATEADD(MINUTE, -10, GETDATE())

AND B.Id_Torpedo IS NULL

IF (OBJECT_ID('tempdb..#Retorno') IS NOT NULL) DROP TABLE #Retorno

CREATE TABLE #Retorno (

Ds_Numero VARCHAR(50),

Nr_Retorno BIGINT,

Ds_Mensagem VARCHAR(MAX)

)

DECLARE

@Contador INT = 1,

@Total INT = (SELECT COUNT(*) FROM #Fila),

@Nr_Telefone VARCHAR(30),

@Ds_Mensagem VARCHAR(160),

@Id_Torpedo INT,

@Cd_Retorno_PG BIGINT

WHILE(@Contador <= @Total)

BEGIN

SELECT

@Id_Torpedo = Id_Torpedo,

@Ds_Mensagem = REPLACE(Ds_Mensagem, '\', '/'),

@Nr_Telefone = Nr_Telefone

FROM

#Fila

WHERE

Ranking = @Contador

BEGIN TRY

TRUNCATE TABLE #Retorno

INSERT INTO #Retorno

EXEC CLR.dbo.stpEnvia_Torpedo_PG

@Nr_Numero = @Nr_Telefone, -- nvarchar(max)

@Ds_Mensagem = @Ds_Mensagem, -- nvarchar(max)

@Dt_Agendamento = NULL,

@Cd_Cliente = 'Nome_Do_Cliente'

SELECT TOP 1 @Cd_Retorno_PG = Nr_Retorno

FROM #Retorno

INSERT INTO dbo.[Torpedos_Enviados]

VALUES(@Id_Torpedo, @Cd_Retorno_PG)

END TRY

BEGIN CATCH

RAISERROR('Erro ao enviar SMS', 16, 1)

END CATCH

SET @Contador = @Contador + 1

END

Agora é só você criar um Job no SQL Agent da sua instância para executar a stpRotina_Envia_Torpedo criada acima. Eu costumo definir o job para rodar a cada minuto, mas fica a seu critério e necessidade.

And that's it, folks!
Espero que tenham gostado desse post.
Um abraço e até a próxima.

sql server clr .net dotnet framework c# csharp integration como criar integração banco de dados database como enviar mensagens how to send messages notifications notificações torpedos sms

Reginaldo Silva disse:

February 8, 2017 às 18:14

Muito bom Dirceu, me surgiu uma necessidade parecida vou estudar essa solução, vai me ajudar muito, obrigado.

Abraços.

Reginaldo Silva

Responder
- Dirceu Resende disse:
  
  February 24, 2017 às 06:55
  
  Obrigado pelo feedback, Reginaldo. Espero que o post tenhas sido útil para ajudar a solução que você está desenvolvendo. Abraço.
  
  Responder

Enviando SMS com C# e VB.NET - André Alves de Lima

October 18, 2017

[…] Mais Resultado (tutorial aqui)– […]

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como enviar Torpedos SMS utilizando o CLR (C#) e a API da Mais Resultado (PG Soluções)

Porque utilizar essa API? E a solução com o Pushbullet?

Como enviar torpedos SMS pela API da Mais Resultado

Automatizando o envio dos torpedos

sql server clr .net dotnet framework c# csharp integration como criar integração banco de dados database como enviar mensagens how to send messages notifications notificações torpedos sms

You may also like...

3 Responses

Deixe uma respostaCancelar resposta

Porque utilizar essa API? E a solução com o Pushbullet?

Como enviar torpedos SMS pela API da Mais Resultado

Automatizando o envio dos torpedos

sql server clr .net dotnet framework c# csharp integration como criar integração banco de dados database como enviar mensagens how to send messages notifications notificações torpedos sms

You may also like...

Lei Geral de Proteção de Dados Pessoais (LGPDP ou LGPD) aplicada a bancos de dados SQL Server

SQL Server – Criptografando senhas com as funções de criptografia simétrica ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

3 Responses

Deixe uma respostaCancelar resposta