WCF Services : Simplest way to create a WCF REST(Representational State Transfer) service.

Goal

To create a WCF REST(Representational State Transfer) service. The main goal is to show it in the most simplest way.

Follow the below given steps

1. Open Visual Studio 2010.
2. File >> New Project >> WCF >> WCF Service Application. I named it SimpleWCFService.
3. I removed the default "IService1.cs" and "Service1.svc". Added a new "Sample.svc". This will automatically create an interface in file "ISample.cs". The structure will look like the image given below.

ISample.cs

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Web;
#endregion

namespace SimpleWCFService
{
    [ServiceContract]
    public interface ISample
    {
        /// <summary>
        /// The parameters are passed in URL expression.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        [OperationContract]
        [WebGet(UriTemplate = "SampleCheck/{Param1}/{Param2}")]
        string SampleCheckUrl(string Param1, string Param2);

        /// <summary>
        /// The parameters are passed as QueryString.
        /// </summary>
        /// <param name="Param1">{Param1}</param>
        /// <param name="Param2">{Param2}</param>
        /// <returns></returns>
        [OperationContract]
        [WebGet(UriTemplate = "SampleCheck?Param1={Param1}&Param2={Param2}")]
        string SampleCheckQueryString(string Param1, string Param2);
    }
}

Sample.svc.cs

#region System
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Web.Script.Serialization;
#endregion

namespace SimpleWCFService
{

    /// <summary>
    /// Simple Class
    /// </summary>
    [DataContract]
    public class SimpleClass
    {
        [DataMember]
        public string CParam1 { get; set; }

        [DataMember]
        public string CParam2 { get; set; }

        [DataMember]
        public string CMessage { get; set; }
    }


    /// <summary>
    /// The simplest way to use a WCF REST(Representational State Transfer) service
    /// </summary>
    public class Sample : ISample
    {

        /// <summary>
        /// The parameters are passed in URL expression.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        public string SampleCheckUrl(string Param1, string Param2)
        {
            JavaScriptSerializer _jScript = new JavaScriptSerializer();
            SimpleClass _simple = new SimpleClass()
            {
                CParam1 = Param1,
                CParam2 = Param2,
                CMessage = "Success with SampleCheckUrl(int Param1, string Param2)!"
            };
            return _jScript.Serialize(_simple);            
        }

        /// <summary>
        /// The parameters are passed as QueryString.
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <returns></returns>
        public string SampleCheckQueryString(string Param1, string Param2)
        {
            JavaScriptSerializer _jScript = new JavaScriptSerializer();
            SimpleClass _simple = new SimpleClass()
            {
                CParam1 = Param1,
                CParam2 = Param2,
                CMessage = "Success with SampleCheckQueryString(int Param1, string Param2)!"
            };
            return _jScript.Serialize(_simple);
        }
    }
}

Web.config - MOST IMPORTANT

<?xml version="1.0"?>
<configuration> 
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior name="SimpleWCFServiceBC">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="restBehaviour">
          <webHttp automaticFormatSelectionEnabled="true" helpEnabled="true"/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
    <bindings>
      <webHttpBinding>
        <binding name="RestBinding" />
      </webHttpBinding>
      <basicHttpBinding>

      </basicHttpBinding>
    </bindings>
    <services>
      <service name="SimpleWCFService.Sample" behaviorConfiguration="SimpleWCFServiceBC">
        <endpoint address="" binding="basicHttpBinding" contract="SimpleWCFService.ISample"></endpoint>
        <endpoint address="rest" binding="webHttpBinding" behaviorConfiguration="restBehaviour" bindingConfiguration="RestBinding" contract="SimpleWCFService.ISample"></endpoint>
        <endpoint contract="IMetadataExchange" binding="mexHttpBinding" address="mex" />
      </service>
    </services>
  </system.serviceModel>

  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>

</configuration>

Checking REST Service - URL FORMAT

Navigate to the below given url(Note:Replace the port number.).
"http://{LOCALHOST:PORTNUMBER}/Sample.svc/rest/SampleCheck/Args1/Args2".
This should give the result in below given format.

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">{"CParam1":"Args1","CParam2":"Args2","CMessage":"Success with SampleCheckUrl(int Param1, string Param2)!"}</string> 

Checking REST Service - QueryString

Navigate to the below given url(Note:Replace the port number.).
"http://{LOCALHOST:PORTNUMBER/Sample.svc/rest/SampleCheck?Param1=Args1&Param2=Args2".
This should give the result in below given format.

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">{"CParam1":"Args1","CParam2":"Args2","CMessage":"Success with SampleCheckQueryString(int Param1, string Param2)!"}</string> 

I spent a lot of time trying to figure this out so writing this up for the ones who just want solutions. Let me know if this helped.

SQL Server : Using CTE(Common Table Expression) fetch a Tree View

Goal

Using CTE show a complete tree view.

Challenge

When a developer tries to search CTE in internet, there are millions of articles which already exists which says to get a tree view using CTE. But there is a problem with most of them. They dont give correct output. For making the data show in correct way users should enter data in an order, else it wont come. So first I will explain how the examples on internet mislead.

---

SQL - Create temp table

-- DECLARE
DECLARE @Company AS TABLE(EmpID INT, ParentID INT, PersonName VARCHAR(100));

-- Insert Temp Records
INSERT INTO @Company(EmpID, ParentID, PersonName) 
VALUES(1 , NULL , 'Maulik Dhorajia')
    , (2 , NULL , 'Bhavesh Gohel')
    , (3 , NULL , 'Dinesh Padhiyar')
    , (4 , 2 , 'Vijay Kumar')
    , (5 , 1 , 'Jitendra Makwana')
    , (6 , 4 , 'Jayesh Dhobi')
    , (7 , 1 , 'Shivpalsinh Jhala')
    , (8 , 5 , 'Amit Patel')
    , (9 , 3 , 'Abidali Suthar')

-- Default data

SELECT * FROM @Company;

---

SQL - Which usually misleads to get correct tree view

-- Incorrect result which we usually find on Internet
;WITH CTECompany
AS
(
    SELECT EmpID, ParentID, PersonName , 0 AS HLevel 
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT C.EmpID, C.ParentID, C.PersonName , (CTE.HLevel + 1) AS HLevel 
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Misleading SQL
SELECT * FROM
(
    SELECT 
        EmpID
        , ParentID
        , HLevel
        , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
    FROM CTECompany
) AS P
ORDER BY HLevel;

---

SQL - Correct CTE with Tree View

-- Working Example
;WITH CTECompany
AS
(
    SELECT 
        EmpID, 
        ParentID, 
        PersonName , 
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        C.EmpID, 
        C.ParentID, 
        C.PersonName , 
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;

Hope this helped and saved a lot of your time!

Reply for the comment "Nice try. still relies on order of data entry. add (44 , 1 , 'XXitendra Makwana')". I tried to see what was wrong but I can see the correct tree with the same query. Not sure what is wrong. Can anyone else see the issue and post a comment? Thanks in advance.