Wednesday, October 27, 2010

Advantage Data Class

As I mentioned in my first post about the Advantage Web API I talked about a class that I wrote which parsed a JSON result and created a DataTable object. This is an alternate to using specific classes to work with the results from a Web API request.

I like this method because it can generate a DataTable for any request so I don't have to create a specific class for each set of data I request. The generated DataTable is also very easy to bind to various .NET controls. Using specific classes provide a more strongly typed representation of the data and can be bound to controls with a little more effort. Defined objects can also be manipulated directly by a JSON serializer.

In order to create a DataTable we will have to parse the JSON ourselves, we cannot rely on the serializer. This is done with the following method. Note that there is a mechanism for error checking in the method.

private bool ParseJSON( string stringJSON )
{
  string sValue = string.Empty;
  dataTable = new DataTable();

  using ( JsonTextReader reader = new JsonTextReader(new StringReader(stringJSON)))
  {             
    // Move to the array of objects
    do
    {
      if (reader.TokenType == JsonToken.PropertyName 
          && reader.Value.ToString().Contains("error"))
      {
        parseError(reader);
        return false;
      }
      reader.Read();
    } while (reader.TokenType != JsonToken.StartArray);


    // Get the dataTable
    PopulateDataTable(reader);
                
    // Get the rest of the Information
    while (reader.Read())
    {
      if (reader.TokenType == JsonToken.PropertyName)
      {
        if (reader.Value.ToString().Contains("__next"))
        {
          reader.Read();
          nextURI = reader.Value.ToString();
        }

        if (reader.Value.ToString().Contains("rows_affected"))
        {
          reader.Read();
          rowsAffected = Convert.ToInt32(reader.Value);
        }

        if (reader.Value.ToString().Contains("last_autoinc"))
        {
          reader.Read();
          lastAutoinc = Convert.ToInt32(reader.Value);
        }
      }
    }
  }

  return true;
}

To create the DataTable object we examine the first object in the results array. The JSON.NET Library has a mechanism for determining the data type which we will use to create the columns in the data table. We also need to store the information we retrieve since we can only move forward through the JSON string. We will do this with a simple helper class that will store the Column Name, Data Type and Value.

private DataTable CreateDataTable(JsonReader reader)
{
  DataTable dt = new DataTable();
  string sItem = string.Empty;
  List lItems = new List();

  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  // Put the values into the datarow
  while (reader.TokenType != JsonToken.EndObject)
  {
    try
    {
      if (reader.TokenType == JsonToken.PropertyName)
        sItem = reader.Value.ToString();
      else
      {
        switch (reader.TokenType)
        {
          case JsonToken.Boolean:
            dt.Columns.Add(sItem, Type.GetType("System.Boolean"));
            break;
          case JsonToken.Date:
            dt.Columns.Add(sItem, Type.GetType("System.DateTime"));
            break;
          case JsonToken.Float:
            dt.Columns.Add(sItem, Type.GetType("System.Double"));
            break;
          case JsonToken.Integer:
            dt.Columns.Add(sItem, Type.GetType("System.Int32"));
            break;
          case JsonToken.String:
          default:
            dt.Columns.Add(sItem, Type.GetType("System.String"));
            break;
        }
        
        // Store the column value
        lItems.Add(new DataRowItem(sItem, reader.TokenType, reader.Value.ToString()));
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
    }

    reader.Read();
  } 

  // add the row to the datatable
  AddDataRow(lItems, dt);

  return dt;
}

The AddDataRow function adds the values that we stored in the DataRowItem object into a new row in the newly created DataTable.

private static bool AddDataRow(List lDataItems, DataTable dt)
{
  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  foreach (DataRowItem di in lDataItems)
  {
    switch (di.ColumnType)
    {
      case JsonToken.Boolean:
        dr[di.ColumnName] = Convert.ToBoolean(di.ColumnValue);
        break;
      case JsonToken.Date:
        dr[di.ColumnName] = Convert.ToDateTime(di.ColumnValue);
        break;
      case JsonToken.Float:
        dr[di.ColumnName] = Convert.ToDouble(di.ColumnValue);
        break;
      case JsonToken.Integer:
        dr[di.ColumnName] = Convert.ToInt32(di.ColumnValue);
        break;
      case JsonToken.Null:
        dr[di.ColumnName] = null;
        break;
      case JsonToken.String:
        dr[di.ColumnName] = di.ColumnValue;
        break;
    }
  }

  // add the row to the datatable
  dt.Rows.Add(dr);

  return true;
}

After the first record has been read and the table has been created the rest of the records can be added without having to create a DataItem object. Therefore, there is an overloaded version of AddRow

private static bool AddDataRow(JsonReader reader, DataTable dt)
{
  string sItem = string.Empty;

  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  // Put the values into the datarow
  while (reader.TokenType != JsonToken.EndObject)
  {
      try
      {
        if (reader.TokenType == JsonToken.PropertyName)
          sItem = reader.Value.ToString();
        else
        {
          switch (reader.TokenType)
          {
            case JsonToken.Boolean:
              dr[sItem] = Convert.ToBoolean(reader.Value);
              break;
            case JsonToken.Date:
              dr[sItem] = Convert.ToDateTime(reader.Value);
              break;
            case JsonToken.Float:
              dr[sItem] = Convert.ToDouble(reader.Value);
              break;
            case JsonToken.Integer:
              dr[sItem] = Convert.ToInt32(reader.Value);
              break;
            case JsonToken.Null:
              dr[sItem] = null;
              break;
            case JsonToken.String:
              dr[sItem] = reader.Value.ToString();
              break;
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
      }

      reader.Read();
  }
  // add the row to the datatable
  dt.Rows.Add(dr);

  return true;
}

As I said in the beginning this is a bit more difficult than using pre-defined objects/classes with a JSON serializer/deserializer. However, DataTables are very easy to bind to many of the controls available in the .NET Compact Framework. The downside is that the data types may not always be correct depending on the serializer you are using.

No comments: