UGData Tutorial

Introduction

This article covers the details of building a simple database-backed HTTP/REST-compliant Web application using the exyus web engine. For this application, SQL Server 2005 is used as the database engine. The process of building a database-backed REST-ful Web application is not very different from bulding file-backed REST-ful applications. The only real differences are the details for defining the data table and stored procedures in the database. Also, the base class you use (XmlSqlResource) has some additional hooks for processing incoming requests and outgoing responses. These additional hooks give you more power when interacting with the database.

Another goal of this tutorial is to show you how to implement the full range of HTTP methods (GET, PUT, POST, DELETE) for a Web browser using HTML FORMs without any client-side scripting or custom HTTP Headers. Since HTML FORMs only support GET for reads and POST for writes, this application will need to resort to 'overloading' the POST method. The pattern used for overloading is to create a new URI that includes the actual method you wish to execute (i.e. /resource/1;put).

NOTE:

For brevity, this article focuses only on an XHTML representation for the resource. The actual online version of the UGData application also supports other representations (for example, application/json or application/xml, etc.).

top

SQL Stored Procedures

The first step in building this application is defining and implementing the data model in SQL Server 2005. The application will use just one data table and five stored procedures. The table will will hold some basic information about User Group Members: id, firstname, lastname, birthdate, and experience level. The stored procedures will handle the read, list, add, update, and delete tasks for the table.

Below is the T-SQL code that will create the needed table:

