Improve VFP data retrieval with WCF

Total votes: 0
Views: 2,710
Comments: 0
Category: Windows Communication Foundation
Print: Print Article

Please login to rate or to leave a comment.

Published: 02 Apr 2008
By: Josip Zohil
Download Sample Code

This article explains how to set up a WCF client to download the data from the server in asynchronous (non blocking) mode, much faster than with VFP functions or oledb data adapters.

Introduction

Windows Communication Foundation (WCF) enables us to create distributed architectures and use Visual FoxPro (VFP) as a client/server system executing the operation on the server (backend). With the WCF client we can download the data from the server in asynchronous (no blocking) mode. The measurements in this article show us that this can be done much faster than downloading the data with VFP functions or oledb data adapters. With WCF, we can take advantage of parallel (asynchronous) operations and distribute the processing of data between the client and the server. The retrieved data can be presented in VFP or .NET (Windows) controls. In the sample code of this article you will find the code for four projects: WCF service, WCF client, COM object and VFP client. We are going to present the code and measure the retrieving of the data from the server in the form of a data table, a dataset, a strong typed collection and the dbf file. The fastest approach is the retrieve and transport of the dbf file we bind to the VFP grid. On the second place (24% slower) is the strong typed .NET collection we bind to the Net grid. The laziest (30% slower) is the .NET data table that we bind to the Windows Net grid. Except for very small record sets, there is a big time gap (almost 100%) between the direct VFP data retrieve over the local network with respect to the WCF service.

The problem

For example, from a table the size of 400 MB let us download six thousand records (four fields of total length less than 50 characters) from the VFP database on the server. The first retrieval of these records from the workstation can exceed 12 seconds (at the server only 1 second [6]). The workstation running the retrieval blocks for 12 seconds. If we download consequently two record sets, we block the VFP form for 24 seconds and so on. How can we retrieve two thousand large records set in few seconds without blocking the client?

The solution

On the first retrieve of data from the server the VFP client downloads also the relatively large index file and dbf table headers. Using WCF, we can retrieve the data on the server (backend) and download only the selected records. We can asynchronously execute the client operations (in parallel with other VFP operations). This may be the way to go if you have a VFP application running long VFP requests. You create and install:

WCF service. (How to create a WCF in Visual Studio 2008? see. [2]).We shall use the net.tcp protocol.
Host the WCF service. In our projects, we shall host it in Windows service. Create a Windows service and install it on the server.
Create the WCF client (How to do this? See [1], [3]).
Create the Windows control (Active-X) and make it COM visible (see [1]). Register it on the workstation.
Create the VFP project, the VFP form and add it to the created Active-X control. Run the VFP application on the workstation.

The components of the above projects are interconnected. We pass the data (objects) between these components. We shall pay special attention to the methods and objects we use for this intra components transport.

The WCF service

The main goal of the WCF service is to accept the requests from the client and pass the requests to the VFP backend; catch the response from the VFP database application (on the server) and pass the response to the client. We shall create the WCF service with a project named WfcVfpWsTcp and six files:

1. Service.cs (Listing 1). This file contain the interface IService1 with 6 operations which allows us to transport the .NET generic data types: String, DataSet, DataTable, Byte and a generated data type named OrderListCollection. In the background the WCF serializes these five objects. In all the methods we have the out parameter retError. With it, we send to the client the error message generated on the server (service) side. The service operation methods accept the names and parameters of the method it will execute in the VFP database and route it to the VFP database by calling VfpServerCon. Normally the service WfcVfpWsTcp is on the same computer as the VFP server.

Listing 1: The service.cs class

