Solved

Returning DataSeta as XML into string from Web Service

Posted on 2006-06-13
6
753 Views
Last Modified: 2007-12-19
I'm hoping this is a simple one, but I'm setting the points to 500 so I can hopefully get a quick answer.

I am trying to return a string from a web service that contains XML code from a dataset.  I have successfully done this except for how the data appears.  The following code is a modified representation of what I'm doing.

    <WebMethod()> _
    Public Function GetInfo(ByVal VehicleMake As String) As String

        Dim dsCatalog As DataSet = New DataSet("Catalog")
        Dim dtYears As DataTable = dsCatalog.Tables.Add("VehicleYears")

      Dim newRow As DataRow
        Dim i As Integer

        dtYears.Columns.Add("Year")

        For i = 2005 To 2006
            newRow = dtYears.NewRow
            newRow("Year") = i
            dtYears.Rows.Add(newRow)
        Next i

        Dim xmlDoc As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(dsCatalog)
        Dim strXML As String = xmlDoc.OuterXml
        Return strXML

    End Function

I expected the return string to appear as follows:
<Catalog><VehicleYears><Year>2005</Year><Year>2006</Year></VehicleYears></Catalog>

but, instead it looks like this:
<Catalog><VehicleYears><Year>2005</Year></VehicleYears><VehicleYears><Year>2006</Year></VehicleYears></Catalog>

What changes to my code do I need to make to get this to appear correctly?

Thanks.
0
Comment
Question by:drschank
  • 4
  • 2
6 Comments
 
LVL 9

Expert Comment

by:lojk
ID: 16898956
I did it this way.... (in vs2005 to be precise)


    <WebMethod()> Public Function RemoteQueryDataBase(ByVal ConnectionStringForRemoteServer As String, ByVal QueryString As String) As String
        Dim tTable As New Data.DataTable

        Dim tConn As New Data.SqlClient.SqlConnection(ConnectionStringForRemoteServer)
        tConn.Open()
        Dim daTempAdaptor As New Data.SqlClient.SqlDataAdapter(QueryString, tConn)
        daTempAdaptor.Fill(tTable)
        Console.WriteLine(tTable.TableName)
        tTable.TableName = "Table"

        Dim tResult As String = SerializeDataTable(tTable)
        Return tResult

    End Function

        Public Function SerializeDataTable(ByVal DataTableToSerialise As Data.DataTable) As String
        Dim oXS As System.Xml.Serialization.XmlSerializer
        Try
            oXS = New System.Xml.Serialization.XmlSerializer(GetType(Data.DataTable))
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Dim oStmW As IO.StringWriter
        oStmW = New IO.StringWriter()
      Try
            oXS.Serialize(oStmW, DataTableToSerialise)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        oStmW.Close()

        Return oStmW.ToString

    End Function


Then in the client App you need to deserialise the table back

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim tRef As New localhost.Service
         Dim tConnString As String = "your db cnn string"
        Dim tData As String   = tRef.RemoteQueryDataBase(tConnString, "Select * from tblTable")
        DisplayTable = DeserialiseDataTable(tData)
        DataGridView1.DataSource = DisplayTable

    End Sub

    Public Function DeserialiseDataTable(ByVal SerializedObject As String) As DataTable
        Dim oXS As Xml.Serialization.XmlSerializer = New Xml.Serialization.XmlSerializer(GetType(DataTable))
        Dim oStmR As IO.StringReader
        oStmR = New IO.StringReader(SerializedObject)
        Dim NewTable As DataTable = CType(oXS.Deserialize(oStmR), DataTable)
        oStmR.Close()
        Return NewTable
    End Function

I chuck my result straight into a DataGridView and pass my connection parameters to the webservice but with a minor tweak or two shoudl   should get you back on the right track...

hth
0
 

Author Comment

by:drschank
ID: 16903888
I'm a relative newbie to .NET so forgive my ignorance, I used your SerializeDataTable function by replacing this portion of my code:
        Dim xmlDoc As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(dsCatalog)
        Dim strXML As String = xmlDoc.OuterXml
        Return strXML

with this:
        Dim strYears As String = SerializeDataTable(dtYears)
        Return strYears

I got the error shown below.  Lines 105 and 113 referenced in the error, refer to these 2 lines in your function:
            oXS = New System.Xml.Serialization.XmlSerializer(GetType(Data.DataTable))
            oXS.Serialize(oStmW, DataTableToSerialise)

By the way, I'm using vs2003.

