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

Views: 1.944 views

In this article

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

using System;

namespace Bibliotecas.Model
{

    public static class Utils
    {

        public static string converteStringWebService(string Ds_Texto)
        {
            
            // Recomendação do site do Slack
            var retorno = Ds_Texto.Replace("<", "&lt;").Replace("&", "&amp;").Replace(">", "&gt;");

            // Quebra de linha
            retorno = retorno.Replace("\\n", "\n");

            // Tratamento do caractere "\"    
            retorno = retorno.Replace(@"\", @"\\");

            // Tratamento de Aspas duplas
            retorno = retorno.Replace(@"""", @"\""");
            
            // Resultado final
            return Uri.EscapeDataString(retorno);
        }
        
    }
    
}

using System;

namespace Bibliotecas.Model

{

public static class Utils

{

public static string converteStringWebService(string Ds_Texto)

{

// Recomendação do site do Slack

var retorno = Ds_Texto.Replace("<", "<").Replace("&", "&").Replace(">", ">");

// Quebra de linha

retorno = retorno.Replace("\\n", "\n");

// Tratamento do caractere "\"

retorno = retorno.Replace(@"\", @"\\");

// Tratamento de Aspas duplas

retorno = retorno.Replace(@"""", @"\""");

// Resultado final

return Uri.EscapeDataString(retorno);

}

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:

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_Slack(SqlString Ds_Canal, SqlString Ds_Mensagem)
    {

        const string slackToken = "seu_token_personalizado";
        const string usuario = "BOT Dirceu Resende";

        try
        {

            var mensagem = Utils.converteStringWebService(Ds_Mensagem.Value);


            if (mensagem.Length > 4000)
                Retorno.Erro("O tamanho máximo da mensagem deve ser inferior a 4.000 caracteres");


            var canal = Ds_Canal.Value;
            var canais = canal.Split(';');

            foreach (var nomeCanal in canais)
            {

                var request = (HttpWebRequest) WebRequest.Create("https://slack.com/api/chat.postMessage");
                
                request.Method = "POST";

                request.UserAgent = "curl/7.45.0";
                request.ContentType = "application/x-www-form-urlencoded";

                var parametros = $"token={slackToken}&channel={nomeCanal.Trim()}&text={mensagem}&username={usuario}&as_user=false";

                var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);
                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();

                                if (responseFromServer.Contains("\"ok\":false"))
                                    Retorno.Erro(responseFromServer);
                                else
                                    Retorno.Mensagem(responseFromServer);

                            }
                        }

                    }

                }

            }

        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message);
        }
    }

};

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_Slack(SqlString Ds_Canal, SqlString Ds_Mensagem)

{

const string slackToken = "seu_token_personalizado";

const string usuario = "BOT Dirceu Resende";

try

{

var mensagem = Utils.converteStringWebService(Ds_Mensagem.Value);

if (mensagem.Length > 4000)

Retorno.Erro("O tamanho máximo da mensagem deve ser inferior a 4.000 caracteres");

var canal = Ds_Canal.Value;

var canais = canal.Split(';');

foreach (var nomeCanal in canais)

{

var request = (HttpWebRequest) WebRequest.Create("https://slack.com/api/chat.postMessage");

request.Method = "POST";

request.UserAgent = "curl/7.45.0";

request.ContentType = "application/x-www-form-urlencoded";

var parametros = $"token={slackToken}&channel={nomeCanal.Trim()}&text={mensagem}&username={usuario}&as_user=false";

var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);

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();

if (responseFromServer.Contains("\"ok\":false"))

Retorno.Erro(responseFromServer);

else

Retorno.Mensagem(responseFromServer);

}

catch (Exception e)

{

Retorno.Erro("Erro : " + e.Message);

}

};

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.

EXEC CLR.dbo.stpEnvia_Mensagem_Slack 
    @Ds_Canal = N'informativos', -- nvarchar(max)
    @Ds_Mensagem = N'Teste de mensagem para o grupo público #informativos' -- nvarchar(max)

EXEC CLR.dbo.stpEnvia_Mensagem_Slack

@Ds_Canal = N'informativos', -- nvarchar(max)

@Ds_Mensagem = N'Teste de mensagem para o grupo público #informativos' -- nvarchar(max)

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

EXEC CLR.dbo.stpEnvia_Mensagem_Slack 
    @Ds_Canal = N'@bot_dirceuresende', -- nvarchar(max)
    @Ds_Mensagem = N'Teste de mensagem privada para o usuário @bot_dirceuresende' -- nvarchar(max)

EXEC CLR.dbo.stpEnvia_Mensagem_Slack

@Ds_Canal = N'@bot_dirceuresende', -- nvarchar(max)

@Ds_Mensagem = N'Teste de mensagem privada para o usuário @bot_dirceuresende' -- nvarchar(max)

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

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_Slack_Completa(SqlString Ds_Canal, SqlString Ds_Mensagem, SqlString Ds_Cor_Hexa, SqlString Ds_Titulo, SqlString Ds_Link, SqlString Ds_Mensagem_Interna)
    {


        try
        {

            const string slackToken = "seu_token_slack";
            const string usuario = "BOT Dirceu Resende";

            var mensagem = Utils.converteStringWebService(Ds_Mensagem.Value);
            var mensagemInterna = Utils.converteStringWebService(Ds_Mensagem_Interna.Value);
            var titulo = Utils.converteStringWebService(Ds_Titulo.Value);
            var link = Utils.converteStringWebService(Ds_Link.Value);
            var cor = (!string.IsNullOrEmpty(Ds_Cor_Hexa.Value.Trim()) ? Ds_Cor_Hexa.Value : "");
            var canal = Ds_Canal.Value;

            if (string.IsNullOrEmpty(mensagem) && string.IsNullOrEmpty(mensagemInterna))
                Retorno.Erro("O valor do parâmetro @Ds_Mensagem E @Ds_Mensagem_Interna não pode ser vazio. Pelo menos um dos dois deve ser preenchido.");

            var texto = @"
[
    {
        ""fallback"": """ + mensagem + @""",
        ""color"": ""#" + cor + @""",
        ""pretext"": """ + mensagem + @""",
        ""title"": """ + titulo + @""",
        ""title_link"": """ + link + @""",
        ""text"": """ + mensagemInterna + @""",
        ""mrkdwn_in"": [""text"", ""pretext""]
    }
]";
            var canais = canal.Split(';');

            foreach (var nomeCanal in canais)
            {

                var request = (HttpWebRequest)WebRequest.Create("https://slack.com/api/chat.postMessage");
                
                request.Method = "POST";

                request.UserAgent = "curl/7.45.0";
                request.ContentType = "application/x-www-form-urlencoded";

                var parametros = $"token={slackToken}&channel={nomeCanal.Trim()}&username={usuario}&as_user=false&attachments={texto}";

                var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);
                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();

                                if (responseFromServer.Contains("\"ok\":false"))
                                    Retorno.Erro(responseFromServer);
                                else
                                    Retorno.Mensagem(responseFromServer);

                            }
                        }

                    }

                }
            }

        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message);
        }

    }

};

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_Slack_Completa(SqlString Ds_Canal, SqlString Ds_Mensagem, SqlString Ds_Cor_Hexa, SqlString Ds_Titulo, SqlString Ds_Link, SqlString Ds_Mensagem_Interna)

