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.
Subscribe to:
Post Comments (Atom)
Posts
No comments:
Post a Comment