Microsoft Dynamics CRM 3.0: Bulk Import

Inna Agranov
Microsoft Corporation

March 2007

Applies to:**
**   Microsoft Dynamics CRM 3.0

Requires:
   Microsoft Dynamics CRM 3.0
   Microsoft Visual Studio 2005

Summary: Learn how to programmatically import bulk data into Microsoft Dynamics CRM 3.0 system and custom entities. Also, learn best practices, such as creating multiple threads and using unsafe connection sharing, for better performance during bulk import using the Microsoft CRM 3.0 SDK.

Download the Visual Studio 2005 Visual C# code sample for this article: BulkImport.exe.

Content

Introduction
Installation and Setup
   Installing and adding Web References in the sample solution
   Building and running the application
   Verifying the results of the bulk import
Implementation
   Adding custom entities to the drop-down list box
   Importing data
   Creating multiple threads
   Using unsafe connection sharing
Performance Best Practices
Additional Information

Introduction

The Microsoft CRM 3.0 Bulk Import Wizard helps you to import bulk data into default and custom attributes of the Lead, Account, Contact and Campaign Response system entities. The data can be retrieved from a spreadsheet, Microsoft Word document, comma-separated values (.csv) text file, or a database.

This sample demonstrates how to write a custom application using the Microsoft CRM 3.0 Software Development Kit (SDK) to import bulk data into custom entities or entities not supported by the Bulk Import Wizard. The sample code uses a comma-separated values (.csv) text file to import rows of data representing entity instances into Microsoft CRM 3.0, one entity instance at a time.

Note   To create a csv-formatted file, in Microsoft Office Excel, on the File menu, click Save As to save a file in the .csv format.

For information about how to improve the performance of your application, see Performance Best Practices.

Installation and Setup

The following sections describe how to install, build, and run a Microsoft CRM 3.0 BulkImport sample application and verify the results of the bulk import.

Installing and adding Web References in the sample solution

1. Download and run the self-extracting BulkImport.exe file on your computer system. The link for downloading the samples is provided in the Summary section earlier in this article.

2. Create a folder named BulkImportVS2005CS on your computer and move the Visual Studio 2005 C# source and project files to this folder. The BulkImportVS2005CS folder should contain the following files:

   • BulkImport.sln – The solution file for the sample.
   • BulkImport.csproj – The project file for the sample.
   • BulkImportForm.cs – The WinForm application that imports the entity data.
   • BulkImportForm.Designer.cs – The Designer file for the WinForm.
   • BulkImportForm.resx – The file containing resources for the WinForm.
   • EntityListItem.cs – The class used to create list items for the Entity drop-down list box on the WinForm.
   • app.config – The file containing application settings and configuration.
   • AssemblyInfo.cs – The file containing the assembly information, located in the Properties subfolder.
   • Settings.Designer.cs – The auto-generated file, located in the Properties subfolder.
   • Settings.settings – The auto-generated file, located in the Properties subfolder.
   • Competitors.csv – The csv-formatted file containing the attributes schema names and data for multiple Microsoft CRM 3.0 Competitor entities instances.

   Note   In order for the sample code to run, the Competitors.csv file must specify five Competitor entity attributes.

3. In the BulkImportVS2005CS folder, double-click the BulkImport.sln file to start Visual Studio 2005.

4. In Solution Explorer, right-click the WebReferences folder, and then click Add Web Reference.

5. In the URL text box, type the URL to the CrmService.asmx:

   http://<servername:port>/mscrmservices/2006/CrmService.asmx
   

   Note: If you run the Microsoft CRM 3.0 BulkImport sample and Microsoft CRM 3.0 on the same server with a standard non-default TCP port 5555, use the following URL:

   https://localhost:5555/mscrmservices/2006/CrmService.asmx
   

6. Click Go.

7. After the Web service is found, in the Web reference name text box, change the text to CrmSdk.

8. Click Add Reference.

9. In Solution Explorer, right-click the WebReferences folder, and then click Add Web Reference.

