WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Last week, I was developing a few pages for a content management application. There, I encountered the following 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.
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:
|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|
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, 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:
- 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()
- 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
- Iteratre through the records in the datareader one by one by put this statement in the outermost loop as:
Do While dr.Read loop
- Then, I loop through the nodes one by one as shown in the following code.