locked
Nested JSON Serialization RRS feed

  • Question

  • User-1305530094 posted

    Dear all

    I m using stored procedure/Vb.net

    I have two related tables shown below.

    Relational Tables - SQL Server

    SELECT        IDTblCtry.IDCtry, IDTblCtry.CtryName, IDTblCtryDtl.StateName, IDTblCtryDtl.StateID, IDTblCtryDtl.IDCtry AS Expr1
    FROM            IDTblCtry INNER JOIN
                             IDTblCtryDtl ON IDTblCtry.IDCtry = IDTblCtryDtl.IDCtry

    I need to create one JSON array for them in this format, 

    {
        "IDCtry":1,"CtryName":"Germany": ["StateID":1,"StateName":"Duesseldorf"],
        "DCtry":2,"CtryName":"spain": ["StateID":1,"StateName":"Barcelona"],
    }

    I can already serialize one table using the following method

    <WebMethod()> _
    <ScriptMethod(ResponseFormat:=ResponseFormat.Json, UseHttpGet:=False, XmlSerializeString:=False)> _
        Public Function populateCtls(ByVal CtlName As String)
            Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand("populateCtls", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@operator", SqlDbType.NVarChar).Value = CtlName
                    cmd.Connection = con
                    Dim ds As New DataSet()
                    Using sda As New SqlDataAdapter(cmd)
                        sda.Fill(ds)
                    End Using
                    Dim jsondata As String = JsonConvert.SerializeObject(ds)
                    Return jsondata
                End Using
            End Using
        End Function

    but i cant figure out how to relate these records before serialization.

    Shall I run two sql readers for two different select statements or is there a way to join them before I serialize the returned result,

    currently I use stored procedures (PopulateCtls) which has my select statement.

     

    Tuesday, May 16, 2017 11:15 PM

Answers

All replies

  • User-1838255255 posted

    Hi Embryologist,

    After reading the description of your requirement, I know you want to show data in that format.

    But as far as I know, "Key":"Value" is the format of the JSON.

    About this format("IDCtry":1,"CtryName":"Germany": ["StateID":1,"StateName":"Duesseldorf"],), I think it is invalid.

    About your needs(valid):

    you could select the StateID and StateName form IDTblCtryDtl, then set the value of the "key" to datatable(another select of the IDTblCtry). \\

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 18, 2017 2:32 AM
  • User-1305530094 posted

    thank for your response,

    could you please guide me with an example

    I just dont know how to put it in action

    Thursday, May 18, 2017 2:36 AM
  • User475983607 posted

    You're using ADO.NET and data sets.  Your SQL fattens the results.  If you want a data set with related tables then you'll need to return two results sets using the DataAdapter.

    https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

    If you want to continue using a reader then build yourself an object model (classes) that represents the shape of the data.  Fill the object then deserialize the object into JSON.

    https://docs.microsoft.com/en-us/dotnet/articles/visual-basic/programming-guide/concepts/object-oriented-programming

    https://msdn.microsoft.com/en-us/library/ms973814.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 18, 2017 7:16 PM