10. In the URL text box, type the URL to the MetadataService.asmx:

    http://<servername:port>/mscrmservices/2006/MetadataService.asmx
    

    Note: If you run the Microsoft CRM 3.0 BulkImport sample and Microsoft CRM 3.0 on the same server with a standard non-default TCP port 5555, use the following URL:

    https://localhost:5555/mscrmservices/2006/MetadataService.asmx
    

11. Click Go.

12. After the Web service is found, in the Web reference name text box, change the text to MetadataService.

13. Click the Add Reference button.

Building and running the application

1. In Visual Studio 2005, build the BulkImport solution.

2. Browse to the BulkImportVS2005CS\bin\debug folder.

3. Double-click the BulkImport.exe executable file to start the application.

   The Bulk Import application is shown in Figure 1.

   

   Figure 1. The Bulk Import application (Click for larger image)

4. In the CRM Server Url text box, enter the name of the server and the port number: http://<servername:port>. If the Microsoft CRM 3.0 BulkImport sample and Microsoft CRM 3.0 run on the same server use the following URL: http://<localhost:port>. In the User ID and the Password text boxes, enter your User ID and Password.

   Note   If User ID and Password are left blank, the current user account information is used. If you do not want to log into Microsoft CRM 3.0 as the user who is logged into Windows, enter in the User ID and Password text boxes the Windows account information of a user who has rights to create Competitor entities instances in Microsoft CRM 3.0.

5. Click Connect. After the connection is established, the Entity drop-down list box is populated with the names of the following Microsoft CRM 3.0 entities: Account, Competitor, Contact, Lead, and Opportunity.

6. Select Competitor in the Entity drop-down list box.

7. Click Browse button to the right of the Import File text box and navigate to the Competitors.csv file.

8. Double-click the Competitors.csv file and observe the Field Mapping data grid being populated with the schema names, data types, and values from the first row of data in the Competitors.csv file.

9. Select a desired number of threads in the Concurrent Threads spin box.

10. Click Go to start importing the data.

Once the data is imported successfully, click OK in the Import Results message box.

Verifying the results of the bulk import

1. Start the Microsoft CRM 3.0 Web application.

2. Select Sales and navigate to the Competitors view.

3. Verify that the data from the Competitors.csv file was imported successfully as shown in Figure 2.

   

   Figure 2. The Competitors view (Click for larger image)

Implementation

The following sections describe how the BulkImport sample extends Microsoft CRM 3.0 to support custom entities and implement bulk data import using the DynamicEntity class. For more information, see Using DynamicEntity in the Microsoft CRM 3.0 SDK.

Adding custom entities to the drop-down list box

The BulkImport sample is implemented as a WinForm application. The user interface contains controls for entering information needed to connect to the Microsoft CRM 3.0 server and selecting entity types for which you want to import bulk data. The Browse button allows you to browse to the csv-formatted data file. The Entity drop-down list box contains the names of several Microsoft CRM 3.0 entities: Account, Competitor, Contact, Lead, and Opportunity. You can easily expand this list to include additional Microsoft CRM 3.0 system entities and your own custom entities by using and extending the following code:

// This code sample retrieves the entities from the Microsoft CRM 3.0 MetaBase and 
// loads the information into the entityComboBox.
private void LoadEntityDropdown()
{
    // Clear out any prior import information.
    ClearImportInformation(false);

    // Retrieve the entity metadata from the Microsoft CRM 3.0 MetaBase.
    Metadata md = 
    this._metadataService.RetrieveMetadata(MetadataFlags.EntitiesOnly);

    // Add the entities to the entity drop-down datasource.
    foreach (EntityMetadata em in md.Entities)
    {
        bool supportedEntity = false;
        
        // Add system entities here.
        if (em.Name == EntityName.account.ToString())
        {
            supportedEntity = true;
        }
        else if (em.Name == EntityName.competitor.ToString())
        {
            supportedEntity = true;
        }
        else if (em.Name == EntityName.contact.ToString())
        {
            supportedEntity = true;
        }
        else if (em.Name == EntityName.lead.ToString())
        {
            supportedEntity = true;
        }
        else if (em.Name == EntityName.opportunity.ToString())
        {
            supportedEntity = true;
        }
        else
        {
            // Add custom entities here.
            supportedEntity =
               (em.IsCustomizable == true && em.IsCustomEntity == true);
        }

        // Create a list item for each supported entity.
        if (supportedEntity)
        {
            this._crmEntities.Add(new EntityListItem(em.Name,
                                                     em.DisplayName));
        }
    }
    // Sort the list.
    this._crmEntities.Sort();

    // Bind the list to the entity drop-down list box.
    this.entityComboBox.DisplayMember = EntityListItem.DisplayMemberName;
    this.entityComboBox.ValueMember = EntityListItem.ValueMemberName;
    this.entityComboBox.DataSource = this._crmEntities;
}  