System.InvalidOperationException: There was an error reflecting type 'System.Data.DataTable'. ---> System.NotSupportedException: Cannot serialize member System.ComponentModel.MarshalByValueComponent.Site of type System.ComponentModel.ISite because it is an interface.
   at System.Xml.Serialization.TypeScope.ImportTypeDesc(Type type, Boolean canBePrimitive, MemberInfo memberInfo)
   at System.Xml.Serialization.TypeScope.GetTypeDesc(Type type, MemberInfo source, Boolean directReference)
   at System.Xml.Serialization.StructModel.GetPropertyModel(PropertyInfo propertyInfo)
   at System.Xml.Serialization.StructModel.GetFieldModel(MemberInfo memberInfo)
   at System.Xml.Serialization.XmlReflectionImporter.ImportStructLikeMapping(StructModel model, String ns)
   at System.Xml.Serialization.XmlReflectionImporter.ImportStructLikeMapping(StructModel model, String ns)
   at System.Xml.Serialization.XmlReflectionImporter.ImportTypeMapping(TypeModel model, String ns, ImportContext context, String dataType, Boolean repeats)
   --- End of inner exception stack trace ---
   at System.Xml.Serialization.XmlReflectionImporter.ImportTypeMapping(TypeModel model, String ns, ImportContext context, String dataType, Boolean repeats)
   at System.Xml.Serialization.XmlReflectionImporter.ImportElement(TypeModel model, XmlRootAttribute root, String defaultNamespace)
   at System.Xml.Serialization.XmlReflectionImporter.ImportTypeMapping(Type type, XmlRootAttribute root, String defaultNamespace)
   at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
   at System.Xml.Serialization.XmlSerializer..ctor(Type type)
   at Catalog.Request.SerializeDataTable(DataTable DataTableToSerialize) in C:\Inetpub\EDIWebService\Catalog\Request.asmx.vb:line 105
System.NullReferenceException: Object reference not set to an instance of an object.
   at Catalog.Request.SerializeDataTable(DataTable DataTableToSerialize) in C:\Inetpub\EDIWebService\Catalog\Request.asmx.vb:line 113
0
 
LVL 9

Accepted Solution

by:
lojk earned 500 total points
ID: 16904687
ah yes possibly... My code is based on vs2005.. If you are using VS2002/3 .net1.x cannot serialise a datatable but should be able to serialise a dataset (yeah i know sems a bit back to front, eh?)..

Use these subs instead

       Public Function SerializeDataset(ByVal DatasetToSerialise As Data.Dataset) As String
        Dim oXS As System.Xml.Serialization.XmlSerializer
        Try
            oXS = New System.Xml.Serialization.XmlSerializer(GetType(Data.Dataset))
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Dim oStmW As IO.StringWriter
        oStmW = New IO.StringWriter()
      Try
            oXS.Serialize(oStmW, DatasetToSerialise)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        oStmW.Close()

        Return oStmW.ToString

    End Function


    Public Function DeserialiseDataset(ByVal SerializedObject As String) As DataSet
        Dim oXS As Xml.Serialization.XmlSerializer = New Xml.Serialization.XmlSerializer(GetType(DataTable))
        Dim oStmR As IO.StringReader
        oStmR = New IO.StringReader(SerializedObject)
        Dim NewDataSet As DataSet= CType(oXS.Deserialize(oStmR), DataSet)
        oStmR.Close()
        Return NewDataSet
    End Function

and pass in (and  out) your dsCatalog instead of the dtYears...

Notice the same principle code (this is what serialisation is all about, you can use it for just about anything to 'Dehydrate' objects for later 'Rehydration' - I use the word Hydration because the original exmaple i read describes it in this context, Perhaps FreezeDriedPowder is a more accurate description)

Serialisation is cool - as an example of use I now tend to store Application User Parameters in a strongly typed class structure and serialise them to a settings file for desrialisation on load, saves me hours and hours of coding from the fat old ways i used to do it in vb6.

Let me know if that sorts it, ill be checking in later this evening to see how you got on...

Good Luck
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 9

Expert Comment

by:lojk
ID: 16904704
that should obviously say...

    Public Function DeserialiseDataset(ByVal SerializedObject As String) As DataSet
        Dim oXS As Xml.Serialization.XmlSerializer = New Xml.Serialization.XmlSerializer(GetType(Data.DataSet))'''oops
        Dim oStmR As IO.StringReader
        oStmR = New IO.StringReader(SerializedObject)
        Dim NewDataSet As Data.DataSet= CType(oXS.Deserialize(oStmR), Data.DataSet)
        oStmR.Close()
        Return NewDataSet
    End Function


oops... ;-)
0
 

Author Comment

by:drschank
ID: 16905067
Perfect.  Thanks for your help.
0
 
LVL 9

Expert Comment

by:lojk
ID: 16905127
Welcome... thx for points...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now