USE [exyus_samples]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UGData](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [firstname] [nvarchar](50) NOT NULL,
  [lastname] [nvarchar](50) NOT NULL,
  [birthdate] [datetime] NOT NULL,
  [experience] [nvarchar](25) NOT NULL,
 CONSTRAINT [PK_UGData] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX  = OFF, 
  STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS  = ON, 
  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
      

Authoring the stored procedures is the next step. The exyus engine takes advantage of the XML support in SQL Server 2005. In fact, the base class that will be used for this example (XmlSqlResource) requires that all output from SQL Server be returned as a stream of XML. For that reason, all the output from these stored procedures will be an XML stream.

Also, exyus works best when clear error conditions are returned from the database calls. That means the stored procedure will include basic error checking and use of the rasierror() method in SQL Server.

Below is the code that returns an XML list of the user group members from the UGData data table:

USE [exyus_samples]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: return list from ugdata table
-- =============================================
ALTER PROCEDURE [dbo].[ugdata_list]
AS
BEGIN
  SET NOCOUNT ON;

  if(select count(*) from ugdata)=0
    begin
      raiserror('records not found',16,1)
      return
    end
  else
    begin
      select 
        id as '@id',
        firstname,
        lastname,
        birthdate,
        experience
      from ugdata
      order by id asc
      for xml path ('member'), root('member-list')
    end
  --endif
END
      

Notice the last line of the procedure (for xml path ('member'), root('member-list')). That is the line that formats the output as an XML stream. Below is an example of the output from the ugdata_list procedure:

<member-list>
  <member id="117">
    <firstname>Mary</firstname>
    <lastname>Michelson</lastname>
    <birthdate>1989-01-01T00:00:00</birthdate>
    <experience>CallMeRoy</experience>
  </member>
  <member id="118">
    <firstname>Marvin</firstname>
    <lastname>Marrison</lastname>
    <birthdate>1979-01-01T00:00:00</birthdate>
    <experience>None</experience>
  </member>
  <member id="119">
    <firstname>Mandy</firstname>
    <lastname>Marvinson</lastname>
    <birthdate>1969-01-01T00:00:00</birthdate>
    <experience>ICanHitF1</experience>
  </member>
  <member id="120">
    <firstname>Mark</firstname>
    <lastname>Mandyville</lastname>
    <birthdate>1959-01-01T00:00:00</birthdate>
    <experience>WhatIsREST</experience>
  </member>
  ...
</member-list>
      

The remaining stored procedures (ugdata_read, ugdata_add, ugdata_update, ugdata_delete) are shown below. Note that the add and update routines also call the read procedure to return the appropriate row of data.

-- =============================================
-- Description: add new ugdata row
-- =============================================
CREATE PROCEDURE [dbo].[ugdata_add]
  @firstname nvarchar(50),
  @lastname nvarchar(50),
  @birthdate datetime,
  @experience nvarchar(25)
AS
BEGIN
  SET NOCOUNT ON;

  -- create record
  insert into ugdata
    (firstname,lastname,birthdate,experience)
  values
    (@firstname,@lastname,@birthdate,@experience)

  -- return new record
  exec ugdata_read @id=@@identity
END

-- =============================================
-- Description: delete a row from ugdata
-- =============================================
ALTER PROCEDURE [dbo].[ugdata_delete]
  @id int
AS
BEGIN
  SET NOCOUNT ON;

  if(select count(*) from ugdata where id=@id)=0
    begin
      raiserror('id not found [%i]',16,1,@id)
      return
    end
  else
    begin
      delete from ugdata where id=@id
    end
  --endif
END      

-- =============================================
-- Description: return single row from ugdata
-- =============================================
ALTER PROCEDURE [dbo].[ugdata_read] 
  @id int
AS
BEGIN
  SET NOCOUNT ON;

  if(select count(*) from ugdata where id=@id)=0
    begin
      raiserror('id not found [%i]',16,1,@id)
      return
    end
  else
    begin
      select
        id as '@id',
        firstname,
        lastname,
        birthdate,
        experience
      from ugdata
      where id=@id
      for xml path ('member')
    end
  --endif
END

-- =============================================
-- Description: update existing ugdata row
-- =============================================
ALTER PROCEDURE [dbo].[ugdata_update]
  @id int,
  @firstname nvarchar(50),
  @lastname nvarchar(50),
  @birthdate datetime,
  @experience nvarchar(25)
AS
BEGIN
  SET NOCOUNT ON;

  if(select count(*) from ugdata where id=@id)=0
    begin
      raiserror('id not found [%i]',16,1,@id)
    end
  else
    begin
      update ugdata set 
        firstname=@firstname,
        lastname=@lastname,
        birthdate=@birthdate,
        experience=@experience
      where id=@id
    end
  --endif

  exec ugdata_read @id

END
      

Once you have created the UGData table and the five stored procedures, you are ready to move on to defining the resource class in C#.

top

UGData Resource

Since this is a database-backed application, we'll start by creating a class that derives from the XmlSqlResource class. This class has all the details for supportng a data-bound read/write HTTP Resource built right in. all we need to do is define the URI and media-type rules and declare the parameters of the resource in the class constructor.

For this example, we want to expose the resource at the folowoing URI: /ugdata/. Also, we want to allow clients to access individual resources using the resource id - which is an integer in the UGDATA table. With that information, we can construction the following URIPattern (see code below).

To keep things simple and focused, this example will focus on the XHTML representation of the resource. However, the full version of the appication will also support XML and JSON representations for the same resource. That means the media-type attribute looks like this:

So, with the URIPattern and MediaTypes attributes defined, the basic class declaration looks like this:

// user group data example
[UriPattern(@"/ugdata/(?<id>[0-9]*)\.xcs")]
[MediaTypes("text/html","text/xml","application/json")]
class UGData : XmlSqlResource
{
    public UGData()
    {
    }
}
      

Now we can outline the remaining details for the resource class. First, we want to make this a read/write resource, so we will mark it to allow POST and DELETE methods. We will also set the resource to redirect clients at the end of the POST and supply a URI template to use when doing the POST redirection. Next, we need to supply the class with the database connection string pointer in the configuration file and indicate where the representation transformation files are located on disk. Also, to make things clean, let's include a default media type (text/html) and tell exyus to cache the results of GET requests for five minutes. Finally, we need to set the update media types (they are the content-type headers sent by clients) and set up the caching templates to allow exyus to refresh the internal cache whenever a PUT, POST, or DELETE are executed against this resource.

That's all we need to code in C# for now. The resulting class constructor that holds all these details looks like this:

// user group data example
[UriPattern(@"/ugdata/(?<id>[0-9]*)\.xcs")]
[MediaTypes("text/html","text/xml","application/json")]
class UGData : XmlSqlResource
{
    public UGData()
    {
        this.AllowPost = true;
        this.AllowDelete = true;
        this.RedirectOnPost = true;
        this.PostLocationUri = "/ugdata/{id}";

        this.ConnectionString = "exyus_samples";
        this.DocumentsFolder = "~/documents/ugdata/";

        this.ContentType = "text/html";
        this.LocalMaxAge = 600;

        this.UpdateMediaTypes = new string[]
        {
            "text/xml",
            "application/x-www-form-urlencoded",
            "application/json"
        };

        // set cache invalidation rules
        this.ImmediateCacheUriTemplates = new string[]
        {
            "/ugdata/.xcs",
            "/ugdata/{id}.xcs"
        };

    }
}
      

Now that the C# code is complete, we can move on to the XSL transformations needed to handle the XHTML representation of the resouce. These XSL and XSD files will be automatically used by the exyus engine to validate and transform all incoming requests and outgoing responses.

top

XHTML Representation

The eyus engine handles all request and response services through XSL transformation files. It is possible to create a set of transformations for each media-type supported by the defined resource. In addition to the XSL transformations, XSD Schema files are used to validate incoming requests. This is the heart of the exyus engine - validation and transformation.

Since this application will access information stored in a database, one of the tasks of the transformation services will be to convert incoming HTTP requests into valid T-SQL stored procedure calls. It is also important to point out that each HTTP method that this resource supports (GET, PUT, POST, DELETE) will needs it's own request and response transformations. Finally, each unique media-type (text/html, text/xml, application/json) will need a complete set of request and response transformations. As you can see, if your resource supports lots of media types, you have quite a few transformations that you must implement.

For this article, we'll only focus on the XHTML representation (text/html). That means we need to write transformations for the request and response events for GET, PUT, POST, and DELETE. Below are the details of each of these transformations.

GET Transformations

HTTP GET requests actually come in two 'modes': 'List' mode (/ugdata/) and 'Item' mode (/ugdata/{id}). When we create our GET transformations, we'll need to keep this in mind. Luckily, that's pretty easy. The exyus engine keeps track of the format of the incoming URI and we can test for the 'document id' within the transform in order to determine if we are in 'List' or 'Item' mode.

First, we need to transform incoming requests from clients into valid T-SQL calls. Here is the one that will work with the sotred procedures we created at the start of this article.

<?xml version="1.0" encoding="utf-8"?>
<!--
  get_request_html.xsl
  ugdata - transform inputs into valid t-sql sproc call
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
  
  <xsl:template match="/">
    <xsl:choose>
      <xsl:when test="//args/id!=''">
        exec ugdata_read <xsl:value-of select="//args/id"/>
      </xsl:when>
      <xsl:otherwise>
        exec ugdata_list
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

</xsl:stylesheet>
      

The transformation needed to respond to the GET request is a bit more involved. Since our goal is to create an application that will allow common Web browsers to perform full read/write (and delete) actions for this resource, we need to include the ability to add new records using the POST method and also update and delete records. For this example, we'll include an HTML FORM for POSTing a new resource to the server whenever we respond to a 'List' mode GET request. We'll return an HTML FORM for updating and an HTML FORM for deleting whenever we get an 'Item' mode GET request. That results in the following XSL transformation for response to GETs.

<?xml version="1.0" encoding="utf-8"?>
<!--
  get_response_html.xsl
  ugdata - transform list/item for html viewers
-->

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" encoding="utf-8"/>

  <xsl:param name="id" />

  <xsl:template match="/">
    <html>
      <head>
        <title>User Group Example [HTML]</title>
        <style type="text/css">
          label {width:5em;display:block;float:left;font-weight:bold;} 
          a {color:blue;}
          h1,h2,h3,h4 {margin-bottom:0;}
          ol,form {margin-top:0}
        </style>
      </head>
      <body>
        <h1>User Group Example [HTML]</h1>
        <xsl:choose>
          <xsl:when test="$id!=''">
            <h2>Member Record</h2>
            <div class="member-item">
              <xsl:apply-templates select="//member"  mode="item"/>
            </div>
            <p class="back-link">
              [<a href="./" title="back to the list">return to list</a>]
            </p>
          </xsl:when>
          <xsl:otherwise>
            <h2>Member List</h2>
            <ol class="member-list">
              <xsl:apply-templates select="//member"  mode="list"/>
            </ol>
            <h2>New Member</h2>
            <form method="post" action=".">
              <label for="firstname">first name:</label>
              <input type="text" name="firstname" />
              <br/>
              <label for="lastname">last name:</label>
              <input type="text" name="lastname" />
              <br/>
              <label for="birthdate">birth date:</label>
              <input type="text" name="birthdate" />
              <br/>
              <label for="experience">experience:</label>
              <select name="experience">
                <option value="None">None</option>
                <option value="WhatIsREST">What Is REST?</option>
                <option value="ICanHitF1">I Can Hit F1</option>
                <option value="GoogleRocks">Google Rocks!</option>
                <option value="CallMeRoy">Call Me Roy</option>
              </select>
              <br />
              <input type="submit" value="Add"/>
            </form>
          </xsl:otherwise>
        </xsl:choose>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="member" mode="list">
    <li>
      <a href="{@id}" title="view member record">
        <xsl:value-of select="lastname"/>, <xsl:value-of select="firstname"/>
      </a>
    </li>
  </xsl:template>

  <xsl:template match="member" mode="item">
    <!-- update form -->
    <form action="./{@id};put" method="post">
      <label>first name:</label>
      <input name="firstname" value="{//firstname}" />
      <br />
      <label>last name:</label>
      <input name="lastname" value="{//lastname}" />
      <br />
      <label>birth date:</label>
      <input name="birthdate" value="{//birthdate}" />
      <br />
      <label for="experience">experience:</label>
      <select name="experience">
        <option value="None">
          <xsl:if test="//experience='None'">
            <xsl:attribute name="selected">true</xsl:attribute>
          </xsl:if> 
          None
        </option>
        <option value="WhatIsREST">
          <xsl:if test="//experience='WhatIsREST'">
            <xsl:attribute name="selected">true</xsl:attribute>
          </xsl:if> 
          What Is REST?
        </option>
        <option value="ICanHitF1">
          <xsl:if test="//experience='ICanHitF1'">
            <xsl:attribute name="selected">true</xsl:attribute>
          </xsl:if>
          I Can Hit F1
        </option>
        <option value="GoogleRocks">
          <xsl:if test="//experience='GoogleRocks'">
            <xsl:attribute name="selected">true</xsl:attribute>
          </xsl:if>
          Google Rocks!
        </option>
        <option value="CallMeRoy">
          <xsl:if test="//experience='CallMeRoy'">
            <xsl:attribute name="selected">true</xsl:attribute>
          </xsl:if>
          Call Me Roy
        </option>
      </select>
      <br />
      <input type="submit" value="Update" title="update this record"/>
    </form>
    <!-- delete form -->
    <form action="./{@id};delete" method="post">
      <input type="submit" value="Delete" title="delete this record"/>
    </form>
  </xsl:template>
  
</xsl:stylesheet>
      

In practice, the GET response for text/html clients is the most involved. The transformation tasks for POST, PUT, and DELETE are much simpler for this application.

POST, PUT, and DELETE Transformations

Since we will be redirecting clients whenever they perform add, update, or delete actions, we do not need to create any response transformations for these actions. However, we still need to create request transformations to convert incoming requests into valid T-SQL calls.

In the case of POST and PUT requests, clients will be sending an entity body along with the request. This entity body will contain the names and values of the fields that clients wish to add or update to the data stored on the server. To make things easy, exyus converts incoming HTML FORM data into a simple XML document (each FORM field is an element in the document). So all we need to do is create a transformation that converts the incoming 'XML document' into a valid T-SQL call. Handling DELETE requests is even easier. We just need a transformation that takes the 'id' from the URL and passes that to the database via a T-SQL call. Below are the transformations for the remaining incoming requests.

<?xml version="1.0" encoding="utf-8"?>
<!--
  post_request_html.xsl
  ugdata - transform HTML inputs into valid t-sql add call
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"/>

  <xsl:param name="id" />
  
  <xsl:template match="/">
    <html>
      <body>
        <h1>Record Added</h1>
        <p>
          <a href="./{$id}" title="view new record">view new record</a>
        </p>
      </body>
    </html>
  </xsl:template>

</xsl:stylesheet>

<?xml version="1.0" encoding="utf-8"?>
<!--
  put_request_html.xsl
  ugdata - transform HTML inputs into valid t-sql add call
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>

  <xsl:param name="id" />

  <xsl:template match="/">
    exec ugdata_update
    @id=<xsl:value-of select="$id" />,
    @firstname='<xsl:value-of select="//firstname"/>',
    @lastname='<xsl:value-of select="//lastname"/>',
    @birthdate='<xsl:value-of select="//birthdate"/>',
    @experience='<xsl:value-of select="//experience"/>'
  </xsl:template>

</xsl:stylesheet>

<?xml version="1.0" encoding="utf-8"?>
<!--
  delete_request_html.xsl
  ugdata : convert args into valid delete sproc call
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>

<xsl:template match="/">
  exec ugdata_delete <xsl:value-of select="//id"/>
</xsl:template>

</xsl:stylesheet> 
      

Once we have all the transformations complete, we're almost finished with the application. We only have two more steps left. We need to create special HTTP Resource classes to handle updating and deleting an HTTP resource using just an HTML FORM POST. We need to implement HTTP POST overloading.

top

UGData Update and Delete

Supporting HTTP PUT and DELETE using common Web browsers is a challenge. Early in the development of the Web, the idea was that all resources would be editable. Instead Web browsers were built to allow only reading and creating new items (GET and POST). Ever since this mistake, PUT and DELETE actions have been difficult to properly implement without resorting to client scripting of some kind.

An alternate to scripting clients is to 'overload' the browser POST method to convince the server that the request is really a PUT or DELETE. There are a number of ways to do this. For this application, we'll create a new resource URI that contains the requested action (/ugdata/1;put and /ugdata/1;delete). That means we need to define a new HTTP resource class to accept these requests. That resource will then have a Post() function that will conver the request into the proper PUT or DELETE action and execute that action on behalf of the client. In this way, the server becomes a 'proxy' for the Web client.

Creating proxy resources in this way has some drawbacks. First, since we are defining a new resource, it is possible that some clients might attempt a GET or HEAD request using the same URI. It's also possible that advanced clients might attempt PUT or DELETE requests, too! For these reasons, our resource will explicitly handle the other HTTP methods in a safe way.

Finally, since this resource is not, itself, making any database calls, we will not use the XmlSqlResource class to implement this resource. Instead we'll use the HTTPResource base class. Below is the complete code for the UGDataUpdate class.

    // handle form-posting for updates
    [UriPattern(@"/ugdata/(?<id>[0-9]*);put\.xcs")]
    [MediaTypes("application/x-www-form-urlencoded")]
    class UGDataUpdate : HTTPResource
    {
        Utility util = new Utility();
        private string[] mediaTypes = null;
        private string UrlPattern;

        public UGDataUpdate()
        {
            this.ContentType = "application/x-www-form-urlencoded";

            //get first pattern (if none set already)
            if (this.UrlPattern == null || this.UrlPattern == string.Empty)
            {
                this.UrlPattern = util.GetDefaultUriPattern(this);
            }

            // copy media types to make things easier
            mediaTypes = util.GetMediaTypes(this);
            
        }

        public override void Get()
        {
            Hashtable arg_list = util.ParseUrlPattern(this.Context.Request.RawUrl, this.UrlPattern);
            if (!arg_list.Contains("id"))
            {
                throw new HttpException(400, "Missing document id");
            }
            string id = arg_list["id"].ToString();
            this.Context.Response.Redirect("/xcs/ugdata/" + id);
        }

        public override void Post()
        {
            // validate media type
            string mtype = util.SetMediaType(this, mediaTypes);

            // validate argument
            Hashtable arg_list = util.ParseUrlPattern(this.Context.Request.RawUrl, this.UrlPattern);
            if (!arg_list.Contains("id"))
            {
                throw new HttpException(400, "Missing document id");
            }

            // get POSTed body
            string data = string.Empty;
            using (StreamReader sr = new StreamReader(Context.Request.InputStream))
            {
                data = sr.ReadToEnd();
                sr.Close();
            }

            // compose tgarget URL
            string url = string.Format("{0}://{1}{2}{3}",
                    this.Context.Request.Url.Scheme,
                    this.Context.Request.Url.DnsSafeHost,
                    "/xcs/ugdata/",
                    arg_list["id"]);

            // build up execution client w/ credentials
            HTTPClient c = new HTTPClient();
            c.Credentials = util.GetCurrentCredentials(this);

            // validate record already exists
            string rtn = c.Execute(url, "head", "text/html");
            string etag = c.ResponseHeaders["etag"];

            // execute PUT to target
            c.RequestHeaders.Add("if-match", etag);
            c.Execute(url, "put", this.ContentType, data);
            c = null;

            // redirect to list
            this.Context.Response.Redirect("/xcs/ugdata/");
        }
    }
      

Note the use of the HTTPClient class. This acts as a full-featured Web client and is used to perform the actual HTTP PUT for the client. Notice that it also automatically redirects the client back to the list (with the updated information) after the PUT is executed.

You can also see that the Get() function has been overridden to strip off the ";put" action from the URL and redirect the client to the *real* resource. This prevents clients from performing GETs against this 'fake' resource URI. It should also be noted that the PUT and DELETE functions for this base class automatically return HTTP Status 405 (method not allowed).

The DELETE implementation is almost identical. You can check out the code in the downloadable code.

top

Summary

In this article, you learned how exyus can be used to create database-backed resources that support full read/write capabilities. You also learned how the XmlSqlResource class uses XSL transformations for both requests and responses for each media type supported by the resource class you define.Finally, you learned how to use exyus to create 'proxy' resources to allow HTML FORMS clients (common web browsers) to execute PUT and DELETE actions without resorting to client-side scripting.

There are quite a few other options and features of database-backed resource programming with exyus including XSD validations, supporting multiple media types, and much more. Check out the online version of the UGData application to see more on the possibilities of building data-bound applications with exyus.

top

About the Author

Mike Amundsen lives and works as a contract programmer in Kentucky, USA. He currently spends most of his time creating and supporting large-scale web sites running under Windows and ASP.NET. In the past, he spent quite a bit of time as a trainer/speaker and was involved in the writing of several books on programming with Microsoft technologies.