Cvent API: Compare Groups with your SQL Database

It’s been a couple years since I created the program to add and delete people from cvent standard groups as they’re added or deleted from our SQL database. Over time some of the groups have gotten out of sync because there will always be times with network outages, cvent is down or the nightly script just goes bad. With 10K users and over 100+ groups these is no way to update att groups all the time because of API limitations through Cvent, So I needed a way to compare the groups. The program I worte can be run every once in a while (maybe every 6 months) and get all groups back in sync.

Again because of the amount of members/groups we have, this needs to be done on a per group basis.

I first created a drop down list of all our Cvent Standard groups, I pulled these from our SQL table that I have our internal group name, cvents group name and cvents group ID. This helped because it doesn’t look as if in Cvent API you can choose between standard and distrubution lists.

Once someone choose the group they wanted to compare, the magic would begin.

First I pull a list of users from our SQL and put them in a list(of T)


Sub GetMembersFromSQL(ByVal groupAbbr As String)

Dim SQLlist As New List(Of Integer)

Using connection As New SqlConnection("Server=YOUR SERVER CONNECTION")

Dim sql As String = "SELECT YOUR MEMBERIDS FROM YOUR GROUP"
 connection.Open()
 Dim command As New SqlCommand(sql, connection)

Using reader As SqlDataReader = command.ExecuteReader()
 While reader.Read()
 SQLlist.Add(reader.GetInt32(reader.GetOrdinal("IndividualID")))
 End While
 End Using
 End Using

GetGroup(ddlGroupNames.SelectedValue)

End Sub

Now we get info from Cvent:

login into to Cvent API (see.. http://tek-works.com/knee-deep-in-cvent-api-logging-in/)

Grab all the id’s of users in the choosen group: (remember Cvent groupID pulled from database and added to the drop down list)


Sub GetGroup(ByVal groupID As String)
 Dim ids As String()
 Dim thesearch As New com.cvent.api.CvSearch()

 thesearch.Filter = New com.cvent.api.Filter(0) {}
 thesearch.Filter(0) = New com.cvent.api.Filter()

thesearch.Filter(0).Field = "GroupId"
 thesearch.Filter(0).Value = groupID

thesearch.Filter(0).Operator = com.cvent.api.CvSearchOperatorType.Equals

ids = _ws.Search(com.cvent.api.CvObjectType.[Contact], thesearch)
 GetCventSourceIDs(ids)
 End Sub

In Cvent we have all users have a sourceid (or at least they should, I also cover later if they don’t have one) but I need that source id to match with our SQL memberid’s

Here we run into another of the API limits, Cvent will only allow you to retrieve up to 200 objects at a time, so if the group has over 200 members, we need to do the retrevials in batches.

This code will retrieve all the id’s but if the list is over 190 (I give a little breathing room) I split the list, you can also see that if the user doesn’t have a sourceID, I put them in a different list, so we can correct it.


Sub GetCventSourceIDs(ByVal ids As String())

Dim objects As com.cvent.api.CvObject()
 Dim i As Int16
Dim Cventlist As New List(Of Integer)
 If ids.Length > 190 Then

 SplitIds(ids)
 Else
 If Not IsDBNull(ids) Then

objects = _ws.Retrieve(com.cvent.api.CvObjectType.contacts, ids) 'drop the s in contacts, it was messing up the blog
 For i = 0 To objects.Length - 1

Dim source As com.cvent.api.Contacts = objects(i) 'drop the s in contacts, it was messing up the blog
 If source.SourceId = "" Then
 NoSourcelist.Add(source.FirstName & " " & source.LastName & "(in Cvent)")
 Else
 Cventlist.Add(source.SourceId)
 End If

 Next i
 End If

LoopThroughLists()
 End If
 End Sub

Sub SplitIds(ByVal ids As String())

Dim arrayString As [String]() = ids
 Dim splitted As New List(Of String())()
 'This list will contain all the splitted arrays.
 Dim lengthToSplit As Integer = 190

Dim arrayLength As Integer = arrayString.Length

Dim i As Integer = 0
 While i < arrayLength
 Dim val As String() = New String(lengthToSplit - 1) {}

If arrayLength < i + lengthToSplit Then
 lengthToSplit = arrayLength - i
 End If
 Array.Copy(arrayString, i, val, 0, lengthToSplit)
 splitted.Add(val)
 i = i + lengthToSplit
 End While

Dim WhatsTheLength As Integer = splitted.Count
 Dim j As Integer = 0
 Dim k As Integer = 0
 Dim objects As com.cvent.api.CvObject()

 If Not IsDBNull(ids) Then
 While k < WhatsTheLength
 objects = _ws.Retrieve(com.cvent.api.CvObjectType.Contacts, splitted(k)) 'drop the s in contacts, it was messing up the blog
 For j = 0 To objects.Length - 1

 Dim source As com.cvent.api.Contacts = objects(j) 'drop the s in contacts, it was messing up the blog
 If source.FirstName = "" Then

Else
 If source.SourceId = "" Then
 NoSourcelist.Add(source.FirstName & " " & source.LastName & "(in Cvent)")
 Else
 Cventlist.Add(source.SourceId)
 End If
 End If
 Next j
 k = k + 1
 End While
 End If

LoopThroughLists()

End Sub

Now that we have a list of people that should be in the group (from our SQL) and people that are in the group (from Cvent) we can compare them. Since you can’t do a compare with list (of T) I loop through each list and see if that memberid is in the other list, this gives me an oput put of who isn’t in each list, I then know who needs deleted from the Cvent group (because they shouldn’t be there) and who needs added to the cvent group (because they’re in our SQL group)


Sub LoopThroughLists()
Dim CventListID As Integer
 For Each objectItem As Object In Cventlist

 CventListID = objectItem

 Dim IsInCventresult As Integer = SQLlist.Find(AddressOf FindIDinSQL)
 If InSQLReturned = "False" Then
 NotinSQL = NotinSQL & " " & getindividualnames(CventListID)

 End If
 Next

For Each objectSQLItem As Object In SQLlist

 SQLListID = objectSQLItem

 Dim IsInSQLresult As Integer = Cventlist.Find(AddressOf FindIDinCvent)
 If InCventReturned = "False" Then
 NotinCvent = NotinCvent & " " & getindividualnames(SQLListID)

 End If
 Next

End Sub

Private Function FindIDinSQL(ByVal bk As Integer) As Boolean
 'looks for cvent id in sql list
 If bk = CventListID Then
 InSQLReturned = "True"
 Return True
 Else
 InSQLReturned = "False"
 Return False
 End If
 End Function
 Private Function FindIDinCvent(ByVal bk1 As Integer) As Boolean
 'looks for sql id in cvent list
 If bk1 = SQLListID Then
 InCventReturned = "True"
 Return True
 Else
 InCventReturned = "False"
 Return False
 End If
 End Function

from here I now have my list of who to delete and who to add. For now I list them out and they can be manually done, my next step is to add a module to use the Cvent API add/delete those that are in each list (I just didn’t have time, since this was a something we need but do it between projects type of program)

     

Leave a Reply