Hey guys,
Good morning!
In this post, I will demonstrate how to send SMS Torpedoes using the CLR (C#) and the Mais Resultado (PG Soluções) API through the SQL Server database itself. This is especially useful when you need to create alerts and monitoring of your critical routines, which operate late at night or on weekends and require immediate action.
Why use this API? And the solution with Pushbullet?
I had already made the post How to use the Pushbullet API to send SMS text messages in C#, PHP, Java or SQL Server (with CLR), which allowed the sending of SMS text messages via the SQL Server database using the Pushbullet API and a physical cell phone to carry out the sending, but despite being a much cheaper method (it will use the “unlimited” text messages from your chip), there is a need to have a phone always on and connected to the Internet.
Furthermore, 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 quantity of SMS. Some operators limit them to around 10,000 SMS text messages (in the best case scenario and if they do not cancel their contract), others require that the number of text messages sent is a maximum of 2,500 text messages/month and it must be a maximum of double the number of text messages received, etc.
As I needed a solution that would allow me to send more than 10,000 text messages per month, this solution did not meet that need. You could even buy several chips and cell phones and create a “pool of cell phones”, but depending on the criticality of the information, it might not be worth the risk.
Given this need, I present to you the PG Solutions, a company from Paraná specialized in sending bulk SMS text 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 depending on the negotiation).
PS: Just to be clear, I'm not part of the company nor do I know anyone there. I'm just demonstrating this solution because it was the one I found most viable for sending the SMS that I use in the company where I work.
Once you have already completed your contract with PG, you will be able to access the administrative area, where you can view the text messages sent by the API and manage customer registration (In the API, you can create several customers, which can be sectors of your company or different customers).
How to send SMS text messages using the Mais Resultado API
Now that you are familiar with the tool and already have a contract with PG Soluções, you can start sending your SMS text messages through your C# system or through your SQL Server database using the CLR (C#).
Before starting, you need to have the authentication token provided by PG on hand. This token is what allows you to identify the text messages sent by you as your text messages and therefore, never disclose or share this token.
As a prerequisite to 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 database using the CLR (C#) to use the method Return.Error and thus, send error messages, if they occur. You can also choose to comment the code and remove calls to this method (and also comment using Bibliotecas.Model), but I don't advise it, as you won't know when an error occurred in your call to the SMS text message sending SP.
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);
}
}
};
Usage examples:
-- 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 the sending of torpedoes
A tip for you to use, which I use on a daily basis, is to create a torpedo table, so that your routines just insert the torpedo data into this table and create a Job that reads the unsent torpedoes from this table and fires the torpedoes using the CLR.
Creating the base table:
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
Stored Procedure to perform automated sending:
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
Now you just need to create a Job in the SQL Agent of your instance to execute the stpRotina_Send_Torpedo created above. I usually set the job to run every minute, but it's up to you and your needs.
And that's it, folks!
I hope you liked this post.
A hug and see you next time.
sql server clr .net dotnet framework c# csharp integration how to create database integration database how to send messages how to send messages notifications text messages sms
sql server clr .net dotnet framework c# csharp integration how to create database integration database how to send messages how to send messages notifications text messages sms
Comentários (0)
Carregando comentários…