Using a Socket API in SQL CLR

Question

I'm trying to use a third-party API that is a socket based API with SQL CLR and vb.net. I know how to do this with REST APIs, however, I cannot figure out how to get started with a Socket API.

Does anyone have any references or a very basic "GET" example I can use to learn from?

score 0 · Answer 1 · answered Aug 17 '19 at 04:27

@ptownbro, it seems to me you ask how to actually use a Socket API, not so much Socket API in SQLCLR. My suggestion is to read the documentation of the API and see what methods there are. Generally speaking this is what you do:

Define an endpoint, where to send to.
Create an instance of the socket class.
Connect the socket instance to the endpoint.
Send the message as a byte array using whatever method the socket instance exposes for sending.

The above is very generic explanation, as I do not know what API you intend to use. Here is a page with an example how to do it in C# using .NET API.

Notice that I only mention sending. I strongly advise against receiving if you intend to do this from SQLCLR, as you should use "normal" T-SQL DML statements to get data into SQL Server.

Thanks. I'll take a look at your example. In the meantime, forgive me but couple things were confusing. I did read their documentation of their methods and it's straightforward (you send JSON with your method call and data (if any) included). However, I just know where to start in where to send the JSON, how to send it, how to read what is returned. Again, if this was REST I'd know exactly what to do, but I'm not familiar with how to with Socket API and was hoping to find something simple to get started — ptownbro, Aug 17 '19 at 20:55

score 0 · Answer 2 · answered Aug 18 '19 at 18:52

I think I found what I was looking for - which was a start to get me understanding how this works. I found few tutorials on youtube to get me started

This one has two parts:

https://www.youtube.com/watch?v=T2hgTKL_Haw&t=382s

https://www.youtube.com/watch?v=X2Hvs2qepBw&t=126s

This one has one part:

https://www.youtube.com/watch?v=MSiBbtxWpI8

The first tutorial creates a console application that acts as the "Server" and then creates a windows form application to act as the "Client". With these demo applications I was able to figure out how to use the "System.Net.Socket" class to learn how a Socket API is likely to work so I can see if I can apply that knowledge to the third-party Socket API in my final solution.

From here, I will be able to utilize it in/with SQL CLR and do what is needed.

I'm good for now. Wish me luck!

To help others, in addition to the links above, here is the code I used and what I did.

First the setup:

I did this on my personal computer where I have IIS setup to can treat my computer as a server and use localhost.
I created 2 separate projects/solutions in Visual Studio 1) one as console application which acts as a "Server" and 2) one as a windows form application which acts as a client.

Code for the Server application:

Imports System.Net.Sockets
Imports System.Text

Module Module1

Sub Main()
    Dim VarSocketServer As New TcpListener(8888)
    Dim VarSocketClient As TcpClient
    Dim VarRequestCount As Integer = 0

    VarSocketServer.Start()
    Console.WriteLine(">> Server Started")

    VarSocketClient = VarSocketServer.AcceptTcpClient
    Console.WriteLine(">> Accept connection from client")

    While (True)
        Try
            VarRequestCount = VarRequestCount + 1

            Dim VarNetworkStream As NetworkStream = VarSocketClient.GetStream()
            Dim VarInStream(10024) As Byte
            Dim VarBytesReadAs Integer = VarNetworkStream.Read(VarInStream, 0, VarInStream.Length)
            Dim VarDataFromClient As String = System.Text.Encoding.ASCII.GetString(VarInStream)
            VarDataFromClient = VarDataFromClient.Substring(0, VarDataFromClient.IndexOf("$"))

        Console.WriteLine(">> Data from client: " + VarDataFromClient)

            Dim VarServerResponse As String = "Server response: " + Convert.ToString(VarRequestCount)
            Dim VarSendByte As [Byte]() = System.Text.Encoding.ASCII.GetBytes(VarServerResponse)
            VarNetworkStream.Write(VarSendByte, 0, VarSendByte.Length)
            VarNetworkStream.Flush()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End While

    VarSocketClient.Close()
    VarSocketServer.Stop()

    Console.WriteLine(">> Exit")
    Console.ReadLine()
End Sub
End Module

Code for client code:

Imports System.Net.Sockets
Imports System.Text

Public Class Form1
Dim VarSocketServer As New TcpClient
Dim VarNetworkStream As NetworkStream

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    TextBox1.Text = TextBox1.Text + Environment.NewLine + ">> Client Started")

    VarSocketServer.Connect("127.0.0.1", 8888)
    Label1.Text = "The client is connected to server"

    Dim VarNetworkStream As NetworkStream = VarSocketServer.GetStream()

    Dim VarClientRequest As String = "Message from client$"
    Dim VarSendByte As [Byte]() = System.Text.Encoding.ASCII.GetBytes(VarClientRequest)
    VarNetworkStream.Write(VarSendByte, 0, VarSendByte.Length)
    VarNetworkStream.Flush()

    Dim VarInStream(10024) As Byte
    Dim VarBytesRead = VarNetworkStream.Read(VarInStream, 0, VarInStream.Length)
    Dim VarDataFromServer As String = System.Text.Encoding.ASCII.GetString(VarInStream)
    TextBox1.Text = TextBox1.Text + Environment.NewLine + ">> Data from server: " + VarDataFromServer)

    VarNetworkStream.Flush()
End Sub
End Class

Using a Socket API in SQL CLR

2 Answers2