Split List with more than a thousand lines - .NET

Navigation
0

I'm getting an error while performing this select 

sb = New StringBuilder
sb.Append(" SELECT  ")
sb.Append(" SE.NU_SERIE_NF, ")
sb.Append(" SE.NU_NF, ")
sb.Append(" SE.DH_ENTREGA, ")
sb.Append(" SE.DH_IMPORTACAO, ")
sb.Append(" SE.ARMAZEM, ")
sb.Append(" SE.TRANSPORTADORA ")
sb.Append(" FROM ")
sb.Append(" SEP_ENTREGA SE ")
sb.Append(" WHERE ")
sb.Append(" SE.NU_NF IN (" & nuNota.ToString() & ") AND ")
sb.Append(" SE.NU_SERIE_NF IN (" & nuSerieNota.Distinct().ToList().FirstOrDefault().ToString() & ") ")
dtEmail = ListarDados(sb.ToString, conexao)

The error happens in this line sb.Append(" SE.NU_NF IN (" & nuNota.ToString() & ") AND ") because the result has a thousand lines or more, something that the select in Oracle does not accept.

Code that populates variable nuNota : 

'Monta lista com os Números de Nota
 Dim nuNota As String = ""
 Dim nuSerieNota As String = ""
 For i As Integer = 0 To listaNotaDHSolColeta.Count - 1
          nuNota += listaNotaDHSolColeta.Item(i).nuNota.ToString()
          nuSerieNota += listaNotaDHSolColeta.Item(i).nuSerieNota.ToString()
          If i < listaNotaDHSolColeta.Count - 1 Then
             nuNota += ","
             nuSerieNota += ","
          End If
 Next

I searched the net, but I could not solve this problem.

My question is, how can I split this list into 2 parts, so I can search the select?

    
asked by anonymous 15.08.2016 / 16:44

1 answer

1

In principle, it seems to me that the solution indicated by Motta would be the most appropriate (in case the elements in listaNotaDHSolColeta are also coming from a query to the database).

In any case, if your intention is to perform the query in steps, each with a group of "n" note numbers, your starting point is to divide your array into groups of "n" elements. You can use the function below to do this (as I do not know the type of listaNotaDHSolColeta , I did the generic function, but could also have used the type Object ): 

Function Pacotes(Of T)(lista As IEnumerable(Of T), elementos As Integer) As T()()
    Dim dicItens As New Dictionary(Of Integer, T), dicGrupos As New Dictionary(Of Integer, List(Of T)), iGrupo As Integer
    For Each item As T In lista
        dicItens(dicItens.Count) = item
    Next
    For Each kvpItem As KeyValuePair(Of Integer, T) In dicItens
        iGrupo = kvpItem.Key \ elementos
        If Not dicGrupos.ContainsKey(iGrupo) Then
            dicGrupos(iGrupo) = New List(Of T)
        End If
        dicGrupos(iGrupo).Add(kvpItem.Value)
    Next
    Return dicGrupos.Select(Function(kvp) kvp.Value.ToArray).ToArray
End Function

You can use it like this: 

Dim gruposNotaDHSolColeta = Pacotes(listaNotaDHSolColeta, 1000)
    
27.08.2016 / 16:38
Problem with inserting TIMER into MySQL table using JDBC How to do form field checks correctly