Miscellaneous Ramblings on Hacking

February 24, 2010

Fun with c# and the RightNow API

Filed under: .Net, RightNow — Zack Bethem @ 12:29 am

I wanted to dump out data from RightNow’s Contact tables. The intention was to load a SQL Server table that would then be used in later ETL for data quality rules.

The code does the magic I want.

  • Execute a RightNow report for a range of contacts.c_id values
  • Store the results in a SQL Server table on my local machine

The only problem is performance. SFDC can dump out 2-3million rows in an hour. My code was far from efficient as it was built for a quick-exercise. The code for 1.5million rows would finish in about 14hours. YUCK! Update: I was able to score 760k records in 1.5hrs … much better.

Here’s my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using obj.api.rightnow.com;
using fault.api.rightnow.com;
using api.rightnow.com;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Becker_DataExtract
{
    class Program
    {
        static void Main(string[] args)
        {

            RNOWObjectFactory ofactory = new RNOWObjectFactory(Properties.Resources.RNOWConnection);
            ofactory.login(Properties.Resources.RNOWLogin, Properties.Resources.RNOWPass);

            //database stuff
            string connString = "server=(local)\\SQLEXPRESS;Initial Catalog=BeckerPOC;User ID=becker;Password=becker";
            string insertSQL = @"insert into RNContacts(c_id,org_id,last_name,first_name,email,email1,email2,street
,city,prov,postal_code,country,ph_asst,ph_fax,ph_home,ph_mobile,ph_office, RNLastUpdated)
values
(@c_id, @org_id, @last_name, @first_name, @email, @email1, @email2, @street
,@city, @prov,@postal_code,@country,@ph_asst,@ph_fax,@ph_home,@ph_mobile,@ph_office, @RNLastUpdated)
";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            object[][] reportResult;

            System.Console.WriteLine("Staring process: " + DateTime.Now.ToShortTimeString());

            for (int i = 1; i <= 25; i++)
            {
                List<RNOWAcFilter> reportFilters = new List<RNOWAcFilter>();
                RNOWAcFilter reportFilter = new RNOWAcFilter();
                reportFilter.Operator = Convert.ToInt32(RNOWUtil.SearchOperator.RANGE);
                reportFilter.Value = i.ToString() + "|" + (i * 1000).ToString();
                reportFilter.FilterId = 1;
                reportFilters.Add(reportFilter);
                reportResult = ofactory.ExecuteReport(Convert.ToInt32(Properties.Resources.RNOWExtractReport), reportFilters.ToArray());

                foreach (Object[] result in reportResult)
                {
                    SqlCommand cmdSQL = new SqlCommand(insertSQL, conn);

                    SqlParameter c_id = new SqlParameter("@c_id", Convert.ToString(result[0]));
                    cmdSQL.Parameters.Add( c_id );

                    SqlParameter org_id = new SqlParameter("@org_id", Convert.ToString(result[1]));
                    cmdSQL.Parameters.Add(org_id);
                    SqlParameter last_name = new SqlParameter("@last_name", Convert.ToString(result[2]));
                    cmdSQL.Parameters.Add(last_name);
                    SqlParameter first_name = new SqlParameter("@first_name", Convert.ToString(result[3]));
                    cmdSQL.Parameters.Add(first_name);
                    SqlParameter email = new SqlParameter("@email", Convert.ToString(result[4]));
                    cmdSQL.Parameters.Add(email);
                    SqlParameter email1 = new SqlParameter("@email1", Convert.ToString(result[5]));
                    cmdSQL.Parameters.Add(email1);
                    SqlParameter email2 = new SqlParameter("@email2", Convert.ToString(result[6]));
                    cmdSQL.Parameters.Add(email2);
                    SqlParameter street = new SqlParameter("@street", Convert.ToString(result[7]));
                    cmdSQL.Parameters.Add(street);
                    SqlParameter city = new SqlParameter("@city", Convert.ToString(result[8]));
                    cmdSQL.Parameters.Add(city);
                    SqlParameter prov = new SqlParameter("@prov", Convert.ToString(result[9]));
                    cmdSQL.Parameters.Add(prov);
                    SqlParameter postal_code = new SqlParameter("@postal_code", Convert.ToString(result[10]));
                    cmdSQL.Parameters.Add(postal_code);
                    SqlParameter country = new SqlParameter("@country", Convert.ToString(result[11]));
                    cmdSQL.Parameters.Add(country);
                    SqlParameter ph_asst = new SqlParameter("@ph_asst", Convert.ToString(result[12]));
                    cmdSQL.Parameters.Add(ph_asst);
                    SqlParameter ph_fax = new SqlParameter("@ph_fax", Convert.ToString(result[13]));
                    cmdSQL.Parameters.Add(ph_fax);
                    SqlParameter ph_home = new SqlParameter("@ph_home", Convert.ToString(result[14]));
                    cmdSQL.Parameters.Add(ph_home);
                    SqlParameter ph_mobile = new SqlParameter("@ph_mobile", Convert.ToString(result[15]));
                    cmdSQL.Parameters.Add(ph_mobile);
                    SqlParameter ph_office = new SqlParameter("@ph_office", Convert.ToString(result[16]));
                    cmdSQL.Parameters.Add(ph_office);
                    SqlParameter RNLastUpdated = new SqlParameter("RNLastUpdated", Convert.ToString(result[17]));
                    cmdSQL.Parameters.Add(RNLastUpdated);

                    //da.InsertCommand = cmdSQL;
                    cmdSQL.ExecuteNonQuery();
                    cmdSQL.Dispose();
                }

                System.Console.WriteLine("Done with loop run: " + i.ToString());
            }

            System.Console.WriteLine("End of process: " + DateTime.Now.ToShortTimeString());
            conn.Close();
            ofactory.logout();
            ofactory.Dispose();

        }

    }

}

Update:

Looking at the comments on this post, it appears that I have RightNow checking my code. Yes. there is a bug in it. The idea is to use a report to dump out data. So the start range needs to be incremented more than it is in the AcFilter.

Advertisements

3 Comments »

  1. Hey,

    I am trying to recreate your problem and have few questions:

    – What your report’s filter 1 is filtering on? I assume Contact ID, but please verify.
    – In your code:
    reportFilter.Value = i.ToString() + “|” + (i * 1000).ToString();
    shouldn’t be:
    reportFilter.Value = ((i – 1) * 1000).ToString() + “|” + (i * 1000).ToString();
    or something similiar? What are you trying to do with the range of the filter value? I dont understand…

    Thanks,

    Ryszard

    Comment by ryszardantonczyk — March 2, 2010 @ 12:13 am

  2. Actually, assuming that you want to retrieve up to 1000 contacts at once, the filter 1 value should be set as follows:

    reportFilter.Value = ((i – 1) * 1000 + 1).ToString() + “|” + (i * 1000).ToString();

    This also assumes that Filter 1 is on c_id (Contact ID), which is the most efficient way since c_id is the primary key value for contacts table.

    Ryszard

    Comment by ryszardantonczyk — March 2, 2010 @ 5:23 pm

  3. Yes, I checked your code since you requested our help by posting on RightNow Developer Community Forum: http://communities.rightnow.com/posts/2ed23b230e

    And just to preserve the integrity of your blog, let me state here that since your code is actually retrieving 324,700 records, the targeted 1.5 million should only take about 84 minutes.

    Glad we could help out!

    Ryszard Antonczyk
    RightNow Connect Development Lead.

    Comment by ryszardantonczyk — March 3, 2010 @ 7:48 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: