Posts

....
Technical Blog for .NET Developers ©

Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts

Wednesday, July 24, 2013

WCF Rest Services

When we are designing processes which expose data across multi-platform and systems, we have to consider the implementation of REST Services

REST stands for Representational State Transfer. In RESTful systems, servers expose resources using a URI, and clients access these resources using the four HTTP verbs
GET: used exclusively to retrieve data

DELETE: used for deleting resources

PUT: used to add or change a resource

POST: used to modify and update a resource
In this example, we will develop a REST Service with Xml and Json responses, using WCF, and will make the whole circuit to the call

The first step is beginning a new WCF Application. This is the implementation of the interface with the definition of the service contract

 
	namespace RestWCFService
    {
        [ServiceContract]
        public interface IRestService
        {
            [OperationContract]
            [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Xml,
                BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "getXml/{id}")]
            string XmlElement(string id);

            [OperationContract]
            [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json,
                BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "getJson/{id}")]
            string JsonElement(string id);
        }
    }
    


The next step is implementing the service contract

 
	public class RestService : IRestService
    {
        public string XmlElement(string atomicSymbol)
        {
            return getElement(atomicSymbol);
        }

        public string JsonElement(string atomicSymbol)
        {
            return getElement(atomicSymbol);
        }

        private string getElement(string aSymbol)
        {
            Dictionary<string, string> Elements = getElementsTable();

            if (Elements.Keys.Contains(aSymbol))
                return "the element is " + Elements[aSymbol];
            else
                return "there is no match for " + aSymbol;
        }
    }


Now we have to set up the web.config file, changing the next sections:

 
	<services>
      <service name="RestWCFService.RestService" behaviorConfiguration="ServiceBehavior">
        <endpoint binding="webHttpBinding" contract="RestWCFService.IRestService" 
            behaviorConfiguration="web">
        </endpoint>
      </service>
    </services>
    
    <behaviors>
      <serviceBehaviors>
        <behavior name="ServiceBehavior">
          <!-- To avoid disclosing metadata information, set the value below to false 
           and remove the metadata endpoint above 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="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="web">
          <webHttp helpEnabled="true" faultExceptionEnabled="true"/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
- The 'services' section will point to our service contract definition, and the 'behaviors' section will enable the http get method

Now we publish the service and check it from the browser



http://localhost:9479/RestService.svc/getXml/O



http://localhost:9479/RestService.svc/getJson/K



To call this service from the client layer we can make use of jQuery Ajax, the code is the next

 
	function getChemical(atomicSymbol) {

        $.ajax({
            url: "http://localhost:9479/RestService.svc/getJson/" + atomicSymbol,
            type: "GET",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            data: {},
            processdata: true,
            success: function (response) {

                var element = response.JsonElementResult;
                $('#lblElement').html(element);
            },

            error: function (e) {
                alert(e.status + ". " + e.statusText);
            }
        });
    }





<METHOD SOFTWARE © 2013>

Sunday, March 24, 2013

XML Type in SQL Server

XML is present in every sort of developments today. Knowingly or unknowingly, every application deals with XML in one way or another. For example, .NET applications use XML files to store configuration information. ASP.NET web pages are XML documents. Almost all modern websites generate and publish information as XML feeds (RDF, RSS, ATOM, OPML, etc.)

In this post we are going to check the XML data type introduced with SQL Server 2005 and improved in SQL Server 2008

The table definition with XML data type is as follows

     
	CREATE TABLE C_EMPLOYEES_PROJECTS
    (
        IdEmployee    INT NOT NULL,
        IdProject   INT NOT NULL,
        ProjectDetails XML,
        
        CONSTRAINT PK_EMP_PROJECTS
            PRIMARY KEY (IdEmployee, IdProject),
        CONSTRAINT FK_EMPLOYEES
            FOREIGN KEY (IdEmployee)
            REFERENCES C_EMPLOYEES(IdEmployee),
        CONSTRAINT FK_PROJECTS
            FOREIGN KEY (IdProject)
            REFERENCES C_PROJECTS(IdProject)
    )
    GO
    


