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.
Create a SiteConfigReadOnly object and initialize it for the "Retail" site.
Dim oSiteConfigReadOnly Set oSiteConfigReadOnly = Server.CreateObject _ ("Commerce.SiteConfigReadOnly") oSiteConfigReadOnly.Initialize "Retail"
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
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
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
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()
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()
Delete all inactive accounts.
sQuery = "DELETE FROM Customers " & "WHERE i_account_status = 0" oCommand.CommandText = sQuery oCommand.Execute()
Close the connection and release the objects.
oConnection.Close Set rsProfiles = Nothing Set oConnection = Nothing Set oCommand = Nothing