C# DLL COM that calls web service not working in Excel VB

Question

I currently have a project guideline to try to make C# DLL as a COM to be usable in Excel as well as other platforms. Currently it calls web services and does calculations such as getting a gravity prediction from a web service. Everything is working fine except for when I need to use class methods that are utilizing web services. I get an error in Excel stating it could not establish a SSL/TSL connection to the service and I cannot seem to find a way around this. Any help forward would be greatly appreciated as I am a life long developer, but working it into Excel is brand new to me. Below is how I am handling it in the code.

GravityResultObject.cs

public class GravityObjResult
{
    public string lat { get; set; }
    public string latDms { get; set; }
    public string lon { get; set; }
    public string lonDms { get; set; }
    public string eht { get; set; }

    public string predictedGravity { get; set; }
}

static void Main(string[] args)
{
}

public static async Task<string> TestGravApi(float lat, float lon, float eht)
{
    GravityObjResult gResult = new GravityObjResult();

    using (var httpClient = new HttpClient())
    {
        string url = String.Format("my-api-here-gp?lat={0}&lon={1}&eht={2}", lat, lon, eht);
        var json = await httpClient.GetStringAsync(url);

        if (json != null)
        {
                gResult = JsonConvert.DeserializeObject<GravityObjResult>(json);
                // Now parse with JSON.Net
                return gResult.predictedGravity;
            }
        }
        return String.Empty;
    }
}

GPredict.cs

public class GPredict
{
    public float Lon { get; set; }
    public float Lat { get; set; }
    public float Elip { get; set; }

    public float Grav { get; set; }

    public GPredict(float lon, float lat, float elip)
    {
        Lon = lon;
        Lat = lat;
        Elip = elip;
    }

    public GPredict()
    {
    }

    public float SetLON(float a)
    {
        Lon = a;
        return Lon;
    }

    public float SetLAT(float a)
    {
        Lat = a;
        return Lat;
    }

    public float SetELIP(float a)
    {
        Elip = a;
        return Elip;
    }

    public string GetG()
    {
        string gReturn = String.Empty;
        try
        {
            var task = WebRequestCall.TestGravApi(Lat, Lon, Elip);
            gReturn = float.Parse(task.Result).ToString();
        }
        catch (Exception e)
        {
            gReturn = e.ToString();
        }

        return gReturn;
    }
}

DynamicCalcWrapper.cs

public class DynamicCalcWrapper : DynamicObject
{
    Calculator calc;

    GPredict gPredict;

    public DynamicCalcWrapper()
    {
        calc = new Calculator();
        gPredict = new GPredict();
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        result = null;

        switch (binder.Name)
        {
            case "add":
                result = (Func<double, double, double>)((double a, double b) => calc.add(a, b));
                return true; 
            case "sub":
                result = (Func<double, double, double>)((double a, double b) => calc.sub(a, b));
                return true; 
            case "SetLON":
                result = (Func<float, float>)((float a) => gPredict.SetLON(a));
                return true;
            case "SetLAT":
                result = (Func<float, float>)((float a) => gPredict.SetLAT(a));
                return true;
            case "SetELIP":
                result = (Func<float, float>)((float a) => gPredict.SetELIP(a));
                return true;
            case "GetG":
                result = (Func<float, string>)((float a) => gPredict.GetG().ToString());
                return true;
        }
        return false; 
    }
}

Testing the result in Excel VB:

Sub T()
    Dim getG As New GPredict
    getG.Lon = 145#
    getG.Lat = 41.11
    getG.Elip = 33

    Dim result As String
    result = getG.getG()
    MsgBox result
End Sub

Note that I have the regular getter / setter for the properties, but also have a secondary way to set it since I have to allow Fortran based code to work with this and there are some restrictions on how long param settings are and such.

You are using `Task.Result` in `GetG`. You [should not](https://blog.stephencleary.com/2012/07/dont-block-on-async-code.html). That will bite your later though, when you fix the SSL issue which is mostly likely [this](https://stackoverflow.com/a/33091871/11683). — GSerg, Aug 20 '19 at 19:17
Awesome thanks. Should i just use a regular method call and not the await task style? — Casey ScriptFu Pharr, Aug 20 '19 at 19:40

score 0 · Answer 1 · answered Aug 21 '19 at 03:22

I would like to note that prior to posting my code above in the sample, I also had tried it first pointing directly to the url in the context "https://" it was till throwing the error. I was able to find out I had to add the following settings to get it to run by adding the code lines for ServicePointManager below:

public static async Task<string> TestGravApi(float lat, float lon, float eht)
    {
        GravityObjResult gResult = new GravityObjResult();

   ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls
   | SecurityProtocolType.Tls11
   | SecurityProtocolType.Tls12
   | SecurityProtocolType.Ssl3;

        using (var httpClient = new HttpClient())
        {
            string url = String.Format("https://{my-url-here}/api/gravd/gp?lat={0}&lon={1}&eht={2}", lat, lon, eht);
            var json = await httpClient.GetStringAsync(url);

            //sends from object to string json
            //string output = JsonConvert.SerializeObject(product);
            if (json != null)
            {
                gResult = JsonConvert.DeserializeObject<GravityObjResult>(json);
                // Now parse with JSON.Net
                return gResult.predictedGravity;
            }
        }

        return String.Empty;
    }

C# DLL COM that calls web service not working in Excel VB

1 Answers1