How to return a datatable from a wcf service rest VB.NET

How to return a datatable from a wcf service rest

• Create a WCF Service Application
• Add WCF Service name it as IRESTWCF
• Add a method called DataTable GetDataTableXml() 
• <ServiceContract> _
      Public Interface IRestWCF
  
          <OperationContract> _
          <WebGet(BodyStyle = WebMessageBodyStyle.Bare, ResponseFormat = WebMessageFormat.Xml)> _
          Dim GetDataTableXml() As DataTable
  
      End Interface
• Service Implementation class RestWCF.vb

   
Public Class RestWCF
     Implements IRestWCF
                              Public Function GetDataTableXml() As DataTable

Dim salesdata As DataTable =  New DataTable("SalesPerson")

                  Try
                      Imports (SqlConnection conn = New SqlConnection("server=(local)\sqlexpress2012database=AdventureWorks2012uid=sapwd=password&"))
                      {
                          Imports (SqlDataAdapter adapter = New SqlDataAdapter("select * from (AdventureWorks2012).(Sales).(SalesPerson)", conn))
                          {
                              adapter.Fill(salesdata)

                              salesdata.AcceptChanges()
                              conn.Close()
                          }
                      }
                  Catch ex As SqlException
                      Throw ex
                  End Try
                  salesdata.RemotingFormat = SerializationFormat.Xml
                  Return salesdata

                              End Function

End Class

• Web.config

    <behaviors>
      <serviceBehaviors>
        <behavior>
          <!-- To avoid disclosing metadata information, set the value below to false before deployment -->
          <serviceMetadata httpGetEnabled="true" />
          <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="true" />
        </behavior>
        <behavior name="restbeh">
          <serviceDebug httpHelpPageEnabled="True" includeExceptionDetailInFaults="true" />
          <serviceMetadata  httpGetEnabled="True"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="restendbeh">
          <!--<soapProcessing processMessages="True"/>-->
          <webHttp helpEnabled="True" faultExceptionEnabled="True" defaultBodyStyle="Bare" defaultOutgoingResponseFormat="Xml"/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
    <bindings>
      <webHttpBinding>
        <binding name="myrestbinding">
          <security mode="None"></security>
          <readerQuotas maxArrayLength="65536"/>
        </binding>
      </webHttpBinding>
    </bindings>

    <services>
      <service name="WCFServicesREST.RestWCF" behaviorConfiguration="restbeh">
        <endpoint  contract="WCFServicesREST.IRestWCF" binding="webHttpBinding"  bindingConfiguration="myrestbinding"
                   behaviorConfiguration="restendbeh"></endpoint>
       
      </service>
    </services>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true"  aspNetCompatibilityEnabled="True"/>
  </system.serviceModel>
  <system.webServer>
 </configuration>

Consuming  in WPF Application

• Create a WPF Application using .NET 4.0/4.5 using VB.NET
• Add a DataGrid to XAML name it as datagrid1
• Add namespaces in .cs file
• using System.Xml;
• using System.Xml.Serialization;
• using System.Net;

    Declare a class called SalesPerson class for XML Serialization/DeSerialization.

<XmlRoot(ElementName = "SalesPerson", Namespace = "")> _
    Public Class SalesPerson
        <XmlAttribute(Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1")> _
        Public Property id() As String
        End Property
        <XmlAttribute(Namespace = "urn:schemas-microsoft-com:xml-msdata")> _
        Public Property rowOrder() As Integer
        End Property

        <XmlElement> _
        Public Property BusinessEntityID() As int?
        End Property
        <XmlElement> _
        Public Property Bonus() As decimal?
        End Property
        <XmlElement> _
        Public Property CommissionPct() As decimal?
        End Property
        <XmlElement> _
        Public Property SalesLastYear() As decimal?
        End Property
        <XmlElement> _
        Public Property SalesYTD() As decimal?
        End Property
        <XmlElement> _
        Public Property TerritoryID() As int?
        End Property
        <XmlElement> _
        Public Property ModifiedDate() As DateTime?
        End Property
    End Class

Note: Each element in this class should match columns returned from wcf  Datatable, column names are case sensitive. null columns should use ?. Namespaces also must match, otherwise "serialization error saying <SalesPerson xmlns=''> not expected here" will appear.

Because WCF Rest doesn't serve proxy class. So You need ti call WebRequest/HttpWebRequest in System.Net.

as shown below

Dim req As WebRequest =  WebRequest.Create("http://localhost:3054/RestWCF.svc/GetDataTableXml")
                  req.ContentType = "text/xml"
                    Dim resp As WebResponse =  req.GetResponse()
                    Dim stream As Stream =  resp.GetResponseStream()
                    Dim fact As XmlSerializerFactory =  New XmlSerializerFactory()

                    Dim re As XmlReader =  XmlReader.Create(stream)
                    While re.Read()
                        If re.NodeType = XmlNodeType.Element And re.LocalName.Equals("SalesPerson") Then
                            Dim ser As XmlSerializer =  fact.CreateSerializer(Type.GetType(SalesPerson))

                            Dim p As SalesPerson = CType(ser.Deserialize(re), SalesPerson)
                           'add to list.
                            salesPersonXML.Add(p)
                        End If
                    End While


//Bind to datagrid in WPF
                    datagrid1.ItemsSource = salesPersonXML

OUTPUT