UDP to SQL receiver

Бывают ситуации, как это было, например, у нас, когда нужно принимать данные по UDP b записывать их в SQL таблицу. В нашем случае это было нужно чтобы принимать сигналы с контроллера mitsubishi, так же я писал и о том как принять такой сигнал и записать в SQL. Но по сути этот формат передачи довольно-таки универсальный и может понадобиться для других целей, и потому каждый раз переписывать программу для приема и передачи это не правильно. Сегодня вот родилось такое, почти универсальное решение, о чем я и хочу поведать в данной статье.
Для начала, как я уже и писал в предыдущей статье, была реализована функция приема сигнала. На примере из интернета это делается довольно просто: вот статья как сделать UDP chat.
Для универсальности программы к ней был добавлен файл конфигурации, где можно было бы задавать номер порта для приема. Как это сделать можно почитать тут. Позднее туда же добавил строку для соединения с SQL (How to Use ConnectionString from App config file in Visual Studio C#). Об этом есть материал в ютубе и опять же на сайте майкрософта. Ну и потом было самое сложное правильно согласовать типы данных, чтобы запись в таблицу происходила без ошибок. В итоге, после полутора часов гугления и проб, выяснилось, что для того чтобы записывать бинарные данные нужно обязательно использовать параметры. Короче предлагаю вашему вниманию итоговый код, где попытаюсь расписать что где и к сему, но т.к. я совсем не программист где-то могут быть не точности, но сама прога рабочая 100% была у меня.

using System;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace UdpSample
{
    class udprec
    {
        private static int localPort;
        private static string table_name;
       
        public static SqlConnection myConn = new SqlConnection(

ConfigurationManager.ConnectionStrings["connstr"].ConnectionString); //строка соединения с SQL которая берется из конфига
        SqlCommand myCommand = new SqlCommand("Command String", myConn);
        [STAThread]
        static void Main(string[] args)
        {
            try
            {
                // Получаем данные, необходимые для соединения
                localPort = Convert.ToInt16(ConfigurationManager.AppSettings["port"]); //номер порта берется тоже из конфига и преобразуется в число
                // Создаем поток для прослушивания
                Thread tRec = new Thread(new ThreadStart(Receiver));
                tRec.Start();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Возникло исключение: " + ex.ToString() + "\n  " + ex.Message);
            }
        }

        public static void Receiver()
        {
            // Создаем UdpClient для чтения входящих данных
            UdpClient receivingUdpClient = new UdpClient(localPort);
            IPEndPoint RemoteIpEndPoint = null;

            try
            {
               while (true)
                {
                    // Ожидание дейтаграммы
                    byte[] receiveBytes = receivingUdpClient.Receive(ref RemoteIpEndPoint);

                    // Преобразуем и отображаем данные
                    string returnData = Encoding.UTF8.GetString(receiveBytes);
                    // Console.WriteLine(" -> " + returnData.ToString()); 

// можно убрать комментирование и тогда принятые данные будут показываться текстом в окне консоли
                    try
                    {
                        table_name = (ConfigurationManager.AppSettings["table"]); // название таблицы из конфига
                        SqlCommand cmd = new SqlCommand("INSERT INTO " + table_name +" values (@bindata)", myConn);
                        myConn.Open();
                        var param = new SqlParameter("@bindata", SqlDbType.Binary) // а это параметр о чем и говорилось на одном из форумов
                        {   Value = receiveBytes  };
                        cmd.Parameters.Add(param);
                        cmd.ExecuteNonQuery();
                        myConn.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("SQL исключение: " + ex.ToString() + "\n  " + ex.Message);
                    }
                }    
                         
            }
            catch (Exception ex)
            {
                Console.WriteLine("Возникло исключение: " + ex.ToString() + "\n  " + ex.Message);
            }
        }
    }
}

Вот и весь код. Файл конфигурации открывается в локноте и легко редактируется:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
 
  <appSettings>
    <add key="port" value="1155"/>
    <add key="table" value="Raw_data"/>
  </appSettings>

  <connectionStrings>
    <add connectionString="Password=pass1;Persist Security Info=True;User ID=user1;Initial Catalog=TestDB;Data Source=192.168.0.77" name="connstr"/>
  </connectionStrings>
</configuration>

Со стороны SQL просто создается таблица с одной колонкой в формате varbinary(MAX):

 

Ну а чтобы данные разделить и выделить именно то что необходимо из всего это, в Raw_data добавляетяс триггер который запускается при вставке новых данных в эту таблицу. Все вставленные данные можно разделить на слова используя substring, а слова на биты используя cast. Код триггера ниже

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[instrig] ON [dbo].[Raw_data]
   AFTER INSERT
AS
DECLARE @data AS varbinary(max)
DECLARE @word1 AS int
DECLARE @word2 AS int
DECLARE @word3 AS int
DECLARE @down_bit AS bit
Set @data = (select bindat from inserted)
set @word1 = (select substring(@data,1,2) as initial)
set @word2 = (select substring(@data,3,2) as initial)
set @word3 = (select substring(@data,5,2) as initial)
set @down_bit= cast (@word1 & 2 as bit)
    if @down_bit = 1
    BEGIN
      set @word3=555
    END
INSERT INTO tab2 (w1,w2,w3) values (@word1,@word2,@word3)

ссылка на скачивание udp2sql.zip
В общем чтобы использовать программу нужно создать таблицу с любым именем и одним столбцом с любым именем, но с конкретным типом varbinary (max). В файле конфигурации нужно прописать строку подключения и название таблицы, а так же номер порта для udp.

UPD теперь в конфиге можно задать и имя столбца куда будет помещаться телеграмма в бинарном формате, и тогда теперь в эту же таблицу можно добавить еще несколько столбцов, например столбец с датой, куда можно записывать триггеров время приема телеграммы, например так:

update UDP_RAW set date_time_loc = GETDATE(), ID_Coil = @ID_Coil from inserted where UDP_RAW.id = inserted.id

И еще несколько моментов по триггерам и выделению данных. У нас данные передаются перевернуьые, байты поменяны местами в слове, но порядок слов соблюдается. И теперь чтобы узнать реальную толщину, котороя передается четвертым словом (то есть это 7 и 8 байты) я сделал так:
SET @Thickness = (substring(@data,8,1) + substring(@data,7,1))*1 - то есть снача 8, а потом 7. умножаю на 1 чтобы формат стал real.
Довольно интересно и оказалось просто было принимать текстовые данные:
SET @ID_Coil = CAST(substring(@data,19,8) AS VARCHAR(8))
восемь байт начиная с 19 это 8 символов и их не пришлось переворачивать, записываются с таблицу как есть. Если символ описывается не одним байтом а двумя, то возможно есть смысл попробовать использовать формат nvarchar

И еще момент - чтобы сделать работу программы в виде сервиса можно как и говорилось использовать nssm download here
запустить команду c:\\test\nssm install name_service и все, это если распаковать архив в папку тест

Дополнение:
В связи с открытием способа компилить C# код из батника, необходимость в добавлении файла конфигурации пропала, можно жестко прописать все конфигурируемые параметры в программе и скомпилить ее под свои нужды
Ниже привожу код целиком, который тупо копируется, вставляется в txt файл, переименовывается в bat, и запускается для компиляции - на выходе получаем программу которая получает данные по udp и складывает их в специальную таблицу SQL
В этой версии добавлена возможность сохранять исключения в текстовый файл, с именем содержащим текущее время:
System.IO.File.WriteAllText("C:\\logs\\err_" + DateTime.Now.ToString("HHmmss") + ".txt", "Возникло исключение: " + ex.ToString() + "\n " + ex.Message); 
Данный метод создает файл с заданным именем (все что в скобках до запятой), открывает его (если файл уже имеется то не создает, а просто сразу открывает), записывает все что в скобках после запятой, и закрывает.
Если указанной папки logs на диске С:\ не существует, то будет выдаваться ошибка, поэтому ранее в самом начале эта папка создается:
System.IO.Directory.CreateDirectory("C:\\logs\\");
Опять же нет необходимости проверять есть ли данная папка или нет. Если папка есть то ничего не произойдет, если нет, то она будет создана.
Если отсюда код скопируется не очень красиво, то можно воспользоваться украшателем кода на С#

// 2>nul||@goto :batch
/*
:batch
@echo off
setlocal

:: find csc.exe
set "csc="
for /r "%SystemRoot%\Microsoft.NET\Framework\" %%# in ("*csc.exe") do  set "csc=%%#"

if not exist "%csc%" (
   echo no .net framework installed
   exit /b 10
)

if not exist "%~n0.exe" (
   call %csc% /nologo /r:"Microsoft.VisualBasic.dll" /out:"%~n0.exe" "%~dpsfnx0" || (
      exit /b %errorlevel%
   )
)
%~n0.exe %*
endlocal & exit /b %errorlevel%

*/

using System;
using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace UdpSample {
 class udprec {
  private static int localPort = Convert.ToInt16("1155"); // на какой порт принимаются UDP
  private static string table_name = ("raw_data"); // в какую таблицу добавляются данные
  private static string column_name = ("data"); // в какую колонку этой таблицы добавляются данные
  public static SqlConnection myConn = new SqlConnection("Password=12345;Persist Security Info=True;User ID=user123;Initial Catalog=testdb;Data Source=WIN7VM\\WINCC");
  // строка подключения к SQL
  SqlCommand myCommand = new SqlCommand("Command String", myConn);
  [STAThread]
  static void Main(string[] args) {
   System.IO.Directory.CreateDirectory("C:\\logs\\");
   try {
    Thread tRec = new Thread(new ThreadStart(Receiver));
    tRec.Start();
   } catch (Exception ex) {
    Console.WriteLine("Возникло исключение: " + ex.ToString() + "\n " + ex.Message);
    System.IO.File.WriteAllText("C:\\logs\\err_" + DateTime.Now.ToString("HHmmss") + ".txt", "Возникло исключение: " + ex.ToString() + "\n " + ex.Message);
   }
  }

  public static void Receiver() {
   // Создаем UdpClient для чтения входящих данных
   UdpClient receivingUdpClient = new UdpClient(localPort);
   IPEndPoint RemoteIpEndPoint = null;

   try {
    while (true) {
     // Ожидание дейтаграммы
     byte[] receiveBytes = receivingUdpClient.Receive(ref RemoteIpEndPoint);

     // Преобразуем и отображаем данные. Раскоментировать для отображения в консоли
     string returnData = Encoding.UTF8.GetString(receiveBytes);
     Console.WriteLine(" -> " + returnData.ToString());

     // вставить в таблицу полученную дейтаграмму
     try {
      SqlCommand cmd = new SqlCommand("INSERT INTO " + table_name + " (" + column_name + ") values (@bindata)", myConn);
      myConn.Open();
      var param = new SqlParameter("@bindata", SqlDbType.Binary) {
       Value = receiveBytes
      };
      cmd.Parameters.Add(param);
      cmd.ExecuteNonQuery();
      myConn.Close();
     } catch (Exception ex) {
      Console.WriteLine("SQL исключение: " + ex.ToString() + "\n " + ex.Message);
      System.IO.File.WriteAllText("C:\\logs\\err_sql_" + DateTime.Now.ToString("HHmmss") + ".txt", "SQL исключение: " + ex.ToString() + "\n " + ex.Message);
     }
    }
   } catch (Exception ex) {
    Console.WriteLine("UDP exception: reciever " + ex.ToString() + "\n " + ex.Message);
    System.IO.File.WriteAllText("C:\\logs\\err_udp_" + DateTime.Now.ToString("HHmmss") + ".txt", "UDP exception: reciever " + ex.ToString() + "\n " + ex.Message);
   }
  }
 }
}