using System;
using System.Reflection;
using System.Collections.Generic;
using System.Text;
using System.ServiceModel;
using System.Runtime.Serialization;
using System.IO;
using System.Configuration;
using System.Data;
using System.Collections;
using System.ServiceModel.Channels;
namespace WfcVfpWsTcp
{
    [ServiceContract]
    [ServiceKnownType(typeof(Orders))]
       public interface IService1 
    {
        [OperationContract]
        string MyOperation1(out string retValue,string myValue);
        [OperationContract]
        byte[] StoreHit(out string retError,string RequestMethod, 
                        string paramFrom,string paramTo,string param3);
        [OperationContract]
        byte[] StoreFile(out string retError,string RequestMethod, 
                        string paramFrom, string paramTo);
        [OperationContract]
        byte[]  StoreDataSet(out string retError,string RequestMethod, 
                        string paramFrom, string paramTo);
        [OperationContract]
        DataTable StoreDataTable(out string retError, string RequestMethod, 
                        string paramFrom, string paramTo);
        [OperationContract]
        OrderListCollection StoreCollection (out string retError,
                        string RequestMethod, string paramFrom, string paramTo);
    }
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, IncludeExceptionDetailInFaults = true)]
    public class service1 : IService1
    {
        private string _filePath = ConfigurationManager.AppSettings["filePath"];
        VfpServerCon VFPSer = new VfpServerCon();
        public string MyOperation1(out string retValue,string myValue)
        {
            OperationContext context = OperationContext.Current;
            MessageProperties messageProperties = context.IncomingMessageProperties;
            RemoteEndpointMessageProperty endpointProperty 
                  = messageProperties[RemoteEndpointMessageProperty.Name]  
                       as RemoteEndpointMessageProperty;
            retValue = "From service-error:" + myValue;
            return string.Format("Hello {0}! Your IP address is {1} and your port is
              {2}",myValue , endpointProperty.Address, endpointProperty.Port);
        }
        public DataTable StoreDataTable(out string retError, string MethodName, 
             string paramFrom, string paramTo)
        {
            DataTable retds = this.VFPSer.StoreDataTable(out  retError, MethodName, 
                  paramFrom, paramTo);
            retError = this.VFPSer.Merror;
            return retds;
        }
        public byte[] StoreDataSet(out string retError, string MethodName, 
           string paramFrom, string paramTo)
        {
          byte[]     retds = this.VFPSer.StoreDataSet(out  retError,MethodName, 
             paramFrom, paramTo);
          retError = this.VFPSer.Merror;
          return retds;
        }
        public byte[] StoreHit(out string retError, string MethodName, 
           string paramFrom, string paramTo, string param3)
        {
            byte[] retValue = this.VFPSer.StoreFile(out  retError, MethodName, paramFrom, paramTo);
            retError = this.VFPSer.Merror;
            return retValue;
        }
        public byte[] StoreFile(out string retError, string MethodName, 
           string paramFrom, string paramTo)
        {
           byte[] retValue = this.VFPSer.StoreFile(out  retError,MethodName,
              paramFrom, paramTo);
           retError = this.VFPSer.Merror;
            return retValue;
        }
        public OrderListCollection StoreCollection(out string retError,
           string MethodName, string paramFrom, string paramTo)
        {
          OrderListCollection orl= this.VFPSer.StoreCollection (out retError, 
             MethodName, paramFrom, paramTo);
          retError = retError;
          return orl;
        }
           /// <summary>
        /// Disposes the current instance.
        /// </summary>
        private string _errorMessage = "";
        public string ErrorMessage
        {
            get { return _errorMessage; }
            set { _errorMessage = value; }
        }
    }
}

2. VfpServerCon.cs (Listing 2) (Its main goal is to retrieve the data from the VFP database using the OleDb driver). It accepts the names and parameters of the method it will execute in the VFP database, accept the response from the VFP (normally as the OleDbDataReader), transform the data (for example in byte array) and pass the data to the service. (You can retrieve the data from the VFP database also with the VFP COM object (see [3])).

