Click here to Skip to main content
15,071,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Sir

I need insert API - Json Data into mssql server.
I did halfway through. I have no idea how to proceed....

Please advise me

Maideen

What I have tried:

<pre>Public Class BouncesAndBlocks
        Public Property name As String
        Public Property city As String
    End Class

    Protected Sub ProcessaPI()
        Dim url As String = "https://api.mocki.io/v1/b043df5a"
        Dim JS As String = GetRouteUrl(url)
        Dim res As List(Of BouncesAndBlocks) = CType(JsonConvert.DeserializeObject(JS, GetType(List(Of BouncesAndBlocks))), List(Of BouncesAndBlocks))

        For Each item In res

            lbl.Text = "name:" & item.name.ToString() & "    city:" + item.city.ToString() & ""
        Next
    End Sub
Posted
Updated 5-Apr-21 18:33pm
Comments
Richard MacCutchan 3-Apr-21 3:51am
   
You need to extract the JSON data into the fields that you want to save and then use SQL commands to add it to the database.

But the above code is not clear.
Maideen Abdul Kader 6-Apr-21 0:35am
   
Thanks Richard

this is the code

Dim url As String = "http://localhost/delivery/JsonData/WebService.asmx/GetAllCustomer"
ServicePointManager.Expect100Continue = True
ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
Dim json As String = (New WebClient).DownloadString(url.TrimEnd())
Dim res As List(Of BouncesAndBlocks) = JsonConvert.DeserializeObject(Of List(Of BouncesAndBlocks))(json)
For Each bouncesAndBlock In res
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "usp_API_Customer_List"
cmd.Parameters.Add(New SqlParameter("@Action", "INSERT"))

cmd.Parameters.AddWithValue("@code", bouncesAndBlock.code)
cmd.Parameters.AddWithValue("@Name", bouncesAndBlock.name)
cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)

Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)

cmd.Parameters.Clear()
cmd.Dispose()
Next
Maideen Abdul Kader 5-Apr-21 1:50am
   
Thank you Richard. I will try

1 solution

Hi
This is the code inserting json data into mssql database

Dim url As String = "http://localhost/delivery/JsonData/WebService.asmx/GetAllCustomer"
ServicePointManager.Expect100Continue = True
ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
Dim json As String = (New WebClient).DownloadString(url.TrimEnd())
Dim res As List(Of BouncesAndBlocks) = JsonConvert.DeserializeObject(Of List(Of BouncesAndBlocks))(json)
For Each bouncesAndBlock In res
    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "usp_API_Customer_List"
    cmd.Parameters.Add(New SqlParameter("@Action", "INSERT"))

    cmd.Parameters.AddWithValue("@code", bouncesAndBlock.code)
    cmd.Parameters.AddWithValue("@Name", bouncesAndBlock.name)
    cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
    cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)

    Dim da As New SqlDataAdapter(cmd)
    Dim dt As New DataTable
    da.Fill(dt)

    cmd.Parameters.Clear()
    cmd.Dispose()
Next
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900