Microsoft Remote Data ServiceMicrosoft Remote Data Service*
*Contents  *Index  *Topic Contents

Getting a Recordset to the Client

One of the most important features of Remote Data Service is data caching on the client. It reduces the number of requests for data between the client-side application components and the database server, which improves both the actual performance and the user’s perceived performance of the application.

There are three ways to pass a Recordset back from your server to the client with RDS. You can:

This section explains how to pass Recordset objects using any of these methods, and it also contains information on:

Getting a Recordset with the RDS.DataControl Object

You can open a disconnected Recordset by setting the RDS.DataControl object's Connect, Server, and SQL properties.

The following code shows how to set these properties at design time: 

<OBJECT CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID="ADC1">
	<PARAM NAME="SQL" VALUE="Select * from Products">
	<PARAM NAME="Connect" VALUE="DSN=AdvWorks;">
	<PARAM NAME="Server" VALUE="http://SalesWeb/">
</OBJECT>

Calling the Refresh method after setting these properties on the RDS.DataControl automatically calls the RDSServer.DataFactory object "behind the scenes," and Remote Data Service returns a Recordset object to the client. You don't have to actually write any code to use the RDSServer.DataFactory, but if you do want to do this, see "Getting a Recordset with the RDSServer.DataFactory Object."

Asynchronous Population

You can open and populate a disconnected Recordset object asynchronously. This improves performance by leaving the client free to execute other tasks while records are still being returned. Three properties of the RDS.DataControl object make this possible:

Here is a VBScript code snippet that shows the use of these properties: 

ADC1.Connect = "UID=sa;PWD=permission;DSN=Pubs"
ADC1.SQL = "select * from authors A1, authors A2, authors A3"
ADC1.Server = "http://SERVER_NAME"

ADC1.ExecuteOptions = adcExecAsync
ADC1.FetchOptions = adcFetchAsync

ADC1.Refresh

… … …
select case ADC1.ReadyState
	case adcReadyStateLoaded: MsgBox "Executing"
	case adcReadyStateInteractive: MsgBox "Fetching in background"
	case adcReadyStateComplete: MsgBox "All records fetched"
end select

Getting a Recordset with the RDSServer.DataFactory Object

Remote Data Service contains a server-side business object (ActiveX DLL) called RDSServer.DataFactory that sends commands to a data source and passes the results back across the Internet or an intranet for you. RDSServer.DataFactory is the default ActiveX DLL that you can use to provide live data on your Web page with little programming.

The following example shows how to call the RDSServer.DataFactory object from a VBScript Web page. You use the RDS.DataSpace object on the client to instantiate the RDSServer.DataFactory object on the server. 

<HTML>
<HEAD></HEAD>
<BODY>

<!-- RDS.DataControl -->
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID=ADC1>
</OBJECT>
<!-- RDS.DataSpace -->
<OBJECT ID="ADS1" WIDTH=1 HEIGHT=1
  CLASSID="CLSID:BD96C556-65A3-11D0-983A-00C04FC29E36">
</OBJECT>
.
.
.
<SCRIPT LANGUAGE="VBScript">
Option Explicit
Sub Window_OnLoad()
  Dim ADF1, myRS
  Set ADF1 = ADS1.CreateObject("RDSServer.DataFactory", _
  "http://<%=Request.ServerVariables("SERVER_NAME")%>")
  Set myRS = _
    ADF1.Query("DSN=pubs;UID=sa;PWD=permission;", _
    "Select * From Authors")
  ' Assign the returned recordset to SourceRecordset.
  ADC1.SourceRecordset = myRS
End Sub
</SCRIPT>
</BODY>
</HTML>

Security and your Web Server

If you use the RDSServer.DataFactory object on your Internet Web server, remember that doing so creates a potential security risk. External users who obtain valid data source name (DSN), user ID, and password information could write pages to send any query to that data source. If you want more restricted access to a data source, one option is to unregister and delete the RDSServer.DataFactory object (msadcf.dll), and instead use custom business objects with hard-coded queries. The next section describes how to do this.

Getting a Recordset with a Custom Business Object

If you don't want to use the RDSServer.DataFactory object to pass Recordset objects back to the client, you can create your own custom business object that will run on the server. The DLL (dynamic-link library) can be any generic Automation object created with Visual Basic®, Visual C++®, and so forth, or a server-side HTML page with Active Server Pages scripting code. Clients and Web front-end applications call the business objects to perform a particular function, and those middle-tier business objects in turn can communicate with back-end databases.

The custom DLLs could also contain methods that aren't provided by the simple RDSServer.DataFactory ActiveX DLL. These methods don't have to be related to data access — they could just encompass a business rule.

This section contains information on:

Writing Code to Pass Recordset Objects with a Custom ActiveX DLL

The following client VBScript code performs the same action as the previous RDSServer.DataFactory code, except that it uses a custom business object. You still use the RDS.DataSpace object on the client to instantiate the business object (in this case, MyCustomBusinessObject) on the server. 

<HTML>
<HEAD></HEAD>
<BODY>

<!-- RDS.DataControl -->
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID=ADC1>
</OBJECT>
<!-- RDS.DataSpace -->
<OBJECT ID="ADS1" WIDTH=1 HEIGHT=1
  CLASSID="CLSID:BD96C556-65A3-11D0-983A-00C04FC29E36">
</OBJECT>
.
.
.
<SCRIPT LANGUAGE="VBScript">
Option Explicit
Sub GetRecords()
  Dim objMyCustomBusinessObject, myRS
  Set objMyCustomBusinessObject = _
    ADS1.CreateObject("MyCustomBusinessObject", _
    "http://<%=Request.ServerVariables("SERVER_NAME")%>")
  ' Assume MyCustomBusinessObject has a method called
  ' GetData that takes connection string and SQL 
  ' parameters.
  Set myRS = _
    objCustomBusinessObject.GetData _
    ("DSN=pubs;UID=sa;PWD=permission;", _
    "Select * From Authors")
  ' Assign the returned recordset to SourceRecordset.
  ADC1.SourceRecordset = myRS
End Sub
</SCRIPT>
</BODY>
</HTML>

Assuming you use Visual Basic to create the MyCustomBusinessObject ActiveX DLL that is located on the middle tier, the code in the GetData method of the MyCustomBusinessObject class could look something like this. Notice that you can use ActiveX™ Data Objects (ADO) directly. 

' Returns an ADO resultset.
Public Function GetData(szCnStr As String, szSQL _
  As String) As Object

  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset

  cn.Open szCnStr
  ' The ADODB.Recordset should generate Recordset 
  ' objects that can be disconnected and later 
  ' reconnected to process batch updates.
  rs.CursorLocation = adUseClientBatch
  ' Using the Unspecified parameters, an ADO/R
  ' recordset is returned.
  rs.Open szSQL, cn, _
    adOpenUnspecified, adLockUnspecified, _
    adCmdUnspecified
  Set GetData = rs
End Function

Tips

Always place one simple method in your server component to test for minimum functionality before attempting to pass Recordset objects back.

Build a simple client application to test your server component before deploying and testing it with Internet Explorer.

It is easier to develop your application on a local test Web server. You will need to copy and register the .dll on the test server after each compile.

The DSN passed to your business object will need to be a System DSN on your server. If it doesn't exist or is set up improperly, your component will fail. It is a good idea to test the DSN on the server with another ODBC application such as MSQuery to make sure the DSN is set up properly.

Method names on custom business objects cannot exceed 255 characters. This allows compatibility across all RDS-supported protocols (HTTP, HTTPS, DCOM, and running in-process).

If you used Visual Basic to create a custom business object that uses early binding with the ADOR 1.0 type library, you should rebuild you custom business object to use the ADOR 1.5 type library.

Required Custom Business Object Registry Entry

To successfully launch a custom business object (DLL or EXE) through the Web server, the business object's ProgID must be entered into the registry as explained in this procedure. This RDS feature protects the security of your Web server by running only sanctioned executables. The default business object, RDSServer.DataFactory, is already fully registered.

To register a custom business object

  1. From the Start menu, click Run.

  2. Type RegEdit and click OK.

  3. In the Registry Editor, navigate to the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\Parameters\ADCLaunch registry key.

  4. Select the ADCLaunch key, and then on the Edit menu, point to New, and click Key.

  5. Type the ProgID of your custom business object and click Enter. Leave the Value entry blank.

Marking Business Objects as "Safe for Scripting"

To help ensure a secure Internet environment, you need to mark any business objects instantiated with the RDS.DataSpace object's CreateObject method as "safe for scripting." You need to ensure they are marked as such in the License area of the system registry before they can be used in DCOM.

To manually mark your business object as safe for scripting, create a text file with a .reg extension that contains the following text shown in capitals. The following two numbers enable the safe-for-scripting feature: 

