Converting XML Reader to SQL Reader

[gderosa]

Hey everyone, I have a function that reads XML data and converts it into a string, is there anyway to convert this to read from a SQL database while keeping the same functionality? This code is part of an Autofill user control. Here is the code:

     Private Function GetFilteredData(ByVal filter As String) As DataTable
        Dim users As String = _
            "<users>" & _
            "<usertable><userid>1</userid><username>John Doe</username></usertable>" & _
            "<usertable><userid>2</userid><username>Jane Doe</username></usertable>" & _
            "<usertable><userid>3</userid><username>Ant Hill</username></usertable>" & _
            "<usertable><userid>4</userid><username>Ben Ten</username></usertable>" & _
            "<usertable><userid>5</userid><username>Craig List</username></usertable>" & _
            "<usertable><userid>6</userid><username>Godfrey Jones</username></usertable>" & _
            "<usertable><userid>7</userid><username>Tonya Roberts</username></usertable>" & _
            "</users>"

        Dim ds As New DataSet
        Dim xReader As System.IO.StringReader = New System.IO.StringReader(users)
        ds.ReadXml(xReader, XmlReadMode.Auto)
        Return ConvertDataRowArrayToDataTable(ds.Tables(0).Select(filter))
    End Function

Thanks for your help!!!

[gderosa]

Oops, I forgot to include the full code..here it is:

Hey everyone, I have a function that reads XML data and converts it into a string, is there anyway to convert this to read from a SQL database while keeping the same functionality? This code is part of an Autofill user control. Here is the code:

     Private Function GetFilteredData(ByVal filter As String) As DataTable
        Dim users As String = _
            "<users>" & _
            "<usertable><userid>1</userid><username>John Doe</username></usertable>" & _
            "<usertable><userid>2</userid><username>Jane Doe</username></usertable>" & _
            "<usertable><userid>3</userid><username>Ant Hill</username></usertable>" & _
            "<usertable><userid>4</userid><username>Ben Ten</username></usertable>" & _
            "<usertable><userid>5</userid><username>Craig List</username></usertable>" & _
            "<usertable><userid>6</userid><username>Godfrey Jones</username></usertable>" & _
            "<usertable><userid>7</userid><username>Tonya Roberts</username></usertable>" & _
            "</users>"

        Dim ds As New DataSet
        Dim xReader As System.IO.StringReader = New System.IO.StringReader(users)
        ds.ReadXml(xReader, XmlReadMode.Auto)
        Return ConvertDataRowArrayToDataTable(ds.Tables(0).Select(filter))
    End Function

    Public Function ConvertDataRowArrayToDataTable(ByVal drAry As DataRow(), _

Optional ByVal DistinctColumn As String = "") As DataTable
        Dim dt As New DataTable
        If drAry.Length > 0 Then
            Dim dr As DataRow
            Dim hshDistinct As New Hashtable
            Dim hshKey As String = ""
            For Each dc As DataColumn In drAry(0).Table.Columns
                dt.Columns.Add(dc.ColumnName, dc.DataType)
            Next
 
            For i As Integer = 0 To drAry.Length - 1
                dr = dt.NewRow
                For Each dc As DataColumn In drAry(i).Table.Columns
                    dr(dc.ColumnName) = drAry(i)(dc.ColumnName)
                Next
 
                If DistinctColumn <> "" Then
                    hshKey = dr(DistinctColumn).ToString()
                    If hshDistinct.Contains(hshKey) = False Then
                        dt.Rows.Add(dr)
                        hshDistinct.Add(hshKey, Nothing)
                    End If
                Else
                    dt.Rows.Add(dr)
                End If
            Next
        End If
        Return dt
    End Function

Thanks for your help!!![/QUOTE]