Importing data

The bulk data you are importing is stored in the Competitors.csv file. The first row in the file contains the schema names of the Competitor entity attributes. The subsequent rows contain data for multiple Competitor entities instances. The data is read by the application and loaded into the _importDataTable DataTable. Later, the data is added to the entityProperties ArrayList. Each instance of entityProperties contains attribute name/value pairs for a single Competitor entity instance.

The CrmSdk Web reference that you added in Step 5 of the Installing and adding Web References in the sample solution section contains class definitions for all system entities that are installed on the Microsoft CRM 3.0 server. To provide support for your custom entities, Microsoft CRM 3.0 uses the DynamicEntity class. For more information, see Using DynamicEntity to Access Entities at Runtime in the Microsoft CRM 3.0 SDK. Instantiate the newEntity entity as DynamicEntity. Assign the Properties property to entityProperties and the Name property to the name of the entity you selected in the drop-down list box, in this case, the Competitor entity.

The following code contains steps to import a single Competitor entity instance. You need to execute this code for every DataRowhttps://msdn2.microsoft.com/en-us/library/system.data.datarow.aspx in the _importDataTable to import all data from the Competitor.csv file into Microsoft CRM 3.0.

// Check if there is any data to import.
if (entityProperties.Count > 0)
{
    // Create the DynamicEntity object.
    DynamicEntity newEntity = new DynamicEntity();

    // Set the name of the entity type.
    newEntity.Name = this._selectedEntity.Name;

    // Set the properties of the entity.
    newEntity.Properties = 
       (Property[])entityProperties.ToArray(typeof(Property));

    // Create the target object.
    TargetCreateDynamic dynamicCreateTarget = new TargetCreateDynamic();
    dynamicCreateTarget.Entity = newEntity;

    // Create the request object.
    CreateRequest create = new CreateRequest();

    // Set the properties of the request object.
    create.Target = dynamicCreateTarget;

    // Execute the request.
    CreateResponse created = 
       (CreateResponse)this._crmService.Execute(create);

    // Verify whether the entity was created.
    return (created.id != Guid.Empty);
}
else
{
    return false;
}

Creating multiple threads

One way to improve the performance of the bulk import operation is to use multithreading in your application. Depending on the number of processors on your Microsoft CRM 3.0 server, you can use 1, 2, 3 or more concurrent threads to increase the throughput of your bulk operation. In this sample, the entire input data file is read into memory before the import operation begins. You may choose to use different partitioning schemes. For example, you can partition the data file into a number of sections equal to the number of concurrently running threads. Each thread will be responsible for processing and importing data from a particular section in the file.

The table below shows how the throughput changes depending on the number of threads running concurrently. The throughput is measured in a number of creates per seconds. A create means that an entity instance is created in the Microsoft CRM database.

Note   The throughput values shown in the table are specific to a particular Microsoft CRM 3.0 server. The throughput results are based on importing 114,066 bytes of data contained in the sample .csv file. The optimal number of threads for your server may differ from the values in this particular example.

The following code demonstrates how to implement multithreading for a bulk import operation. The RunImport method controls the data import. The RunImportDynamic method creates a thread for each concurrent thread specified in the Concurrent Threads spin box. The GetNextRowIndex method serializes the multiple threads and prevents the insertion of duplicate data or skipping rows (entity instances).

