Getting a Recordset to the Client

One of the most important features of Remote Data Services is its in-memory data caching on the client. Using disconnected recordsets reduces the number of requests for data between the client-side application components and the database server. It also makes data immediately available to the client-side application logic, without the application having to wait for data to travel across the network.

There are three ways to pass a recordset back from your server to the client with Remote Data Services. You can:

This section explains how to pass recordsets using either of these methods, and it also contains information on:

Getting a Recordset with the AdvancedDataControl Object

You can open a disconnected recordset by setting the AdvancedDataControl 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 these properties on the AdvancedDataControl automatically calls the AdvancedDataFactory object "behind the scenes" and Remote Data Services returns a recordset to the client. You don't have to actually write any code to call AdvancedDataFactory, but if you do want to do this, see "Getting a Recordset with the AdvancedDataFactory Object." You might want to use the AdvancedDataFactory object (or a custom business object) if you want to programmatically manipulate the recordset directly.

Note You can also open a disconnected recordset by using the ADO ActiveConnection or ConnectionString property. For more information, see the ADO documentation.

Asynchronous Population

You can open and populate a disconnected recordset asynchronously by using the following properties of the AdvancedDataControl object:

The Cancel method cancels the currently running asynchronous execution.

Getting a Recordset with the AdvancedDataFactory Object

Remote Data Services contains a server-side business object (ActiveX DLL) called AdvancedDataFactory that sends SQL statements to a DBMS, and passes the results back across the Internet or an intranet for you. AdvancedDataFactory is the default ActiveX DLL that you can use to provide live data on your Web page, but with little programming.

The following example shows how to call the AdvancedDataFactory object from a VBScript Web page. You use the AdvancedDataSpace object on the client to instantiate the AdvancedDataFactory object on the server.

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

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

Security and your Web Server

If you use the AdvancedDataFactory object on your Internet Web server, please be advised of the following 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 a more restricted access to a data source, you could unregister and delete the AdvancedDataFactory object (msadcf.dll), and instead use custom business objects with hard-coded queries.

Getting a Recordset with a Custom ActiveX DLL

If you don't want to use the AdvancedDataFactory object to pass recordsets back to the client, you can create your own custom ActiveX DLL that will run on the server. The DLL can be any generic Automation object created with Visual Basic®, Visual C++®, 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 are not provided by the simple AdvancedDataFactory ActiveX DLL. These methods do not have to be related to data access — they could just encompass a business rule.

This section contains information on:

Writing Code to Pass Recordsets with a Custom ActiveX DLL

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

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

<!-- AdvancedDataControl -->
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID=ADC1>
</OBJECT>
<!-- AdvancedDataSpace -->
<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")
  MsgBox myRS.Fields("au_lname")
  ' 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 would 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 recordsets 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 your test Web server. If you develop it elsewhere you will need to copy and register the .dll on the test server after each compile.

The DSN passed to your ActiveX DLL will need to be a registered System DSN on your server. If it does not 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 supported Remote Data Services protocols (HTTP, HTTPS, DCOM, and 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 ensures the security of your Web server. The default business object, AdvancedDataFactory, 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 press Enter. Leave the Value entry blank.

You should also make sure that your createable object has launch rights on your server. You can do this by making the following registry entry.

REGEDIT4
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC
  \Parameters\ADCLaunch\ProgID]

For added security, you may also want to enhance DCOM security for .exe files by setting the Default Authentication Level to Packet. This configures Windows NT to check each network packet of information for proper authentication rather than checking authentication just at connect time.

To set the Default Authentication Level to Packet

  1. On your Windows NT® Server Web server computer, from the Start menu, click Run.

  2. Type DCOMCNFG, and click OK to start the Distributed COM Configuration Properties utility.

  3. Select the Default Properties tab.

  4. In the Default Authentication Level list, select Packet, and click OK.

You can also use the Distributed COM Configuration Properties utility to configure Access Permission settings to restrict group or individual launch permissions for .exe files. For more information, see the Help file for that utility (default installation location is C:\winnt\help\DCOMCNFG.hlp).

Marking Business Objects as "Safe for scripting"

To help ensure a secure Internet environment, you need to mark any client-side business objects instantiated with the AdvancedDataSpace 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:

REGEDIT4

[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 choose 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. Since 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 is not 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).

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 is not 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}"

Custom Business Object Launch Parameters

Custom business objects need to conform to the following restrictions when being created, or when being marshaled via HTTP or HTTPS.

Object invocations cannot include:

Transport Protocols

When a client-side component invokes a server-side component, there are a number of protocols that you can use to send the request. These protocols 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 Services provides a proxy creation mechanism (CreateObject) on the client side. A business object proxy is created for every server-side business object invoked from the client side. This Remote Data Services 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 Remote Data Services 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 Services 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.

In-Process Protocol

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

Connection Pooling

You can improve the speed with which you open recordsets and establish connections by using:

Using the IIS Connection Pooling Option

If you're using SQL Server, 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.

Please note that enabling Connection Pooling may subject the Web server to other restrictions. For more information on those restrictions, see the IIS documentation.

To enable Connection Pooling

  1. From the Start menu, click Run. On the command line, type RegEdit and click OK.

  2. In the Registry Editor, navigate to the following key:

    HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\ W3SVC\ASP\Parameters

  3. Double-click the StartConnectionPool entry in the right pane. In the Edit String dialog box, change the value 0 (Off) to 1 (On).

  4. Click OK to save the registry changes.

SQL Server — Performance and Stability with Connection Pooling

To ensure connection pooling is stable and provides additional performance gains, you must configure 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.

Using the MTS 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 AdvancedDataFactory 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 AdvancedDataFactory 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 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 AdvancedDataFactory object or your custom business object in the MTS run-time environment, you can also boost your performance and scalability. 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 AdvancedDataFactory 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 AdvancedDataSpace 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 AdvancedDataFactory object into the package.

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

    To get 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 read the Transaction Server documentation.


© 1997 Microsoft Corporation. All rights reserved. Terms of Use.