Listing 2: The VfpServerCon.cs class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Diagnostics;
using System.Data.OleDb;
using System.IO;
using System.Xml;
using System.Collections;
using System.Text;
    /// <summary>
    /// Summary description for VfpServerO
    /// </summary>
    /// 
    public class VfpServerCon
    {
        private string conStr = 
           System.Configuration.ConfigurationManager.AppSettings["connStr"];
        private string filePath = 
           System.Configuration.ConfigurationManager.AppSettings["filePath"];
        System.Data.OleDb.OleDbConnection conn;
        private string _merror;
        EventLog Log = null;
        public VfpServerCon()
        {
            conn = new System.Data.OleDb.OleDbConnection(this.conStr);
        }
        public string Test(string MethodName, string RequestParameter)
        {
            return MethodName + RequestParameter;
        }

public DataTable StoreDataTable(out string retError,string MethodName, 
           string RequestParameter1, string RequestParameter2)
        {
           
            DataTable dt0 = new DataTable();
            dt0.TableName = "Order";
            OleDbCommand cmds = this.OpenCon(MethodName, RequestParameter1,
               RequestParameter2);
            if (this.Merror != string.Empty)
            {
                retError = this.Merror;
                return null;
            }
            System.Data.OleDb.OleDbDataReader dr = null;
           try
            {
                dr = cmds.ExecuteReader();
               dt0.Load(dr, LoadOption.OverwriteChanges);
            }
            catch (Exception ex)
            {
                this.Merror = ex.Message.ToString() + "Can not load dataTable";
                retError = this.Merror;
                return null;
            }
            dr.Dispose();
            conn.Close();
           retError = "";
            return dt0;
        }
        public byte[] StoreDataSet(out string retError, string MethodName,
           string RequestParameter1, string RequestParameter2)
        {
            DataSet ds1 = new DataSet();
            DataTable dt0 = new DataTable();
            dt0.TableName = "Order";
            ds1.Tables.Add(dt0);
           OleDbCommand cmds = this.OpenCon(MethodName, RequestParameter1,
              RequestParameter2);
            if (this.Merror != string.Empty)
            {
                retError = this.Merror;
                return null;
            }
            System.Data.OleDb.OleDbDataReader dr = null;
            try
            {
                dr = cmds.ExecuteReader();
                ds1.Load(dr, LoadOption.OverwriteChanges, ds1.Tables[0]);
            }
            catch (Exception ex)
            {
                this.Merror = ex.Message.ToString() + "Can not load dataset";
                retError = this.Merror;
                return null;
            }
            dr.Dispose();
            conn.Close();
            ds1.RemotingFormat = SerializationFormat.Binary;
            MemoryStream ms = new MemoryStream();
            ds1.WriteXml(ms, XmlWriteMode.WriteSchema);
            ms.Position = 0;
            Byte[] buff = new byte[ms.Length];
            ms.Read(buff, 0, buff.Length);
            ms.Close();
            ms.Dispose();
            retError = "";
            return buff;
        }   
        public byte[] StoreFile(out string retError,string MethodName, 
           string RequestParameter1, string RequestParameter2)
        {
            OleDbCommand cmds = this.OpenCon(MethodName, RequestParameter1,
               RequestParameter2);
            if (this.Merror != string.Empty)
            {
                retError = this.Merror;
                return null;
            }
            System.Data.OleDb.OleDbDataReader dr = null;
            string rez = "";
            try
            {
                dr = cmds.ExecuteReader();
                dr.Read();
                rez = dr[0].ToString(); // return string filePath
            }
            catch (Exception ex)
            {
                this.Merror = "From storefile empty string:" + ex.Message + "  " 
                  + filePath;
                retError = this.Merror;
                return Encoding.UTF8.GetBytes(this.Merror);
            }
            dr.Dispose();
            conn.Close();
            if (rez != string.Empty) //Return file
            {
                rez = @filePath + rez;
            }
            else
            {
                this.Merror = "From storefile empty string:" + "  " + filePath;
                retError = "";
                return Encoding.UTF8.GetBytes(this.Merror);
            }
            byte[] buffer = null;
            try
            {
                FileStream fs = new FileStream(@rez, FileMode.Open, FileAccess.Read);
                buffer = new byte[fs.Length];
                fs.Read(buffer, 0, (int)fs.Length);
                fs.Close();
                FileSystemInfo fsi = new FileInfo(@rez);
                fsi.Delete();
            }
            catch (Exception ex)
            {
                this.Merror = "From storefile:" + ex.Message.ToString() + "  " + rez;
                retError = this.Merror;
                return Encoding.UTF8.GetBytes(this.Merror);
            }
            retError = "";
            return buffer;
        }
        public string Merror
        {
            get
            {
                return _merror;
            }
            set
            {
                _merror = value;
            }
        }
        public OleDbCommand OpenCon(string MethodName, string RequestParameter1,
           string RequestParameter2)
        {
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from pgp", conn); //Open data
            string metpar = MethodName + "('" + RequestParameter1 + "','" 
               + RequestParameter2 + "')";
            System.Data.OleDb.OleDbCommand cmds = 
               new System.Data.OleDb.OleDbCommand(metpar, conn);
            cmds.CommandType = CommandType.StoredProcedure;
            this.Merror = string.Empty;
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                this.Merror = ex.Message.ToString() + " Can not open conn.";

return cmds;
            }
            try
            {
                cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                this.Merror = ex.Message.ToString() + " Can not read " 
                   + MethodName + " " + RequestParameter1 + "  " + conn;
                return cmds;
            }
            cmd.Dispose();
            return cmds;
        }
        public OrderListCollection  StoreCollection(out string  retError,
           string MethodName, string RequestParameter1, string RequestParameter2)
        {
            OrderListCollection arl = new OrderListCollection();
            byte[] buf = null;
            OleDbCommand cmds = this.OpenCon(MethodName, RequestParameter1, 
               RequestParameter2);
            if (this.Merror != string.Empty)
            {
                retError= this.Merror;
                return arl;
            }
            System.Data.OleDb.OleDbDataReader dr = null;
            try
            {
                dr = cmds.ExecuteReader();
                Orders  order = null;
                while (dr.Read())
                {
                    order = new Orders();
                    order.Orderid = (Decimal)dr[0];
                    order.Doctype = dr["doctype"].ToString();
                    order.Wareid = dr["wareid"].ToString();
                    order.Orderdate = (DateTime)dr["orderdate"];
                    arl.Add(order);
                }
            }
            catch (Exception ex)
            {
                this.Merror = ex.Message.ToString() + " Can not execute " + 
                   MethodName + " " + RequestParameter1 + "  " + conn;
                retError = this.Merror;
                return arl;
            }
            dr.Dispose();
            conn.Close();
            retError = "";
            return arl;
        }
    }

