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

Views: 1.180
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 SMS to be sent through the SQL Server database using, but using the Pushbullet API and a physical mobile phone to do the sending, but despite being a much cheaper method (you will use the “unlimited” SMS torpedoes There is a need to have a phone always on and connected to the Internet.

In addition, we know that mobile operators' 'unlimited' text messages are never really UNLIMITED, as claiming to prevent spam, they create a number of limitations on the monthly amount of SMS. Some operators limit 10.000 SMS messages at best (at best and if they do not cancel your contract), others require that the number of SMS sent should be at most 2.500 SMS / month and must be at most twice the number SMS received. , etc.

Since I needed a solution that would allow me to send more than 10.000 torpedoes per month, this solution didn't meet that need. You could even buy a lot of chips and cell phones and make a “pool of cell phones,” but depending on the criticality of the information, it might not be worth the risk.

Faced with this need, I present to you the PG Solutions, a company from Paraná specialized in sending SMS bulk messages, which provides an API for integration with other programming languages ​​and thus allows automated sending. The cost of SMS is relatively low (If I'm not mistaken, it costs 5 cents per torpedo - but the amount may vary depending on 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:

Examples of use:

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:

Stored Procedure to perform automated submission:

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