Now we will add a couple of records to the table

     
INSERT INTO C_EMPLOYEES_PROJECTS 
    VALUES (1, 1, 
        '<Project Title="Migration of web application">
            <AssignmentDate>10-10-2012</AssignmentDate>
            <DueDate>04-01-2013</DueDate>
            <Technology>.NET</Technology>
            <CustomerId>4</CustomerId>
        </Project>')

    INSERT INTO C_EMPLOYEES_PROJECTS 
    VALUES (2, 4, 
        '<Project Title="Applets for intranet">
            <AssignmentDate>11-20-2012</AssignmentDate>
            <DueDate>05-01-2013</DueDate>
            <Technology>Java</Technology>
            <CustomerId>3</CustomerId>
        </Project>')   
        


The result of a select statement is the next



XQuery is a language for querying XML data that allows navigational access based on XPath 2.0
The XML data type supports the next methods:

- value(): is useful for extracting scalar values from XML documents as a relational value. This method takes an XQuery expression that identifies a single node and the desired SQL type to be returned

- query(): takes an XQuery expression that evaluates to a list of XML nodes and allows the user to extract fragments of an XML document

- exist(): allows the user to perform checks on XML documents to determine if the result of an XQuery expression is empty or nonempty. The result of this method is 1 if the XQuery expression returns a nonempty result, 0 if the result is empty, and NULL if the XML instance itself is NULL

- modify(): can be used to modify the content of an XML document. It accepts XML DML statements to insert, update, or delete one or more nodes from an XML instance

- nodes(): accepts an XQuery expression and returns a rowset in which each row represents a context node identified by the query expression. Methods of the XML data type such as query(), value(), exist(), and nodes() can also be invoked on the context nodes returned from the nodes() method


Below we present examples of the usage of each method

     
-- Examples of value() 
-- gets the value of the Title attribute of Project node 
SELECT
ProjectDetails.value('/Project[1]/@Title', 'VARCHAR(50)') AS ProjectTitle
FROM C_EMPLOYEES_PROJECTS

-- gets the value of the Title attribute of Project node
SELECT
ProjectDetails.value('(/Project/DueDate)[1]', 'VARCHAR(50)') AS [Due Date]
FROM C_EMPLOYEES_PROJECTS

-- You can also include xquery expressions in WHERE clause
SELECT IdEmployee
FROM C_EMPLOYEES_PROJECTS
WHERE ProjectDetails.value('(/Project/@Title)[1]', 'VARCHAR(50)') LIKE 'Applets%'

SELECT IdEmployee
FROM C_EMPLOYEES_PROJECTS
WHERE ProjectDetails.value('(/Project/Technology)[1]', 'VARCHAR(50)') = '.NET'

--
-- Examples of query()

-- gets the specified xml fragment of the document
SELECT
ProjectDetails.query('/Project/DueDate') AS [Due Date]
FROM C_EMPLOYEES_PROJECTS 

-- gets the text value of the node in the specified xml framgment of the document
SELECT
ProjectDetails.query('/Project/DueDate/text()')  AS [Due Date]
FROM C_EMPLOYEES_PROJECTS 

--
-- Examples of exist()

-- will iterates the Project childnodes and returning 0 / 1 for not exist / exist respectively
SELECT
ProjectDetails.exist('/Project[1]/CustomerId[text() = "4"]')
FROM C_EMPLOYEES_PROJECTS 

--
-- Examples of modify()

-- sets the value of CustomerId node to "2" where idproject = 1
UPDATE C_EMPLOYEES_PROJECTS 
SET ProjectDetails.modify('replace value of (/Project/CustomerId/text())[1] with xs:string("2")')
WHERE IdProject = 1

-- sets the value of Title attribute where idproject = 1
UPDATE C_EMPLOYEES_PROJECTS 
SET ProjectDetails.modify('replace value of (/Project/@Title)[1] with xs:string("Migration of application and database")')
WHERE IdProject = 1

--
-- Example of nodes()

-- gets the value of the AssignmentDate node specified in the xquery expression of nodes function
SELECT P.A.query('text()')
FROM C_EMPLOYEES_PROJECTS
CROSS APPLY ProjectDetails.nodes('/Project/AssignmentDate') as P(A)

-- gets the value of thee Title attribute from the node specified in the xquery expression of nodes function
SELECT P.A.value('@Title', 'VARCHAR(50)')
FROM C_EMPLOYEES_PROJECTS
CROSS APPLY ProjectDetails.nodes('/Project') as P(A)


Finally we will apply a JOIN clause between the value of an xml node with a Relational table

     
SELECT
    EP.ProjectDetails.value('/Project[1]/@Title', 'VARCHAR(50)') 
    AS [Project Title], C.Name
FROM C_EMPLOYEES_PROJECTS EP  
CROSS APPLY ProjectDetails.nodes('/Project/CustomerId') as P(C)
JOIN C_CUSTOMERS C
ON P.C.value('.', 'INT') = C.IdCustomer





<METHOD SOFTWARE © 2013>