Remote integration between VBA and C# (using JSON)

Question

I'm trying to find an easy way to send a string (in my case JSON) from VBA data provider to C# server.

My Server already has a high level communication between server and client. In my case I use CORBA IIOP.NET, but I can easily change it to WCF.

My question is: How can I send a string to my C# Server?

From my C# server I got the code from Code Project (not tested)

public void Server()
{
    //start server
    TcpListener tcpServerListener = new TcpListener(6666);
    tcpServerListener.Start(); 

    //block tcplistener to accept incoming connection
    Socket serverSocket = tcpServerListener.AcceptSocket();

    //open network stream on accepted socket
    NetworkStream serverSockStream = new NetworkStream(serverSocket);
    StreamReader serverStreamReader = new StreamReader(serverSockStream);

    Console.writeLine(serverStreamReader.ReadeLine());
}

I'm having dificult to send the string via VBA. Can anyone help me with that.

PS: To parse JSON in C# I'm using JSON.NET and VBA I'm using VB JSON

Answer 1

Now I see that we can't ask for good practice and ideias to help in VBA, Excel-VBA tags. So I did my research and made my choices. Let me know if someone has a better ideia to do this remote integration.

1. First I chose JSON because I think it's a good ideia to send packages without using CORBA . So I found a JSON for C# implementation and VBA implementation.
2. Then I didn't find any protocol better than TCP to use.