// Control the data import.
private void RunImport()
{
    this._recordCount = 0;
    this._importedCount = 0;

    this._currentRowIndex = 0;

    DateTime start, stop;
    TimeSpan duration;

    Thread [] workers = new Thread[(int)numThreads.Value];
    try
    {
        // Reset the progress bar.
        SetProgressBarValue(0);

        start = DateTime.Now;
        SetRemoteTBText (tbStartTime, start.ToString());
        // Create a worker thread for each concurrent thread 
        // specified in the Concurrent Threads spin box.
        for (int i = 0; i < workers.Length; i++)
        {
            ThreadStart ts_worker = new ThreadStart(this.RunImportDynamic);
            workers[i] = new Thread(ts_worker);
            workers[i].Name = "WorkerThread" + i.ToString();
            workers[i].Start();
        }

        for (int i = 0; i < workers.Length; i++)
        {
            workers[i].Join();
        }

        stop = DateTime.Now;
        SetRemoteTBText (tbEndTime, stop.ToString());

        duration = stop - start;

        double tput;
        tput = ((double)_importedCount) / duration.TotalSeconds;

        SetRemoteTBText (tbThroughput, tput.ToString());

        StreamWriter logWriter = null;
        if (tbResultsFile.Text != null)
        {
            try
            {
                logWriter = File.AppendText(@".\results.txt");
                logWriter.WriteLine("End Time: {0}, Concurrent Threads: {1}, Unsafe: {2}, Tput: {3}, Duration(s): {4}",
                    stop.ToString(), workers.Length, cbUseUnsafe.Checked ? "true" : "false", tput.ToString(), duration.TotalSeconds);
            }
            catch (Exception e)
            {
                tbResultsFile.Text = "Unable to save results: {0}" + e.Message;
            }
            finally
            {
                if (logWriter != null)
                {
                    logWriter.Flush();
                    logWriter.Close();
                }
            }
        }
    }
    finally
    {
        // Set the progress bar as complete.
        SetProgressBarValue(100);
        
        // Display the results of the import to the user.
        if ((this._recordCount > 0) && (this._recordCount == this._importedCount))
        {
            MessageBox.Show("The import was successful.  You should verify that all data has been imported.",
                "Import Results",
                MessageBoxButtons.OK);
        }
        else
        {
            MessageBox.Show("The import was not successful (one or more records did not import).  Check the layout of your import file and your import selections.",
                "Import Results",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation);
        }

        
        // Enable the import controls.
        ToggleImportControls(true);

        // Clear the import information.
        ClearImportInformation(true);
    }

    return;
}

// Serialize the multiple threads and prevent the insertion 
// of duplicate data or skipping rows (entity instances).
int GetNextRowIndex()
{
    int returnValue;
    lock (_rowIndexLock)
    {
        if (_currentRowIndex < this._importDataTable.Rows.Count)
        {
            returnValue = _currentRowIndex++;
        }
        else
        {
            returnValue = -1;
        }
    }
    return returnValue;
}

// Create a thread for each concurrent thread specified 
// in the Concurrent Threads spin box.
private void RunImportDynamic()
{
    int nextRow;
    CrmService _localService = new CrmService();
    _localService.Credentials = _crmService.Credentials;
    _localService.Url = _crmService.Url;
    if (cbUseUnsafe.Checked)
    {
        _localService.UnsafeAuthenticatedConnectionSharing = true;
        _localService.ConnectionGroupName = "default";
    }

    while ((nextRow = GetNextRowIndex()) >= 0)
    {
        ImportRecord(_importDataTable.Rows[nextRow], _localService);
    }

}

Using unsafe connection sharing

To increase the throughput of the bulk import operation further, you can enable unsafe connection sharing by selecting the Use Unsafe Authenticated Connection Sharing check box. Connection sharing is safe when you are running an application as a single user.

The table below shows how the data import throughput changes depending on the number of threads with unsafe connection sharing enabled.

