vb.net problem with recursive function inside while cycle

Question

I try to create XtraDiagram (with DataTable) but this topic is related on DataTable construction. Exscuse me if i ask you this problem. This is recursion problem. My scope is populate DataTable with all member of hierarchical structure (like for example file system). In the last while i try to cycle for all children finded. In this section, i call recursive function for continue the analisys. The routine start ok and analisys until the last box. But after come back without analyze the other children… The sqldatareader lost his function? Because? Thanks very much anticipated.

Public Function CreateDiagram(connection As SqlConnection, IDdoc2 As Integer, IDdoc1 As Integer) As Integer

'creation XtraDiagram
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader
Dim strItemName As String = String.Empty

'creation item
dt.Rows.Add(IDdoc2, strItemName, IDdoc1, "")

'extract how many children there are
cmd.Connection = connection
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT IDdocumento_1 FROM tbRelazioniDocumenti WHERE 
IDdocumento_2=" & IDdoc2
dr = cmd.ExecuteReader
If dr.HasRows Then
    While dr.Read()
        'for every children
        Return CreateDiagram(connection, dr.Item("IDdocumento_1"), IDdoc2)
    End While
Else
    Return 0
End If
cmd.Dispose()
dr.Close()

End Function

Public Function CreateDiagram2(connection As SqlConnection, IDdoc2 As Integer, IDdoc1 As Integer) As Integer

Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader
Dim strItemName As String = String.Empty   

Dim arr() As Integer
ReDim arr(0)

'tento di estrapolare i figli
cmd.Connection = connection
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT IDdocumento_1 FROM tbRelazioniDocumenti WHERE IDdocumento_2=" & IDdoc2
dr = cmd.ExecuteReader
If dr.HasRows Then
    While dr.Read()
        ReDim Preserve arr(UBound(arr) + 1)
        arr(UBound(arr)) = dr.Item("IDdocumento_1")
    End While
Else
    Return 0
End If
cmd.Dispose()
dr.Close()

Dim i As Integer
For i = 1 To UBound(arr)
    Return CreateDiagram2(connection, arr(i), IDdoc2)
Next i

End Function

Phoenix Stoneham · Accepted Answer · 2020-04-03 12:11:08Z

1

When you close the datareader it also closes the connection. This means that when you move back up the stack the connection is now closed.

Additionally, you've got return CreateDiagram in your while loop. This will cause the method to execute CreateDiagram once and then return to the previous level.

I would suggest either using a try ... finally block around the creation item and the last if statement with the dispose and close in the finally or using blocks so that the command and reader are automatically disposed.

Next, I'd be inclined to put the results from datareader into a temporary table, and then iterate through the results recusively so that you don't run into trouble with your connection being closed while it's still being used.

Finally, is there any reason why you're not using a DataAdapter, and DataAdapter.fill(DataTable)? Once it has filled you could then use DataTable1.Load(DataTable2.CreateDataReader, upsert) to add the new rows to the existing datatable. See the official microsoft documentation here https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.load?view=netframework-4.8#System_Data_DataTable_Load_System_Data_IDataReader_System_Data_LoadOption_

Try this, I know I've modified a lot.

    Public Function CreateDiagram(connection As SqlConnection, IDdoc2 As Integer, IDdoc1 As Integer) As Integer

        'creation XtraDiagram
        Dim cmd As SqlCommand = New SqlCommand()
        'Dim dr As SqlDataReader
        Dim strItemName As String = String.Empty
        ' Dim dt As DataTable
        'creation item
        dt.Rows.Add(IDdoc2, strItemName, IDdoc1, "")
        Dim tmpdt As DataTable = dt.Clone
        Dim cmdstr As String = String.Format("SELECT  IDdocumento_1, {2} as strItemName, {1} as IDdoc1, '' as columnName3 FROM tbRelazioniDocumenti WHERE IDdocumento_2={0}", {IDdoc2, IDdoc1, strItemName})
        'extract how many children there are
        cmd.Connection = connection
        cmd.CommandType = CommandType.Text
        'cmd.CommandText = "SELECT IDdocumento_1 FROM tbRelazioniDocumenti WHERE IDdocumento_2=" & IDdoc2
        cmd.commandtext = cmdstr
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        da.Fill(tmpdt)
        cmd.Dispose()
        dt.Load(tmpdt.CreateDataReader, LoadOption.Upsert)
        ' dr = cmd.ExecuteReader
        'If dr.HasRows Then
        If tmpdt.Rows.Count > 0 Then
            For Each dr As DataRow In tmpdt.Rows
                'for every children
                CreateDiagram(connection, dr.Item("IDdocumento_1"), IDdoc2)
            Next
        End If
        Return 0
        'dr.Close()

    End Function

Or this which is your original with minimal changes

    Public Function CreateDiagram(connection As SqlConnection, IDdoc2 As Integer, IDdoc1 As Integer) As Integer

        'creation XtraDiagram
        Dim cmd As SqlCommand = New SqlCommand()
        Dim dr As SqlDataReader
        Dim l As New List(Of Integer)
        Dim strItemName As String = String.Empty
        Try

            'creation item
            dt.Rows.Add(IDdoc2, strItemName, IDdoc1, "")

            'extract how many children there are
            cmd.Connection = connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT IDdocumento_1 FROM tbRelazioniDocumenti WHERE 
IDdocumento_2=" & IDdoc2
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                While dr.Read()
                    l.Add(dr.Item("IDdocumento_1"))
                End While
            Else
                Return 0
            End If
        Finally
            cmd.Dispose()
            dr.Close()
        End Try
        For Each value In l
            'for every children
            CreateDiagram(connection, value, IDdoc2)
        Next

    End Function

edited Apr 3, 2020 at 12:11

answered Apr 2, 2020 at 15:20

Phoenix Stoneham

14711 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Turro Over a year ago

First thanks very much. If i understand the problem is the datareader lost connection and it can not execute iteration. For extract children i need to access in database. If i understand i need to access db and extract my info and put it in array and close datareader. After i can cycle the array in the same mode correct?

Turro Over a year ago

Exscuse me but i wrong something. I update code in first topic because i not able to copy code in this textbox

Turro Over a year ago

Phoenix i analyze your solution but in my test don't run. With your solution i debug step by step and i think i have find teh problem. The problem is the line that call recursive function. The line Return CreateDiagram(connection, value, IDdoc2) must be change in the line without RETURN like CreateDiagram(connection, value, IDdoc2). With this line the routine run ok. I test it also in the first my routine. I update the topics.

Turro Over a year ago

I confirm the error. Also with the solution with datareader, the routine run ok.

Phoenix Stoneham Over a year ago

If your question has been answered, can you mark the answer please?

Collectives™ on Stack Overflow

vb.net problem with recursive function inside while cycle

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related