Click on the banner to learn about and purchase my database training on Azure

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

Views: 2.028 views
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:

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