Converting a json object into a datatable

How do you convert a json object

{ "mode": "collage", "objects": [ { "height": 207, "id": 46, "left": 370.333, "top": 76.4, "type": "image", "width": 276, "zIndex": 2 }, { "height": 40, "id": 109, "left": 364.383, "top": 283.4, "type": "word", "width": 95, "zIndex": 8 }, { "height": 40, "id": 91, "left": 527.783, "top": 187.4, "type": "word", "width": 170, "zIndex": 4 }, { "height": 75, "id": 107, "left": 199, "top": 167, "type": "word", "width": 264, "zIndex": 5 }, { "height": 55, "id": 30, "left": 321.667, "top": 250, "type": "image", "width": 73, "zIndex": 9 } ] }

into a datatable
JDEE8297Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guramritCommented:
There're few ways to do this
1. Try JSON.NET (availaible from codeplex.com)
2. Parse it manually
3. Convert to dictionary then datatable. refer to following code, it converts json to dictionary
 
var serializer = new JavaScriptSerializer() ;
var obj = serializer.Deserialize<Dictionary<string, object>>("{ 'mode': 'collage', 'objects': [ { 'height': 207, 'id': 46, 'left': 370.333, 'top': 76.4, 'type': 'image', 'width': 276, 'zIndex': 2 }, { 'height': 40, 'id': 109, 'left': 364.383, 'top': 283.4, 'type': 'word', 'width': 95, 'zIndex': 8 }, { 'height': 40, 'id': 91, 'left': 527.783, 'top': 187.4, 'type': 'word', 'width': 170, 'zIndex': 4 }, { 'height': 75, 'id': 107, 'left': 199, 'top': 167, 'type': 'word', 'width': 264, 'zIndex': 5 }, { 'height': 55, 'id': 30, 'left': 321.667, 'top': 250, 'type': 'image', 'width': 73, 'zIndex': 9 } ] }");

Open in new window

0
JDEE8297Author Commented:
I tried using json.net and here was my attempt at it.

data_model.DicObjects there is nothing present in this object

and not sure why.
Imports _sysData = System.Data
Imports Newtonsoft.Json

