Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning DataSeta as XML into string from Web Service

Posted on 2006-06-13
6
Medium Priority
?
788 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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