how to return a datatable from a wcf service rest

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)]
          DataTable GetDataTableXml();
  
      }
• Service Implementation class

    public class RestWCF : IRestWCF                       {
                              public DataTable GetDataTableXml()      {

DataTable salesdata = new DataTable("SalesPerson");

•           try
            {
                using (SqlConnection conn = new SqlConnection(@"server=(local)\sqlexpress2012;database=AdventureWorks2012;uid=sa;pwd=password&"))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter("select * from [AdventureWorks2012].[Sales].[SalesPerson]", conn))
                    {
                        adapter.Fill(salesdata);
  
                        salesdata.AcceptChanges();
                        conn.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            salesdata.RemotingFormat = SerializationFormat.Xml;
            return salesdata;
  
        }

                  }

• 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 C#
• 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 String id { get; set; }
        [XmlAttribute(Namespace = "urn:schemas-microsoft-com:xml-msdata")]
        public int rowOrder { get; set; }

        [XmlElement]
        public int? BusinessEntityID { get; set; }
        [XmlElement]
        public decimal? Bonus { get; set; }
        [XmlElement]
        public decimal? CommissionPct { get; set; }
        [XmlElement]
        public decimal? SalesLastYear { get; set; }
        [XmlElement]
        public decimal? SalesYTD { get; set; }
        [XmlElement]
        public int? TerritoryID { get; set; }
        [XmlElement]
        public DateTime? ModifiedDate { get; set; }
    }
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

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

                    XmlReader re = XmlReader.Create(stream);
                    while (re.Read())
                    {
                        if (re.NodeType == XmlNodeType.Element &&   re.LocalName.Equals("SalesPerson"))
                        {
                            XmlSerializer ser = fact.CreateSerializer(typeof(SalesPerson));

                            SalesPerson p = (SalesPerson)ser.Deserialize(re);
                           //add to list.
                            salesPersonXML.Add(p);
                        }
                    }
//Bind to datagrid in WPF
                    datagrid1.ItemsSource = salesPersonXML;

OUTPUT