Code to Perform Bulk Operations using ADO and SQL

This topic shows how to perform bulk operations on profile data using ActiveX Data Objects (ADO) and SQL. New profiles are added to the UserObject profile, one of the properties is then updated, and finally, all inactive accounts are deleted. For more information about the UserObject profile, see UserObject Profile Schema.

  1. Create a SiteConfigReadOnly object and initialize it for the "Retail" site.

    Dim oSiteConfigReadOnly
    Set oSiteConfigReadOnly = Server.CreateObject _
     ("Commerce.SiteConfigReadOnly")
    oSiteConfigReadOnly.Initialize "Retail"
    
  2. Retrieve the connection string to the Profile Store and release the SiteConfigReadOnly object.

    Dim sCSConnect
    sCSConnect = oSiteConfigReadOnly.Fields("Biz Data Service") _
     .Value.Fields("s_CommerceProviderConnectionString").Value
    
    Set oSiteConfigReadOnly = Nothing
    
  3. Create an ADO Connection object and an ADO Command object and open a connection to the Profile Store.

    Dim oConnection As New ADODB.Connection
    Dim oCommand As New ADODB.Command
    Set oCommand.ActiveConnection = oConnection
    
  4. Create and execute a query to add new profiles to the UserObject profile. Assume the data is stored in an array with the following format (not all fields are shown).

    Dim sQuery
    Dim arNewCustomers()
    arNewCustomers(0, 0) = "logon_name"         ' Unicode string
    arNewCustomers(0, 1) = "user_type"          ' Integer
    arNewCustomers(0, 2) = "date_registered"    ' {d 'YYYY-MM-DD'}
    arNewCustomers(0, 3) = "org_id"             ' GUID
    
    For i = 1 To UBound(arNewCustomers)
       sQuery = "INSERT INTO Customers (u_logon_name, " & _
                                       "i_user_type, " & _
                                       "d_date_registered, " & _
                                       "g_org_id) " & _
                           "VALUES " & (arNewCustomers(i, 0) & ", " & _
                                        arNewCustomers(i, 1) & ", " & _
                                        arNewCustomers(i, 2) & ", " & _
                                        arNewCustomers(i, 3) & ")"
       oCommand.CommandText = sQuery
       oCommand.Execute()
    Next
    
  5. Check the procedure. If an INSERT method only partially succeeds against aggregated stores, the DeleteProfile method of the ProfileService object must be called to clean up the partially inserted data.

    Dim rsProfiles As ADODB.Recordset
    
    sQuery = "SELECT (u_logon_name, i_user_type, d_date_registered, " & _
                      g_org_id) FROM Customers " & _
             "WHERE date_created = @today " & _
             "ORDER BY d_date_registered"
    
    oCommand.CommandText = sQuery
    Set rsProfiles = oCommand.Execute()
    
  6. Change the organization ID for the new customers.

    sQuery = "UPDATE Customers " & _
             "SET g_org_id = '{2B544800-4169-4C49-8713-1B794078B4C9}'" & _         "WHERE date_created = @today"oCommand.CommandText = sQueryoCommand.Execute()
    
  7. Delete all inactive accounts.

    sQuery = "DELETE FROM Customers " &
             "WHERE i_account_status = 0"
    
    oCommand.CommandText = sQuery
    oCommand.Execute()
    
  8. Close the connection and release the objects.

    oConnection.Close
    Set rsProfiles = Nothing
    Set oConnection = Nothing
    Set oCommand = Nothing
    


All rights reserved.