SQL Server - How to Integrate Database with Telegram and Send Messages Using CLR (C #)

Views: 3.873
Reading Time: 8 minutes

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:

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 the "Get Started" button, Bot will send you 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 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's safer, having to store channel and user ID's is a bit of a boring and unproductive task.

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 code to the user himself, and he must inform you of this ID in order to send the private messages (DM's).

How to identify a user ID by getUpdates API

Another way to do this is by using the getUpdates method of the Telegram API. After the user initiates 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.

Examples of use

Sending message to the group “Test Dirceu Resende”

Sending message to “Test Dirceu Resende” group with external URL

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:

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