{

try

{

const string slackToken = "seu_token_slack";

const string usuario = "BOT Dirceu Resende";

var mensagem = Utils.converteStringWebService(Ds_Mensagem.Value);

var mensagemInterna = Utils.converteStringWebService(Ds_Mensagem_Interna.Value);

var titulo = Utils.converteStringWebService(Ds_Titulo.Value);

var link = Utils.converteStringWebService(Ds_Link.Value);

var cor = (!string.IsNullOrEmpty(Ds_Cor_Hexa.Value.Trim()) ? Ds_Cor_Hexa.Value : "");

var canal = Ds_Canal.Value;

if (string.IsNullOrEmpty(mensagem) && string.IsNullOrEmpty(mensagemInterna))

Retorno.Erro("O valor do parâmetro @Ds_Mensagem E @Ds_Mensagem_Interna não pode ser vazio. Pelo menos um dos dois deve ser preenchido.");

var texto = @"

[

{

""fallback"": """ + mensagem + @""",

""color"": ""#" + cor + @""",

""pretext"": """ + mensagem + @""",

""title"": """ + titulo + @""",

""title_link"": """ + link + @""",

""text"": """ + mensagemInterna + @""",

""mrkdwn_in"": [""text"", ""pretext""]

}

]";

var canais = canal.Split(';');

foreach (var nomeCanal in canais)

{

var request = (HttpWebRequest)WebRequest.Create("https://slack.com/api/chat.postMessage");

request.Method = "POST";

request.UserAgent = "curl/7.45.0";

request.ContentType = "application/x-www-form-urlencoded";

var parametros = $"token={slackToken}&channel={nomeCanal.Trim()}&username={usuario}&as_user=false&attachments={texto}";

var buffer = Encoding.GetEncoding("UTF-8").GetBytes(parametros);

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();

if (responseFromServer.Contains("\"ok\":false"))

Retorno.Erro(responseFromServer);

else

Retorno.Mensagem(responseFromServer);

}

catch (Exception e)

{

Retorno.Erro("Erro : " + e.Message);

}

};

Examples of use

Sending Formatted Messages

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa 
    @Ds_Canal = N'informativos', -- nvarchar(max)
    @Ds_Mensagem = N'Teste de Mensagem', -- nvarchar(max)
    @Ds_Cor_Hexa = N'ff0000', -- nvarchar(max)
    @Ds_Titulo = N'Título da Mensagem com Borda Vermelha', -- nvarchar(max)
    @Ds_Link = N'', -- nvarchar(max)
    @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max)

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa

