VB.Net: Database/xml enabled, self-cacheing, TooltipDropDownList ServerControl
By Peter A. Bromberg, Ph.D.
Printer - Friendly Version

Peter Bromberg

About a year ago, I got on this ASP.NET ServerControl kick. I read the Wrox book on ASP.NET Server Controls, I literally devoured everything I could find that was written about them, I read Susan Warren's excellent tutorial, and I attempted to decompile as many controls as I could to try and find out how they were built and what they did.

In the process, I learned a great deal about ServerControls, and wrote three or four really good ones on my own. This particular control was my effort to cure some of the obvious deficiencies in the DropDownList, while adding a few neat features that I believed would be useful. The result is my "DbListBox" control, and I present it here.

The control has some unusual and very useful features:



1) You can set a DataSource query type (either stored procedure, SQL Text Query, or Xml suitable to load into a DataSet). For the SQL, you can set a Connection String right on the control. For a Stored Procedure, you can provide a parameter name and value. For a SQL Text Query, you can provide a "WHERE" Clause, and for an Xml Query, you can provide a URL to the XML datasource, which can be an HTTP url. The result is that the control takes care of its own databinding, and you don't have to write a line of code!

2) If you set a CacheId property name, your Data will be stored in Cache after the first "go" which greatly speeds up the performance of the DbListBox.

3) DropDownList does not support the Tooltip attribute, so I added a ToolTitle and a ToolText property, and if supplied, the control will render a nice - looking custom ToolTip on mouseover.

4) I also added a Licensing class which requires a License Key string (another property on the control) and I"ve left that in. Although I'm not giving away any of my custom Licensing code, anyone who is willing to experiment should be able to come up with something useful!

All the above features are enabled via standard Property Sheet entries on the control. Although I do almost all my .NET work in C#, I wrote this particular ServerControl in VB.NET. The result, including a number of properties I haven't mentioned above, results in a "DropDownList" server control that sports a whole bunch of very cool, user-configurable properties:

 

We start out with the required namespace and class definition, and private variable declarations and attributes, as follows:

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Diagnostics
Imports System.ComponentModel
Imports System.Web.UI.Design
Imports System.Xml
Imports System.IO
Namespace PAB.WebControls
<DefaultProperty("TableName"),Designer(GetType(PAB.WebControls.ControlDesigner)), ToolboxData("<{0}:DbListBox runat=server></{0}:DbListBox>")> _
Public Class DbListBox
Inherits System.Web.UI.WebControls.DropDownList
Private dt As New DataTable
Private _TableName As String = String.Empty
Private _DisplayColumn As String = String.Empty
Private _ValueColumn As String = String.Empty
Private _FirstField As Boolean = False
Private _FirstFieldText As String = String.Empty
Private _ConnectionString As String = String.Empty
Private _CacheId As String = String.Empty
Private _WhereClause As String = String.Empty
Private _CommandType As String = String.Empty
Private _StoredProcName As String = String.Empty
Private _ParameterValue As String = String.Empty
Private _ParameterName As String = String.Empty
Private _LicenseKey As String = String.Empty
Private _ToolText As String = String.Empty
Private _ToolTitle As String = String.Empty
Private _XmlUrl As String = String.Empty

Then we write out our property definitions (these all will appear in the Property Sheet). Since they all are done the same way, I will just show the first two:

<Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(True), DescriptionAttribute("Valid Trial or Permanent License Key String")> _
Public Property LicenseKey() As String
Get
Return _LicenseKey
End Get
Set(ByVal Value As String)
_LicenseKey = Value
End Set
End Property

<Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(True), DescriptionAttribute("Name of Table (if Command Type set to Text Query)")> _
Public Property TableName() As String
Get
Return _TableName
End Get
Set(ByVal Value As String)
_TableName = Value
End Set
End Property

The main method is LoadListItems:

Private Sub LoadListItems()
Try
Try
If Not Me.CacheId Is Nothing Then
dt = DirectCast(System.Web.HttpContext.Current.Cache(Me.CacheId), DataTable)
End If


Catch
End Try
If dt Is Nothing Then
Dim strQry As String = String.Empty
If Me.CommandType = "Xml" Then
dt = LoadXml()
Debug.WriteLine(dt.Rows.Count.ToString)
If Not Me.CacheId Is Nothing Then
System.Web.HttpContext.Current.Cache(Me.CacheId) = dt
End If

GoTo XML
End If

