Populate Table Control dynamically from DataReader according to format given in an XML file

Introduction

Last week, I was developing a few pages for a content management application. There, I encountered this problem.

Problem

We were using the SQL Data Reader to extract the results for various search screens from SQL Server 2000. You know that the code for displaying the various search results is always quite similar. We move through the datareader, extract the required fields from the data reader and display it in a tabular manner. Our project had a lot of search / results pages. The nature of the searches was that every time only a handful of records (10 - 20 at the max.) would show up. We wanted to avoid writing the code for each and every search results page. Also, the same application was to be customized for different clients. We wanted flexibility. I was asked to come up with a generalized function which should be called by all the search pages and it should display the correct results. I should provide some sort of configuration mechanism for that function too.

If it is getting confusing, let me explain it in another way. I have to use a datareader which will be passed to my generalized function by some other method. This data reader would contain the records that a certain search brought. I have to display the results of that search into a given <asp:Table> control. I do not know anything about the fields of the records contained in the data reader. Also I do not know anything about the format in which this data is to be presented. The constraint is that the data display should be in the given table control (of course on an aspx page). The problem is how would I know about which fields In need to display and then in which format are these fields to be displayed? I found the solution in XML.

XML

Here is what I did. I decided to use an XML file for telling the function about what and how to display the results. This XML file gave me

• the required fields in the data reader that I had to use and
• the format in which I had to present that data on the aspx page.

To simplify, I am given the data reader containing records, a table control in which I have to display the records and the XML file came to my help and it tells me which fields to extract from the datareader and how they are to be displayed in the table control.

Signature of the Method to be developed

Here is the signature of the generalized method that I developed for this problem:

Public Function SearchResults(ByVal strXML as string, _
  ByVal sColumns As string, _
  ByVal oTable As Table, byVal sRedirect as string, _
  ByRef dr As SqlClient.SqlDataReader) As Long

Here strXML - contains the XML used for the format oTable - < asp:table > control, I have to create its rows and cells and show the results in this table control dynamically; dr - data reader which contains the data in the form of records from SQL Server 2000; sColumns - Columns labels for the table delimited by ~. Suppose if it has

sColumns = Product Name ~ Description ~ Options

it means that there will be three columns in the table namely Product Name, Description and Unit Price. sRedirect - redirection URL, if you want to show a link (just as an example).

Format given in XML

Here is the XML that is fed to my method for one of the search pages. Each page will have its own XML that is to be fed to the method.

<COLUMNS>
  <COLUMN index='0'>
    <LINE index='0'>
      <ITEM index='0' type='FIELD' datatype = 'String'> ProductCode</ITEM>
    </LINE>
  </COLUMN>
  <COLUMN index='1'>
    <LINE index='0'>
    <ITEM index='0' type='FIELD' datatype = 'String'>Name</ITEM>
    </LINE>
    <LINE index='1'>
    <ITEM index='0' type='FIELD' datatype='String' > ProductClassName</ITEM>
    </LINE>
    <LINE index='2'>
     <ITEM index='0' type='FIELD' datatype = 'Integer' >  LowerNodeCount</ITEM>
     <ITEM index='1' type='LITERAL'>to </ITEM>
     <ITEM index='2' type='FIELD' datatype = 'Integer'>  UpperNodeCount</ITEM>
     <ITEM index='3' type='LITERAL'>Nodes</ITEM>
    </LINE>
  </COLUMN>
  <COLUMN index='2'>
    <LINE index='0'>
      <ITEM index='0' type='LINK'>
        <LABEL>Select</LABEL>
    <FIELD_NAME datatype = 'Integer'>ProductID
       </FIELD_NAME>
    <FIELD_NAME datatype = 'Integer'>Quantity
        </FIELD_NAME>
    <FIELD_NAME datatype = 'Decimal'>UnitPrice
        </FIELD_NAME>
      </ITEM>
    </LINE>
  </COLUMN>
</COLUMNS>

So in this scenario, columns have lines and lines have items and items may have fields. This particular xml shows that there will be 3 columns to be displayed. Column 1 will have a single line, columns 2 will have 3 lines and columns 3 will have only 1 line. The items in each line are described by their item type, field name and data type. For example, for column 1 , I have to display one line and I have to display the string field "ProductCode" from the data reader in this line and so on ... For the above mentioned XML, the display should look like this:

