Alpha Five v11 integrates with a .NET Web Service

Our enterprise and consultant customers often ask about web service integration. We recently received the video below from Charles Andersson in Finland showing how he integrated a .NET web service into an Alpha Five v11 application that he built.

[youtube http://www.youtube.com/watch?v=CYWfsr0m0eo?feature=player_detailpage&w=640&h=360]

After viewing the video we decided to ask Charles for more info and background on the app so that we could share it  with you.

Here is the reply from Charles:

—————————————————————————————————————————————–

My name is Charles Andersson and I work as a System Architect in my own consulting company Systemteknik Ab (www.systemteknik.fi). For the last couple of years we have been using Alpha Five to develop web applications on several projects.

In this blogpost I will share my experiences of integrating Alpha Five with the Microsoft .NET Framework and I will also take it one step further and show a little bit about integrating with an external web service and a stored procedure call to SQL Server.

Background

One of our clients for whom we built a livestock procurement and settlement line of business application, had a requirement to let farmers perform self-registration and checking of livestock against the Finnish FDA (http://www.evira.fi/portal/en/evira/)  database prior to delivery.

The Solution

What we did was to build a procurement portal for livestock in Alpha Five and integrated it directly to the main systems SQL Server database. We furthermore decided to use the possibility to integrate it with .NET as we are familiar with the platform and its WCF framework for communicating with web services.

Evira (Finnish FDA) has developed a set of web services that we could use for the checking of livestock status. The requirement from Evira was that we need to have certificates to authenticate and encrypt the communication with them. For this purpose we used openSSL (http://www.openssl.org/) to create the certificate  request files and received certificate files back from Evira.

A video showing an overview of the solution can be seen on this Youtube link: http://www.youtube.com/watch?v=CYWfsr0m0eo.

The Architecture

The overall architecture of the system can be seen on this diagram:

Kivio architecture diagram

The farmer connects with the Alpha Five portal application using any web browser. The farmer then registers the livestock that he intend to send for processing and then commands the application to perform the check against Evira’s database. After the livestock has beendelivered and processed a settlement is created and a subsequent payment is made.

[Read more about .NET and Alpha Five. Download Alpha Five.]

The Implementation

.NET integration in Alpha is quite straightforward. You compile a .NET assembly and copy the binaries from the bin\release catalog to a suitable place on the A5 Application server and development machine. We recommend using Visual Studio 2010 and .NET framework 4.0.

We implemented it on an Action Button as an Ajax Callback: 

var rowNum = {grid.object}._selectedRow;
{grid.Object}.ajaxCallback('G',rowNum,'CheckContractEarId','','_getData=true');

This is the Xbasic function that calls the function in the .NET class:

FUNCTION CheckContractEarId AS C (e AS P )
 dim Sv as DotNet::Services
 dim assy as DotNet::AssemblyReference
 assy.filename = "C:\netdll\EviraControl\EviraControl.dll"
 Sv.RegisterClass("EviraControl", "EviraLib", "EviraControl.EviraLib", assy)
 dim EarChecker as EviraControl::EviraLib
 dim retmsg as C
 dim contract as N
 contract = e._currentRowDataNew.contract_pkey
 retmsg = EarChecker.CheckAllInContract(contract)
 CheckContractEarId = "A5.msgBox.hide();alert('" + retmsg + "');"
END FUNCTION

And this is the solution explorer for the .NET project:

Kivio .NET project

This is the .NET function in the EviraLib class:

Public Function CheckAllInContract(pkey As Integer) As String

 Try

 ' Clear log

 If Not ClearDbLog() Then
   Return "Kunde inte utf??kontroll (Felkod=1), v.g. kontakta Dahlmans."
 End If

 dblog("-", "P01", "Evirakontroll f??vtal", pkey.ToString)

 ' Retrieve livestock animals for the contract

 Notifications = SqlQuery("select pkey, idnum from procure_notify where contract_pkey=@p0 order by pkey", pkey)
 dblog("-", "P02", "Antal anm䬮ingar p堡vtalet", Notifications.Rows.Count.ToString)
 If Notifications.NotFound Then
   Return "Inget att kontrollera."
 End If

 dblog("-", "P03", "ֲonkontroll", "Start")

 ' Check livestock ID numbers by comparing checksum

 If Not CheckId() Then
   dblog("-", "P03", "ֲonkontroll", "Avslutad med fel")
 Return "ֲonkontroll avslutad med fel, v.g. se i loggen"
 End If
 dblog("-", "P03", "ֲonkontroll", "Avslutad OK")

 ' Perform the livestock control

 Dim retmsg As String = Notifycontrol()

 ' Return the result back to the caller (Alpha Xbasic function)

 Return retmsg

 Catch ex As Exception
 'dblog("-", "EX", "Exception.Message", ex.Message)
 Return "Kontrollen avbr??med fel, v.g. kontakta Dahlmans." & Chr(10) & _
 "Felmeddelande: " & ex.Message
 End Try

 End Function

We took the approach of sending the primary key of the contract for which the livestock is registered, and the function then returns a message string which is then displayed as a JavaScript alert.

Aspects on Web Services and app.config

We recommend that you make the assembly “self-contained” and not depend on app.config for these kind of integration projects. In this case we received from Evira a .wsdl file and accompanying .xsd files for the web services so we only had to add a Service Reference, name it (RegisterQuery), point to the .wsdl file, and let the Visual Studio code generator do the rest.

Kivio Service Reference

In code we can then programmatically do all that we need with the reference and call the service.

Public Function Notifycontrol() As String

 ' RegisterQuery is the name given as Service Reference when
 ' elainrekisteri_rekisterikysely_wsdl_2010_07_16.wsdl
 ' was imported

 ' Notifications is a datatable holding the registered livestock for the contract being inquired
 ' I have this handy function SqlQuery where jou can throw a SQL statement, parameters and
 ' receive back a data table

 Notifications = SqlQuery( _
"select pkey, eu_idnum from procure_notify where contract_pkey=@p0 order by pkey", _
ContractPkey)

 ' In the following sections we declare and initialeze stufff that normally goes into app.config

 ' Set up tracing

 Dim logFile As String = "evira_trace.log"
 Dim listener As New TextWriterTraceListener(logFile)
 Trace.Listeners.Add(listener)
 Trace.AutoFlush = True
 Dim mySource As New TraceSource("TraceSourceApp")

 ' Bindings

 Dim binding As New BasicHttpBinding()

 ' BasicHttpBinding

 binding.Name = "ELRASOAPBinding"
 binding.CloseTimeout = TimeSpan.FromMinutes(1)
 binding.Security.Mode = BasicHttpSecurityMode.Transport
 binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Certificate
 binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None
 binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.Certificate

 ' Endpoint

 Dim endpoint As New EndpointAddress("https://<not_revealed>.fi/elainrekisteri/ELRAService")

 ' Declare Web Service reference

 Dim regQuery As New RegisterQuery.ELRAPortTypeClient(binding, endpoint)

 ' EndpointBehaviors

 regQuery.ClientCredentials.ClientCertificate.SetCertificate(StoreLocation.CurrentUser, StoreName.My, X509FindType.FindBySubjectName, "Company")
 regQuery.ClientCredentials.ServiceCertificate.Authentication.CertificateValidationMode = _
 ServiceModel.Security.X509CertificateValidationMode.PeerOrChainTrust

 ' Declare Evira registry query

 Dim regRequest As New RegisterQuery.TeeRekisterikyselyPyyntoTyyppi()

 ' t will hold an array of livestock id numbers to inquire

 Dim t(Notifications.Rows.Count - 1) As RegisterQuery.YksiloivaEUTunnusTietoTyyppi

 ' ElainEUTunnus = Livestock EU id

 Dim idnum As String = String.Empty

 ' regRequest will hold an array of livestock records to inquire

 ReDim regRequest.YksiloivaElaintunnusTieto(Notifications.Rows.Count - 1)

 Dim idx As Integer = 0

 ' Loop through livestock animals

 For Each notification As DataRow In Notifications.Rows

 idnum = notification.Item("EU_IDNUM")

 ' Last number is check sum and shall be omitted in the request

 idnum = idnum.Substring(0, 14)

 ' Add the id number to the array

 t(idx) = New RegisterQuery.YksiloivaEUTunnusTietoTyyppi()
 t(idx).ElainEUTunnus = idnum
 regRequest.YksiloivaElaintunnusTieto(idx) = t(idx)
 idx = idx + 1

 Next

 ' User information

 regRequest.ElainluokkaKoodi = "02"
 regRequest.UlkoinenKayttajaTunnus = "user"
 regRequest.Item = "0000"

 ' RekisterikyselyTulosTyyppi = Response structure from Evira
 ' teeRekisterikysely = Request structure

 ' Send the request and receive a response

 Dim response() As RegisterQuery.RekisterikyselyTulosTyyppi = _
 regQuery.teeRekisterikysely("COMPANY", regRequest)

 Dim errorCount As Integer = 0
 Dim retmsg As String = String.Empty
 Try

 ' Loop through responses for each livestock

 For Each result In response

 Try
 idnum = result.ElainPerustiedot.ElainEUTunnus & result.ElainPerustiedot.ElainEUTunnusTarkisteNumero

. . .

Deployment of .NET assembly.

After the VB project has been built in Visual Studio, the files in bin\release are copied to c:\netdll\EviraControl in this case. Remember to set Configuration management to Release in the Build menu in VS prior to building.

When you make changes and re-deploy assembly files, the Application Server must be stopped if the assembly has been called from the application as the Application server seems to keep the .dll file open.

A Work In Progress Message

As the call to the web service may take some time to complete, there is a need to notify the user with some form of Work In Progress message. The solution was to call the msgBox method in Xbasic before the Ajax callback (Thanks Rich Kam on Alpha message board).

A5.msgBox.show('Kontrollerar...','<div style=\'padding: 20px;width:80px;height:25px;\'><img src=\'CSS/A5System/Images/wait.gif\' /></div>','none',function() {});
var rowNum = {grid.object}._selectedRow;
{grid.Object}.ajaxCallback('G',rowNum,'CheckContractEarId','','_getData=true');
{grid.Object}.refresh();

And then in the CheckContractEarId function return the JavaScript code to close the messagebox.

FUNCTION CheckContractEarId AS C (e AS P )
 dim Sv as DotNet::Services
 dim assy as DotNet::AssemblyReference
 assy.filename = "C:\netdll\EviraControl\EviraControl.dll"
 Sv.RegisterClass("EviraControl", "EviraLib", "EviraControl.EviraLib", assy)
 dim EarChecker as EviraControl::EviraLib
 dim retmsg as C
 dim contract as N
 contract = e._currentRowDataNew.contract_pkey
 retmsg = EarChecker.CheckAllInContract(contract)
 CheckContractEarId = "A5.msgBox.hide();alert('" + retmsg + "');"
END FUNCTION

Copying records with a Stored Procedure

As the livestock can consist of many animals, there must be an easy way of creating the required number of registry records in the Grid component. Our solution was to call a Dialog, sending the primary key for the record to be copied as parameter. After the user has entered the number of copies to be made, a stored procedure is called and then the Grid is refreshed.

dim pkey as N
dim copycount as N 
pkey = e.DataSubmitted.pkey
copycount = e.DataSubmitted.copycount
dim args as sql::arguments
dim conn as sql::connection
args.set("pkey", pkey)
args.set("copycount", copycount)
dim errorstr as C
if conn.open("::Name::portalConnection") = .t. then
  conn.PortableSQLEnabled = .f.
if conn.Execute("exec dbo.CopyNotification :pkey, :copycount", args) = .t. then
  CopyNotification = "alert('Anmälan kopierad.');"
else
  errorstr = conn.CallResult.NativeText
  CopyNotification = "alert('Kunde inte kopiera.');"
end if
conn.Close()
else
  CopyNotification = "alert('Databasfel.');"
end if

When you call a stored procedure you should set PortableSQLEnabled to false.

You could of course copy the records on the client but doing it in a stored procedure speeds the process, gives greater control of the data, and saves a lot of network round trips.

CREATE PROCEDURE [dbo].[CopyNotification]
(
@pkey int
,@copycount int
)
AS

DECLARE @fid INT;
DECLARE @txt VARCHAR(400);

declare @contract_pkey pkey;
declare @transtype_pkey pkey;
declare @notify_qty qty;
declare @notify_weigth [weight];
declare @note note;
declare @prod_pkey pkey;
declare @notify_date datetime;
declare @company_pkey pkey;
declare @birth_date datetime;
declare @variety_pkey pkey;
declare @organic yesno;
declare @imported yesno;
declare @web_flag flag;
declare @web_ack flag;
declare @count int;

BEGIN TRY
--
set @fid = 10;
begin transaction;
--
-- -------------------------------
-- Retrieve source data
-- -------------------------------
--
select
@contract_pkey = CONTRACT_PKEY
,@transtype_pkey = TRANSTYPE_PKEY
,@notify_qty = NOTIFY_QTY
,@notify_weigth = NOTIFY_WEIGHT
,@note = NOTE
,@prod_pkey = PROD_PKEY
,@notify_date = NOTIFY_DATE
,@company_pkey = COMPANY_PKEY
,@birth_date = BIRTH_DATE
,@variety_pkey = VARIETY_PKEY
,@organic = ORGANIC
,@imported = IMPORTED
,@web_flag = WEB_FLAG
from procure_notify
where pkey = @pkey;
--
if @@rowcount = 0
begin
rollback transaction;
execute RaiseSqlError 'CopyNotification', @fid;
return 1;
end;
--
set @fid = 20;
--
-- --------------------------
-- Create @copycount new rows
-- --------------------------
--
set @count = 1;
while @count <= @copycount
begin
--
insert into procure_notify
(
CONTRACT_PKEY
,TRANSTYPE_PKEY
,NOTIFY_QTY
,NOTIFY_WEIGHT
,NOTE
,PROD_PKEY
,NOTIFY_DATE
,COMPANY_PKEY
,BIRTH_DATE
,VARIETY_PKEY
,ORGANIC
,IMPORTED
,WEB_FLAG
,WEB_ACK )
values
(
@contract_pkey
,@transtype_pkey
,@notify_qty
,@notify_weigth
,@note
,@prod_pkey
,@notify_date
,@company_pkey
,@birth_date
,@variety_pkey
,@organic
,@imported
,@web_flag
,0
);
--
set @count = @count +1;
--
end;
--
-- ----------------
-- End with success
-- ----------------
--
commit transaction;
RETURN 0;
--
end try
begin catch
--
-- ----------------
-- End with failure
-- ----------------
--
rollback transaction;
execute RaiseSqlError 'CopyNotification', @fid;
return error_Number();
--
end catch

I hope you find this informative as we have covered quite a few concepts that show just how versatile Alpha Five is in developing web line of business applications.

Credits

With me on this project I also had Anders Enges, Solution Architect, Developer and .NET expert

About Charles

Charles Andersson  has more than 25 years of experience in IT systems development primarily in developing enterprise business systems, from specification to advanced database solutions and end user applications. As a Systems Architect Charles works on behalf of his clients to describe and document systems, processes and businesses and propose how these can be developed and enhanced using IT. As a Business Developer Charles participates in the realization of ideas to business opportunities by networking with people, develop products and knowledge and help with financing and corporate governance. Charles has a Diploma in Leadership from Edupoli in Helsinki and is a certified .NET Architect from Sundblad & Sundblad in Uppsala. He is also active in IASA (International Association of Software Architects) where he is an auditor of the Swedish Chapter. He also has a Diploma on working in Corporation Boards from Stockholm Chamber of Commerce. He has international experience from working with projects in Sweden, Great Britain, Norway and the U.S.A. Charles and Anders can be contacted via Alpha Consulting Services (or directly on mailto:info@systemteknik.fi)