Visual Basic —

Remoting

Efficient passing of data between processes, and particularly across machine boundaries, is critical to the success of scalable, distributed, tiered Visual Basic systems. We've already (I hope) established that passing data in variables that are grouped together on methods-rather than lots of property touching-is the way to go. We also looked at when to use ByVal and ByRef on parameters. Now we have to look at how we can get the most efficient performance out of remoting. Marshalling data across machines and process boundaries attracts a heavy penalty from COM, so in Visual Basic 4 and 5 a few ways established themselves as favored. They were used particularly for passing large amounts of data, which would otherwise have ended up being collections of objects. Let's remind ourselves of those ways first.

Variant arrays and GetRowsAn efficient way of passing larger amounts of data across process and machine boundaries is as Variant arrays. It is possible to make your own arrays using the Array function. But Variant arrays gained in popularity mainly because they were coded directly into the data access interfaces that most Visual Basic developers use. GetRows exists on RDO's rdoResultset objects and both DAO's and ADO's Recordset objects. The ADO version is more flexible than the DAO and RDO implementations-check out its extra parameters. Using Getrows in the data access code looks like this:

Public Function GiveMeData() As Variant
      Dim rsfADO  As New ADODB.Recordset  'The Recordset object
      Dim vResult As Variant              'to put data in.
      rsfADO.Open strSQL, strCon     'Open a connection.
      vResult = rsfADO.GetRows     'Park the results in a Variant array.
      GiveMeData = vResult         'Send the Variant array back.
  End Function

Finally the Variant array reaches the user interface (having perhaps passed through a number of hands, classes, and components) and is used.

Public Sub GetData()
      Dim nRowCount As Integer    'A row count holder.
      Dim nColCount As Integer    'A row count holder.
      Dim sCurrentRow As String   'A string to build each row in.
      Dim vData As Variant        'A Variant to hold our data.
      'Call the server and get back a Variant array.
      vData = oDataServer.GiveMeData
      'For each row (rows are dimension 2 of the array).
      For nRowCount = LBound(vData, 2) To UBound(vData, 2)
          sCurrentRow = "" 'Initialize the current row.
          'For each column in the row.
          For nColCount = LBound(vData, 1) To UBound(vData, 1)
              'Add the column data to the current row.
              sCurrentRow = sCurrentRow & Space(2) & _
   CStr(vData(nColCount, nRowCount))
          Next nColCount
          'Add the row to a list box - or use it somehow.
          lstDataRows.AddItem sCurrentRow
      Next nRowCount
  End Sub

In each case, GetRows creates a two-dimensional array, which it puts into a Variant. The first array subscript identifies the column and the second identifies the row number. The only downside of GetRows is that, when you have grown used to manipulating collections of objects and accessing their data through their properties, going back to arrays feels clumsy. It is a specific and increasingly unusual implementation. As a result, at TMS we often use a DLL that turns a two-dimensional Variant array into either a recordset object again (either our own implementation, or less frequently one of the standalone implementations available in ADO and RDO) on the client-side, purely for ease of coding. (See the Gendata and DataArray projects in the samples directory for this chapter. These projects use GetRows with ADO, and a custom implementation of the recordset.)

UDTs and LSET Some people (I was not among them) so mourned the passing of the user-defined type (UDT) into history (since UDTs could be passed as a parameter) that they came up with work-arounds, to which they became devoted. The UDT has its Visual Basic roots in file access, particularly where files were structured as records.

Imagine a UDT for an Account record:

Public Type AccountStruct
      AccountID As Long
      Balance As Currency
      Status  As AccountStatus
      AccountTitle As String
      OverdraftAmount As Currency
      AvailableFunds As Currency
  End Type

Since a UDT cannot be passed as a parameter, it has first to be converted. Therefore, a corresponding user-defined type that only has one member, but is the same size as the original is defined:

Type Passer
      Buffer As String * 36
  End Type

Caution

Beware on sizing that in 32-bit Windows, strings are Unicode. Therefore each character = 2 bytes, so if you need an odd number size (such as 17 bytes because you have a byte member in the original type) you would actually have a spare byte.

