-- text figures

Figure 2 UDA Components

Component Description

ActiveX Data Objects Application-level programming interface to data and information. ADO supports a variety of development needs including front-end database clients and middle-tier business objects using applications, tools, languages, or browsers.

Remote Data Services Previously known as Active Data Connector, RDS supports client-side caching, updateable data, and data-aware controls.

OLE DB System-level programming interface to data across the enterprise. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data (not just relational).

Open Database Connectivity The established standard for accessing relational data on many platforms.

Figure 6 OLE DB Components

Component Description

Enumerator Enumerators search for available data sources and other enumerators. Consumers that are not customized for a particular data source use enumerators to search for a data source to use.

Data Source Data Source objects contain the machinery to connect to a data source, such as a file or a DBMS. They are a factory for sessions.

Session Sessions provide a context for transactions and can be implicitly or explicitly transacted. A single Data Source object can create multiple sessions. Sessions are a factory for transactions, commands, and rowsets.

Transaction Transaction objects are used when committing or aborting nested transactions at other than the lowest level.

Command Commands execute a text command, such as a SQL statement. If the text command specifies a rowset, such as a SQL SELECT statement, the command is a factory for the rowset. A single session can create multiple commands.

Rowset Rowsets expose data in tabular format. A special case of a rowset is an index. Rowsets can be created from the session or the command.

Error Errors can be created by any interface on any OLE DB object. They contain additional information about an error, including an optional custom Error object.

Figure 8 OLE DB Provider Requirements

Object Interface

DataSource IDBCreateSession

IDBInititalize

IDBProperties

IPersist

Session IGetDataSource

IOpenRowset

ISessionProperties

Rowset IAccessor

IConvertType

IColumnsInfo

IRowset

IRowsetChange

IRowsetIdentity

IRowsetInfo

Figure 9 Execute a Command

/**********************************************
* Execute a Command and retrieve a Rowset
***********************************************/
HRESULT myCommand
    (
    IDBInitialize*  pIDBInitialize, // [in]
    IRowset**       ppIRowset       // [out]
    ) 
    {
    IDBCreateSession*   pIDBCreateSession;
    IDBCreateCommand*   pIDBCreateCommand;
    IRowset*            pIRowset;
    ICommandText*       pICommandText;
    LPCTSTR             wSQLString = 
                            OLESTR("SELECT CompanyName, City, Phone, Fax")
                            OLESTR(" FROM Customers")
                            OLESTR(" ORDER BY CompanyName, City");
    LONG                cRowsAffected;

    // Get the DB Session object
    pIDBInitialize->QueryInterface(IID_IDBCreateSession, 
                                   (void**) &pIDBCreateSession);

    // Create the session, getting an interface for command creation
    pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, 
                                     (IUnknown**) &pIDBCreateCommand);
    pIDBCreateSession->Release();
 
    // Create the command object
    pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, 
                                     (IUnknown**) &pICommandText);
    pIDBCreateCommand->Release();

    // The command requires the actual text as well as an indicator of its 
    // language and dialect
    pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);

    // Execute the command
    pICommandText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, 
                           (IUnknown**) &pIRowset);
    pICommandText->Release();

    *ppIRowset = pIRowset;
    
    return (0);
    }

Figure 11 AddNewX

Public Sub AddNewX()    
    
    Dim cnn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset    
    Dim strCnn As String    
    Dim strID As String
    Dim strFirstName As String    
    Dim strLastName As String
    Dim booRecordAdded As Boolean    
    
    ' Open a connection.
    Set cnn1 = New ADODB.Connection    
    strCnn = "driver={SQL Server};server=srv;" & _
        "uid=sa;pwd=;database=pubs"    
    cnn1.Open strCnn            
    
    ' Open Employee table.
    Set rstEmployees = New ADODB.Recordset    
    rstEmployees.CursorType = adOpenKeyset
    rstEmployees.LockType = adLockOptimistic
    rstEmployees.Open "employee", cnn1, , , adCmdTable    
    
    ' Get data from the user.
    strID = Trim(InputBox("Enter employee ID:"))
    strFirstName = Trim(InputBox("Enter first name:"))
    strLastName = Trim(InputBox("Enter last name:"))
    
    ' Proceed only if the user actually entered something
    ' for both the first and last names.
    If (strID <> "") And (strFirstName <> "") And (strLastName <> "") Then
        rstEmployees.AddNew        
        rstEmployees!emp_id = strID
        rstEmployees!fname = strFirstName        
        rstEmployees!lname = strLastName
        rstEmployees.Update        
        booRecordAdded = True        
        
        ' Show the newly added data.
        MsgBox "New record: " & rstEmployees!emp_id & " " & _
            rstEmployees!fname & " " & rstEmployees!lname    
    Else
        MsgBox "Please enter an employee ID, first name, and last name."
    End If            

    ' Close recordset and connection
    rstEmployees.Close    
    cnn1.Close

End Sub

Figure 12 AddNew.asp

<!— #Include file="ADOVBS.INC" —>
<% Language = VBScript %>

<HTML>
<HEAD>
<TITLE>ADO 1.5 Open Method</TITLE>
</HEAD>

<BODY> 

<FONT FACE="MS SANS SERIF" SIZE=2>
<Center>
<H3>ADO AddNew Method</H3>

<!— ADO Connection Object used to create recordset—>
<% 
'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
%>