[HKEY_CLASSES_ROOT\CLSID\<MyActiveXGUID>\Implemented Categories\{7DD95801-9882-11CF-9FA9-00AA006C42C4}]

[HKEY_CLASSES_ROOT\CLSID\<MyActiveXGUID>\Implemented Categories\{7DD95802-9882-11CF-9FA9-00AA006C42C4}]

where <MyActiveXGUID> is the hexadecimal GUID number of your business object. Save it and merge it into your registry by using the Registry Editor or double-clicking directly the .reg file in the Windows Explorer.

Business objects created in Visual Basic® 5.0 can be automatically marked as "safe for scripting" with the Application Setup Wizard.

To mark business objects safe for scripting in Visual Basic 5.0:

  1. Start the Visual Basic 5.0 Application Setup Wizard.

  2. On step one, choose your project, and then click Create Internet Download Setup.

  3. On step four, click Safety and select Safe for initialization and Safe for scripting.

  4. On the last step, the Application Setup Wizard creates an .htm and a .cab file. You can then copy these two files to the target computer and double-click the .htm file to load the page and correctly register the server.

  5. Because the business object will be installed in the Windows\System32\Occache directory by default, you should move it to the Windows\System32 directory and change HKEY_CLASSES_ROOT\CLSID\<MyActiveXGUID>\InprocServer32 registry key to match the correct path.

  6. If you find that your business object isn't working at this point, check the .inf file that is included in the \Support directory under the Application Setup Wizard installation directory. It should contain "RegisterServer=yes" and the [AddSafeToScript] section.

See Also The ActiveX SDK Web page at http://www.microsoft.com/intdev/sdk/ contains more information about this standard and how to implement it. The safe-for-scripting information is under the Signing/Marking Controls topic (signmark.htm).

Passing Parameters to Business Objects

Remote Data Service 1.5 supports passing of parameters to and from business objects (ActiveX components) by reference. This allows your business object to modify a referenced input parameter and return it as an "out" parameter back to the client code. This parameter can be passed in addition to a return value allowed by a function.

To use this feature, you need to be aware of how the business object accepts arguments so you can optimize the code for functionality and performance. If it is not important to return an extra output argument, you will want to specify that the method is accepting the argument by value. This is especially important in Microsoft Visual Basic because it accepts parameters by reference by default.

The following is a Visual Basic example of how you can accept parameters "by value" and "by reference": 

' This function returns one return value to the client.
Function TestByVal(ByVal arg1 As Variant) As Variant
  TestByVal = "A copy of a new return value."
End Function

' This function returns one return value and 
' one out parameter to the client.
Function TestByRef(ByRef arg1 As Variant) As Variant
  arg1 = "This value will be returned as an output parameter."
  TestByRef = "A copy of a new return value."
End Function

You also need to pay attention to the data type of the ByRef arguments. For example, VBScript does not let you specify the variable type, so you will always be passing a Variant. When using HTTP, RDS will allow you to pass a Variant to a method that expects a non-Variant if you invoke it with the CreateObject method of the RDS.DataSpace object. When using DCOM or an in-process server, you will need to make sure you match the parameter types to be the same on each side or you will receive a "Type Mismatch" error.

Client Impersonation and Security

If the Password Authentication property for your IIS Web server is set to Windows NT Challenge/Response, then business objects are invoked under the client's security context. This is a new feature in RDS 1.5 that allows Client Impersonation over HTTP. When working in this mode, the login to the Web server (IIS) is not anonymous but uses the user ID and password the client computer is running under. If the ODBC DSNs are set up to use Trusted Connection, then access to databases like SQL Server also happens under the client's security context. But this only works if the database is on the same computer as the IIS; the client credentials cannot be carried over to yet another computer.

For example, a client, John Doe, with userid="JohnD" and password="secret" is logged on a client computer. He runs a browser-based application that needs to access the RDSServer.DataFactory object to create an ADOR.Recordset by executing a SQL Query on the "MyServer" computer running IIS. MyServer is set up to use "Windows NT Challenge/Response" for Password Authentication, its ODBC DSN has "Use Trusted Connection" checked, and the server also contains the SQL Server data source. When a request is received on the Web server, it asks the client for the user ID and password. Thus the request is logged on MyServer as coming from "JohnD"/"Secret" instead of IUSER_MyServer (which is the default when Anonymous Password Authentication is on). Similarly, when logging on to SQL Server, "JohnD"/"Secret" is used.

