SQL Server - How to send SMS messages using CLR (C #) and More Result API (PG Solutions)

Reading Time: 6 minutes

Hello people,
Good Morning!

In this post, I will demonstrate how to send SMS messages using CLR (C #) and the More Result API (PG Solutions) through the SQL Server database itself. This is especially useful when you need to create alerts and monitoring of your critical routines, which work at dawn or on weekends and require immediate action.

Why use this API? And the solution with Pushbullet?

I had already made the post Using the Pushbullet API to send SMS messages in C #, PHP, Java, or SQL Server (with CLR), which allowed SMS text messages to be sent by the SQL Server database using, but using the Pushbullet API and a physical cell phone to perform the sending, but despite being a much cheaper method (it will use the “unlimited” text messages of your chip), there is a need to have a phone always on and connected to the Internet.

In addition, we know that the “unlimited” text messages from cell phone operators are never really UNLIMITED, because claiming to prevent spam, they create a series of limitations on the monthly amount of SMS. Some operators limit SMS messages to around 10.000 SMS (at best and if they do not cancel your contract), others require that the number of SMS sent is a maximum of 2.500 SMS / month and has to be at most double the amount of SMS received. , etc.

As I needed a solution that would allow me to send more than 10.000 torpedoes per month, this solution did not meet this need. You could even buy several chips and cell phones and make a "cell phone pool", but depending on the criticality of the information, it might not be worth taking the risk.

Faced with this need, I present to you the PG Solutions, a company from Paraná specialized in sending bulk SMS messages, which provides an API for integration with other programming languages and thus allowing automated sending. The cost of SMS is relatively low (if I'm not mistaken, it costs 5 cents per text - but the amount may vary according to the negotiation).

PS: Just to be clear, I'm not part of the company and I don't even know anyone there. I am just demonstrating this solution because it was the one I found most viable for sending the SMS I use in the company where I work.

Once you have already entered into your contract with PG, you will be able to access the administrative area, where you can view the torpedoes sent by the API and manage the customer base (In the API, you can create multiple customers, which can be sectors of your business or even distinct customers).

How to send SMS messages via the More Result API

Now that you are familiar with the tool and have a contract with PG Solutions, you can start sending your SMS messages through your C # system or your SQL Server database using CLR (C #).

Before you get started, you need to have the authentication token available from PG. This token is what allows you to identify the torpedoes sent by you as your torpedoes, so never disclose or share this token.

As a prerequisite for using this procedure, you will need to create the Return class, available in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #) to use the method Return.Error and thus send error messages if they occur. You can also choose to comment out the code and remove calls to this method (and comment out using Libraries.Model as well), but I do not advise as you will not know when an error has occurred in your SMS SMS sending call.

Procedure Source Code:

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

}

};

Examples of use:

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

Automating Torpedo Dispatch

A tip for you, which I use on a daily basis, is to create a torpedo table, so that your routines just enter torpedo data in that table and create a Job that reads the unsent torpedoes from that table and fires torpedoes using the CLR.

Base table creation:

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 to perform automated submission:

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

Now you just create a Job in your instance's SQL Agent to execute the stpRotina_Envia_Torpedo created above. I usually set the job to run every minute, but it's up to you and need it.

And that's it folks!
I hope you enjoyed this post.
A hug and see you next.

sql server clr .net dotnet framework C # csharp integration how to create database integration database how to send messages how to send messages notifications sms notifications

Reginaldo Silva said:

8 February 2017 to 18: 14

Very good Dirceu, I had a similar need I will study this solution, will help me a lot, thank you.

Hugs.

Reginaldo Silva

- Dirceu Resende said:
  
  24 February 2017 to 06: 55
  
  Thanks for the feedback, Reginaldo. I hope the post has been helpful in helping the solution you are developing. Hug.

Sending SMS with C # and VB.NET - André Alves de Lima

October 18th, 2017

[…] More Result (tutorial here) - […]

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

SQL Server - How to Send SMS Messages Using the CLR (C #) and the More Result API (PG Solutions)

Why use this API? And the solution with Pushbullet?

How to send SMS messages via the More Result API

Automating Torpedo Dispatch

sql server clr .net dotnet framework C # csharp integration how to create database integration database how to send messages how to send messages notifications sms notifications

You may also like ...

Comments

Leave a Comment Cancel reply

Why use this API? And the solution with Pushbullet?

How to send SMS messages via the More Result API

Automating Torpedo Dispatch

sql server clr .net dotnet framework C # csharp integration how to create database integration database how to send messages how to send messages notifications sms notifications

You may also like ...

SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #)

Creating and restoring logical backups (DUMPs) in Oracle Database 11g (exp and imp)

SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Comments

Leave a Comment Cancel reply