SQL Server to Webservice VB.net

Question

Hi
i was wondering if possible
i want to extract the JSON data from SQL table and POST the JSON output to a Webservice

all this done using VB.net code

is this possible

Answer 1

Ok, it is assumed that you have a table or some table data.
(quite much a "given").

We want to take that data and convert the table data to JSON.

So, say we have this;

SELECT TOP 3 FirstName, LastName, HotelName FROM tblHotels ORDER BY ID  

In sql server, this outputs this:

Now, in VB, lets convert our table to JSON.

We have this code:

       Dim strSQL As String  
        strSQL = "SELECT TOP 3 FirstName, LastName, HotelName FROM tblHotels ORDER BY ID"  
  
        Dim MyTable As DataTable = MyRst(strSQL)  
  
        Dim sBuf As String = JsonConvert.SerializeObject(MyTable).ToString  
  
        Debug.Print(sBuf)  

OutPut:

[{"FirstName":"Albert","LastName":"Kallal","HotelName":"My Cool"},{"FirstName":null,"LastName":null,"HotelName":"Radisson Inn Hotel"},{"FirstName":null,"LastName":null,"HotelName":"Ramada Inn #2"}]  

So you can use the NewtonSoft (use nuget to install it).

Thus any simple table data, or data in a list, or in fact even a single "row" of data you pull from SQL server into a table, row or even list object? NewtonSoft will "serialize' it into JSON for you.

In fact, you can even pass NewtonSoft a class object - and it will do its magic.

The next part + challenge is once you have the JSON is to make that web service call.

You have to provide more details as to what the format and setup and requirementss and paramters of that web service in question is.

So, use NewtonSoft for conversion - it a walk in the park, and as noted, be a class, a table, a list? Newsoft convert handles all of these formats. Including for some VERY STRANGE reason the built-in .net serlizers NOT supporting a dataRow type (don't know why???).

Anyway - as you can see - getting "some thing" in JSON is easy - but setting up the web service call will in fact be your "real" challenge.

If you give some details about the web service call, then I can/will follow up.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Are you writing the Web service and Web client programs yourself?