When we initialize the VfpServerCon class, it reads the connection string from the app.config file (see the key connString in app.config).

You have to write your own connection string in the app.config and write your own store procedures. In Listing 2 [6] is the stored procedure used in this article. It accepts two parameters and returns a result set. With small modifications, this procedure can write the results in the dbf file and return a string with its name. We suppose the last method is the fastest retrieve method. Transporting a dbf file is relatively unusual in WCF project, so we describe this process in more detail.

When we call the StoreFile method of the VfpServerCon instance (Listing 2), the StoreFile method is executed. The VFP database method StoreFile retrieves the data from the database, copies it in the dbf file and passes its name back to the calling method. It reads the dbf file from the disk and writes it in the byte buffer (Listing 2). The program passes this buffer to the service, which sends it to the client (Listing 1).

Listing 3: The OrderListCollection class

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.ServiceModel;
using System.Runtime.Serialization;
    /// <summary>
    /// Summary description for OrderListCollection
    /// </summary>
    /// 
    [Serializable]
    [DataContract]
    public class Orders : IComparable
    {
        private decimal _orderid;
        private string _wareid;
        private DateTime _orderdate;
        private string _doctype;
        [DataMember]
        public decimal Orderid
        {
            get { return _orderid; }
            set { _orderid = value; }
        }
        [DataMember]
        public string Wareid
        {
            get { return _wareid; }
            set { _wareid = value; }
        }
        [DataMember]
        public string Doctype
        {
            get { return _doctype; }
            set { _doctype = value; }
        }
        [DataMember]
        public DateTime Orderdate
        {
            get { return _orderdate; }
            set { _orderdate = value; }
        }
        public Orders()
        {
            _orderdate = DateTime.Now;
            _doctype = string.Empty;
            _wareid = string.Empty;
            _orderid = 0;
        }
        public Orders(decimal orderid, DateTime orderdate, string wareid, 
           string doctype)
        {
            _orderdate = orderdate;
            _doctype = doctype;
            _wareid = wareid;
            _orderid = orderid;
        }
        public int CompareTo(object obj)
        {
            if (!(obj is Orders))
            {
                throw new ArgumentException("Object provided is of the wrong type");
            }
            Orders ord = (Orders)obj;
            int cmpl = this.Orderid.CompareTo(ord.Orderid);
            if (!(cmpl == 0))
            {
                return cmpl;
            }
            return this.Orderid.CompareTo(ord.Orderid);
        }
    }
    [Serializable]
    [CollectionDataContract]
    public class OrderListCollection : CollectionBase
    {

public Orders this[int idx]
        {
            get
            {
                return (Orders)this.InnerList[idx];
            }

}
        public void Add(Orders ord)
        {
            this.InnerList.Add(ord);
        }
    }

3. OrderListCollection.cs (Listing 3). We frame the elements of this class with DataContract, DataMember, CollectionDataContract and Serializable attributes. Do not forget to add to the service.cs the attribute [ServiceKnownType(typeof(Orders))]. Decorated with this attributes the new data type can be serialized with the WCF serialization tools. If you change any of this attribute, you have to update the client with the new service metadata.

Listing 4: The app.config file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="connStr" value="Provider=VFPOLEDB.1;Data Source=d:\moj\ateks2006\data;Collating Sequence=MACHINE"/>
    <add key="VFPServerName" value="vfpserver.ComData"/>
    <add key="filePath" value="d:\moj\ateks2006\"/>
  </appSettings>

