Sorted List and Web Server Control

This article shows you to take advantage of SortedList in data binding with Web Server controls.

We are familiar with the "Select distinct <field> from <table>".This SELECT statement is used to filter out duplicate results from a query's output. Lets consider the handy Database Northwind and Table Suppliers. The field Country has duplicate records. Normally the Stored Procedures are already created and we are supposed to work on it.

The Stored Procedure is as follows:

Create Procedure GetSuppliers AS

SELECT  * FROM Suppliers

Our task is to display Country. Calling just this stored Procedure would give us all the Data. Even binding this with any of the server control would give duplicated countries. .NET Framework Class Libraries has System.Collections namespace that contains interfaces and classes that define various collection objects such as lists, queues, bit arrays, HashTables and dictionaries.

The appropriate Class that can be used to tackle with the issue would be SortedList. The SortedList represents a collection of key-and-value pairs that are sorted by the keys and are accessible by key and by index. For more details refer SortedList. We woould not go in details of the SortedList but take what's required for us to do the coding. In Our case we have the field Country which has duplicate values. So when we try to add the values in the SortedList we'll first check if the value exists.

Moving to the coding. We'll have a Webform with a DropDownlist.

The steps are as follows:

Step 1: Populate the DataSet with the DataAdapter

Dim sqlStmt As String = "GetSuppliers"
Dim conString As String = "server=localhost;database=Northwind;uid=sa;pwd=;"
Dim myda As SqlDataAdapter = New SqlDataAdapter(sqlStmt, conString)
myda.SelectCommand.CommandType = CommandType.StoredProcedure
myda.Fill(ds, "Table")

Step 2: Populating the SortedList based on the values in the Field Country

Dim slist As SortedList = New SortedList
Dim i As Integer
dr As DataRow
For Each dr In ds.Tables(0).Rows
If Not slist.ContainsValue(dr("Country")) Then
slist.Add(dr("Supplierid"), dr("Country"))
End If

Step 3
: Populate the DropDownList with the values in SortedList.

DropDownList1.DataSource = slist.GetValueList

Note : In case of Populating a Datalist

In webform1.aspx

asp:DataList id="DataList1" runat="server">
<%#Container.DataItem %>

In webform1.aspx.vb

DataList1.DataSource = slist.GetValueList