REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET

Setting up the .NET application

1. Download the npgsql 1.01 driver from pgfoundary - http://pgfoundry.org/projects/npgsql. For ASP.NET 2.0 you'll want - Npgsql1.0.1-bin-ms2.0.zip and for Mono.NET you'll want Npgsql1.0.1-bin-mono-2.0.zip. Unzip and place the files in bin folder of your web app project.

2. Since we are just creating a simple REST web service and don't need any plumming of the standard SOAP like webservice, we will be using a .NET handler class (ashx) instead of an asmx. We have two versions listed below. One for C# and one for VB.NET/Mono Basic

The following application files were tested on both Microsoft ASP.NET 2.0 IIS and Mono.NET 1.2.6 XSP Test Server ASP.NET 2

Below is the web.config file that works on both .NET implementations.

<?xml version="1.0"?>
<configuration>
    <appSettings/>
  <connectionStrings>
    <add name="DSN" 
        connectionString="Server=127.0.0.1;Port=5432;User Id=pagila_app;Password=pg@123;Database=pagila;"/>
  </connectionStrings>
    <system.web>
        <compilation debug="true" />
    </system.web>
</configuration>

Our VB.NET/Mono Basic Rest Service looks like this

'--PagilaSearch_VB.ashx

<%@ WebHandler Language="VB" Class="PagilaSearch_VB" %>
Imports System
Imports System.Web
Imports Npgsql

Public Class PagilaSearch_VB : Implements IHttpHandler
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim maxrecs, offset As Integer
        maxrecs = 30 :  offset = 1
        If (Not IsNothing(context.Request("query"))) Then
            context.Response.ContentType = "text/xml"
            If IsNumeric(context.Request("maxrecs")) Then
                maxrecs = context.Request("maxrecs")
            End If
            If IsNumeric(context.Request("offset")) Then
                offset = context.Request("offset")
            End If
            context.Response.Write(GetResults(context.Request("query"), maxrecs, offset))
        End If
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
    
    Public Function GetResults(ByVal aquery As String, ByVal maxrecs As Integer, ByVal offset As Integer) As String
        Dim result As String = ""
        Dim command As NpgsqlCommand
        Using conn As NpgsqlConnection = New NpgsqlConnection( _
            System.Configuration.ConfigurationManager.ConnectionStrings("DSN").ConnectionString)
            
            conn.Open()
            command = New NpgsqlCommand("SELECT fnget_film_search_results(:search_criteria, :maxrecs, :offset)", conn)
            command.Parameters.Add(New NpgsqlParameter("search_criteria", System.Data.DbType.String, 300)).Value = aquery
            command.Parameters.Add(New NpgsqlParameter("maxrecs", System.Data.DbType.Int16)).Value = maxrecs
            command.Parameters.Add(New NpgsqlParameter("offset", System.Data.DbType.Int16)).Value = offset

            Try
                result = command.ExecuteScalar()
            Catch ex As Exception
                Return "<error>ERROR " & ex.ToString() & "</error>"
            End Try
        End Using
        Return result
    End Function
End Class

CSharp REST Service

Our CSharp equivalent looks like this

<%@ WebHandler Language="C#" Class="PagilaSearch_CS" %>
using System;
using System.Web;
using Npgsql;

public class PagilaSearch_CS : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {
        int maxrecs = 30, offset = 1;
        if (context.Request["query"] != null){
            context.Response.ContentType = "text/xml";
            if (context.Request["maxrecs"] != null) {
                try {
                    maxrecs = Convert.ToInt16(context.Request["maxrecs"].ToString());
                }
                catch {//ignore the exception
                }
            }
         
            if(context.Request["offset"] != null) {
                try {
                    offset = Convert.ToInt16(context.Request["offset"].ToString());
                }
                catch { //ignore the exception
                }
            }
            context.Response.Write(GetResults(context.Request["query"], maxrecs, offset));
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }
    
    public String GetResults(String aquery, int maxrecs, int offset){
        String result = "";
        NpgsqlCommand command; 
        using (NpgsqlConnection conn = new NpgsqlConnection(
                System.Configuration.ConfigurationManager.ConnectionStrings["DSN"].ConnectionString))
        {
            conn.Open();

            command = new NpgsqlCommand("SELECT fnget_film_search_results(:search_criteria, :maxrecs, :offset)", conn);
            command.Parameters.Add(new NpgsqlParameter("search_criteria", System.Data.DbType.String, 300)).Value = aquery;
            command.Parameters.Add(new NpgsqlParameter("maxrecs", System.Data.DbType.Int16)).Value = maxrecs;
            command.Parameters.Add(new NpgsqlParameter("offset", System.Data.DbType.Int16)).Value = offset;

            try
            {
                result = (String)command.ExecuteScalar();
            }
            catch(Exception ex)
            {
                return "<error>ERROR " + ex.ToString() + "</error>";
            }
        }
        return result;
    }

}

Running in Mono

To run on Mono -

• Install Mono by picking respective install from here - http://www.go-mono.com/mono-downloads/download.html. NOTE: There is a newer version than what I was using, but should work fine with the newer version as well.
• Create a folder called pagila in the lib/xsp/2.0 folder
• throw the files in the folder
• launch the web server

Testing our REST Service

Now to test our service - we shall try searching for films not about dentists and not about dogs but involving mad nigeria

Our search phrase is not dentist not dog mad nigeria and our browser call is like http://localhost:8080/2.0/pagila/PagilaSearch_VB.ashx?query=not%20dentist%20mad%20not%20dog%20nigeria&maxrecs=20&offset=1
http://localhost:8080/2.0/pagila/PagilaSearch_CS.ashx?query=not%20dentist%20mad%20not%20dog%20nigeria&maxrecs=20&offset=1

The result of our REST query looks like this

<results>
 <resultsummary>
    <count>4</count> 
</resultsummary>
<table xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance" xmlns="vwfilms">
 <row>
<fid>883</fid> 
<title>TEQUILA PAST</title> 
<description>A ActionPacked Panorama of a Mad Scientist 
And a Robot who must Challenge a Student in Nigeria</description> 
<category>Children</category> 
<price>4.99</price> 
<length>53</length> 
<rating>PG</rating> 
</row>
 <row>
<fid>614</fid> 
<title>NAME DETECTIVE</title> 
<description>A Touching Saga of a Sumo Wrestler 
And a Cat who must Pursue a Mad Scientist in Nigeria</description> 
<category>Games</category> 
<price>4.99</price> 
<length>178</length> 
<rating>PG13</rating> 
</row>
 <row>
<fid>34</fid> 
<title>ARABIA DOGMA</title> 
<description>A Touching Epistle of a Madman 
And a Mad Cow who must Defeat a Student in Nigeria</description> 
<category>Horror</category> 
<price>0.99</price> 
<length>62</length> 
<rating>NC17</rating> 
</row>
</table>
</results>