How can I NOT get my WCF REST service to return JSON from a dataset

The normal process to serialize a DataSet, is to convert it to an XML string.  My guess is that you have a JSON response with an XML string, but I don't work with DataSet objects in a web service anymore, so that is only conjecture...

Thanks for picking up the thread.

Maybe instead of code you might help me with the theory then.  The service I am making will need to query a database - being an SQL Server 2000 database at the moment.  So what object should I be looking at getting from my query - dataset, datatable or some other.????

What do you suggest I do with it?  It seems that the native way for .NET is xml so should I be first converting from "Data" to xml and then from XML to Json?

I am a big fan of LINQ, which would suggest either Entity Framework or LINQ-to-SQL.  Either technology (LINQ-to-SQL is on the way out, but not dead yet), works with sets of business objects.  The WCF service could provide these objects, through the interface, to the client.

I like opening a connection, executing a SQL and getting a dataset :-)

Assuming I learn LINQ (which will not work with SQL 2000) I still do not see how it will serialize my results properly into JSON.

OK, you are right that SQL Server 2000 doesn't work and play well with Entity Framework and LINQ-to-SQL (queries aren't generated correctly).  

"being an SQL Server 2000 database at the moment" suggests that it might be possible to upgrade this database.

Entity Framework and LINQ-to-SQL are just a drop in the bucket when it comes to ORM solutions (NHibernate, Spring.NET, CoolStorage.NET).  They were just suggestions to focus our thinking toward business objects, and not DataSet.  Business objects are very easily serialized into JSON.

At the moment I am only on a test server for this stuff.  So I have installed SQL Server 2005 express as well.  I can make/add a new entity etc to Visual Studio 2010, but I have no idea how to use it.

Sometimes the problem is not how to do something, the problem is what I should search for to find out how to do something.

The things I have the biggest worry about or problem with is the serialization especially if there are array lists or objects within the JSON that need to be serialized from the data because of 1 > N relationships in the tables.

Let's take a small walk down this path, to see if it meets your expectations.  I have SQL Server 2008 installed, with the AdventureWorks sample database, which has a lot of entities in multiple schemas, so we can target just a subset.

1) Create a new 4.0 Windows application (i.e. EntityFrameworkProto)

2) Add a new ADO.NET Entity Data Model item to the project

3) Connect to the database, by either creating a new connection, or reusing an existing connection in the Server Explorer.

4) Choose the database objects that you would like to work with in the model--you don't need to select them all.

5) Create a data context class to work with the entity model.

6) Define a method for the data layer class

Public Class ProductDataLayer

    Private m_dataContext As AdventureWorksEntities

    Public Sub New()
        m_dataContext = New AdventureWorksEntities()
    End Sub

    Public Function GetProduct(productId As Integer) As Product
        Return m_dataContext.Products.Where(Function(x) x.ProductID = productId).FirstOrDefault()
    End Function

End Class

Select allOpen in new window

7) Define a serialization class

Imports System.IO
Imports System.Runtime.Serialization.Json
Imports System.Text

