Uniqueness check against the Metaverse

ILM works on a single object at a time, so it can be a little tricky if you want to make a decision based on other objects in your metaverse. It is, however, perfectly possible (though, I believe, officially discouraged) to run SQL queries against the metaverse table from your extension code.

My example is a fairly simple one: I create a uid based on first initial and surname, test for uniqueness, and if a match is found I append an incrementing index number until I get a unique uid.

The code, in this case, is entered as an Import Flow Rule in the MAExtension of the importing MA. It’s pretty simplistic, the main point being to demonstrate the query of the metaverse table.

Note the use of NOLOCK in the SQL query – this is essential as we are querying the metaverse table at the same time as synchronising to it.

Snippet 

Const DB_CONNECTION_STRING As String = "Database=MicrosoftIdentityIntegrationServer;Data Source=localhost;Integrated Security=TRUE;;"

Public Sub MapAttributesForImport(ByVal FlowRuleName As String, ByVal csentry As CSEntry, ByVal mventry As MVEntry) Implements IMASynchronization.MapAttributesForImport
  Select Case FlowRuleName
    Case "import_uid"
      Dim uid As String = ""
      Dim index As Integer
      Dim sqlQueryConnection As New SqlConnection(DB_CONNECTION_STRING)
      Dim rowReader As SqlDataReader
      Dim unique As Boolean = False

      If csentry("firstName").IsPresent AndAlso csentry("lastName").IsPresent Then
        uid = csentry("firstName").Value.Chars(0) & csentry("lastName").Value
        index = 1

        Do Until unique Or index > 99
          Dim query As String = "select count (*) from mms_metaverse with (NOLOCK) " _
                              & "where object_type = 'person' and uid = '" & uid & "'"
          Dim sqlQuery As New SqlCommand(query, sqlQueryConnection)
          sqlQueryConnection.Open()
          rowReader = sqlQuery.ExecuteReader
          rowReader.Read()
          If rowReader.GetInt32(0) > 0 Then
            If index > 1 Then
              uid = uid.Replace((index - 1).ToString, "")
            End If
            uid = uid & index.ToString
          Else
            unique = True
          End If
          index = index + 1
          sqlQueryConnection.Close()
        Loop

        If index < 100 And uid <> "" Then
           mventry("uid").Value = uid
        End If

      End If
  End Select
End Sub