''' <summary>
''' 
''' </summary>
''' <copyright>
''' My © 2011
''' </copyright>
''' <history>
''' My (My) - 10/11/2011
''' My-PC
''' </history>
Public Class Json_Data
    ''' <summary>
    ''' Gets or sets the type.
    ''' </summary>
    ''' <value>
    ''' The type.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property Type As String
        Get
            Return strType
        End Get
        Set(value As String)
            strType = value
        End Set
    End Property
    Private strType As String
    ''' <summary>
    ''' Gets or sets the index of the z.
    ''' </summary>
    ''' <value>
    ''' The index of the z.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property zIndex As Long
        Get
            Return lngZindex
        End Get
        Set(value As Long)
            lngZindex = value
        End Set
    End Property
    Private lngZindex As Long
    ''' <summary>
    ''' Gets or sets the width.
    ''' </summary>
    ''' <value>
    ''' The width.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property Width As Long
        Get
            Return lngWidth
        End Get
        Set(value As Long)
            lngWidth = value
        End Set
    End Property
    Private lngWidth As Long
    ''' <summary>
    ''' Gets or sets the top.
    ''' </summary>
    ''' <value>
    ''' The top.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property Top As Long
        Get
            Return lngTop
        End Get
        Set(value As Long)
            lngTop = value
        End Set
    End Property
    Private lngTop As Long
    ''' <summary>
    ''' Gets or sets the height.
    ''' </summary>
    ''' <value>
    ''' The height.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property height() As Long
        Get
            Return lngHeight
        End Get
        Set(value As Long)
            lngHeight = value
        End Set
    End Property
    Private lngHeight As Long
    ''' <summary>
    ''' Gets or sets the id.
    ''' </summary>
    ''' <value>
    ''' The id.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property id() As Long
        Get
            Return lngId
        End Get
        Set(value As Long)
            lngId = value
        End Set
    End Property
    Private lngId As Long
    ''' <summary>
    ''' Gets or sets the left.
    ''' </summary>
    ''' <value>
    ''' The left.
    ''' </value>
    ''' <copyright>
    ''' My © 2011
    ''' </copyright>
    ''' <history>
    ''' My (My) - 10/11/2011
    ''' My-PC
    ''' </history>
    Public Property left As Long
        Get
            Return lngLeft
        End Get
        Set(value As Long)
            lngLeft = value
        End Set
    End Property
    Private lngLeft As Long
End Class


''' <summary>
''' 
''' </summary>
''' <copyright>
''' My © 2011
''' </copyright>
''' <history>
''' My (My) - 10/11/2011
''' My-PC
''' </history>
Public Class Json_Model
    Public Property DicObjects() As Dictionary(Of Integer, Json_Data)
        Get
            Return dicObject
        End Get
        Set(value As Dictionary(Of Integer, Json_Data))
            dicObject = value
        End Set
    End Property
    Private dicObject As Dictionary(Of Integer, Json_Data)
End Class
''' <summary>
''' 
''' </summary>
''' <copyright>
''' My © 2011
''' </copyright>
''' <history>
''' My (My) - 10/11/2011
''' My-PC
''' </history>
Partial Class participant_view
    Inherits cPageBase


#Region "Module Level Variables"
    'User defined object
    Private _DataAccess As New cDataAccess
    Private _ErrorHandler As New cErrorHandler
    Private _ExpressionExercise As New cExpressionExercise

    'Strings
    Private _strErrMsg As String = String.Empty
    Private _strExerciseName As String = String.Empty
    'Boolean
    Private _blnErrorOccurred As Boolean = False
    'Long
    Private _lngExerciseId As Long = 0
    Private _lngFacId As Long = 0
    'Constants
    Const _cstrMODULE_NAME As String = "content_dialog"
#End Region


#Region "Page Events"
    ''' <summary>
    ''' Handles the Load event of the Page control.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    ''' <copyright>
    ''' My, Inc. © 2008
    ''' </copyright>
    ''' <history>
    ''' My (My) - 6/4/2008
    ''' </history>
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Local variables

        'Give it a whirl
        Try
            'Pull back values
            Long.TryParse(GetQueryStringFormValue("uid"), _lngFacId)
            Long.TryParse(GetQueryStringFormValue("itmid"), _lngExerciseId)
            _strExerciseName = GetQueryStringFormValue("fn")
            'Check to see if you continue or not
            If _lngExerciseId > 0 And _lngFacId > 0 Then
                'Connect to the database
                If SetupDataBaseConnection(_DataAccess) Then
                    With _ExpressionExercise
                        'Set the object properties
                        .SetDatabaseConnection = _DataAccess
                        .SetErrorHandler = _ErrorHandler
                        'Pull back the details for the selected view
                        If Not .GetExerciseView(_lngFacId, _lngExerciseId, _strExerciseName) Then
                            Response.Write(.GetErrorMsg)
                        Else
                            Dim strJson As String
                            strJson = .ExerciseView.Rows(0)("item_data").ToString.Trim()
                            Response.Write(strJson & "<br>")
                            Dim data_model As Json_Model = JsonConvert.DeserializeObject(Of Json_Model)(strJson)

                            For lngIdx As Long = 0 To data_model.DicObjects.Count - 1

                                Response.Write(data_model.DicObjects.Item(lngIdx).id)
                            Next
                        End If
                    End With
                End If
            End If
        Catch ex As Exception
            'Store the error message in ELMAH database
            _ErrorHandler.LogError(Context, ex)
            Response.Write(ex.Message)

        End Try

    End Sub

    ''' <summary>
    ''' Handles the PreRender event of the Page control.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    ''' <copyright>
    ''' My, Inc. © 2008
    ''' </copyright>
    ''' <history>
    ''' My (My) - 6/11/2008
    ''' </history>
    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        'Local variables
        'Give it a whirl
        Try

        Catch ex As Exception
            'Store the error message in ELMAH database
            _ErrorHandler.LogError(Context, ex)
        Finally
            If Not IsNothing(_DataAccess) Then
                'Close the database connection
                With _DataAccess
                    If .IsDbOpen Then
                        CloseDatabaseConnection(_DataAccess)
                    End If
                End With
            End If
        End Try
    End Sub

#End Region
#Region "ASP.NET Control Events"

#End Region
#Region "Third Party Control Events"

#End Region
#Region "Public Properties - Read/Write"
    ''' <summary>
    ''' Gets or sets the error MSG.
    ''' </summary>
    ''' <value>The error MSG.</value>
    ''' <copyright>
    ''' My, Inc. © 2008
    ''' </copyright>
    ''' <history>
    ''' My (My) - 3/31/2008
    ''' Created the property ErrorMsg
    ''' </history>
    Public Property ErrorMsg() As String
        Get
            Return _strErrMsg
        End Get
        Set(ByVal value As String)
            _strErrMsg = value
        End Set
    End Property
    ''' <summary>
    ''' Gets or sets a value indicating whether [error occurred].
    ''' </summary>
    ''' <value><c>true</c> if [error occurred]; otherwise, <c>false</c>.</value>
    ''' <copyright>
    ''' My, Inc. © 2008
    ''' </copyright>
    ''' <history>
    ''' My (My) - 3/31/2008
    ''' Created the property ErrorOccurred
    ''' </history>
    Public Property ErrorOccurred() As Boolean
        Get
            Return _blnErrorOccurred
        End Get
        Set(ByVal value As Boolean)
            _blnErrorOccurred = value
        End Set
    End Property
#End Region
#Region "Public Properties - Read"
#End Region
#Region "Public Properties - Write"



#End Region
#Region "Private Procedures and Methods"

#End Region
#Region "Public Procedures and Methods"
#End Region
#Region "Class Events"


    ''' <summary>
    ''' Allows an <see cref="T:System.Object"></see> to attempt to free resources and perform other cleanup operations before the <see cref="T:System.Object"></see> is reclaimed by garbage collection.
    ''' </summary>
    ''' <copyright>
    ''' My, Inc. © 2008
    ''' </copyright>
    ''' <history>
    ''' My (My) - 3/31/2008
    ''' Created the function for the first time.
    ''' </history>
    Protected Overrides Sub Finalize()
        _DataAccess = Nothing
        _ErrorHandler = Nothing
        MyBase.Finalize()
    End Sub
#End Region


End Class

Open in new window

0
CodeCruiserCommented:
See if this tool is of any help

http://jsonclassgenerator.codeplex.com/
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

JDEE8297Author Commented:
@guramrit:

how would you access the individual records in this set up using
var serializer = new JavaScriptSerializer() ;
var obj = serializer.Deserialize<Dictionary<string, object>>("{ 'mode': 'collage', 'objects': [ { 'height': 207, 'id': 46, 'left': 370.333, 'top': 76.4, 'type': 'image', 'width': 276, 'zIndex': 2 }, { 'height': 40, 'id': 109, 'left': 364.383, 'top': 283.4, 'type': 'word', 'width': 95, 'zIndex': 8 }, { 'height': 40, 'id': 91, 'left': 527.783, 'top': 187.4, 'type': 'word', 'width': 170, 'zIndex': 4 }, { 'height': 75, 'id': 107, 'left': 199, 'top': 167, 'type': 'word', 'width': 264, 'zIndex': 5 }, { 'height': 55, 'id': 30, 'left': 321.667, 'top': 250, 'type': 'image', 'width': 73, 'zIndex': 9 } ] }");

@Codecruiser
Thanks for the little tool, I may use, but I want to see how I can parse these items out in individual records
0
guramritCommented:
You've two methods to get values from json string. Use whichever is easy to you.
note: You need to add reference System.Web.Extensions

1.
 
var serializer = new JavaScriptSerializer();
Dictionary<string, object> dic1 = serializer.Deserialize<Dictionary<string, object>>("{ 'mode': 'collage', 'objects': [ { 'height': 207, 'id': 46, 'left': 370.333, 'top': 76.4, 'type': 'image', 'width': 276, 'zIndex': 2 }, { 'height': 40, 'id': 109, 'left': 364.383, 'top': 283.4, 'type': 'word', 'width': 95, 'zIndex': 8 }, { 'height': 40, 'id': 91, 'left': 527.783, 'top': 187.4, 'type': 'word', 'width': 170, 'zIndex': 4 }, { 'height': 75, 'id': 107, 'left': 199, 'top': 167, 'type': 'word', 'width': 264, 'zIndex': 5 }, { 'height': 55, 'id': 30, 'left': 321.667, 'top': 250, 'type': 'image', 'width': 73, 'zIndex': 9 } ] }");
string mode = (string)dic1["mode"];
Dictionary<string, object>[] objects = (Dictionary<string, object>[])((ArrayList)dic1["objects"]).ToArray(typeof(Dictionary<string, object>));
int height = (int)objects[0]["height"];

Open in new window

2.
 
public class JsonModel
{
    public string mode;
    public JsonObject1[] objects;
}

public class JsonObject1
{
    public int height;
    public int id;
    public double left;
    public double top;
    public string type;
    public double width;
    public int zIndex;
}

static void Main(string[] args)
{
    var serializer = new JavaScriptSerializer();
    JsonModel model = serializer.Deserialize<JsonModel>("{ 'mode': 'collage', 'objects': [ { 'height': 207, 'id': 46, 'left': 370.333, 'top': 76.4, 'type': 'image', 'width': 276, 'zIndex': 2 }, { 'height': 40, 'id': 109, 'left': 364.383, 'top': 283.4, 'type': 'word', 'width': 95, 'zIndex': 8 }, { 'height': 40, 'id': 91, 'left': 527.783, 'top': 187.4, 'type': 'word', 'width': 170, 'zIndex': 4 }, { 'height': 75, 'id': 107, 'left': 199, 'top': 167, 'type': 'word', 'width': 264, 'zIndex': 5 }, { 'height': 55, 'id': 30, 'left': 321.667, 'top': 250, 'type': 'image', 'width': 73, 'zIndex': 9 } ] }");
    //Do something with model
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDEE8297Author Commented:
Some of it is my own personal code, but I managed to get json.net to work.

 'Set up datatable for views
                            Dim dtView As New _sysData.DataTable
                            Dim arrFields As New ArrayList
                            With arrFields
                                .Add("id|I")
                                .Add("height|D")
                                .Add("left|D")
                                .Add("top|D")
                                .Add("width|D")
                                .Add("imgfile|S")
                            End With
                            _DataAccess.MakeDataTable(dtView, arrFields)

                            Dim JsonObj As New JObject
                            JsonObj = JObject.Parse(strJson)
                            For Each jo As KeyValuePair(Of String, JToken) In JsonObj
                                If jo.Key.ToLower.ToString = "objects" Then
                                    For Each itm As Object In jo.Value
                                        Dim drRow As _sysData.DataRow
                                        drRow = dtView.NewRow
                                        drRow("id") = itm.SelectToken("id").ToString
                                        drRow("height") = itm.selecttoken("height").ToString
                                        drRow("left") = itm.selecttoken("left").ToString
                                        drRow("top") = itm.selecttoken("top").ToString
                                        drRow("width") = itm.selecttoken("width").ToString
                                        drRow("imgfile") = ""
                                        dtView.Rows.Add(drRow)
                                        'Response.Write(jo.Value.Item(itm).SelectToken("id")))
                                    Next
                                End If
                            Next
0
JDEE8297Author Commented:
thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.