Public Class ObjectSerializer

    Private Shared m_serializers As Dictionary(Of Type, DataContractJsonSerializer)

    Shared Sub New()
        m_serializers = New Dictionary(Of Type, DataContractJsonSerializer)
    End Sub

    ''' <summary>
    ''' Serializes the specified object into a JSON string
    ''' </summary>
    ''' <typeparam name="T">Business object type</typeparam>
    ''' <param name="object">The object.</param>
    ''' <returns>JSON string</returns>
    Public Shared Function Serialize(Of T As Class)([object] As T) As String
        Dim serializer As DataContractJsonSerializer = GetInstance(GetType(T))

        Using stream As New MemoryStream()
            serializer.WriteObject(stream, [object])
            Return Encoding.UTF8.GetString(stream.ToArray())
        End Using
    End Function

    ''' <summary>
    ''' Deserializes the JSON string into an object
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="jsonText">The JSON text.</param>
    ''' <returns>Object</returns>
    Public Shared Function Deserialize(Of T As Class)(jsonText As String) As T
        Dim serializer As DataContractJsonSerializer = GetInstance(GetType(T))

        Dim buffer As Byte() = Encoding.UTF8.GetBytes(jsonText)
        Using stream As New MemoryStream()
            Return TryCast(serializer.ReadObject(stream), T)
        End Using

    End Function

    ' Create an instance of a DataContractJsonSerializer for each type.
    Private Shared Function GetInstance(type As Type) As DataContractJsonSerializer
        If m_serializers.ContainsKey(type) Then
            Return m_serializers(type)
        Else
            Dim serializer As New DataContractJsonSerializer(type)

            m_serializers.Add(type, serializer)
            Return serializer
        End If
    End Function

End Class

Select allOpen in new window

8) Create a small test

        Dim dataLayer As New ProductDataLayer()

        Dim product As Product = dataLayer.GetProduct(1)

        If product IsNot Nothing Then
            Dim jsonText As String = ObjectSerializer.Serialize(product)

            Console.WriteLine(jsonText)
        End If

Select allOpen in new window

9) (To be determined)

I ran into a big problem with that previous post with the Entity Framework:

The type 'EntityFrameworkProto.Product' cannot be serialized to JSON because its IsReference setting is 'True'. The JSON format does not support references because there is no standardized format for representing references. To enable serialization, disable the IsReference setting on the type or an appropriate parent class of the type.

This would also be a problem with WCF JSON serialization, since it uses the same serializer.

Screen shot PDF


Entity-Framework.pdf

wow T.L.O that is way more than I was expecting.  Thank you very much.  I am not going to be able to do this within the next 24 hours so I will have to give you feedback then.

Thank you again.

I now have had time to look at your suggestion.

All is not lost - I can see now how this method of obtaining data is done.  Although at the moment it is largely theoretical as I need to work with SQL Server 2000.  So thank you for teaching/influencing me to learn something new.

I must admit I do not even understand the comments when googleing this problem.

First thing I read (MSDN Thread)

From what I have read I would like to make some summary points, could you comment and confirm if I am understanding everything here properly;

1. WCF intended to make the serialization steps automatic by allow a simple eg "ResponseFormat:=WebMessageFormat.Json" to be used.
2. The datacontractserializer is used to perform 1
3. In doing 1 and 2 MS made it very hard to get WCF NOT to do its own serialization.
4. End result - most people either settle for XML which .Net can do better or they try to avoid the WCF automatic serialization.

5.  If I want to get JSON returned in REST I need to do the JSON serialization myself

(this is where I am getting weak on my understanding)
6.  I need to serialize objects with an unknown datacontract (I hope this is right) eg here is an object > work out the data structure > serialize
7. If I take the time to create/customize a datacontract based on my tables/SQL queries then serialization will be easier because it is up to me to get the contract right.

I think that is all I can say at the moment.

by the way I played around with your example.

In the designer for I THINK the entity module (you get to it if you step through the code)

i did this

Global.System.Runtime.Serialization.DataContractAttribute(IsReference:=False)

Select allOpen in new window

But it then has a error saying that

Global.System.Data.Objects.DataClasses.EntityObject

Select allOpen in new window

is set to TRUE and must be set to false.  I could not work out that.

Thanks for your reply.  LINQ to SQL is yet another unfamiliar thing for me so i hesitate to go down that path if it is not needed.  I am sensing your strengths are with the data getting and not so much with the serialization part.

Surely if it appears that we have to use DataContractJsonSerializer then should I not be just doing research on how to use it with a dataset or datatable?

I must admit that DataContractJsonSerializer is also something I do not know, but from what I have read so far I need to set up a contract (the structure of my table) so that the DataContractJsonSerializer understands how to serialize.  Is this right?

I have done a lot of work with serialization, from the day of XmlSerializer, BinaryFormatter, MTOM, DIME, to XDocument, LINQ-to-XML, DataContractSerializer, DataContractJsonSerializer.  I haven't had much interest in the Entity Framework, but I do like LINQ-to-SQL for quick and dirty work.  Entity Framework and LINQ-to-SQL both generate classes, so that I don't have to.  
WCF uses serialization inheritently, so you don't need to know the serialization process, in order to use it.

In order to use LINQ-to-SQL, add a DataContext class to a project, and drag and drop tables and stored procedures to the designer.  When you save the .dbml file, you can create an instance of the DataContext and make calls to get the data exactly as you would with the Entity Framework.  

I believe that serializing business objects makes a lot more sense then serializing a DataTable or DataSet, since they are strong typed, and provide the properties through Intellisense.

Would this be an example of what you're talking about except serializing XML Msdn stuff?

Sure, that would be what you need, since you need an operation contract, and a data contract for WCF.  That would satisfy the requirement for a data contract (decorating your class with the DataMember attribute).

great we are on the same page.  I have created a LINQ to SQL file and dragged a table into this Object Relational Designer - I cannot for the life of me see how to view the (what I hope are) the automatically generated Entity classes.

The auto-generated classes should be in the .designer file.  If you are working with VB.NET, you need to check the "Show All Files" option in the Solution Explorer to see the .designer file.

C# example:

#pragma warning disable 1591
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.1
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace Tool.SqlServer.Odc.Data
{
	using System.Data.Linq;
	using System.Data.Linq.Mapping;
	using System.Data;
	using System.Collections.Generic;
	using System.Reflection;
	using System.Linq;
	using System.Linq.Expressions;
	using System.ComponentModel;
	using System;
	
	
	[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="ODC")]
	public partial class OdcDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

         ...		

Select allOpen in new window

Well that works I think, And I think I have used up enough "semi questions" under this thread.

There is just one thing left if I may.  And then I think I will close this thread.

The following (I just added one table to this LINQ to SQL thingy) works for returning a single row from the table but how do I return multiple rows.

For example if I just wanted to get the whole table serialized or (Where c.wID = 1 or c.wID = 2)

Dim db As New DataClasses1DataContext

        Dim cust = (From c In db.tbl_Test_Servers _
                   Where c.wID = 1).Single

        Dim stream1 As New MemoryStream()
        Dim ser As New DataContractJsonSerializer(GetType(Lutadmin_tbl_Bearbeiter_Server))
        ser.WriteObject(stream1, cust)
        Debug.Print(Encoding.UTF8.GetString(stream1.ToArray()))

Select allOpen in new window

If you want multiple rows, then you would just need to remove the Where conditions, use the ToList() method to convert to List(Of), and change the GetType to the List(Of) type.

Thank you very much for sticking with me.  Sometimes I think EE should allow for more points for the hard questions or hard people like me who do not just have a simple thing to solve.

I am off to learn SQL to LINQ especially how to convert complex SQL queries into using my new Entity Classes.
I am also off to learn more about Serialization as I a assume I have to understand that for the various queries.  For example single rows or multiple rows etc.

Thank you again TheLearnerOne (Thank you especially for picking up a difficult question)

The solution accepted is part of the whole solution, but basically LINQ to SQL is what I found to work for me to do proper JSON Serialization.