Forum Discussion

ST1's avatar
ST1
New Contributor III
6 months ago

Reading dictionary type

I am using below sql to get validation messages from the workflow transformation process to be used in  Transformation Event Handler. But, I need to put the output of this dictionary into a flat file. How I can read the contents of this dictionary created.

Thanks so much for your help!

sqlValidation.Append("Select * ")
sqlValidation.Append("From StageToFinanceValidationError ")
sqlValidation.Append("Where StageToFinanceValidationError.WorkflowProfileKey = '" & wfPk.ProfileKey.ToString & "' ")
sqlValidation.Append("And StageToFinanceValidationError.WorkflowTimeKey = '" & wfPk.TimeKey.ToString & "' ")
sqlValidation.Append("And StageToFinanceValidationError.WorkflowScenarioKey = '" & wfPk.ScenarioKey.ToString & "' ")
 
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
dtValidation = BRAPi.Database.ExecuteSqlUsingReader(dbConnApp, sqlValidation.ToString, False)
Dim sb_ValErrorsReport As New Text.StringBuilder()
For Each drVal As DataRow In dtValidation.Rows
Dim strValMsg = drVal("ValidationmessagesXml")'.ToString()
Dim validationMsgList As DataCellValidationMsgList = XmlObject.ReadXmlString(si, GetType(DataCEllValidationMsgList), strValMsg, True)
If Not validationMsgList.Messages Is Nothing And validationMsgList.Messages.Count <> 0 Then
Dim errmessage As String = validationMsgList.Messages(0).ErrorMsg
Dim errdetails As List(Of String) = validationMsgList.Messages(0).StringParams.ToList()
 
errmessage = string.Format(errmessage, errdetails.ToArray())
If Not errorDict.Keys.Contains(errmessage) Then
Dim templist As New List(Of List(Of String))
        templist.Add(errdetails)
errorDict.Add(errmessage, templist)
Else
errorDict(errmessage).Add(errdetails)
End If
End If
Next
 
 
 
Workflow
    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      6 months ago

      I agree, if you can do this during your initial for loop then it would save doing more potentially redundant looping down the track.

      I would also suggest including more detail in your code to at least show the definition of the variable that you're asking about.

      It looks like your dictionary contains a list of strings for each key. I would strongly advise doing it in your datarow loop but here is some generic VB.net code that could have assisted if that option was not available:

              ' Specify the file path for the CSV file
        Dim filePath As String = "errorOutput.csv"

        ' Generate CSV output
        Using writer As New StreamWriter(filePath)
            ' Write the header (optional)
            writer.WriteLine("Error Code,Description")

            ' Iterate through the dictionary
            For Each kvp As KeyValuePair(Of String, List(Of String)) In errorDict
                Dim key As String = kvp.Key
                For Each value As String In kvp.Value
                    ' Write the key and value to the CSV file
                    writer.WriteLine($"{key},{value}")
                Next
            Next
        End Using

       

  • aformenti's avatar
    aformenti
    Contributor II
    6 months ago

    HI ST1,

    This snippet should give you what you need:

    #Region "Dictionary To DataTable"				
	'------------------------------------------------------------------------------------------------------------
	'Referenced Code: 	DictionaryToDataTable -> DataTable
	'
	'Description:		Converts Dictionary(Of String, String) to DataTable from Dictionary where column = Key and row = Value
	'
	'Notes:				
	'
	'Usage:				
	'
	'Created By:		OneStream
	'Date Created:		04-04-2023
	'------------------------------------------------------------------------------------------------------------					
	Public Function DictionaryToDataTable(ByVal si As SessionInfo, ByVal dictParam As Dictionary(Of String, String)) As DataTable
		' Method name
		Dim methodName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name	
		' Object to return
		Dim dt As New DataTable()				
		Try
			' Add columns (Keys)
			dt.Columns.AddRange(dictParam.Keys.ToList().Select(Function(lambda) New DataColumn(lambda, GetType(String))).ToArray())
			' Add row (Values)
			dt.Rows.Add(dictParam.Values.ToArray())
			
			' Return
			Return dt
			
		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(ex))					
		End Try		
	End Function
	
#End Region