Populate Table Control Dynamically from DataReader According to a Format Given in an XML File

Introduction

Last week, I was developing a few pages for a content management application. There, I encountered the following 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 that 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 that 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 I 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 following:

  • 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. 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 is the data reader that contains the data in the form of records from SQL Server 2000; sColumns is the Columns labels for the table delimited by a ~. Suppose 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 is the 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>

In this scenario, columns have lines, lines have items, and items may have fields. This particular XML shows that there will be three columns to be displayed. Column 1 will have a single line, column 2 will have three lines, and column 3 will have only one 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 fieldData 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

There are two approaches that you can adopt:

  1. 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.
  2. However, if you do not know about the format of the XML file, 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 okay, 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 by using a 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 by 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 put 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 following code.

Populate Table Control Dynamically from DataReader According to a Format Given in an XML File

Introduction

Last week, I was developing a few pages for a content management application. There, I encountered the following 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 that 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 that 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 I 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 following:

  • 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. 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 is the data reader that contains the data in the form of records from SQL Server 2000; sColumns is the Columns labels for the table delimited by a ~. Suppose 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 is the 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>

In this scenario, columns have lines, lines have items, and items may have fields. This particular XML shows that there will be three columns to be displayed. Column 1 will have a single line, column 2 will have three lines, and column 3 will have only one 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 fieldData 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

There are two approaches that you can adopt:

  1. 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.
  2. However, if you do not know about the format of the XML file, 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 okay, 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 by using a 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 by 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 put 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 following code.

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
              
               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
               
               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.

Alternative 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 e-mail if you have any questions.



About the Author

Muhammad Musa Ali

MCAD, MCSD - Early Achiever in .Net, has more than seven years of experience in software development and system analysis, currently working as a consultant for a fortune 500 company in Chicago.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Instead of only managing projects organizations do need to manage value! "Doing the right things" and "doing things right" are the essential ingredients for successful software and systems delivery. Unfortunately, with distributed delivery spanning multiple disciplines, geographies and time zones, many organizations struggle with teams working in silos, broken lines of communication, lack of collaboration, inadequate traceability, and poor project visibility. This often results in organizations "doing the …

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds