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
Dim ColLabels() As String = sColumns.Split("~")
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
Dim xmldoc As XmlDocument = New XmlDocument()
xmldoc.LoadXml(sXML)
Dim col As XmlNode = xmldoc.DocumentElement
Dim colist As XmlNodeList = col.ChildNodes
Dim iCol As Integer = colist.Count
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
Dim ColLabels() As String = sColumns.Split("~")
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()
Dim xmldoc As XmlDocument = New XmlDocument()
xmldoc.LoadXml(sXML)
Dim col As XmlNode = xmldoc.DocumentElement
Dim colist As XmlNodeList = col.ChildNodes
Dim iCol As Integer = colist.Count
Do While dr.Read
oRow = New TableRow()
oRow.CssClass = "tableDataBackground"
Dim sFld As String
For Each colnode In colist
linelist = colnode.ChildNodes
ilines = linelist.Count
Dim sLineList As String = ""
For Each linenode In linelist
itemlist = linenode.ChildNodes
iitems = itemlist.Count
Dim sItems As String = ""
For Each itemnode In itemlist
If itemnode.Attributes.Item(1).Value = "FIELD" Then
name = itemnode.Name
value = itemnode.InnerText
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
sItems = sItems + value
ElseIf itemnode.Attributes.Item(1).Value = "LINK" Then
ilist = itemnode.ChildNodes
iField = ilist.Count
Dim sLink, sLabel, sQString As String
For Each inode In ilist
endnode = inode
name = endnode.Name
value = endnode.InnerText
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>"
End If
Next
sLineList += sItems
Next
oCell = New TableCell() : _
oCell.HorizontalAlign = HorizontalAlign.Left
oCell.Text = sLineList
oRow.Cells.Add(oCell) : oCell.Dispose()
Next
oRow.VerticalAlign = VerticalAlign.Top
oTable.Rows.Add(oRow)
oRow.Dispose()
Loop
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.
Musa is an MCAD and MCSD - Early Achiever in .Net. He is PMP certified and holds a Bachelors Degree in Electrical Engineering and a Post Graduate Diploma in Software Development. Currently he is working as Senior Developer in New York.