<system.serviceModel>
    <bindings>
      <netTcpBinding>
        <binding name="NetTcpBinding" transferMode="Buffered" maxReceivedMessageSize="5242880">
          <security mode="None" />
        </binding>
      </netTcpBinding>
    </bindings>
    <diagnostics>
      <messageLogging logEntireMessage="true" logMalformedMessages="true"
        logMessagesAtServiceLevel="true" logMessagesAtTransportLevel="true"
        maxMessagesToLog="1000" />
    </diagnostics>
    <behaviors>
      <serviceBehaviors>
        <behavior name="mex">
          <serviceDebug includeExceptionDetailInFaults="true" />
           <serviceMetadata />
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <services>
      <service name="WfcVfpWsTcp.service1" behaviorConfiguration="mex">
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://192.168.123.63:8081/wcfvfpwstcp" />
          </baseAddresses>
        </host>
        <endpoint  binding="netTcpBinding"
          bindingConfiguration="NetTcpBinding" name="NetTcpEndPoint" contract="WfcVfpWsTcp.IService1" />
        
        <endpoint address="mex" binding="mexTcpBinding"  name="MEX" contract="IMetadataExchange" />
      </service>
    </services>
  </system.serviceModel> </configuration>

4. app.config (Listing 4). In the app.config we put a net.tcp binding, its endpoints and the corresponding mex binding, which allow us to download the service description to the client.

We use the key filePath (Listing 4) in the method StoreFile (Listing 2). It passes to the VFP database method the path of the file written on the disk.

Do not forget to change this filePath to your database path. Change the base endpoint address (192.168.123.63) to the IP of your computer, where the service is installed.

Listing 5: The HostServiceVfp file

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.ServiceModel;
using System.Text;
namespace WfcVfpWsTcp
{
    public partial class HostServiceVfp : ServiceBase
    {
        public ServiceHost serviceHost = null;
        public HostServiceVfp()
        {
            InitializeComponent();
            this.ServiceName = "WcfVfpWsTcp";
            this.AutoLog = true;
            this.CanShutdown = false;
            this.CanStop = true;
            this.CanPauseAndContinue = false;
         }
        protected override void OnStart(string[] args)
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
            }
            serviceHost = new ServiceHost(typeof(service1));
            serviceHost.Faulted += new EventHandler(Host_Faulted);
            // Open the ServiceHostBase to create listeners and start 
            // listening for messages.
            serviceHost.Open();
        }
        protected override void OnStop()
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
                this.EventLog.WriteEntry("ServiceHost has been closed.",
                EventLogEntryType.Information, 0, 0);
                serviceHost = null;
            }
        }
        void Host_Faulted(object sender, EventArgs e)
        {
            this.EventLog.WriteEntry("ServiceHost has faulted.", EventLogEntryType.Error, 0, 0);
        }
    }
}

5. HostServiceVfp.cs (Listing 5). The class to start and stop the service. In case of a fault of the service, it writes the error message to the log file.

6. ProjectInstaller.cs. Enables the installation of the Windows service and makes it visible in the MMC (Administrative Tools - Services; see [5]). It creates the setup project WfcVfpWsTcpIn (see [4]).

About Josip Zohil

Josip Zohil, Koper, Slovenia, Josip.Zohil1@guest.arnes.si

This author has published 3 articles on DotNetSlackers. View other articles or the complete profile here.

You might also be interested in the following related blog posts

Self-reference hierarchy with Telerik TreeView for Silverlight read more
Announcing Microsoft Ajax Library (Preview 6) and the Microsoft Ajax Minifier read more
Building a class browser with Microsoft Ajax 4.0 Preview 5 read more
Business Apps Example for Silverlight 3 RTM and .NET RIA Services July Update: Part 7: ADO.NET Data Services Based Data Store read more
Scenarios for WS-Passive and OpenID read more
Introducing Versatile DataSources read more
Business Apps Example for Silverlight 3 RTM and .NET RIA Services July Update: Part 19: ASP.NET Dynamic Data read more
Convert Web.UI ASP.NET Grid into a data entry spreadsheet read more
Web.UI 2008.2 Grid News: Grouping read more
Web.UI 2008.2 Grid News: Grouping read more

Top

Please login to rate or to leave a comment.

Product Spotlight

Login

Lost Password?