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

Views: 1.089
Reading Time: 8 minutes

Hello people,
Good Morning!

In this post I will demonstrate how to do a database integration with the Slack and send messages using CLR (C #), which is Ryver's main competitor, in which I already demonstrated how to do this same post integration SQL Server - How to Integrate Database with Ryver 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.

If you are using SQL Server 2017 and want to use only native DBMS commands instead of having to create a SQLCLR (C #) library, you can still create your alerts using Slack with the Python solution I shared in the post. SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script.

What is Slack?

For those who do not know, the Slack is one of the most famous and widely used corporate communicators in the world, Ryver's main competitor, and is proposing to replace email communication with private and public (in-team) groups focused on particular teams and / or issues. You can create public groups where your entire team can interact and collaborate, and private groups for each industry where only those in the group can view messages.

Slack allows you to reply to emails from your own tool, send and share files, send private messages, install multiple plugins to add new features and more.

In the free plan, message fetching retrieves only the latest 10 thousand messages, the storage limit is 5 GB and some more limitations you can check on this link.

Where and how can I use Slack 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 cool way to use Slack 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 Slack, HR can use the service to send messages and information to employees. Send Christmas messages, a special message on the employee's birthday, etc.

Anyway, the advantage of Slack is that it can be sent in many different ways (Email, Web, Smartphone) wherever you are, inside or outside the company and it represents a big revolution in the way people interact with The informations.

I use Slack where I work as a tool for communication, monitoring and alerts of IT routines, such as critical job failures, backup problems, database corruption, slow queries, locks, etc. Whenever a critical routine fails, an internal email and notification via Slack are sent, where a push notification arrives on my mobile phone (just like Whatsapp) and wherever I am, I become aware of that failure.

How to add users to Slack

The first step in building the integration is to create your Slack account, accessing this link. Once created, you will receive an email to log in to your account.

Slack splash screen, with some groups I created for this post

To add new users, just click on the menu (Your name) and select the option “Invite people”

You can add your contacts from your Google account, import multiple contacts in CSV format and more. Once you invite others in, they will receive a link to join your team and after they finish signing up, they will be able to view the data through their web browser or mobile app.

How to enable Slack API

In order to be able to send messages through Slack, we need to create a custom integration. To do this go to the URL https://api.slack.com/docs/oauth-test-tokens logged in to the user account that will serve as BOT and will send the automatic messages.

Click the "Create Token" button.

Copy the generated token to your application. It is with this huge code (76 characters in my case) that you will be authenticating with the API and will be able to send your messages via CLR (C #).

How to integrate the database with Slack

Once you have created your Slack account and registered the user you will use to send messages to teams, let's go to the interesting part: Create .NET (C #) code that will perform SQL database integration Server with Slack API.

Prerequisite: Utils.cs

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 SP to send Slack .

As with Ryver, in order to be able to send messages, the BOT user has to 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.

StpEnvia_Mensagem_Slack Source Code:

Usage examples for groups (public or private)
Unlike Ryver, where you need to get the ID of each group, either private or public, to be able to send messages, where the Ryver API differentiates sending to private group is different from public group, in Slack the Submission is made by the name of the group, with no difference between private or public to perform the submission.

Usage examples for private messages (DM)
To send direct messages (DM) or also private calls, simply enter @username.

Sending colorful messages in Slack

A nice feature of Slack's API is the so-called attachments, which allows you to improve the formatting and visualization of messages sent by the API, making the information clearer and more elegant. Let's see how it works:

Stored Procedure Code stpEnvia_Mensagem_Slack_Completa

Examples of use

Sending Formatted Messages

Sending Messages with External Links
Clicking on the message title will take you to the URL https://www.dirceuresende.com/blog 🙂

Message wrapping with line break

To learn more about Slack formatting, visit this link.

And that's it folks!
I hope you enjoyed this post and see you next time!

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 dm Slack

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 dm Slack