@Ds_Canal = N'informativos', -- nvarchar(max)

@Ds_Mensagem = N'Teste de Mensagem', -- nvarchar(max)

@Ds_Cor_Hexa = N'ff0000', -- nvarchar(max)

@Ds_Titulo = N'Título da Mensagem com Borda Vermelha', -- nvarchar(max)

@Ds_Link = N'', -- nvarchar(max)

@Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max)

Sending Messages with External Links
By clicking on the message title, you will be directed to the URL https://dirceuresende.com/blog 🙂

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa 
    @Ds_Canal = N'informativos', -- nvarchar(max)
    @Ds_Mensagem = N'', -- nvarchar(max)
    @Ds_Cor_Hexa = N'00ff00', -- nvarchar(max)
    @Ds_Titulo = N'Título da Mensagem com Borda Verde', -- nvarchar(max)
    @Ds_Link = N'https://dirceuresende.com/blog', -- nvarchar(max)
    @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max)

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa

@Ds_Canal = N'informativos', -- nvarchar(max)

@Ds_Mensagem = N'', -- nvarchar(max)

@Ds_Cor_Hexa = N'00ff00', -- nvarchar(max)

@Ds_Titulo = N'Título da Mensagem com Borda Verde', -- nvarchar(max)

@Ds_Link = N'https://dirceuresende.com/blog', -- nvarchar(max)

@Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max)

Message wrapping with line break

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa 
    @Ds_Canal = N'informativos', -- nvarchar(max)
    @Ds_Mensagem = N'', -- nvarchar(max)
    @Ds_Cor_Hexa = N'0000ff', -- nvarchar(max)
    @Ds_Titulo = N'', -- nvarchar(max)
    @Ds_Link = N'', -- nvarchar(max)
    @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack com Borda Azul.\n\nAceita *negrito*\nAceita_italico_\nAceita ~sublinhado~' -- nvarchar(max)

EXEC CLR.dbo.stpEnvia_Mensagem_Slack_Completa

@Ds_Canal = N'informativos', -- nvarchar(max)

@Ds_Mensagem = N'', -- nvarchar(max)

@Ds_Cor_Hexa = N'0000ff', -- nvarchar(max)

@Ds_Titulo = N'', -- nvarchar(max)

@Ds_Link = N'', -- nvarchar(max)

@Ds_Mensagem_Interna = N'Essa é a mensagem do Slack com Borda Azul.\n\nAceita *negrito*\nAceita_italico_\nAceita ~sublinhado~' -- nvarchar(max)

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

Diego said:

26 February 2018 to 10: 06

Good morning Dirceu… I tried to implement this solution, but when I run STP I have the following error message:

Lookup Error - SQL Server Database Error: A .NET Framework error occurred during execution of user-defined routine or aggregate “STP_Envia_Slack”:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version = 4.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check (Object demand, StackCrawlMark & stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand ()
at System.Data.Common.DbConnectionOptions.DemandPermission ()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand (DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen (TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open ()
at Libraries.Model.Retorno.Error (String Error)
at StoredProcedures.STP_Envia_Slack (SqlString Ds_Canal, SqlString Ds_Message)
.

Can you help me with this error?

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

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

What is Slack?

Where and how can I use Slack in my business?

How to add users to Slack

How to enable Slack API

How to integrate the database with Slack

Sending colorful messages in Slack

Examples of use

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

You may also like ...

1 Response

Leave a Comment Cancel reply

What is Slack?

Where and how can I use Slack in my business?

How to add users to Slack

How to enable Slack API

How to integrate the database with Slack

Sending colorful messages in Slack

Examples of use

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

You may also like ...

SQL Server - How to create a code version of your Stored Procedures in HTML and with comments on the change

Installing and Configuring SQL Server 2014 on Windows Server 2012 R2

SQL Server – How to filter and separate the number and text of a string

1 Response

Leave a Comment Cancel reply