Friday, October 06, 2006

ASP.Net: A More Complex Web Service to Retrieve Employee and Transcript Information

One of the current ideas I am throwing around to people who will listen at the office is the idea of implementing commonly used report items as web services rather than as database queries. So I set about building a few web services to retrieve Employee and Transcript information.

Basically, I want two functions, one to retrieve information on an Employee with Transcript information, and one to retrieve a list of transcript information only. I played around with several methods of doing this in .Net, since transcript information is typically a list of classes that have been completed. The problem with passing this information through SOAP is that the classes need to be serializable, and by default Collections and Array are not. I tried creating a basic Dataset and populating the information manually, but this turned out to be a little cumbersome to decipher on the client end, and I felt this was not going to work with my next task, which is to build reports off of these web services. If I get the OK from the original author of these connectors, I will write more about them later.

In this project, I have a few identifiable classes. Of course, there are Employees, which will consist of a ID, a Name, and a Transcript. The Transcript is a list of classes that have been completed. Transcripts are built up by TranscriptEntries (which I should have called classes to be consistent). TranscriptEntries contain information such as Course Name, Course Code, and Completion Date.

The tricky part is the Transcript. Since it needs to be a collection of some sort, I had to inherit from the CollectionBase class. The reason I consider this strange is that if I implement a collection, I get all sorts of errors about the lack of an ISerializable interface, yet if I inherit from a CollectionBase, all I need to do is expose a default method (which in my case was the Index function), and an Add function. That’s it. The rest takes care of itself when I publish this as a web service. The following project is developed in Visual Web Developer Express 2005.

So below are the 3 data structure classes that were defined in the project, in order or TranscriptEntry, Transcript, and Employee.

File: transcript.vb
Imports Microsoft.VisualBasic

Public Class Transcript
    Inherits CollectionBase

    Public Sub Add(ByVal value As TranscriptEntry)
        Me.InnerList.Add(value)
    End Sub

    Default Public ReadOnly Property Item(ByVal Index As Integer) As TranscriptEntry
        Get
            If InnerList.Count() = 0 Then
                Exit Property
            End If
            'maintain compatibility (Underlying ArrayList 0 based)
            Item = InnerList.Item(Index)
        End Get
    End Property

End Class

Public Class TranscriptEntry
    Dim ccourseCode As String
    Dim ccourseName As String
    Dim cdateComplete As Date

    Property CourseCode() As String
        Get
            Return ccourseCode
        End Get
        Set(ByVal value As String)
            ccourseCode = value
        End Set
    End Property

    Property CourseName() As String
        Get
            Return ccourseName
        End Get
        Set(ByVal value As String)
            ccourseName = value
        End Set
    End Property

    Property CompletionDate() As Date
        Get
            Return cdateComplete
        End Get
        Set(ByVal value As Date)
            cdateComplete = value
        End Set
    End Property
End Class

File: Employee.VB
Imports Microsoft.VisualBasic

Public Class Employee
    Private cvalid As String
    Private cvalname As String
    Private ctrnTranscript As New Transcript

    Property ID() As String
        Get
            Return cvalid
        End Get
        Set(ByVal value As String)
            cvalid = value
        End Set
    End Property
    Property Name() As String
        Get
            Return cvalname
        End Get
        Set(ByVal value As String)
            cvalname = value
        End Set
    End Property
    Property Transcript() As Transcript
        Get
            Return ctrnTranscript
        End Get
        Set(ByVal value As Transcript)
            ctrnTranscript = value
        End Set
    End Property
End Class

Very basic. Now, I create a new WebService called GetEmployeeTranscript with two methods, getEmployeeAndTranscript, and one called getTranscript. Both take the Employee ID as a parameter, and return the appropriate classes. Below is the code for this service:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols

<WebService(Namespace:="http://localhost")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class GetEmployeeTranscript
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function getEmployeeAndTranscript(ByVal id As String) As Employee
        Dim e As New Employee
        Dim con As New System.Data.OleDb.OleDbConnection
        Dim com As New System.Data.OleDb.OleDbCommand
        Dim rs As System.Data.OleDb.OleDbDataReader
        Dim p As New System.Data.OleDb.OleDbParameter

        Dim t As TranscriptEntry

        con.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=user;Data Source=TSERVE"
        con.Open()

        com.CommandType = Data.CommandType.Text
        com.Connection = con
        com.CommandText = "select no_emp, nm_emp_last || ', ' || nm_emp_first name from employees where no_emp = ?"

        p.ParameterName = "GEID"
        p.Value = id
        com.Parameters.Add(p)

        Try
            rs = com.ExecuteReader

            While (rs.Read)
                e.ID = rs("no_emp")
                e.Name = rs("name")
            End While

            rs.Close()
            rs = Nothing
        Catch ex As Exception
            'insert some exception handler here
        End Try

        com.CommandText = "select course_histories.cd_crs, nm_crs, course_histories.dt_ch_compdt, course_histories.cd_ch_status from course_histories, courses where course_histories.cd_crs = courses.cd_crs and no_emp = ?"

        Try
            rs = com.ExecuteReader

            While (rs.Read)
                t = New TranscriptEntry

                Try
                    t.CourseCode = rs("cd_crs")
                Catch ex11 As Exception
                    t.CourseCode = ""
                End Try
                Try
                    t.CourseName = rs("nm_crs")
                Catch ex As Exception
                    t.CourseName = ""
                End Try
                Try
                    t.CompletionDate = CDate(rs("dt_ch_compdt"))
                Catch ex1 As Exception
                    t.CompletionDate = CDate("01-JAN-1900")
                End Try

                e.Transcript.Add(t)
                t = Nothing
            End While

            rs.Close()
        Catch ex As Exception
            'Insert some exception handler here
        End Try

        p = Nothing
        com.Parameters.Clear()
        con.Close()
        com = Nothing
        con = Nothing
        rs = Nothing

        Return e
        e = Nothing
    End Function

    <WebMethod()> _
    Public Function getTranscript(ByVal id As String) As Transcript
        Dim con As New System.Data.OleDb.OleDbConnection
        Dim com As New System.Data.OleDb.OleDbCommand
        Dim rs As System.Data.OleDb.OleDbDataReader
        Dim p As New System.Data.OleDb.OleDbParameter

        Dim transcript As New Transcript
        Dim t As TranscriptEntry

        con.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=user;Data Source=TSERVE"
        con.Open()

        com.CommandType = Data.CommandType.Text
        com.Connection = con
        com.CommandText = "select course_histories.cd_crs, nm_crs, course_histories.dt_ch_compdt, course_histories.cd_ch_status from course_histories, courses where course_histories.cd_crs = courses.cd_crs and no_emp = ?"

        p.ParameterName = "GEID"
        p.Value = id
        com.Parameters.Add(p)

        Try
            rs = com.ExecuteReader

            While (rs.Read)
                t = New TranscriptEntry

                Try
                    t.CourseCode = rs("cd_crs")
                Catch ex11 As Exception
                    t.CourseCode = ""
                End Try
                Try
                    t.CourseName = rs("nm_crs")
                Catch ex As Exception
                    t.CourseName = ""
                End Try
                Try
                    t.CompletionDate = CDate(rs("dt_ch_compdt"))
                Catch ex1 As Exception
                    t.CompletionDate = CDate("01-JAN-1900")
                End Try

                transcript.Add(t)
                t = Nothing
            End While

            rs.Close()
        Catch ex As Exception
            'Insert some exception handler here
        End Try

        p = Nothing
        com.Parameters.Clear()
        con.Close()
        com = Nothing
        con = Nothing
        rs = Nothing

        Return transcript
        transcript = Nothing
    End Function
End Class

One thing to note, I had a hell of a time with the Oracle driver when I published this. I kept getting errors like “The 'OraOLEDB.Oracle.1' provider is not registered on the local machine” and driver not found. Of course, I knew better since I had the Oracle 9I client installed. Turns out it is a permission issue on the Oracle folder. I just set the permission to full control on Everyone and let it trickle down to the child folders. This is not adviseable on a production machine, however. I am only doing this since it is a development machine.

Once published, I go to the publishing location http://localhost/GetEmployeeTranscript/Service.asmx to get an idea if the publish was successful. One of the nice features about .Net is you can preview your web services before you consume them. One little tip, if you need the WSDL version of the description, you can append ?WSDL to the end of the asmx url. So for me I would use http://localhost/GetEmployeeTranscript/Service.asmx?WSDL.

Now, I need to build a client. I start a new project (it really doesn’t matter what language it is in, but I keep in Web Developer .Net since I do not want to change into another IDE). I add a Web Reference to the above URLs, and import my web reference into the project. The client itself is simply 3 text boxes, one to get an Employee ID, and two to display the output. I also add a listbox to display the transcript of the Employee. I add the code to retrieve the results to a Command button. Below is the code for the client itself:

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim temp_id As String
        Dim tempEmployee As localhost.Employee
        Dim transcriptService As New localhost.GetEmployeeTranscript
        dim transcriptEntryTemp as localhost.TranscriptEntry  
                        
        temp_id = txtGEID.Text
        tempEmployee = transcriptService.getEmployeeTranscript(temp_id)
        
        txtName.Text = tempEmployee.Name
        txtGEID2.Text = tempEmployee.ID
          
                
        for each transcriptEntrytemp in tempEmployee.Transcript
            listbox1.Items.Add(transcriptEntrytemp.CourseCode & ": " & transcriptEntrytemp.CourseName & " - " & transcriptEntrytemp.CompletionDate)
        next
        
        transcriptService = nothing
        tempEmployee = nothing      
    End Sub


</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Enter a GEID"></asp:Label>
        <asp:TextBox ID="txtGEID" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="cmdGetTranscript" runat="server" OnClick="Button1_Click" Text="Button" /><br />
        <br />
        <asp:Label ID="Label2" runat="server" Text="Results"></asp:Label>
        <br />
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        <br />
        <asp:TextBox ID="txtGEID2" runat="server"></asp:TextBox><br />
        <asp:ListBox ID="ListBox1" runat="server" Height="265px" Width="692px"></asp:ListBox>
         
    </div>
    </form>
</body>
</html>

Nothing to it really. Now that it is published, I need to find out how to build reports on this service.  I do have a ODA component for Actuate that will allow this, however I am awaiting permission from the author to write about it and showcase it. Otherwise, I can always do it the manual way and write either an ASPX or Coldfusion page to do so.

No comments: