Last post May 18, 2017 07:16 PM by mgebhard
May 16, 2017 11:15 PM|Embryologist|LINK
I m using stored procedure/Vb.net
I have two related tables shown below.
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,
I can already serialize one table using the following method
<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)
Dim jsondata As String = JsonConvert.SerializeObject(ds)
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.
May 18, 2017 02:32 AM|Eric Du|LINK
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). \\
May 18, 2017 02:36 AM|Embryologist|LINK
thank for your response,
could you please guide me with an example
I just dont know how to put it in action
May 18, 2017 07:16 PM|mgebhard|LINK
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.
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.