Note   The throughput values shown in the table are specific to a particular Microsoft CRM 3.0 server. The throughput results are based on importing of 114,066 bytes of data contained in the sample .csv file. The optimal number of threads for your server may differ from the values in this particular example.

The following code shows how to enable unsafe connection sharing. To assure that all concurrent threads are running with the same set of credentials, set the ConnectionGroupName property to default.

// Control data import.
private void RunImportDynamic()
{
    int nextRow;
    CrmService _localService = new CrmService();
    _localService.Credentials = _crmService.Credentials;
    _localService.Url = _crmService.Url;
    if (cbUseUnsafe.Checked)
    {
        // Enable unsafe connection sharing.
        _localService.UnsafeAuthenticatedConnectionSharing = true;
        // Provide all threads with the same set of credentials
        _localService.ConnectionGroupName = "default";
    }

    while ((nextRow = GetNextRowIndex()) >= 0)
    {
        ImportRecord(_importDataTable.Rows[nextRow], _localService);
    }

}

Performance Best Practices

The following best practices will help you write better performing code using the Microsoft CRM 3.0 SDK.

Use multiple threads

Add threading support to your application to break the work up across multiple CPUs. This assumes that you are running the code on a multiprocessor system.

For more information, see Managed Threading in the .NET Framework Developer's Guide.

Use Integrated Windows authentication

For more information about Integrated Windows authentication (formerly known as NTLM authentication), see IIS Authentication.

Use connection sharing

This is also called unsafe connection sharing. Connection sharing is safe when you are running an application as a single user, for example, if you write a program to do bulk import for a custom entity. If your application will have multiple users, you can create a separate application pool for the program to run in to increase the measure of safety when using connection sharing.

Example

// Create the service.
CrmService _localService = new CrmService();
_localService.Credentials = _crmService.Credentials;
_localService.Url = _crmService.Url;

// Turn on unsafe connection sharing.
_localService.UnsafeAuthenticatedConnectionSharing = true;

// For a program with multiple users, create a new application pool
// rather than using the default.
_localService.ConnectionGroupName = "default";

For more information, see HttpWebRequest.UnsafeAuthenticatedConnectionSharing Property in the MSDN library.

Use common methods

The CrmService common methods are faster than using the CrmService.Executehttps://msdn2.microsoft.com/en-us/library/aa680902.aspx method with the corresponding message. The following table lists these methods and messages.

Use strong types

The DynamicEntity class is useful for when your code needs to work on entities and attributes that are not known at the time the code is written. However, this flexibility comes at a price. If your entities are already defined at code time, you should use the strong types provided for you in the Web Services Description Language (WSDL). For more information, see the SDK topic Using Dynamic Entities to Access Entities at Runtime.

Disable callouts

If possible, disable all registered callouts and workflows before running your application.

Limit data retrieved

When using the methods that retrieve data from the server, only retrieve the minimum amount of data needed by your application. This is done by specifying the column set, which is the set of entity attributes to retrieve.

When using the Retrieve method, use the columnSet parameter to specify the attributes you need.

When using the RetrieveMultiple method, specify the attributes to retrieve in the query using the QueryBase.ColumnSet field.

When using any message that uses the QueryExpression class to specify the query criteria, use the QueryBase.ColumnSet field. The following messages use QueryExpression:

• RetrieveByGroupResource
• RetrieveByResourceResourceGroup
• RetrieveByResourcesService
• RetrieveMultiple
• RetrieveParentGroupsResourceGroup
• RetrieveSubGroupsResourceGroup
• Rollup

Limit operations that cascade to related entities

When using Update, don’t set the ownerid attribute on an entity instance unless the owner has actually changed. Setting this attribute often requires changes to cascade to related entities, increasing the amount of time required for the update operation. For more information, see Cascading Rules in the Microsoft CRM 3.0 SDK.

Additional Information

For more information, see the Microsoft CRM 3.0 Software Development Kit (SDK).

For more information about improving the performance of Microsoft CRM 3.0, see Optimizing the Performance of Microsoft Dynamics CRM 3.0 White Paper.