Database Query or Datatable or Dataset into JSON with WCF Service[Answered] RSS

• • Reply
  NeedSomeAnsw...

  None

  0 Points

  30 Posts

  Database Query or Datatable or Dataset into JSON with WCF Service

  Feb 28, 2012 03:36 PM|NeedSomeAnswers|LINK

  I'm trying to convert a database query into JSON.  I finally realized how to send back a JSON Reponse with this code:

  IService1.vb File

      <WebGet(ResponseFormat:=WebMessageFormat.Json)>
      <OperationContract()>
      Function Names As Stream

  Service1.Svc File

    Public Function Names As Stream Implements IService1.Names
          Return WriteJson(New With {Key .MyValue = "Did Something!"})
      End Function

      Public Function WriteJson(ByVal value As Object) As Stream
          Dim javaScriptSerializer = New JavaScriptSerializer()
          Dim json = Encoding.UTF8.GetBytes(javaScriptSerializer.Serialize(value))
          Dim memoryStream = New MemoryStream(json)
          WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8"
          Return memoryStream
      End Function

  --------------------

  So my question, is that i have been using this code to turn my data into a datatable/dataset:

    Dim InOut_ADAPTOR As OleDbDataAdapter
              Dim InOut_DT As DataTable
              Dim InOut_DS As New DataSet()
              Dim connection As OleDbConnection = Connections.GetDatabaseConnection
              Dim selectStatement As String = Statement
              Dim selectCommand As New OleDbCommand(selectStatement, connection)
              connection.Open()
              InOut_ADAPTOR = New OleDbDataAdapter(selectStatement, connection)
              Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(InOut_ADAPTOR)
              InOut_ADAPTOR.Fill(InOut_DS, Table)
              InOut_DT = InOut_DS.Tables(Table)
              connection.Close()

  Shoud I still do this and then convert the dataset/datatable into JSON, or should I do soemthing different?  If different, how would you suggest?

• • Reply
  Muhammad Fak...

  Participant

  1254 Points

  495 Posts

  Re: Database Query or Datatable or Dataset into JSON with WCF Service

  Feb 28, 2012 03:47 PM|Muhammad Fakhr Elden Sami|LINK

  Dear friend,

    Two steps and you will have the Json string

  1. Convert the data table to a Json

      public static string FromDataTable(DataTable dt)
      {
          string rowDelimiter = "";
  
          StringBuilder result = new StringBuilder("[");
          foreach (DataRow row in dt.Rows)
          {
              result.Append(rowDelimiter);
              result.Append(FromDataRow(row));
              rowDelimiter = ",";
          }
          result.Append("]");
  
          return result.ToString();
      }
  
      public static string FromDataRow(DataRow row)
      {
          DataColumnCollection cols = row.Table.Columns;
          string colDelimiter = "";
  
          StringBuilder result = new StringBuilder("{");       
          for (int i = 0; i < cols.Count; i++)
          { // use index rather than foreach, so we can use the index for both the row and cols collection
              result.Append(colDelimiter).Append("\"")
                    .Append(cols[i].ColumnName).Append("\":")
                    .Append(JSONValueFromDataRowObject(row[i], cols[i].DataType));
  
              colDelimiter = ",";
          }
          result.Append("}");
          return result.ToString();
      }

  // I don't want to rebuild this value for every date cell in the table
      private static long EpochTicks = new DateTime(1970, 1, 1).Ticks;
  
      private static string JSONValueFromDataRowObject(object value, Type DataType)
      {
          // null
          if (value == DBNull.Value) return "null";
  
          // numeric
          if (Array.IndexOf(numeric, DataType) > -1)
              return value.ToString(); // TODO: eventually want to use a stricter format
  
          // boolean
          if (DataType == typeof(bool))
              return ((bool)value) ? "true" : "false";
  
          // date -- see http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx
          if (DataType == typeof(DateTime))       
              return "\"\\/Date(" + new TimeSpan(((DateTime)value).ToUniversalTime().Ticks - EpochTicks).TotalMilliseconds.ToString() + ")\\/\"";
  
          // TODO: add Timespan support
          // TODO: add Byte[] support
  
          //TODO: this would be _much_ faster with a state machine
          // string/char  
          return "\"" + value.ToString().Replace(@"\", @"\\").Replace(Environment.NewLine, @"\n").Replace("\"", @"\""") + "\"";
      }

  2. Convert List to a Json string 

  var oSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
  string sJSON = oSerializer.Serialize(yourList);

  MCTS
  If you feel it helps, Mark as answered so that it can help others to find solution.
  For Any further questions, please contact me.
• • Reply
  NeedSomeAnsw...

  None

  0 Points

  30 Posts

  Re: Database Query or Datatable or Dataset into JSON with WCF Service

  Feb 29, 2012 10:14 AM|NeedSomeAnswers|LINK

  Thanks Muhammed, but I ran into a couple of problems --

  1)  I'm guessing that "yourList" that you had in your number two function is supposed to be the string result of the "FromDataTable" function, correct?

  2)  I am also getting an error on the "JSONValueFromDataRowObject" saying that it is not declared.

• • Reply
  NeedSomeAnsw...

  None

  0 Points

  30 Posts

  Re: Database Query or Datatable or Dataset into JSON with WCF Service

  Feb 29, 2012 02:08 PM|NeedSomeAnswers|LINK

  I think i got it!  It involves 3 functions. 

  My IService1.vb file looks like this:

      <WebGet(ResponseFormat:=WebMessageFormat.Json, requestformat:=WebMessageFormat.Json)>
      <OperationContract()>
      Function Names(ByVal FirstName As String) As Stream

  My Service1.svc file looks like this:

      Public Function Names(ByVal FirstName As String) As Stream Implements IService1.Names
          Dim DS As DataSet = ' ....do whatever to get your dataset
          Return WriteJson(ToDictionary(DS))
      End Function
  
      Public Function WriteJson(ByVal value As Object) As Stream
          Dim javaScriptSerializer = New JavaScriptSerializer()
          Dim json = Encoding.UTF8.GetBytes(javaScriptSerializer.Serialize(value))
          Dim memoryStream = New MemoryStream(json)
          WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8"
          Return memoryStream
      End Function
  
      Private Function ToDictionary(ByVal data As DataSet) As Dictionary(Of String, Object)
          Return data.Tables.Cast(Of DataTable)().ToDictionary(Function(t) t.TableName, Function(t) toRowDictionary(t))
      End Function

  ALSO

  Make sure to right-click "Service1.svc" and click "View Markup".  Then i have this code in that file:

  <%@ ServiceHost Language="VB" Debug="true" Service="WCF2.Service1" CodeBehind="Service1.svc.vb" Factory="System.ServiceModel.Activation.WebServiceHostFactory"%>