SalesForce Data comparator

Introduction:

Salesforce is one of the widely used CRM cloud system. Most of the times SalesForce will be the end point for the data and front end browser for the End user. So it is very important to verify that the data that resides inside SalesForce should be in the desired format. To ensure that the data comparison must be required between the source system and the SalesForce database.

I will demonstrate you how can you perform the same by using .Net coding.

Pre-requisite:

Download the patner Web service from SalesForce website and add a reference to the .Net solution.

Code:

 

PrivateSubcompare(srcQueryAsString, sfdcQueryAsString)

DimstTimeAsDateTime = Date.Now()

DimdateColsAsNewList(OfString)()

DimedwExtraAsNewList(OfString)()

DimsfdcExtraAsNewList(OfString)()

DimnumberColsAsNewList(OfString)()

DimboolColsAsNewList(OfString)()

DimdetailMsgAsString = ""

DimshotMsgAsString = ""

Try

detailMsg = "---------------------------------------------------"&vbCrLf&"Summary"&vbCrLf&"Entity: "&[Global].sfdcObjectName&vbCrLf&"Filter applied: "&filterApplied&vbCrLf

 

sfdc = NewDataTable()

AppendTextBox("Comparision starts for " + objectName + "."&vbCr&vbLf&"Fetching EDW records....."&vbCr&vbLf)

 

src = da.GetDataTable(srcQuery, [Global].srcConString)

Ifsrc.Rows.Count = 0 Then

detailMsg = "No EDW records present in the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf

AppendTextBox("No EDW records present in the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf)

shotMsg = "No EDW records present in the specified condition."

Return

EndIf

AppendTextBox("EDW records fetched:" + src.Rows.Count.ToString() + vbCr&vbLf)

detailMsg = detailMsg&"Total Records read from EDW: "&src.Rows.Count.ToString() &vbCrLf

IfNotlogin() Then

MessageBox.Show("Unable to connect to SFDC.")

Return

EndIf

AppendTextBox("Loged in to SFDC."&vbCr&vbLf)

Dim des AsDescribeSObjectResult = binding.describeSObject([Global].sfdcObjectName)

Dim fields AsField() = des.fields

ForEach field AsFieldIn fields

Iffield.type.ToString() = "boolean"Then

boolCols.Add(field.name)

EndIf

Iffield.type.ToString() = "datetime"Then

dateCols.Add(field.name)

EndIf

Iffield.type.ToString() = "double"Then

numberCols.Add(field.name)

EndIf

Next

binding.QueryOptionsValue = NewQueryOptions()

binding.QueryOptionsValue.batchSize = 2000

binding.QueryOptionsValue.batchSizeSpecified = True

 

AppendTextBox("Fetching SalesForce records....."&vbCr&vbLf)

DimqrAsQueryResult = binding.query(sfdcQuery)

Ifqr.size = 0 Then

detailMsg = "No records present in SalesForce for the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf

AppendTextBox("No records present in SalesForce for the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf)

shotMsg = "No records present in SalesForce for the specified condition."

Return

EndIf

detailMsg = detailMsg&"Total Records read from SFDC: "&qr.size.ToString() &vbCrLf

Dim con AssObject = qr.records(0)

Dim done AsBoolean = False

Ifqr.size> 0 Then

 

 

ForiAsInteger = 0 Tocon.Any.GetUpperBound(0)

Dim t AsType = GetType([String])

IfdateCols.Contains(con.Any(i).LocalName) Then

                        t = GetType(DateTime)

EndIf

IfnumberCols.Contains(con.Any(i).LocalName) Then

                        t = GetType([Double])

EndIf

sfdc.Columns.Add(con.Any(i).LocalName, t)

Next

WhileNot done

ForiAsInteger = 0 Toqr.records.GetUpperBound(0)

con = qr.records(i)

DimdrAsDataRow = sfdc.NewRow()

For j AsInteger = 0 Tocon.Any.GetUpperBound(0)

IfdateCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = "01-01-1900"

Else

dr(j) = Convert.ToDateTime(con.Any(j).InnerText)

EndIf

ElseIfnumberCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = 0

Else

dr(j) = Convert.ToDouble(con.Any(j).InnerText)

EndIf

ElseIfboolCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = 0

Else

dr(j) = Convert.ToBoolean(con.Any(j).InnerText)

EndIf

Else

dr(j) = con.Any(j).InnerText

EndIf

Next

sfdc.Rows.Add(dr)

Next

Ifqr.doneThen

done = True

Else

qr = binding.queryMore(qr.queryLocator)

 

EndIf

EndWhile

Else

MessageBox.Show("No records found.")

EndIf

AppendTextBox("SalesForce records fetched:" + qr.size.ToString() + vbCr&vbLf)

 

Dim ds AsNewDataSet()

ds.Tables.Add(src)

ds.Tables.Add(sfdc)

ForEachdataRowAsDataRowInds.Tables(0).Rows

Dim where AsString = "[" + ds.Tables(1).Columns(0).ColumnName + "]='" + dataRow(0).ToString() + "'"

DimdrLAsDataRow() = ds.Tables(1).[Select](where)

 

If (drL.Length = 0) Then

edwExtra.Add(dataRow(0).ToString())

 

EndIf

Next

ForEach s AsStringInedwExtra

Dim where AsString = "[" + ds.Tables(0).Columns(0).ColumnName + "]='" + s + "'"

DimdrLAsDataRow() = ds.Tables(0).[Select](where)

ds.Tables(0).Rows.Remove(drL(0))

Next

ForEachdataRowAsDataRowInds.Tables(1).Rows

Dim where AsString = "[" + ds.Tables(0).Columns(0).ColumnName + "]='" + dataRow(0).ToString() + "'"

DimdrLAsDataRow() = ds.Tables(0).[Select](where)

If (drL.Length = 0) Then

sfdcExtra.Add(dataRow(0).ToString())

 

EndIf

Next

ForEach s AsStringInsfdcExtra

Dim where AsString = "[" + ds.Tables(1).Columns(0).ColumnName + "]='" + s + "'"

DimdrLAsDataRow() = ds.Tables(1).[Select](where)

ds.Tables(1).Rows.Remove(drL(0))

Next

DimdataRow_relatedAsDataRow = ds.Tables(0).NewRow()

DimsrcColAsDataColumn = ds.Tables(0).Columns(0)

DimsfdcColAsDataColumn = ds.Tables(1).Columns(0)

DimdataRelationAsNewDataRelation("EquiJoin", srcCol, sfdcCol)

Try

ds.Relations.Add(dataRelation)

Catch e AsException

MessageBox.Show("SFDC is having extra records.")

EndTry

DimkeyMatchedAsInt32 = 0

DimrecsSyncedAsInt32 = 0

Dim flag AsBoolean = True

 

 

 

ForEachdataRowAsDataRowInds.Tables(1).Rows

dataRow_related = dataRow.GetParentRow("EquiJoin")

keyMatched += 1

ForiAsInteger = 0 Tocon.Any.GetUpperBound(0)

IfdateCols.Contains(sfdc.Columns(i).ColumnName) Then

IfTimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(dataRow(i))).ToString() <>Convert.ToDateTime(dataRow_related(i)).ToString() Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(dataRow(i))).ToString())

