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

February 17, 2010

C# interacting with a web form

Filed under: .Net, RightNow — Zack Bethem @ 11:45 pm

Sweet.. I love the web. I needed to build a POC for a RightNow project I’m working on. BAZINGA

Not only was it an awesome domain name. But, the code is exactly what I require. THANKS!

February 11, 2010

Querying SQL Server for Table sizes

Filed under: Uncategorized — Zack Bethem @ 9:23 pm

Found this gem and I MUST save it for future reference.

http://www.mssqltips.com/tip.asp?tip=1177

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 
INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
SET @table_name = REPLACE(@table_name, ']',''); 
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname  insert into temp_table_sizes

FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

I followed it up with the following modification, however:

select *, cast(substring(data,1, charindex(' ',data,1)-1)/1024/1024 as decimal) as data_GB from temp_table_sizes order by cast(substring(data,1, charindex(' ',data,1)-1) as integer) desc , row_count desc

Create a free website or blog at WordPress.com.