Consequently, the IIS NT Challenge/Response authentication mode allows HTML pages to be created without the user being explicitly prompted for the user ID and password information needed to log on to the database. If the IIS Basic Authentication was being used, then this would be also be required.

Password Authentication

RDS 1.5 can communicate with an IIS Web server running in any one of the three Password Authentication modes: Anonymous, Basic, or NT Challenge/Response. These settings define how a Web server controls access through it, such as requiring that a client computer have explicit access privileges on the NT Web server.

Client-side Registry Entries for Business Objects with DCOM

Custom business objects need to ensure that the client side can map their program name (ProgId) to an identifier (CLSID) that can be used over DCOM. For this reason, the ProgID of the DCOM object must be in the client side registry and map to the class ID of the server-side business object. For the other supported protocols (HTTP, HTTPS, and in-process), this isn't necessary.

For example, if you expose a server-side business object called MyBObj with a specific class ID, for instance, "{00112233-4455-6677-8899-00AABBCCDDEE}", you should make sure the following entries are added to the client-side registry: 

[HKEY_CLASSES_ROOT]
\MyBObj
	\Clsid
	(Default) "{00112233-4455-6677-8899-00AABBCCDDEE}"

Transport Protocols

When a client-side component invokes a server-side component, there are a number of ways that you can use to send the request. These include:

When the client-side component and the server-side component reside on different computers (that is, you will use the HTTP, HTTPS, or DCOM protocol), a client-side proxy must be created for each server-side business object stub to ensure proper marshaling. How the proxy is created depends on which protocol you are using.

HTTP and HTTPS Protocols

If you use the HTTP or HTTPS protocol, client-side components can't access remote server-side components directly. Therefore, Remote Data Service provides a proxy creation method (CreateObject) on the client side. A business object proxy is created for every server-side business object invoked from the client side. This RDS mechanism takes care of packaging and receiving remote application parts. This means that you don't have to write a lot of code to use custom business objects over HTTP or HTTPS.

Business object proxies are Automation objects and can be called from within Visual Basic® and Visual C++® applications, or any application that enables ActiveX™ technology. No type checking is done on the client side, instead RDS assumes that each method supports a variable number of variant arguments. An instance of the business object is created on the server-side only when the first method call is made, so no round trip is made just to instantiate the object.

When you use HTTP, the business object proxy packages and unpackages the parameters it sends and receives in MIME format. Each argument is packaged into a separate MIME subpart. The proxy can handle all Automation data types and ADODB.Recordset and ADOR.Recordset objects. The proxy translates the method invocation into HTTP Post format, which it passes to the Advanced Data ISAPI DLL (ADISAPI) on the Web server.

DCOM Protocol

You can also implement Remote Data Service applications on an intranet without using HTTP. If the client-side and server-side components are on different computers, you can use DCOM to marshal the interfaces and method arguments across the network. When you do this, the business object proxies and ADISAPI components are replaced, respectively, by COM proxies and stubs.

Running In-Process

If your client-side and server-side components reside on the same computer, they can access each other directly. There is no need for a proxy-stub mechanism.

Connection Pooling

To improve the speed with which you open Recordset objects and establish connections, use:

The Connection Pooling Options

If you're using an ODBC data source, you can use the Connection Pooling option in Internet Information Server (IIS) to achieve high performance handling of client load. Connection Pooling is a resource manager for connections, maintaining the open state on frequently used connections.

To enable Connection Pooling, refer to the Internet Information Server documentation.

Please note that enabling Connection Pooling may subject the Web server to other restrictions, as noted in the Microsoft Internet Information Server documentation.

Microsoft SQL Server — Performance and Stability with Connection Pooling

To ensure connection pooling is stable and provides additional performance gains, you must configure Microsoft SQL Server to use the TCP/IP Socket network library.

To do this, you need to:

Configuring the SQL Server Computer to Use TCP/IP Sockets

On the SQL Server computer, run the SQL Server Setup program so that interactions with the data source use the TCP/IP Socket network library.

To specify the TCP/IP Socket network library on the SQL Server computer

  1. From the Start menu, point to Programs, point to Microsoft SQL Server 6.5, then click SQL Setup.

  2. Click Continue twice. The Microsoft SQL Server 6.5 — Options dialog box appears.

  3. Select Change Network Support, and click Continue.

  4. Make sure the TCP/IP Sockets check box is selected, and click OK.

  5. Click Continue to finish, and exit setup.

