Hello people,
Good Morning!
In this post I will demonstrate how to do a database integration with the Telegram and send messages using CLR (C #), which is Whatsapp's main competitor. Earlier, I demonstrated how to do this same integration in posts:
- SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script
- SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #)
If you don't know or know how to use CLR in SQL Server, find out how to access the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.
What is Telegram?
For those who do not know, the Telegram is one of the most famous and widely used communicators in the world, Whatsapp's main competitor and has the proposal to allow fast, safe and easy communication between people from all over the world.
You can create public groups where anyone can join and interact or private groups where only those in the group can view messages. Telegram has a Portuguese version, an open API that allows you to integrate with your applications and send automated messages. Its look is very similar to Whatsapp and it is safer, faster and has more features than its main competitor and market leader, but has an even smaller number of users, especially in Brazil.
Where and how can I use Telegram in my business?
This feature is especially useful for building teams in your company and generating alerts and monitoring by team, where each team receives alerts only for their activities. In addition, you can use open groups to send information and notifications that matter to the entire company. All at no cost and you can receive by email, view on the web and on your smartphone.
Another legal way to use Telegram is to send sales target tracking to the sales department so that regional and local managers can track sales team performance in real time without having to consult and open up. reports wherever they are, even outside the company.
If your company adopts Telegram, HR can use the service to send messages and information to employees. Send Christmas messages, a special message on the employee's birthday, etc.
Finally, the advantage of Telegram is that it can be sent in many different ways (web, smartphone), wherever you are, inside or outside the company and represents a major revolution in the way people interact with information.
You can use Telegram as a tool for communicating, monitoring and alerting IT routines such as critical job failures, backup issues, database corruptions, slow queries, locks, etc. Whenever a critical routine fails, an internal email and notification via Telegram are sent, where a push notification arrives on my mobile phone (just like Whatsapp) and wherever I am, I become aware of that failure.
A disadvantage of Telegram (and Whatsapp) over Slack and Ryver is that the first 2 communicators are open to the world, and can be used for personal and private conversations with anyone, leading to a lack of focus and loss of focus. Slack and Ryver are much more corporate-oriented, so much so that people who interact will need an invitation.
How to enable Telegram API
To start using the Telegram API you will need to create a new BOT which will be responsible for sending the messages. To do so, log in to your Telegram account, either on the web or mobile, and start a conversation with the user. @BotFather
Click the "Get started" button.
As soon as you click on the “Start” button, the Bot will send a list of commands:
Telegram - Bot Options
Select option / newbot and then choose the name of the bot and the user, as print below:
Write down the generated token, as it will be used by the Stored Procedure to send messages.
How to identify channel or user ID?
Like with Ryver, the Telegram API requires you to enter the channel ID instead of a name that is easy to memorize and use, as is the Slack API. Although it is more secure, having to keep storing channel and user IDs is a bit boring and unproductive.
Here I will demonstrate how to identify channel and user IDs to send private messages.
How to identify a group ID:
To identify the ID of a Telegram group, simply go to Telegram Web, click on the desired group and observe the group URL. Remember that the BOT must be invited to the group you want to send the message to.
If you do not invite BOT and try to send a message to the group, you will see this error message:
Error: The remote server returned an error: (400) Incorrect Request.
When using the user ID in your Stored Procedure call, the value is usually NEGATIVE.
How to identify a user ID:
To identify the ID of a Telegram group is a bit more work, because the user himself will have to send you this information. To do this he will have to start a conversation with BOT @get_id
After starting the conversation, the BOT will already send the user's code to the user himself, and he must inform him of this ID in order to send private messages (DM's).
How to identify a user ID by getUpdates API
Another way to do this is to use the Telegram API's getUpdates method. After the user starts a conversation with his BOT, this action is recorded in this method, which retrieves all the “news” that interact with his BOT.
Using a tool like the Postman, you can use this API and easily view the user ID who initiated the conversation with your BOT.
Remember that the URL of this method is: https://api.telegram.org/botSEU_TOKEN_AQUI/getUpdates
In either method above, the user will need to start a conversation with his BOT so that he can send the messages to his user. This is a Telegram API security definition to prevent SPAM.
When using the user ID in your Stored Procedure call, the value is usually POSITIVE.
How to integrate the database with Telegram
Once you have created your Telegram account and created the bot that you will use to send messages to groups, let's go to the interesting part: Create .NET (C #) code that will perform SQL database integration Server with Telegram API.
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 also comment out using Libraries.Model), but I do not advise as you will not know when there was an error in your call to Telegram .
As with Ryver and Slack, in order to be able to send messages, the BOT user must be added to all groups, private and public, because he who sends the messages and cannot send the messages if he is not in the group.
Now that we have the account created, the BOT user is in the desired groups and we have already generated our token, it's time to send the messages through the database with the CLR.
|
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 |
using System; using System.Data.SqlTypes; using System.IO; using System.Net; using System.Text; using Bibliotecas.Model; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpEnvia_Mensagem_Telegram(SqlString Ds_Canal, SqlString Ds_Mensagem) { const string token = "seu_token_telegram"; try { var mensagem = Ds_Mensagem.Value; var canais = Ds_Canal.Value.Split(';'); foreach (var canal in canais) { var dsScript = $"chat_id={canal.Trim()}&text={mensagem}&parse_mode=Markdown"; var url = $"https://api.telegram.org/bot{token}/sendMessage"; var request = (HttpWebRequest) WebRequest.Create(url); request.Method = "POST"; request.UserAgent = "curl/7.45.0"; request.ContentType = "application/x-www-form-urlencoded"; var buffer = Encoding.GetEncoding("UTF-8").GetBytes(dsScript); 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(); Retorno.Mensagem(responseFromServer); } } } } } } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } }; |
Examples of use
Sending message to the group “Teste Dirceu Resende”
|
1 2 3 4 5 6 7 |
EXEC CLR.dbo.stpEnvia_Mensagem_Telegram @Ds_Canal = N'-155990210', -- nvarchar(max) | grupos = ID negativo / DM = ID positivo @Ds_Mensagem = N'Teste de *negrito*, _italico_ e Quebra de linha.' -- nvarchar(max) |
Sending message to the group “Teste Dirceu Resende” with external URL
|
1 2 3 4 |
-- Enviando mensagem para o grupo "Teste Dirceu Resende" com URL EXEC CLR.dbo.stpEnvia_Mensagem_Telegram @Ds_Canal = N'-155990210', -- nvarchar(max) | grupos = ID negativo / DM = ID positivo @Ds_Mensagem = N'Gostou desse recurso? Veja mais no meu blog: (https://dirceuresende.com/blog)' -- nvarchar(max) |
Sending Private Message (DM)
Once you have the user ID, simply start a conversation with BOT and they will be allowed to send you messages. If you do not do this, you will receive this error message when you try to send the message:
Error: The remote server returned an error: (400) Incorrect Request.
After starting a conversation with your BOT, simply run the SP below:
|
1 2 3 |
EXEC CLR.dbo.stpEnvia_Mensagem_Telegram @Ds_Canal = N'191345344', -- nvarchar(max) | grupos = ID negativo / DM = ID positivo @Ds_Mensagem = N'Teste de mensagem *privada*' -- nvarchar(max) |
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 private group notifications dm Telegram
sql server clr .net dotnet framework C # csharp integration how to create database integration database how to send messages how to send messages notifications private group notifications dm Telegram
Good night, I tried to test the code in sql, I try to send the msg, not the error, but it does not send the msg.
the call to @get_id does not show as shown. groups only.
Great article!
Do you have an example with Whatsapp?
Good afternoon,
I tested this code by C # and it works fine, but when I play in SQL the message sending is in an infinite loop, it just runs a lifetime and no error message generates, any tips?
Call me in private to try to understand what's going on
Great article.
Do you study this article in java language?
Thank you