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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 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:
1 2 3 4 5 6 7 | 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 submission:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | 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 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
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
Very good Dirceu, I had a similar need I will study this solution, will help me a lot, thank you.
Hugs.
Reginaldo Silva
Thanks for the feedback, Reginaldo. I hope the post has been helpful in helping the solution you are developing. Hug.