flag = False

EndIf

ElseIfnumberCols.Contains(sfdc.Columns(i).ColumnName) Then

Dim d AsDouble

IfdataRow_related(i).ToString() = ""Then

                            d = 0

Else

                            d = Convert.ToDouble(dataRow_related(i))

EndIf

IfConvert.ToDouble(dataRow(i)) <> d Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

ElseIfboolCols.Contains(sfdc.Columns(i).ColumnName) Then

Dim d AsBoolean

IfdataRow_related(i).ToString() = ""Then

                            d = False

Else

                            d = Convert.ToBoolean(dataRow_related(i))

EndIf

IfConvert.ToBoolean(dataRow(i)) <> d Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

Else

IfdataRow(i).ToString() <>dataRow_related(i).ToString() Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

EndIf

Next

If (flag) Then

recsSynced += 1

EndIf

flag = True

Next

detailMsg = detailMsg&"Total Keys Matched between EDW and SFDC: "&keyMatched.ToString() &vbCrLf&"Total Records Synched: "&recsSynced.ToString() &vbCrLf

DimedTimeAsDateTime = Date.Now()

Dim TS AsTimeSpan = edTime - stTime

 

Dim hour AsInteger = TS.Hours

DimminsAsInteger = TS.Minutes

DimsecsAsInteger = TS.Seconds

DimtimeDiffAsString = ((hour.ToString("00") &":") + mins.ToString("00") &":") + secs.ToString("00")

detailMsg = detailMsg&"Start Time: "&stTime.ToString() &vbCrLf&"End Time: "&edTime.ToString() &vbCrLf&"Run Time: "&timeDiff&vbCrLf&"---------------------------------------------------"&vbCrLf

 

textColor = "BLUE"

AppendTextBox(detailMsg)

 

Ifmismatch.Rows.Count> 0 Then

ForiAsInteger = 0 Tomismatch.Rows.Count - 1

'textLen = RichTextBox1.TextLength

textColor = "RED"

AppendTextBox("Key not matched : "&mismatch.Rows(i)(2).ToString() &" ,EDW "&mismatch.Rows(i)(3).ToString() &" : "&mismatch.Rows(i)(4).ToString() &" ,SFDC "&mismatch.Rows(i)(5).ToString() &" : "&mismatch.Rows(i)(6).ToString() + vbCr&vbLf)

Next

'ea.Excelwriter(mismatch, "Mismatch.xlsx", [Global].outputexcelPath)

shotMsg = "Data Consistency Check: Failed"

Else

shotMsg = "Data Consistency Check: Passed"

EndIf

AppendTextBox(vbCrLf)

DimedwExtrapkAsString = "Primary key of EDW Extra Records"&vbCrLf

DimsfdcExtrapkAsString = "Primary key of SalesForce Extra Records"&vbCrLf

 

ForEach s AsStringInedwExtra

edwExtrapk = edwExtrapk& s &vbCrLf

Next

 

ForEach s AsStringInsfdcExtra

 

sfdcExtrapk = sfdcExtrapk& s &vbCrLf

Next

 

IfedwExtra.Count> 0 Then

shotMsg = "Data Consistency Check: Failed"

AppendTextBox(edwExtrapk + vbCr&vbLf)

EndIf

 

IfsfdcExtra.Count> 0 Then

shotMsg = "Data Consistency Check: Failed"

AppendTextBox(sfdcExtrapk + vbCr&vbLf)

EndIf

 

textColor = "BLACK"

Catch e AsException

MessageBox.Show("An unexpected error has occurred: " + e.Message + vbLf + e.StackTrace)

shotMsg = "Data Consistency Check completed with errors."

detailMsg = "Data Consistency Check completed with errors."

Finally

MessageBox.Show(shotMsg)

textLen = 0

mismatch.Clear()

EndTry

EndSub