So far, we've used data source objects to implement remote data access through ADO. However, this isn't the only technique. With RDS, we can create instances of the objects lower down the hierarchy directly, and then use them to implement a more customized form of remote data access.
Using the RDS Object Hierarchy
Each DSO is itself a
DataControl object, and when we instantiate a DSO on the page, it automatically creates instances of the DataSpace and DataFactory objects that perform the data transfer from server to client. Following is the diagram we used earlier in the chapter when we first looked at RDS:
However, there are often times when we want to use a custom component on the server that supplies our recordset, rather than querying the data store directly through the default
DataFactory object. This provides ways to reduce the security risks inherent in RDS, because we can structure the object to control how and when updates are applied to the source data. It also allows us to 'hide' the data store from prying eyes more effectively that exposing the original table.
There are two basic techniques for retrieving a recordset directly:
Using a SQL statement or query directly against the
DataFactory object
Using a custom business component on the server that returns a recordset
We'll look at each of these techniques in turn. In each case, we still use a
DataControl object to store the recordset returned from the server, and a DataSpace object to make the connection to the server. However, what differs is the way that these are declared in the client-side page.
Using the DataFactory Object Directly
The
DataFactory object provides a method named Query, which accepts a connection string and a SQL query string. It returns a recordset that is automatically passed to the client over the network, and which can then be assigned to the SourceRecordset property of a client-side DataControl object:
<!-- this is the normal RDS DataControl object with no parameters set --> <OBJECT ID="dsoDataControl"
CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33">
</OBJECT>
<!-- this is the client-side RDS DataSpace object --> <OBJECT ID="dspDataSpace"
CLASSID="CLSID:BD96C556-65A3-11D0-983A-00C04FC29E36">
</OBJECT>
<SCRIPT LANGUAGE="JavaScript">
// first we create a DataFactory object, specifying the server to use: myDataFactory = dspDataSpace.CreateObject("RDSServer.DataFactory",
"http://servername.com"); // now we create a recordset from the DataFactory using its Query method: myRecordset = myDataFactory.Query("DSN=yourdsn;UID=username;PWD=password;",
"Select * From TableName");
// finally, assign the returned recordset to the DataControl object: dsoDataControl.SourceRecordset = myRecordset;
</SCRIPT>
Although we've shown a Web-based connection here, by specifying the URL of the server, the
DataFactory object can also be instantiated using RPC protocols, by specifying the UNC address of the server in the form \\machinename (i.e. \\SUNSPOT).
Using a Custom Business Component
We can create a custom business component that is a COM object, and which returns a recordset, then install and register it on the server. We can then pass the recordset it creates on to the client as a disconnected recordset for use with ADO or data binding on the client.
The custom component can provide a method that returns a recordset object. If required, the method can also accept parameters that define the contents of the recordset, for example a SQL statement, or application-specific values such as a record identifier key or other selection criteria. It's also possible to use a server-side custom business object that accepts a recordset passed in by reference as a parameter to a method within the object. It can then marshal this recordset ready to pass it to the client.
The important point is that the custom object must provide a recordset that specifies the
adUseClient value for the CursorLocation property. (In previous versions of ADO, this property was called adUseClientBatch; this constant name is still supported, and is automatically mapped to the adUseClient value).
You can also use this technique to invoke any method of any business object, as long as it returns automation-compatible data types. This allows invocation of remote components through the HTTP protocol with DCOM.
In a Visual Basic component, we might use a function like this to create the recordset:
Public Function GetRecs(parameter1, parameter2, ... etc.) As Object
Dim objConn As New ADODB.Connection
Dim objRecs As New ADODB.Recordset
'
assuming that parameter1 contains a valid connection string: objConn.Open parameter1
'set the correct cursor location before opening the recordset: objRecs.CursorLocation = adUseClient
'use the 'Unspecified' values for the remaining parameters to make sure
'that an ADO/R remote recordset is created. We'll assume that the second
'parameter sent to the function contains a valid SQL query string: objRecs.Open parameter2, objConn, adOpenUnspecified, adLockUnspecified, _
adCmdUnspecified
Set GetRecs = objRecs
End Function
In the client Web page, we can create an instance of the business object on the server, and then call this function directly to return the recordset. Then it's just a matter of assigning the recordset to the
SourceRecordset property of a client-side DataControl object:
<!-- this is the normal RDS DataControl object with no parameters set --> <OBJECT ID="dsoDataControl"
CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33">
</OBJECT>
<!-- this is the client-side RDS DataSpace object --> <OBJECT ID="dspDataSpace"
CLASSID="CLSID:BD96C556-65A3-11D0-983A-00C04FC29E36">
</OBJECT>
<SCRIPT LANGUAGE="JavaScript">
// first we create a DataFactory object, specifying the server to use: myCustomObject = dspDataSpace.CreateObject("MyObject.ClassName",
"http://servername.com");
// now we create a recordset from the custom object using a custom method: myRecordset = myCustomObject.GetRecs("parameter1, parameter2, ... etc.");
// finally, assign the returned recordset to the DataControl object: dsoDataControl.SourceRecordset = myRecordset;
</SCRIPT>
Security Settings for Custom Business Objects
Custom components that are created by the client-side
DataSpace object's CreateObject method require security settings to be enabled on the server that hosts the custom component. The easiest way to achieve this is to use a simple text file with the .reg extension, and run it on the server against the regedit program. This merges the new keys into the registry. The file we need looks like this:
REGEDIT4
[HKEY_CLASSES_ROOT\CLSID\{your_component_guid}\Implemented Categories\{7DD95801-9882-11CF-9FA9-00AA006C42C4}]
[HKEY_CLASSES_ROOT\CLSID\{your_component_guid}\Implemented Categories\{7DD95802-9882-11CF-9FA9-00AA006C42C4}]
[HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\Parameters\ADCLaunch\your_component_class_string]
The first line tells
regedit that this is a valid .reg file. The next two entries (which should each be on one line, not wrapped like the code above) enable the Safe for Scripting setting, and the third line allows IIS to instantiate the component on the server. Place the file on the server machine, then double-click it to merge the values into the registry. Remember to back up the Registry first before making any changes to it.
Passing Updates Back to the Data Store
Many
DataControl objects (in the form of a DSO in the client-side page) can flush changes to the records back to the server, and on to the data store. This is done simply by calling the SubmitChanges method of the DataControl (DSO) object:
<SCRIPT LANGUAGE="JavaScript">
function updateData() {
dsoDataControl.SubmitChanges();
}
</SCRIPT>
However, when we create the
DataSpace and DataFactory objects ourselves (as shown earlier in this section of the chapter), we can flush changes to the records back to the server, and on to the data store, by simply calling the SubmitChanges method of the DataFactory object instead. In this case, we also have to provide the connection information, and a reference to the recordset (here obtained directly from the DSO's recordset property):
<SCRIPT LANGUAGE="JavaScript">
function updateData() { // myDataFactory is a global variable, set when we fetched the recordset myDataFactory.SubmitChanges("DSN=yourdsn;UID=username;PWD=password;",
dsoDataControl.recordset);
}
</SCRIPT>
If we are using a custom business component to carry out the creation of the recordset on the server, we have to do a little more work. The first step is to provide a method within the component that will accept a recordset, and then call the
UpdateBatch method of the recordset object. In our example, we provide a parameter to allow us to send the connection string to the component, as well as the parameter that passes the updates to the recordset. This recordset parameter is defined as being of type ADOR.Recordset:
Public Sub UpdateRecs(strConnect As String, objRecs As ADOR.Recordset)
'first we have to set the active connection property of the recordset
'to a valid data store connection, so that the disconnected recordset
'can be re-connected to that data store: objRecs.ActiveConnection = strConnect
'now we an update the source data: objRecs.UpdateBatch
End Sub
The ADOR object library is a subset of the ADO library, and is specifically designed for use with RDS and the manipulation of remote recordsets.
In the Web page, we call this method and pass in the recordset that is attached to the client-side
DataControl object:
<SCRIPT LANGUAGE="JavaScript">
function updateData() {
// get a reference to the client-side recordset: objRs = dsoDataControl.recordset;
// tell the recordset to only send back changed records: objRs.MarshalOptions = 1; // adMarshalModifiedOnly // then call the method in our custom business component:
// myCustomObject is a global variable, set when we fetched the recordset myCustomObject.UpdateRecs(objRs);
}
</SCRIPT>
Creating an Empty Client-side Recordset
It's possible to create empty recordsets on the client using ADO, and then attach them to a DSO
DataControl object on the page, or directly to a DataFactory object. This means that we can remote any data, or return parameters of any method calls, as long as it is formatted into a recordset. Each ADO recordset consists of a set of fields, and each field has four properties that you must set in code. These are the field name, the field type, the field size, and a Boolean value that specifies if the field will accept null values.
Defining and Creating the Recordset
The recordset is created by calling the
CreateRecordset method of the DataControl (or DataFactory) object, and providing it with a pre-filled structure of values that define the recordset. The recordset definition is made up of a variant-type array of field definitions, each of which is itself a variant-type array of field shape specifications:
Providing we've already instantiated a
DataControl object on our page, the following code can be used to create a simple recordset like that shown above. You'll notice we've switched to VBScript here. The CreateRecordset method requires a variant-type array of variant-type arrays, and JavaScript has some problems creating these in the exact format required by the method:
<SCRIPT LANGUAGE="VBScript">
Sub createNewRecordset()
' first define the individual fields: Dim arrField1(3)
Dim arrField2(3)
Dim arrField3(3)
' define field 1 shape arrField1(0) = "kBookCode" ' field name, to hold book code string
arrField1(1) = CInt(129) ' field type (adChar) arrField1(2) = CInt(4) ' field size 4 characters arrField1(3) = False ' field cannot contain null
' define field 2 shape arrField2(0) = "dReleaseDate" ' field name, to hold release date arrField2(1) = CInt(135) ' field type (adDBTimeStamp)
arrField2(2) = CInt(-1) ' field size default for TimeStamp arrField2(3) = True ' field can contain null
' define field 3 shape arrField3(0) = "tTitle" ' field name, to hold book title arrField3(1) = CInt(129) ' field type (adChar) arrField3(2) = CInt(50) ' field size 50 characters arrField3(3) = False ' field cannot contain null
' define and fill array holding the record definition: Dim arrRecord(2)
arrRecord(0) = arrField1
arrRecord(1) = arrField2
arrRecord(2) = arrField3
' now create empty recordset using the current DataControl object: Set objRecordset = dsoDataControl.CreateRecordset(arrRecord)
...
Filling and Using the New Recordset
Once we've created the recordset, we can use ordinary ADO techniques to fill it with values, and then assign it to the
RecordSource property of a DataControl DSO object:
' add new records and fill in the values: objRecordset.AddNew
objRecordset.Fields("kBookCode") = "1797"
objRecordset.Fields("dReleaseDate") = "1998-09-01 00:00:00"
objRecordset.Fields("tTitle") = "Professional Web Administration"
objRecordset.Update
...
... // etc. ... ' now connect the new recordset to the DataControl object: dsoDataControl.SourceRecordset = objRecordset
End Sub
</SCRIPT>
The samples for this book contain an example that uses the various RDS/ADO techniques to access a data store, and we also provide the Visual Basic source files to build a simple custom business object. You can download the samples from http://webdev.wrox.co.uk/books/1835/. The next screenshot shows what it looks like when you run it. The bottom half of the page contains a bound table, and the buttons in the top half of the page demonstrate how the recordset for this table can be created using the DataFactory object, a custom business component, and by creating a new local recordset:
Asynchronous Data Retrieval
By default, the
DataControl object fetches records from the server in asynchronous mode. In other words, control returns to the browser or client application immediately, instead of when all the records are available on the client. This is why we used the ondatasetcomplete event to run client-side code that accessed the recordset.
This behavior occurs because, by default, the
DataControl object's FetchOptions property is set to adFetchAsync (3) and the ExecuteOptions property is set to adExecAsync (2). To cause the records to be fetched synchronously, in which case the browser will appear to 'hang' until the records have arrived, we can set the FetchOptions property to adFetchUpFront (1) and the ExecuteOptions property to adExecSync (1).
One useful technique is to use
FetchOptions=adcFetchBackground (2), which allows the client-side code to start working with the records as soon as the first batch has arrived. When the client-side code accesses a record that has not been fetched, the DataControl object automatically fetches the appropriate batch from the server. However, bear in mind that this does not provide a truly disconnected recordset, as only part of it may be cached one the client.
The RDS DSO also provides an event named
onreadystatechanged. This is fired periodically as data is being fetched from the server, and we can query the ReadyState property to check current progress. This property returns adcReadyStateLoaded (2) when the query is still executing on the server and no rows have been fetched, adcReadyStateInteractive (3) once the first batch of rows have been fetched, and adcReadyStateComplete (4) when all rows have been fetched. Note that if an error occurs, the property still returns adcReadyStateComplete.
Using the ADO and RDS Named Constants in Script
Remember that script code does not have access to the ADO constant definitions. However, they are all available in files that are installed with the MS Data Access Components. For server-side programming, the files are
adovbs.inc (VBScript) and adojavas.inc (JScript), which are installed by default in your \Program Files\Common Files\System\ado\ folder. For client-side programming, the files are adcvbs.inc (VBScript) and adcjavas.inc (JScript), which are installed by default in your \Program Files\Common Files\System\msdac\ folder.
You can paste the individual constant declarations you want from the file into a page. If you are using them on the server, you can include the complete file in a page by copying it to a folder on your Web site and using the ASP Server-Side Include instruction:
In this chapter, we've looked at a range of techniques that provide
remote data access for working with data over the Web. The two main areas are the use of XML-formatted data, which is the future for all remote data access, and Remote Data Service (RDS) which currently provides disconnected data management directly.
While XML is the future, it is only just starting to appear in a workable form in current software. For this reason, we've concentrated mainly on the second option-RDS. This provides a range of ways that we can move recordsets from server to client over the Web, and then pass updates back to the server where they can be used to update the data store.
We also spent some time looking at other techniques for sending data to the client, such as in the form of text files to the
Tabular Data Control, and HTML pages to the MSHTML Browser Control. Neither of these can handle updates of the source data on the server, however.
And, because all these technologies revolve around caching and exposing a 'real' ADO recordset on the client, our ADO skills can be used to manipulate the data there, saving regular round-trips to the server each time the user wants to see the data displayed in a different way.
Overall, this chapter covered:
An overview of what remote data access is all about
A look at the different kinds of remote data access technologies
How we can implement remote data access in a Web page
How we can bind data to HTML controls in a Web page
Ways of creating remote recordsets directly using RDS and ADO
In the next chapter, we'll clear up a few of the loose ends that we haven't had the space to include in previous chapters. This includes the concepts of multi-dimensional data (ADO MD), and data-shaping.