Product Name	Description	Options
Data in ProductCode field	Data in Name field Data in ProductClassName field Data in LowerNodeCount field + "to" + Data in UpperNodeCount field + "Nodes"	strRedirect as hyperlink with ProductID, Quantity and UnitPrice as QueryString and "Select" as label for this hyperlink

Code

Now there are two approaches that you can adopt.

• If you fix the format of the XML file, you can persist the XML in an object and can use this object to display the search results. This is the efficient way to go about it.
• However, if you do not know about the format of the XML file, then persisting it to some object/objects is difficult. Then you can use the method that I have presented here.

The drawback of the second approach is the you will be parsing the XML file for each record. For smaller chunks of data as it is in my case, it is ok, but for large data, it is quite inefficient. I am presenting the actual code here. You can read the code and understand my implementation. Here are the steps that I followed:

1. First, find the column labels using split function and build the header row of the table

'extract the columns labels from the sColumns string
 Dim ColLabels() As String = sColumns.Split("~")
 'build the header of the table with these column labels 
 oRow = New TableRow()
 oRow.CssClass = "tableHeaderBackground"
 For iCols = 0 To UBound(ColLabels)
   oCell = New TableCell() : oCell.HorizontalAlign = HorizontalAlign.Left
   oCell.Text = ColLabels(iCols)
   oRow.Cells.Add(oCell) : oCell.Dispose()
 Next
 oRow.VerticalAlign = VerticalAlign.Top
 oTable.Rows.Add(oRow)
 oRow.Dispose()

2. Load the xml using the xmldocument and get the Columns nodes as a list

' get the xml format and build the search results
 Dim xmldoc As XmlDocument = New XmlDocument()
 xmldoc.LoadXml(sXML)
 Dim col As XmlNode = xmldoc.DocumentElement 'columns
 Dim colist As XmlNodeList = col.ChildNodes ' column list
 Dim iCol As Integer = colist.Count ' no of columns

3. Iteratre through the records in the datareader one by one by putting this statement in the outermost loop as

Do While dr.Read

loop

4. Then, I loop through the nodes one by one as shown in the code below.

Actual Method to display the search results

Here is the actual method that I developed for the above mentioned scenario.