Configuring the Web Server to Use TCP/IP Sockets

There are two options for configuring the Web server to use TCP/IP Sockets. What you do depends on whether:

If all SQL Servers are accessed from the Web server, you need to run the SQL Server Client Configuration Utility on the Web server computer. The following steps change the default network library for all SQL Server connections made from this IIS Web server to use the TCP/IP Sockets network library.

To configure the Web server (all SQL Servers)

  1. From the Start menu, point to Programs, point to Microsoft SQL Server 6.5, and then click SQL Client Configuration Utility.

  2. Select the Net Library tab.

  3. In the Default Network box, select TCP/IP Sockets.

  4. Click Done to save changes and exit the utility.

If a specific SQL Server is accessed from a Web server, you need to run the SQL Server Client Configuration Utility on the Web server computer. To change the network library for a specific SQL Server connection, on the Web server computer, configure the SQL Server Client software as follows.

To configure the Web server (a specific SQL Server)

  1. From the Start menu, point to Programs, Microsoft SQL Server 6.5, and then click SQL Client Configuration Utility.

  2. Select the Advanced tab.

  3. In the Server box, type the name of the server to connect to using TCP/IP Sockets.

  4. In the DLL Name box, select TCP/IP Sockets.

  5. Click Add/Modify. All data sources pointing to this server will now use TCP/IP Sockets.

  6. Click Done.

Microsoft Transaction Server Resource Dispensers

In a three-tiered environment, clients can share database connections if you use Microsoft Transaction Server on the middle tier. You can use the RDSServer.DataFactory object or create an ActiveX component DLL that can set up ODBC connections for clients to share; the sharing mechanism comes into play when you run the RDSServer.DataFactory or custom business object in the Transaction Server run-time environment. Instead of using thousands of database connections, you could get away with hundreds, still supporting thousands of clients. This is a feature of the ODBC resource dispenser in Microsoft Transaction Server.

See Also For more information about using Transaction Server, see "Running Business Objects in Transaction Server" or visit the Transaction Server Web site (http://www.microsoft.com/transaction/).

Running Business Objects in Microsoft Transaction Server

Business objects can be executable files (.exe) or dynamic-link libraries (.dll). The configuration you use to run the business object depends on whether the object is a .dll or .exe file:

By running the RDSServer.DataFactory object or your custom business object in the MTS run-time environment, you can also boost your performance and scalability by using the MTS resource dispenser. Because these business objects call ADO, which indirectly calls ODBC, you can take advantage of the MTS ODBC resource dispenser.

Resource dispensers automatically pool and recycle resources. Therefore, when RDSServer.DataFactory or your custom business object releases a database connection, the connection is returned to a pool. When a method is called to create a connection again, it requests the same database connection. Instead of creating a new connection, the ODBC resource dispenser recycles the pooled connection, which saves time and server resources.

Note When business objects on the middle tier are implemented as Microsoft Transaction Server components (using GetObjectContext, SetComplete, and SetAbort), they can use Transaction Server context objects to maintain their state across multiple client calls. This scenario is possible with DCOM, which is typically implemented between trusted clients and servers (an intranet). In this case, the RDS.DataSpace object and CreateObject method on the client side are replaced by the transaction context object and CreateInstance method (provided by the ITransactionContext interface), implemented by Microsoft Transaction Server.

Enabling a DLL to Run on DCOM

The following steps outline how to enable a business object DLL to use both DCOM and Internet Information Server (HTTP) via Microsoft Transaction Server.

  1. Create a new empty package in the Transaction Server Explorer.

    You will use the Transaction Server Explorer to create a package and add the DLL into this package. This makes the DLL accessible through DCOM, but it removes the accessibility through IIS. (If you check in the registry for the DLL, the Inproc key is now empty; setting the Activation attribute, explained later in this topic, adds a value in the Inproc key.)

  2. Install a business object into the package.

    -or-

    Import the RDSServer.DataFactory object into the package.

  3. Set the Activation attribute for the component to In the creator's process.

    To make the DLL accessible through DCOM and IIS on the same computer you must set the component's Activation attribute in the Microsoft Transaction Server Explorer. After you set the attribute to In the creator's process, you will notice that an Inproc server key in the registry has been added that points to a Microsoft Transaction Server surrogate DLL.

See Also For more information about Transaction Server and how to perform these steps, visit the Transaction Server Web site at http://www.microsoft.com/transaction/ or refer to the Microsoft Transaction Server documentation.

Up Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.