Periodical Asynchronous Requests in VFP Forms
Author: Josip
Zohil, Koper, Slovenija, mailto:Josip.Zohil1@guest.arnes.si
Creating a client (VFP) application, which would be continuously calling the server for data changes on the server in asynchronous (no blocking) mode, would be difficult or perhaps even impossible to do in VFP. In this article I present a COM object in C# which downloads the data from the network computer in asynchronous mode and periodically refresh the controls on the VFP form.
The VFP blocking
When working with local buffered data, VFP can refresh it. But also in this case the first network download of data (the fill of buffered cursors) can exceed 30 seconds. While the download is active, the form (and also the whole VFP client) are blocked. To get around this blocking state we fill the form's control with asynchronously downloaded data. When we start an asynchronous call for data, the call returns immediately. The form (and also the application) is free to perform some other task. The call is executing in parallel with this task, for example in parallel:
- with the start of the application,
- with the initialization of the form,
- with the click on the button….
In VFP we can only simulate an asynchronous download of data, for example, on user action or on timer click we download a small chunk of data and add it to the existing local data. So the application is blocking only for a small, imperceptible time period.
In .Net Framework we can built COM classes with asynchronous capabilities able to communicate with a VFP application (1), (2), (3). The COM events have to be consumed in VFP, so an OLE public object is added to the VFP application to communicate with the Net COM object. Usually we test the COM classes with the test application (client) built in .Net Framework.
The asynchronous data download from the database
Let us built the COM object. Its goal is to connect to the database on a local or network computer, and call a stored procedure in it. The call is made asynchronously. When the results are returned, the COM objects pass the results to the calling client as a string containing a dataset and its schema. Additionally the server (COM) also fire the event OnIsCompleted, to inform the calling client the results are ready for use.
In the references of this article we can find a detailed description on how to create a COM object in C#, so we only present the final code in C# (Listing 1). Don’t forget the ProgId and the public interface contained in the class!
using
System;
using
System.Collections.Generic;
using
System.Text;
using
System.Data;
using
System.Xml ;
using
System.Data.SqlClient;
using
System.Configuration;
using
System.Runtime.InteropServices;
using
System.Runtime.InteropServices.ComTypes;
using
System.Runtime.InteropServices.Expando;
using
System.Data.OleDb;
using
System.Windows.Forms;
using
System.IO;
using
System.Diagnostics;
namespace
InteroperCL
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComSourceInterfaces(typeof(MyEvents))]
[ProgId("InteroperCL.Class1")]
public class Class1
{
public delegate void FormEventHandler(object
source,string respStr, bool
isComplete);
public event FormEventHandler
IsCompleted;
private
delegate OleDbCommand delReader(string methodName,string
name);
private delReader
drinvoke;
private
object _objName;//
the object reference to be used on the client (subscriber)
string
_name = "" ;
string
_methodName = "";
static private string
_error="";
private
DataSet _dataT;
string
_connect = "";
string
_respStr=""; //Response string (XML)
DataSet
ds = null;
DataTable
dt = null;
int
_timer1_Interval = 10000;
System.Timers.Timer
timer1;
public Class1()
{
ds = new
DataSet();
dt = new
DataTable();
ds.Tables.Add(dt);
timer1 = new
System.Timers.Timer();
timer1.Elapsed += new System.Timers.ElapsedEventHandler(timer1_Elapsed);
timer1.AutoReset = true;
this.timer1.Interval
= Convert.ToInt32(_timer1_Interval.ToString());
this.timer1.Enabled
= false;
OnIsCompleted(this,"",
false); //notify the
subscriber (client)
}
public void OnIsCompleted(object
source,string respStr, bool
isComp)
{
if
(IsCompleted != null)
IsCompleted(source,respStr, isComp);
}
public void CallDb(string
methodName,string name)
{
this._name
= name;
this._methodName
= methodName;
OnIsCompleted(this,"",
false);
this.timer1.Interval
= Convert.ToInt32(_timer1_Interval.ToString());
if
(this.timer1.Interval < 10) this.timer1.Interval = 10000;
this.timer1.Enabled
= true;
drinvoke = new
delReader(this.DataBaseCall);
drinvoke.BeginInvoke(methodName,name, this.EndDatabaseCall,
null);
//Control is
returned to the client program that Call this COM.
}
private OleDbCommand DataBaseCall(string
methodName,string name)
{
OleDbConnection
connection = new OleDbConnection(this.Connect );
connection.Open();
OleDbCommand command = new
OleDbCommand();
command.Connection = connection;
command.CommandText = methodName+"('"+name+"')";
command.CommandType = CommandType.StoredProcedure;
return
command;
}
private void
EndDatabaseCall(IAsyncResult result)
{
OleDbCommand
command = drinvoke.EndInvoke(result);
try
{
OleDbDataReader
reader = command.ExecuteReader();
ds.Clear();
dt.Clear();
dt.Load(reader, LoadOption.OverwriteChanges);
_dataT = ds;
//we add the schema
to the dataset
MemoryStream
st = new MemoryStream();
//convert the
dataset to bytes
ds.WriteXml(st, XmlWriteMode.WriteSchema);
st.Position = 0;
Byte[]
buf = new byte[st.Length];
st.Read(buf, 0, buf.Length);
st.Close();
st.Dispose();
_respStr = Encoding.UTF8.GetString(buf,
0, buf.Length);
// MessageBox.Show("DOM document XML
with schema:"+_respStr);
}
catch (Exception ex)
{
_error= "Error: " + ex.Message.ToString();
}
finally
{
#region Cleanup
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
#endregion
System.Threading.Thread.Sleep(50);
//notify
the subscriber (client)
OnIsCompleted(this,_respStr, true);
//update the controls, write the log and other things
designed by the caller (subscriber)
}
}
public void timer1_Stop()
{
this.timer1.Enabled
= false;
}
private
void timer1_Elapsed(object
sender,
System.Timers.ElapsedEventArgs e)
{
this.CallDb(this._methodName,this._name) ; //request the
data on elapsed time interval
System.Threading.Thread.Sleep(300);
this.timer1.Enabled
= true;
}
public
int Timer1_Interval
{
get
{ return this._timer1_Interval;}
set
{ this._timer1_Interval = value;}
}
public object ObjName
{
get
{ return this._objName;
}
set
{ this._objName = value;}
}
public string RespStr
{
get
{ return this._respStr;
}
set
{ this._respStr = value;}
}
public DataSet DataT
{
get
{ return this._dataT;
}
set
{ this._dataT = value;}
}
public string Name
{
get
{ return this._name;
}
set
{ this._name = value;}
}
public string MethodName
{
get
{ return this._methodName;}
set
{ this._methodName = value;}
}
public string Error
{
get
{ return _error;}
set
{ _error = value;}
}
public string Connect
{
get
{ return _connect; }
set
{ _connect = value;}
}
}
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface MyEvents
{
//the comm interface for the eventhandler IsCompleted
//On the client (VFP) side we use the command EVENTHANDLER and the
//pubblic OLE class MyEvents to comunicate with the COM generated from
this class
[DispId(1)]
void
IsCompleted(object source,string respStr, bool
icComplete);
}
}
Listing 1. The C# class (Class1) for the periodical asynchronous call to the database (COM with ProgID and ComInterface)
The COM object has similar function as a VFPOLEDB driver. The client applications call the method CallDb in the Class1 (its instance is the COM object) and pass to the COM the name of the database store procedure to call and its parameter. The classes expose also the public field _connect (the name of the connection to the database from which we request the data) and _timer1_Interval (the time interval on which is fired the call to the database).
The command:
OnIsCompleted(this,««,
false)
fire the event OnIsCompleted and using the delegate inform the client the call to the database is stared (the third parameter has the value false-start the call).
drinvoke = new
delReader(this.DataBaseCall)
The line above creates an instance of the delegate delReader. The object drinvoke has to call the method DataBaseCall. I start the asynchronous call to the database invoking the delegate:
drinvoke.BeginInvoke(methodName,name,
this.EndDatabaseCall, null).
The results of this invocation are passed to the method EndDatabaseCall.
The method DataBaseCall opens the connection, constructs the OleDbCommand and returns it.
The EndDatabaseCall method is responsible to catch the download from the database. Using the object result (of type IasyncResult) we access the command object:
OleDbCommand command
= drinvoke.EndInvoke(result)
and execute the datareader call to the database:
OleDbDataReader
reader = command.ExecuteReader().
The reader object fill a datatable dt using the Load method:
dt.Load(reader, LoadOption.OverwriteChanges).
Using memorystream I add a schema to the dataset ds, and generate a string (respStr) containing a dataset and its schema. The command:
OnIsCompleted(this,_respStr,
true)
fire the event IsCompleted (based on the delegate FormEventHandle) and send to the subscribers (clients) three parameters:
- the reference to the object that start the asynchronous operation,
- the string containing the dataset,
- the information the request is completed(true).
I have declared two delegates. With the first I collect the data from the client and control the process of retrieving data from the database. It has two parameters and returns an OleDbComand object:
private delegate
OleDbCommand delReader(string
methodName,string name).
With the second I have passed (sent) the data to the client (subscriber). It has three parameters the client can receive:
public delegate void
FormEventHandler(object source,string respStr, bool isComplete).
I initialize a timer in the constructor of the Class1 and the method CallDB enable it:
this.timer1.Enabled = true.
At elapsed time the timer1_Elapsed event is fired:
timer1.Elapsed += new
System.Timers.ElapsedEventHandler(timer1_Elapsed).
This event makes a new call to the database, enable the timer and so on.
The windows application – testing the COM
Normally we test the COM in the environment it was created. I have created a windows application in Visual Studio 2005, with a windows form. The application has a reference to the COM and in the form class there is a keyword using InteroperCL.
On the windows form I have put a button, the grid control, and two text boxes:
- txtRefresh has the vale of the refresh time interval, for example 20000 (20 seconds),
- txtName has the value of the least document number we retrieve from the data base.
The query in the stored procedure is something like this: select docNo,docType,wareID from documents where docNo>=txtName. It returns the created cursor as the RESULTSET.
The project Windows Test and the class InteroperCL.Class1 are in the download.
The VFP client
The OLE public class
The VFP client is a »normal« VFP client enriched with an OLE public class, in our case a VFP program with the name MyEvents.prg (Listing 2). It is able to access the data (results of COM events) generated by the COM server and pass the data to VFP objects, in our case the VFP form. Remember that in VFP we access the COM in a slightly different manner as we did in .Net!
Using the keyword IMPLEMENTS I have told the class to catch the events delivered by the COM object (the class InteroperCL.Class1) with its interface MyEvents (see Listing 1). The procedure MyEvents_IsCompleted has three parameters sent by the command OnIsCompleted in the COM, see Listing 1. The procedures parameter isComplete inform the VFP client the response data from the server is collected. The respStr pass to the client the arrived string containing a dataset and its XML schema:
source.ObjName.respStr
=respStr.
The reference source.ObjName is in our case the VFP form with two added properties respStr and iscompleted, as will be explained later.
*myevents.prg InteroperCL.Class1
is the COM server (publisher)
DEFINE CLASS
IMPLEMENTS MyEvents
IN "InteroperCL.Class1"
PROCEDURE MyEvents_IsCompleted(
source as object,respStr as string, isComplete as Boolean)
AS VOID
*Listen for
messages from the publisher event OnIsCompleted, which is based on the delegate
FormEventHandler
IF isComplete
*
source.ObjName.iscompleted=.t.
* source.ObjName
is the VFP form object in our case
source.ObjName.respStr
=respStr
*the form
respStr_assign method will udate the grid
ELSE
source.ObjName.iscompleted=.f.
endif
*this is a new datasession, you
mustn't create the cursor here!!!
ENDPROC
ENDDEFINE
Listing 2. The client transmitter of the messages generated by the COM server
The VFP form
Figure 1. The asynchronous VFP form
In this section I present some basic elements of the VFP form in Figure 1. The click event of the button Asynchronous start is in Listing 3.
Local loEx as
Exception
Local loIH as "InteroperCL.Class1"
LOCAL loEvents
as "MyEvents"
LOCAL name as
integer
LOCAL connect as
string
LOCAL lnt as
XMLTable
connect=thisform.connection.value
*"Provider=VFPOLEDB.1;Data
Source=\\xpnovi\d\moj\ateks2006\async.dbc;Password='';Collating
Sequence=MACHINE;"
name=thisform.text1.value
lc1=SYS(2015)
try
IF VARTYPE( thisform.loIH)!="O"
thisform.loIH=
CREATEOBJECT("InteroperCL.Class1")
endif
IF VARTYPE( thisform.loEvents)!="O"
thisform.loEvents = NEWOBJECT("MyEvents","MyEvents.prg")
endif
loIH=thisform.loIH && The form has a property loIH
*loIH.IsCompleted=
.F.
loIH.Connect
=connect
loIH.Timer1_Interval=thisform.txttimerInterval.Value
loIH.ObjName=thisform
&& we pass the object form to the COM object for later
reference
loEvents=thisform.loEvents && The form has a property loWvents
*connect the
interface (OLE public class) to the created COM object
*The COM object
interface will pass the events to the object MyEvents, which pass the data to
this form.
EVENTHANDLER(loIH,loEvents)
loIH.CallDb("test1",name) && start the database call
CATCH TO oErr
oErr.UserValue
= "CATCH message: Error."
=Messagebox('Error. The
error message is on the screen).')
endtry
If Vartype(oErr)='O'
Activate Screen
Clear
?[
Error: ] + Str(oErr.ErrorNo)+Str(oErr.Lineno) +
oErr.Message
Endif
Listing 3. The Click method that start the periodical asynchronous call
In the Click method I have created the object loevents based on the VFP class MyEvents and loIH is an instance of the COM. The command:
EVENTHANDLER(loIH,loEvents)
bind the client events (loevents) with the server (loIH). The command:
loIH.CallDb("test1",name)
start the CallDB method on the COM object and implicitly is started the call to the database.
When the download is done and the COM object get the results, the event OnIsCompleted is fired, the results are transmitted to the object loevents and the procedure MyEvents_IsCompleted is executed. If the received variable iscomplete (Listing 2) has a value of .T. (thru), the procedure updates the forms property respStr:
source.ObjName.respStr =respStr,
where source.ObjName is the name of the form.
LPARAMETERS vNewVal
*To do: Modify
this routine for the Assign method
los= m.vNewVal
IF LEN(TRIM(los))=0
MESSAGEBOX("Start
first the Asynchronous proces or no data is aviable."+ los)
RETURN
ENDIF
LOCAL loXML
As MSXML2.DOMDocument.4.0
loXML=Createobject("MSXML2.DOMDocument.4.0")
loXML.loadXML(los)
LOCAL oXMLAdapter
as XMLAdapter
oXMLAdapter = CreateOBJECT('XMLAdapter')
oXMLadapter.IsDiffgram= .F.
oXMLadapter.Attach
( loXML)
*current active
row and column
actRow=thisform.grid1.ActiveRow
actCol=thisform.grid1.ActiveColumn
IF USED(oXMLadapter.Tables.Item(1).Alias)
SELECT (oXMLadapter.Tables.Item(1).Alias)
USE
ENDIF
oXMLadapter.Tables.Item(1).ToCursor()
*just to inform
the data was refreshed ew change the back color
IF thisform.grid1.BackColor=RGB(255,255,255)
thisform.grid1.BackColor=RGB(255,227,215)
ELSE
thisform.grid1.BackColor=RGB(255,255,255)
ENDIF
SELECT (oXMLadapter.Tables.Item(1).Alias)
thisform.grid1.ColumnCount=-1
thisform.grid1.RecordSource=oXMLadapter.Tables.Item(1).Alias
thisform.BindControls
=.t.
IF actRow>0
AND actCol>0
thisform.grid1.ActivateCell(actRow,actCol)
ENDIF
thisform.iscompleted
=.t. && to be used with the assign method
Listing 4. The respStr_assign method
Next the method respStr_assign (Listing 4) is executed. Certain actions should be performed at the form level as the result of the received string including the dataset generated by the COM object as the result of the call to the database. With the XMLAdapter object I built a cursor from the response string, bind the grid datasource to this cursor and activate the »old« grid row and cell.
Conclusion
On the
first load of a VFP form we can produce large and suffocating network traffic having
blocked entire VFP client application. With the .Net Framework asynchronous and
COM capabilities we can unable VFP to have continuously (in small time
interval) refreshed data on the VFP form. You can also enlarge the
functionality of the presented COM object by adding it the HTTPRequest and
HTTPResponse class and retrieve data from the database located on the Internet.
In another approach you can elaborate the COM and create an asynchronous driver
like the synchronous VFPOLEDB.
Reference:
1) Asynchronous
Use of VFP, M.A. Mazzarino
2) Handling
.NET Events in Visual FoxPro via COM Interop, R.Strahl
3) Compare Events and
Delegates in VFP and .NET, C.
Lassala and M. Egger
4) Multi-threaded VFP
applications, R. Rusanu
5) How to Execute a Stored Procedure in a VFP Database with the VFP ...
7) .NET Delegates:
Making Asynchronous Method Calls in the .NET Environment, R. Grimes
Downloads note. You
have to adjust the path in the connection string in the downloaded projects: InteroperCL-C#.Net and async-VFP.