We then copy the original user-defined type into this temporary one. We can do this with LSet. In the Client component we have:

Dim pass As Passer
      Dim oUDTer As udtvb5er
      Set oUDTer = New udtvb5er
      LSet pass = myorig
        oUDTer.GetUDT pass.Buffer

In the server we need the same user-defined types defined, along with the following method in our udtvb5er class:

Public Sub GetUDT(x As String)
      Dim neworig As AccountStruct
      Dim newpass As Passer
      newpass.Buffer = x
      LSet neworig = newpass
  MsgBox neworig.AccountID & Space(2) & Trim(neworig.AccountTitle)_
   & Space(2) & "Current Balance: " & CStr(neworig.Balance)
  End Sub

To do this we are copying from one type to the other, passing the data as a string, and then reversing the process at the other end. However, as Visual Basic's Help warns, using LSet to copy a variable of one user-defined type into a variable of a different user-defined type is not recommended. Copying data of one data type into space reserved for a different data type can have unpredictable results. When you copy a variable from one user-defined type to another, the binary data from one variable is copied into the memory space of the other, without regard for the data types specified for the elements.

Note
There is an example project in the code samples for this chapter, called UDTPass.vbg. This is not a recommendation, merely a recap of what was possible!

Visual Basic 6 and remoting

With a few exceptions, this chapter has up until now dealt in long-term truths rather than cool new Visual Basic 6 features. I haven't done this to hide things from you deliberately. However passing data from one thing to another is an area where Visual Basic 6 has added quite a few new features, and it's pretty confused in there at the moment. I've attempted to sort them out for you here.

New ways of passing variables (arrays and UDTs) Visual Basic 6 has made it possible to return arrays from functions. In the spirit of variable passing, rather than property touching, this is likely to be of some help. The new syntax looks like this in the function you are calling:

Public Function GetIDs() As Long()
      Dim x() As Long
      Dim curAccount As Account
      Dim iCount As Integer
      ReDim x(1 To mCol.Count)
      iCount = 1
      For Each curAccount In mCol
          x(iCount) = curAccount.AccountID
          iCount = iCount + 1
      Next curAccount
      GetIDs = x
  End Function

From the client's view it is like this:

Private Sub cmdGetIDs_Click()
      Dim x() As Long
      Dim i As Integer
      x() = oAccounts.GetIDs
      For i = LBound(x()) To UBound(x())
          lstIDs.AddItem CStr(x(i))
      Next i
      lstIDs.Visible = True
  End Sub

This example has been included in the project group UDTGrp.vbg in the samples directory for this chapter.

Visual Basic 6 has also added the ability to have public UDTs and to pass them between components. Thus a UDT structure such as we looked at earlier:

Public Type AccountStruct
      AccountID As Long
      Balance As Currency
      Status  As AccountStatus
      AccountTitle As String
      OverdraftAmount As Currency
      AvailableFunds As Currency
  End Type

can be passed back and forth to the server thus in the server class's code:

Public Function GetallData() As AccountStruct
      Dim tGetallData As AccountStruct
      tGetallData.AccountID = AccountID
      tGetallData.Balance = Balance
      tGetallData.Status = Status
      tGetallData.AccountTitle = AccountTitle
      tGetallData.OverdraftAmount = OverdraftAmount
      tGetallData.AvailableFunds = AvailableFunds
      GetallData = tGetallData
  End Function
  Public Sub SetAllData(tAccount As AccountStruct)
      AccountID = tAccount.AccountID
      Balance = tAccount.Balance
      Status = tAccount.Status
      AccountTitle = tAccount.AccountTitle
      OverdraftAmount = tAccount.OverdraftAmount
  End Sub

and called like this from the client:

Dim oAccount As Account
  Dim tAccount As AccountStruct
  For Each oAccount In oAccounts
      tAccount = oAccount.GetallData
      lstAccounts.AddItem tAccount.AccountID & _
          Space(4 - (Len(CStr(tAccount.AccountID)))) & _
          tAccount.Balance & Space(3) & tAccount.OverdraftAmount
  Next oAccount

Remoting ADO recordsets The combination of ADO and the Remote Data Service (RDS) client-side library-intended for speedy, lightweight, disconnected data access for Web applications-can be particularly useful for any distributed client/server system, regardless of its user interface type. The client needs a reference to the Microsoft ActiveX Data Objects Recordset 2.0 Library, while the server has a reference to the Microsoft ActiveX Data Objects 2.0 Library. At its simplest, the client code looks like this:

Private oDD As DataDonor
  Private Sub cmdGetData_Click()
      Dim oRS As ADOR.Recordset
      Set oDD = New DataDonor
      Set oRS = oDD.GiveData
      Set oDD = Nothing
      Set oDataGrid.DataSource = oRS
  End Sub

While in the server component the DataDonor Class's code looks like this:

Public Function GiveData() As ADODB.Recordset
      'A very boring query we can use on any SQL Server.
      Const strSQL As String = "SELECT * FROM authors"
      'We'll use this DSN.
      Const strCon As String = _
          "DSN=pubsit;UID=lawsond;PWD=lawsond;" & _
          "DATABASE=pubs;APP=DudeCli;"
      Dim ors As New ADODB.Recordset
      ors.LockType = adLockBatchOptimistic
      ors.CursorLocation = adUseClient
        ors.CursorType = adOpenStatic
      ors.Open strSQL, strCon
        Set ors.ActiveConnection = Nothing
      Set GiveData = ors
  End Function

In order to create a disconnected recordset, you must create a Recordset object that uses a client-side cursor that is either a static or keyset cursor (adOpenStatic or adOpenKeyset) with a lock type of adLockBatchOptimistic.

(This example is in the samples directory for this chapter, as RemRset.vbg).

If you return a disconnected recordset from a function, either as the return value, or as an output parameter, the recordset copies its data to its caller. If the caller is in a different process, or on a different machine, the recordset marshals the data it is holding to the caller's process. In so doing it compresses the data to avoid occupying substantial network bandwidth, which makes it an ideal way to send large amounts of data to a client machine. Remoting ADO recordsets really has to be done.

The end resultset is a recordset which has been instantiated on the server, then physically passed down to the client. It is no longer connected to the database at all, but can be used as a recordset, and if changes are made to it the recordset could be passed back to a server and reassociated with a database connection to allow updates to take effect. This avoids the penalty of continued network overhead because each column and field is referenced for its data, and is a strong contender as a replacement for passing Variant arrays.

Returning changes from a disconnected recordset: batch updating When a recordset is disconnected and has been remoted to a different machine, it is possible to make changes to it using its Edit, Update, and Delete methods. In fact, it is one of the only times when it makes sense to use these methods on a cursor, since we are not using up all the normal resources or actually talking to the database. When you are finished changing things, you pass the recordset back to a component that has a live connection to the database. It uses the UpdateBatch method to put in all your changes in a single batch.

Public Sub ReconUpdate(rs As ADODB.Recordset)
      Dim conn As ADODB.Connection
      Set conn = New ADODB.Connection
      conn.Open "DSN=Universe"
      Set rs.ActiveConnection = conn
      Rs.UpdateBatch

Fear not the score of other untrustworthy users having updated the same records as you! Just like batch updating in RDO, you have all the tools to sort out collisions, if they occur. However, beware if you are expecting to marshal a recordset from the middle tier to the client machine to resolve any conflicts you have. The three versions of the data (original, value, and underlying) are not marshalled, so you need to do some work yourself. (See Q177720 in the Knowledge Base for more on this.)

Creatable ADO recordsets ADO recordsets can act as a standard interface, even when data is not being accessed from a database using an OLE DB provider, since ADO recordsets can be created independently and filled with data by direct code manipulation. Here is server code for this in a class:

