Sitecore – Custom Provider to integrate External SQL Server data

Here is the solution I found to integrate external SQL data into Sitecore. Although the data loads into Sitecore, it is extremely slow. I ended up organizing the data alphabetically, which helped.

Follow the steps below:

1. Download the NorthwindDataProvider source from HERE.
2. Create a template for the external SQL data you are trying to use.
3. Modify the NortwindDataProvider, by commenting ModifyBase(); in the constructor as shown below:

         #region ctor
   
         public NorthwindDataProvider(
          string connection,  // Connection string
          string table,       // Table name
          string fieldsNames,  // Fields name divided by pipe separator
          string idField,     // ID Field name (primary key)
          string nameField,   // Name field name (name for content editor)
          string filter,      // "Where" clause
          string templateID,// Template name
          string parentItemID, //Parent item ID
          string hostDatabase // Database into which we integrate
          )
         {
            prefix = this.ToString();
   
            this.connectionString = connection;
            this.table = table;
            this.fieldsNames = fieldsNames;
            this.idField = idField;
            this.nameField = nameField;
            this.filter = filter;
            this.templateID = templateID;
            this.parentItemID = parentItemID;
            this.fieldNames = StringUtil.Split(string.Concat(this.idField, ",", this.fieldsNames), ',', true);
            this.hostDatabase = hostDatabase;
            //ModifyBase();
            CacheOptions.DisableAll = true;
            countRecords = 0;
         }
   
         #endregion

4. Add the following to the DataProviders section of the Web.Config:

    <dataProviders>
   ...
   <northwindDataProvider type="Sitecore.Data.DataProviders.NorthwindDataProvider, Sitecore.NorthwindDataProvider">
    <param desc="connection">server=SERVERNAME\SQLEXPRESS;User=abc123;password=1234;database=TestingDB</param>
    <param desc="table">ENTERTABLENAMEHERE</param>
    <param desc="fieldsNames">id,description,col3</param>
   <!-- list the columns corresponding with the template -->
    <param desc="idField">id</param>
    <!-- the name of the primary key field -->
    <param desc="nameField">description</param>
    <!-- the name of the field which will be used as the name of the node in Sitecore -->
    <param desc="filter"></param>
    <param desc="templateID">{3353B4C9-CD55-41DA-9A2E-140522E6185B}</param>
    <!-- the id of the template which was created to mimic the query/table -->
    <param desc="parentItemID">{AB466059-A6DE-4D3A-87CF-8FBB09680997}</param>
    <!-- the id of the folder/parent item where the data will be loaded -->
    <param desc="hostDatabase">master</param>
    </northwindDataProvider>
   ...
    </dataProviders>

5. Add the DataProvider to the Database section:

   <database id="master" singleInstance="true" type="Sitecore.Data.Database, Sitecore.Kernel">
    <param desc="name">$(id)</param>
    <icon>People/16x16/cubes_blue.png</icon>
    <dataProviders hint="list:AddDataProvider">
    <dataProvider ref="dataProviders/northwindDataProvider"/>
    <dataProvider ref="dataProviders/main" param1="$(id)">
    <prefetch hint="raw:AddPrefetch">
    <sc.include file="/App_Config/Prefetch/Common.config" />
    <sc.include file="/App_Config/Prefetch/Master.config" />
    </prefetch>
    </dataProvider>
    </dataProviders>

Once all of this is done, you can restart the Sitecore server and client. Open the content editor and you will see the data loaded under the parent item. If you have any questions please feel free to get in touch with me at asura @ webdatamation . com

Sitecore support was helpful in guiding me. Here are a couple of useful links:

http://sdn.sitecore.net/Resources/Shared%20Source/Shared%20Source%205,-d-,3/Data%20Providers/SQL%20Data%20Provider/Downloads.aspx

http://sdn.sitecore.net/Developer/Integrating%20External%20Data%20Sources/Examples/Hello%20World.aspx

http://sdn5.sitecore.net/Articles/API/Creating%20a%20Composite%20Custom%20Field/Adding%20a%20Custom%20Field%20to%20Sitecore%20Client.aspx

http://sdn5.sitecore.net/faq/api/populate%20lookup%20field%20with%20field%20values.aspx

Some of the source in the above links might not work as intended or cause minor compiler errors.