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) = "johndoe"             ' Unicode string
   arNewCustomers(0, 1) = 1                     ' Integer
   arNewCustomers(0, 2) = {d '2002-01-31'}      ' {d 'YYYY-MM-DD'}
   arNewCustomers(0, 3) = {F8677EA7-1A7E-4969-8771-F917C4EA96C8} ' 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 = {d '2003-01-31'}"" & _
            "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 = {d '2003-01-31'}"" & _
   
   oCommand.CommandText = sQuery
   oCommand.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