Private rs As ADODB.Recordset
  Private Sub Class_Initialize()
      Dim strPath As String, strName As String
      Dim i As Integer
      ' Create an instance of the Recordset.
      Set rs = New ADODB.Recordset
      ' Set the properties of the Recordset.
      With rs
          .Fields.Append "DirID", adInteger
          .Fields.Append "Directory", adBSTR, 255
          .CursorType = adOpenStatic
          .LockType = adLockOptimistic
          .Open
      End With
      ' Loop through the directories and populate
      ' the Recordset.
      strPath = "D:\"
      strName = Dir(strPath, vbDirectory)
      i = 0
      Do While strName <> ""
          If strName <> "." And strName <> ".." Then
              If (GetAttr(strPath & strName) And _
                  vbDirectory) = vbDirectory Then
                  i = i + 1
                  With rs
                      .AddNew
                      .Fields.Item("DirID") = i
                      .Fields.Item("Directory") = strName
                      .Update
                  End With
              End If
          End If
          strName = Dir
      Loop
      ' Return to the first record.
      rs.MoveFirst
  End Sub

This code is in the DataAware.vbp sample project in the samples directory for this chapter.

Persisting recordsets ADO Recordset objects can be saved to a file by using their Save method. This can be valuable if you have a requirement to store data for longer than a run of a program, but without being able to do so in a data source. Imagine a user has made changes to a recordset, and then cannot reconnect to a data source such as a remote database. Persisting data can also be useful for a disconnected recordset, since the connection and the application can be closed while the recordset is still available on the client computer. The code for persisting a Recordset object looks like this:

rsDudes.Save "c:\tms\dudestuff.dat", adPersistADTG

and to get the data out from the file again the following code would do it:

rsDudes.Open " c:\tms\dudestuff.dat "

Files and persistable classes Visual Basic 6 gave classes the capabilities that some other ActiveX instantiables (such as ActiveX Documents and User Controls) have had for a version already-namely the capability to persist their properties through the PropertyBag object. This allows us to store a class's properties between instances. The Persistable property in conjunction with the PropertyBag object lets a class instance be persisted almost anywhere: a file, the Registry, a database, a word-processor document, or a spreadsheet cell.

Why persist? Most components have properties; one of the great annoyances of Visual Basic's Class_Initialize event procedure is that you can't get parameters into it. Typically Class_Initialize is used to set up default values for a class instance. The default values you use are frozen in time when you compile the component, unless you use something like INI settings, Registry entries, files, or command line arguments to vary them. This is where the Visual Basic 6 class's Persistable property comes in, allowing you to save a component's values between runs. To be persistable, a class has to be public and createable. When you set a class's Persistable property to Persistable, three new events are added to the class: ReadProperties, WriteProperties, and InitProperties. Just like in an ActiveX User Control, you can mark a property as persistable by invoking the PropertyChanged method in a Property Let or Property Set procedure, as in the following example:

Private mBattingAverage As Decimal
  Public Property Let BattingAverage (newAverage As Decimal)
      mBattingAverage = newAverage
      PropertyChanged "BattingAverage"
  End Property

Calling the PropertyChanged method marks the property as dirty. The WriteProperties event will fire when the class is terminated if any property in the class has called PropertyChanged. Then we use the events and the PropertyBag object almost the same way as in a User Control.

There is a twist however: we need a second instance of a PropertyBag object, so that when the object goes away, and takes its PropertyBag object with it, there is a persisted set of properties. The following code shows persisting an object to a text file, but remember that they can be persisted wherever you like, even in a database:

Private pb As PropertyBag      ' Declare a PropertyBag object.
  Private oBatsman As Batsman    ' Declare a Cricketer.
  Private Sub Form_Unload(Cancel As Integer)
      Dim varTemp as Variant
      ' Instantiate the second PropertyBag object.
      Set pb = New PropertyBag
      ' Save the object to the PropertyBag using WriteProperty.
      pb.WriteProperty "FirstManIn", oBatsman
      ' Assign the Contents of the PropertyBag to a Variant.
      varTemp = pb.Contents
      ' Save to a text file.
      Open "C:\tms\FirstBat.txt" For Binary As #1
      Put #1, , varTemp
      Close #1
  End Sub

The Contents property of the PropertyBag object contains the Batsman object stored as an array of bytes. In order to save it to a text file, you must first convert it to a data type that a text file understands-here, that data type is a Variant.