<!— If this is the first time the page is opened, the Form 
collection will be empty when data is entered run AddNew method—>
<% 
If Not IsEmpty(Request.Form) Then
    If Not Request.Form("CompanyName") = "" Then        
        RsCustomerList.AddNew
        RsCustomerList("CompanyName") = Request.Form("CompanyName")
        RsCustomerList("ContactLastName") = Request.Form("LastName")
        RsCustomerList("ContactFirstName") = Request.Form("FirstName")
        RsCustomerList("PhoneNumber") = Request.Form("PhoneNumber")
        RsCustomerList("City") = Request.Form("City")
        RsCustomerList("StateOrProvince") = Request.Form("State")
        RsCustomerList.Update        
        RsCustomerList.MoveFirst            
    End If
End If
%>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!— BEGIN column header row for Customer Table—>
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>

<!—Display ADO Data from Customer Table One row on each pass through recordset—>
<% Do While Not RsCustomerList.EOF %>  
  <TR><TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RSCustomerList("CompanyName")%>     
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>    
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("ContactLastName") & ", " %> 
      <%= RScustomerList("ContactFirstName") %>     
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>    
  <FONT STYLE="ARIAL NARROW" SIZE=1>
     <%= RScustomerList("PhoneNumber")%>    
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>    
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("City")%>     
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>    
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("StateOrProvince")%>     
  </FONT></TD></TR> 

<!— Next Row = Record Loop and add to row html table—>
<% 
RScustomerList.MoveNext 
Loop 
%>

</TABLE><HR>

<!— Form to enter new record posts variables back to this page —>
<Table>
<Form Method = Post Action="AddNew.asp" Name=Form>
<TR><TD><P>Company Name:</TD>
<TD><Input Type="Text" Size="50" Name="CompanyName" Value = ""></P></TD>
<TR><TD><P>Contact First Name:</TD>
<TD><Input Type="Text" Size="50" Name="FirstName" Value = ""></P></TD>
<TR><TD><P>Contact Last Name:</TD>
<TD><Input Type="Text" Size="50" Name="LastName" Value = ""></P></TD>
<TR><TD><P>Contact Phone:</TD>
<TD><Input Type="Text" Size="50" Name="PhoneNumber" Value = ""></P></TD>
<TR><TD><P>City:</TD>
<TD><Input Type="Text" Size="50" Name="City" Value = ""></P></TD>
<TR><TD><P>State / Province:</TD>
<TD><Input Type="Text" Size="5" Name="State" Value = ""></P></TD>
<TR><TD><Input Type="Submit" Value="Add New "><Input Type="Reset" Value="Reset Form">
</Form></Table></Center></FONT>

<%'Show location of DSN data source
Response.Write(OBJdbConnection)
%>

<Script Language = "VBScript">
Sub Form_OnSubmit
    MsgBox "Sending New Record to Server",,"ADO-ASP _Example"End Sub

</Script>
</BODY>
</HTML>

Figure 14 RDS Code Sample

<Center><H2>API Code Examples RDS 1.5</H2>
<HR><BR>
<H3>Remote Data Service</H3>
<Object CLASSID="clsid:AC05DC80-7DF1-11d0-839E-00A024A94B3A"
    CODEBASE="http://<%=Request.ServerVariables("SERVER_NAME")%>/MSADC/Samples/Sheridan.cab"
    ID=GRID1 
        datasrc=#ADC 
        HEIGHT=125 
        WIDTH=495>
    <PARAM NAME="AllowAddNew"   VALUE="TRUE">
    <PARAM NAME="AllowDelete"   VALUE="TRUE">
    <PARAM NAME="AllowUpdate"   VALUE="TRUE">
</OBJECT>
<!— Remote Data Service with Parameters set at Design Time —>
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33"
    ID=ADC>
    <PARAM NAME="SQL" VALUE="Select * from Employee for browse">
    <PARAM NAME="SERVER" VALUE="http://<%=Request.ServerVariables("SERVER_NAME")%>">
    <PARAM NAME="CONNECT" VALUE="dsn=ADCDemo;UID=ADCDemo;PWD=ADCDemo;">
</OBJECT><BR><HR></Center>

Component	Description
ActiveX Data Objects	Application-level programming interface to data and information. ADO supports a variety of development needs including front-end database clients and middle-tier business objects using applications, tools, languages, or browsers.
Remote Data Services	Previously known as Active Data Connector, RDS supports client-side caching, updateable data, and data-aware controls.
OLE DB	System-level programming interface to data across the enterprise. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data (not just relational).
Open Database Connectivity	The established standard for accessing relational data on many platforms.

Component	Description
Enumerator	Enumerators search for available data sources and other enumerators. Consumers that are not customized for a particular data source use enumerators to search for a data source to use.
Data Source	Data Source objects contain the machinery to connect to a data source, such as a file or a DBMS. They are a factory for sessions.
Session	Sessions provide a context for transactions and can be implicitly or explicitly transacted. A single Data Source object can create multiple sessions. Sessions are a factory for transactions, commands, and rowsets.
Transaction	Transaction objects are used when committing or aborting nested transactions at other than the lowest level.
Command	Commands execute a text command, such as a SQL statement. If the text command specifies a rowset, such as a SQL SELECT statement, the command is a factory for the rowset. A single session can create multiple commands.
Rowset	Rowsets expose data in tabular format. A special case of a rowset is an index. Rowsets can be created from the session or the command.
Error	Errors can be created by any interface on any OLE DB object. They contain additional information about an error, including an optional custom Error object.

Object	Interface
DataSource	IDBCreateSession
	IDBInititalize
	IDBProperties
	IPersist
Session	IGetDataSource
	IOpenRowset
	ISessionProperties
Rowset	IAccessor
	IConvertType
	IColumnsInfo
	IRowset
	IRowsetChange
	IRowsetIdentity
	IRowsetInfo