How to Retrieve a List of Profiles Using the CSOLEDB Provider

The OLE DB Provider for Commerce (CSOLEDB provider) provides a flexible way to retrieve data from Commerce Server Core Systems by using SQL syntax. You can construct a command string that specifies the criteria for generating profiles and send it to the CSOLEDB provider. For example, you can retrieve all profiles associated with e-mail addresses that end in "microsoft.com".

After you retrieve data through the CSOLEDB provider, you iterate through each record to retrieve the user_id field. Your application uses this field to make additional calls.

To retrieve a list of profiles using the CSOLEDB provider

1. Create a string that contains the SQL statement to execute through the CSOLEDB provider. Make sure that your statement contains the user_id column.

   Note

   Your statement must comply with the SQL query syntax accepted by the CSOLEDB provider. For more information about the correct SQL query syntax to use, see SQL Queries Supported by the OLE DB Provider for Commerce Server.

2. Create an instance of the ProfileContext run-time object.

3. Create a new RecordsetClass object that will hold all the profiles that are selected based on criteria that you specify in the command string.

4. Open the RecordsetClass object by calling the Open method and passing it the CSOLEDB object that is exposed by the ProfileContext run-time object.

   If any records meet your criteria, the RecordsetClass object will contain rows.

5. Iterate through each row and retrieve the user_id field. This field uniquely identifies a profile and you can use it to perform any action that you want for a specific profile. For example, you can use the field to retrieve profile properties and values, to delete profiles, and to perform other actions.

Example

The following example creates a procedure called GetProfiles that accepts a single argument called eMailSuffix. The eMailSuffix argument retrieves values by using the CSOLEDB provider. The code constructs a SQL query that uses the SQL like keyword to search for email_address fields that contain the eMailSuffix value. An example of the value expected for eMailSuffix would be "microsoft.com".

private void GetProfiles(string eMailSuffix)
{
    // Construct a SQL string.
    string cmdText = "SELECT GeneralInfo.user_id FROM UserObject WHERE GeneralInfo.email_address like '%" + eMailSuffix + "'";

    // Get the Profiles run-time object.
    ProfileContext ctxt = CommerceContext.Current.ProfileSystem;

    // Create a new RecordsetClass object.
    ADODB.RecordsetClass rs = new ADODB.RecordsetClass();

    try
    {
        // Open a RecordsetClass instance by executing the SQL statement to the CSOLEDB provider.
        rs.Open(
        cmdText,
        ctxt.CommerceOleDbProvider,
        ADODB.CursorTypeEnum.adOpenForwardOnly,
        ADODB.LockTypeEnum.adLockReadOnly,
        (int)ADODB.CommandTypeEnum.adCmdText);

        // Iterate through the records.
        while (!rs.EOF)
        {
            // Write out the user_id value.
     Console.WriteLine(rs.Fields["GeneralInfo.user_id"].Value.ToString());

            // Move to the next record.
            rs.MoveNext();
        }
    }
    finally
    {
        Marshal.ReleaseComObject(rs);
    }
}

The following code calls the GetProfiles procedure to retrieve email_address fields that contain the string "microsoft.com":

GetProfiles("microsoft.com");

Compiling the Code

To compile the code, you must include the following namespace directives:

using System;
using Microsoft.CommerceServer.Profiles;
using Microsoft.CommerceServer.Runtime;
using Microsoft.CommerceServer.Runtime.Profiles;

See Also

Other Resources

Profiles System and OLE DB Provider

How to Delete Users Based on Criteria

Profiles Concepts and Tasks