If Me.CommandType = "" Then Me.CommandType = "Text Query"
If Me.CommandType = "Text Query" Then
strQry = "SELECT " & Me.ValueColumn & ", " & Me.DisplayColumn & " FROM " & Me.TableName & " "
If Me.WhereClause <> "" Then
strQry += Me.WhereClause
End If
strQry += " ORDER BY " & Me.DisplayColumn
Else
strQry = Me.StoredProcName.Trim()
End If
Dim cn As New OleDbConnection(Me.ConnectionString)
cn.Open()
Dim cmd As New OleDbCommand(strQry, cn)
If Me.CommandType = "Stored Procedure" Then
cmd.CommandType = System.Data.CommandType.StoredProcedure
If Me.ParameterValue <> "" Then
cmd.Parameters.Add(New OleDbParameter(Me.ParameterName, Me.ParameterValue))
End If
End If
Dim da As New OleDbDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
cn.Close()
cmd.Dispose()
If Not Me.CacheId Is Nothing Then
System.Web.HttpContext.Current.Cache(Me.CacheId) = dt
End If
End If
XML:
Dim dt2 As DataTable = dt.Copy()
dt2.TableName = Me.TableName
Dim i As Integer
For i = 0 To dt2.Rows.Count - 1
Me.Items.Add(New ListItem(dt.Rows(i)(Me.DisplayColumn).ToString(), dt.Rows(i)(Me.ValueColumn).ToString()))
Next i
If Me.FirstField Then
If Me.Items(0).Text <> "" Then
Me.Items.Insert(0, New ListItem(Me.FirstFieldText, ""))
End If
End If
Dim lic As New PAB.WebControls.Licensing
If Not lic.CheckLicenseKey(Me.LicenseKey) Then
Me.Items.Insert(0, New ListItem("Unlicensed Evaluation", ""))
End If
Catch ex As Exception

Throw
End Try
End Sub

Notice that the first thing we do is check Cache to see if it's already stored. If not, we do the query (and we also store it in Cache). Basically all the rest of this method does is buld a ListItems collection. We also override the Save and LoadViewState methods:

Protected Overrides Function SaveViewState() As Object
Dim objItems(Me.Items.Count + 1) As Object
Try
If Not Page.IsPostBack Then
Me.LoadListItems()
End If
Dim baseState As Object = MyBase.SaveViewState()
objItems(0) = baseState
Catch ex As Exception
End Try
Return objItems
End Function

Protected Overrides Sub LoadViewState(ByVal savedState As Object)
Try
If Not (savedState Is Nothing) Then
Dim objState As Object() = CType(savedState, Object())
If Not (objState(0) Is Nothing) Then
MyBase.LoadViewState(objState(0))
End If
End If
Catch ex As Exception
End Try
End Sub

Finally, we need to override the Render and RenderContents methods to get our finished control's HTML and script out onto the page:

Protected Overrides Sub Render(ByVal tw As HtmlTextWriter)
MyBase.Render(tw)
' Browser detection, IE only, sorry Nutscrapers!
If MyBase.Page.Request.Browser.ActiveXControls Then
If Me.ToolText <> "" And Not Me.Page.IsStartupScriptRegistered("opop") Then
Dim strScript As String = "<script language=""javascript"">" & vbCrLf
strScript += "var oPopup = window.createPopup();" & vbCrLf
strScript += "function showToolTip(obj) {" & vbCrLf
strScript += "var x = obj.offsetLeft + window.screenLeft;" & vbCrLf
strScript += "var y = obj.offsetTop + window.screenTop;" & vbCrLf
strScript += "var tooltip = '<div style=""position: absolute; top:0; left:0; width:100%; height:100%; border:1px solid black; font:normal 10pt tahoma; filter:progid:DXImageTransform.Microsoft.Gradient(GradientType=0, StartColorStr=gold, EndColorStr=#FFFFFF); padding:5px"">';" & vbCrLf
strScript += "var title = obj.title;" & vbCrLf
strScript += "oPopup.document.body.innerHTML= tooltip + title + '</div>';" & vbCrLf
strScript += "var Hght = parseInt(title.length / 80); " & vbCrLf
strScript += "oPopup.show(x, y+15, 170, Hght *35);" & vbCrLf
strScript += "}" & vbCrLf
strScript += "</script>" & vbCrLf
Me.Page.RegisterStartupScript("opop", strScript)
End If
End If
End Sub

Protected Overrides Sub RenderContents(ByVal tw As HtmlTextWriter)
Try
Dim li As ListItem
For Each li In Me.Items
If li.Attributes.Count > 0 Then
tw.WriteBeginTag("option")
If li.Selected Then
tw.WriteAttribute("selected", "selected", False)
End If
li.Attributes.Render(tw)
tw.WriteAttribute("value", li.Value.ToString())
tw.Write(HtmlTextWriter.TagRightChar)
tw.Write(li.Text)
tw.WriteEndTag("option")
tw.WriteLine()
End If
Next li
Catch ex As Exception
tw.WriteBeginTag("option")
tw.WriteAttribute("value", "Error")
tw.Write(HtmlTextWriter.TagRightChar)
tw.Write(ex.Message)
tw.WriteEndTag("option")
tw.WriteLine()
End Try
Page.DataBind()
MyBase.RenderContents(tw)
End Sub

There are also some other neat methods I have such as LoadXml, and a type Converter to show the three different Command Types in the property sheet as a selectable list, but you can look at all that in the downloadable source code. I've also included a nice NDoc - generated CHM help file for you. Be sure to make the "DbListBoxWeb" folder an IIS VRoot Application before loading the solution, and run the included SQL script against your local SQL Server if you want to use the example on the page that uses a stored procedure. Enjoy!

Download the Example solution

 

Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.