My example has three tests. One server (C#) and two clients (C# and VBA) that can be seen bellow.

I use the ideia of Store and Company just to exemplify JSON for .NET works well with inheritance. Here is my classes:

public class Company {
    public string Name { get; set; }
}

public class Store : Company {
    public string Country { get; set; }
    public Dictionary<String, Double> Salaries { get; set; }
}

My C# client just create two Stores serialize and send it to Server using TcpClient.

Code:

static void Main(string[] args) {

  Store storeUSA = new Store {
    Country = "USA",
    Name = "Amazon.com",
    Salaries = new Dictionary<string, double>{
      {"Jones", 100000},
      {"Deborah",200000}
    }
  };

  Store storeBR = new Store {
    Country = "Brazil",
    Name = "Amazon.com",
    Salaries = new Dictionary<string, double>{
      {"Ricardo", 500000},
      {"Math", 800000}
    }
  };

  TcpClient tcpClient = new TcpClient("192.168.1.63", 6666);
  Console.WriteLine("Connected to Server");

  using (NetworkStream clientSockStream = tcpClient.GetStream()) {
    using (StreamWriter sw = new StreamWriter(clientSockStream)) {

      string output = JsonConvert.SerializeObject(storeUSA, Formatting.None);
      sw.WriteLine(output);
      sw.Flush();

      System.Threading.Thread.Sleep(2000);

      output = JsonConvert.SerializeObject(storeBR, Formatting.None);
      sw.WriteLine(output);
      sw.Flush();

      Console.WriteLine("Closing");
    }
  }

  Console.WriteLine("Done");
  Console.ReadLine();
}

My VBA client is a slightly different. But it's the same ideia

Code:

Sub main()
  Dim tcp As New TCPClient
  Dim ok As Boolean

  ok = tcp.Connect("192.168.1.63", 6666)
  If Not ok Then
      Debug.Print "Error Connecting"
      Exit Sub
  End If

  tcp.Send "{""Country"":""France"",""Salaries"":{""Peyton Manning"":800.0,""Blaine Gabbert"":200000.0},""Name"":""Amazon.net""}"
  tcp.CloseConnection

End Sub

Finally my server that is really simple. It just listen a port and when a message come it deserialize JSON:

static void Main(string[] args){
        IPAddress ip = IPAddress.Parse("192.168.1.63");
        TcpListener tcp = new TcpListener(ip, 6666);

        tcp.Start();
        Console.WriteLine("[DEBUG] End point is: " + tcp.LocalEndpoint);

        Stopwatch time = new Stopwatch();
        time.Start();

        while (time.Elapsed < TimeSpan.FromSeconds(10)){
            TcpClient client = tcp.AcceptTcpClient();

            Console.WriteLine("Receiving message ... ");
            using (NetworkStream networkStream = client.GetStream()){
                using (StreamReader streamReader = new StreamReader(networkStream)){

                    while (!streamReader.EndOfStream){
                        var json = streamReader.ReadLine();
                        Console.WriteLine("JSON:" + json);
                        Store store = JsonConvert.DeserializeObject<Store>(json);
                        Console.WriteLine("Data:");
                        Console.WriteLine(store.ToString());
                    }
                }
            }
            client.Close();
            Console.WriteLine("... Message received");
        }
        tcp.Stop();
        time.Stop();
        Console.WriteLine("Server no longer working");
        Console.ReadKey();
    }

    public class Company{
        public string Name { get; set; }
    }

    public class Store : Company{
        public string Country { get; set; }
        public Dictionary<String, Double> Salaries { get; set; }

        public override string ToString()
        {
            StringBuilder str = new StringBuilder();
            str.Append(String.Format("Name= {0}; Country={1};\n Salaries:\n", Name, Country));
            if (Salaries != null)
            {
                foreach (var salary in Salaries)
                {
                    str.Append(String.Format("  '{0}' = {1}\n", salary.Key, salary.Value));
                }
            }
            return str.ToString();
        }
    }

Note that I use TcpClient for VBA. Thanks rory - code bellow:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Const sckClosed = 0            ' Default. Closed
Const sckOpen = 1              ' Open
Const sckListening = 2         ' Listening
Const sckConnectionPending = 3 ' Connection pending
Const sckResolvingHost = 4     ' Resolving host
Const sckHostResolved = 5      ' Host resolved
Const sckConnecting = 6        ' Connecting
Const sckConnected = 7         ' Connected
Const sckClosing = 8           ' Peer is closing the connection
Const sckError = 9             ' Error

Const TIMEOUT_MAX As Integer = 10

Private host As String

Private port As String

Private socket As winsock

Function Connect(ByVal fhost As String, ByVal fport As Integer) As Boolean
    Dim secs As Integer


    host = fhost
    port = fport

    Debug.Print Now & " - Client Started"
    Connect = False

    'Create WinSock
    On Error Resume Next
    Set socket = CreateObject("MSWINSOCK.Winsock")
    If Err.Number <> 0 Then
        MsgBox "You need to add MsWinSock reference:" & vbCrLf & "Tools -> References -> Browse -> %SYSEM%\MSWINSCK.OCX"
        Debug.Print Now & " - Winsock Object Error."
        Exit Function
    End If
    On Error GoTo 0

    'Connect
    socket.RemoteHost = host
    socket.RemotePort = port
    socket.Connect

    'Wait for connected state (or TIMEOUT_MAX)
    While socket.State <> sckConnected And secs < TIMEOUT_MAX
        DoEvents
        secs = secs + 1
        Debug.Print Now & " - Waitting for server [" & secs & "/" & TIMEOUT_MAX; "]."
        Sleep 1000
    Wend

    If secs >= TIMEOUT_MAX Then
        Debug.Print Now & " - Client Timed Out"
        CloseConnection
    End If

    Connect = True
End Function

Sub Send(ByVal str As String)
    If socket Is Nothing Then
        Call Err.Raise(3222, "TCPClient::Send", "Socket not Initialized")
    End If

    If socket.State <> sckConnected Then
        Call Err.Raise(3222, "TCPClient::Send", "Not connected")
    End If

    socket.SendData str
    DoEvents
End Sub

Sub CloseConnection()
    If socket.State <> sckClosed Then
        socket.Close
    End If

    Set socket = Nothing
    Debug.Print Now & " - Client Closed."
End Sub


' WINSOCK DATA ARRIVES
Sub winsock_dataArrival(bytesTotal)
    Dim strData
    winsock.GetData strData, vbString
    RecieveData = strData
    WriteData Now & " - Client Recieved: " & RecieveData
    winsock.SendData "Test"
    Wscript.Sleep 1000
    WriteData Now & " - Client Sent Data"
    Call ClientClose
End Sub

' WINSOCK ERROR
Sub winsock_Error(Number, Description, SCode, Source, HelpFile, HelpContext, CancelDisplay)
    MsgBox "Cient Error: " & Number & vbCrLf & Description
    WriteData Now & " - Cient Error: " & Number & ". " & Description
    Call ClientClose
End Sub

I hope that it can be usefull for someone.