Public Function DispalySearchResults(ByVal strXML as string, _
ByVal sColumns As string, _
    ByVal oTable As Table, byVal sRedirect as string,_
 ByRef dr As SqlClient.SqlDataReader) As Long
        Dim lRetVal As Long
        Dim colnode, linenode, itemnode, endnode, inode, _
            fieldnode As XmlNode
        Dim linelist, itemlist, fieldlist, ilist As XmlNodeList
        Dim i, ilines, iitems, iField As Integer
        Dim name, sColumns, sXML, sRedirect As String
        Dim value As String
        Dim oRow As TableRow
        Dim oCell As TableCell
        Dim iRowCount As Integer = 0
        Dim iContentTypeID, iValueInt, iCols As Integer
        Try

            'extract the columns labels from the sColumns string
            Dim ColLabels() As String = sColumns.Split("~")
            'build the header of the table with these column labels 
            oRow = New TableRow()
            oRow.CssClass = "tableHeaderBackground"
            For iCols = 0 To UBound(ColLabels)
                oCell = New TableCell() : oCell.HorizontalAlign = _
                    HorizontalAlign.Left
                oCell.Text = ColLabels(iCols)
                oRow.Cells.Add(oCell) : oCell.Dispose()
            Next
            oRow.VerticalAlign = VerticalAlign.Top
            oTable.Rows.Add(oRow)
            oRow.Dispose()
            ' get the xml format and build the search results
            Dim xmldoc As XmlDocument = New XmlDocument()
            xmldoc.LoadXml(sXML)
            Dim col As XmlNode = xmldoc.DocumentElement 'columns
            Dim colist As XmlNodeList = col.ChildNodes ' column list
            Dim iCol As Integer = colist.Count ' no of columns

            Do While dr.Read
                oRow = New TableRow()
                oRow.CssClass = "tableDataBackground"
                Dim sFld As String
                For Each colnode In colist ' columns loop
                    linelist = colnode.ChildNodes ' lines in a column
                    ilines = linelist.Count ' no of lines in a column
                    
                    Dim sLineList As String = ""
                    For Each linenode In linelist ' lines loop
               itemlist = linenode.ChildNodes ' items in a line in a column
               iitems = itemlist.Count ' no of items in a line in a column
               Dim sItems As String = ""
                  For Each itemnode In itemlist ' items loop
                      If itemnode.Attributes.Item(1).Value = "FIELD" Then
                          name = itemnode.Name
                          value = itemnode.InnerText ' value of the field
                    'get the field from the datareader and add it to string
                    'note: in future, add data types according to the format
                    If itemnode.Attributes.Item(2).Value = "String" Then
                        sFld = dr.GetString(dr.GetOrdinal(value))
                        sItems = sItems + sFld + "<br>"
                    ElseIf itemnode.Attributes.Item(2).Value = "Integer" Then
                        sFld = (dr.GetInt32(dr.GetOrdinal(value))).ToString
                        sItems = sItems + sFld
                    ElseIf itemnode.Attributes.Item(2).Value = "Decimal" Then
                        sFld = (dr.GetDecimal(dr.GetOrdinal(value))).ToString
                        sItems = sItems + sFld
                    End If

                      ElseIf itemnode.Attributes.Item(1).Value = "LITERAL" Then
                          name = itemnode.Name
                          value = itemnode.InnerText ' value of the field
                          sItems = sItems + value
                      ElseIf itemnode.Attributes.Item(1).Value = "LINK" Then
                          ilist = itemnode.ChildNodes ' fields in an item 
                          iField = ilist.Count ' no of fields in an item 
                          Dim sLink, sLabel, sQString As String
                          For Each inode In ilist  'fields loop or text node loop 
                              endnode = inode
                              name = endnode.Name
                              value = endnode.InnerText ' value of the field
                              If name = "FIELD_NAME" Then
                    'note: in future, add data types according to the format
                    If endnode.Attributes.Item(0).Value = "String" Then
                      sLink = dr.GetString(dr.GetOrdinal(value))
                      ElseIf endnode.Attributes.Item(0).Value = "Integer" Then
                         sLink = (dr.GetInt32(dr.GetOrdinal(value))).ToString
                      ElseIf endnode.Attributes.Item(0).Value = "Decimal" Then
                         sLink = (dr.GetDecimal(dr.GetOrdinal(value))).ToString
                      End If
                          sQString += "&" + value + "=" + sLink
                      Else
                         sLabel = value
                      End If
                                Next
                                
                     sItems = "<a href='" + sRedirect + sQString + _
                 "'>" + sLabel + "</a>" ' this will contain the 
                            ' link label and any qs value
                            End If
                        Next
                        sLineList += sItems
                    Next
                    ' make a cell for the row
                    oCell = New TableCell() : _
               oCell.HorizontalAlign = HorizontalAlign.Left
                    oCell.Text = sLineList
                    oRow.Cells.Add(oCell) : oCell.Dispose()
                Next
                'add the row to the table 
                oRow.VerticalAlign = VerticalAlign.Top
                oTable.Rows.Add(oRow)
                oRow.Dispose()
            Loop
            'success
            Return 0
        Catch ex As Exception
            If lRetVal <> 0 Then Return lRetVal
            Return Err.Number
        Finally
           If Not dr Is Nothing Then
                If Not dr.IsClosed Then dr.Close()
                dr = Nothing
            End If
        End Try
    End Function

Now, this becomes a generalized method and can be used anywhere to display the search results. You have to supply the format in XML along with the actual datareader.

Alternate approach

Another easier approach would be to use a repeater control. In that case, use the XML file to set up the Repeater control. Then bind the Repeater to the DataReader. However, for the solution presented above, the constraint was that the data had to be shown in the Table control.

Final Word

My main aim of writing this article is just to share the concept. I hope that I am able to convey it. You can send me an email if you have any questions.