When we are generating multiple reports in a production environment chances are the options we provide for filtering data will be identical across multiple projects. ASP.Net makes it easy for us to generate drop down menus etc but we can make it even easier by creating a UserControl to help with this:
The following is an example how to do it.
In this example, I only provide two menu options (add options as the situation requires):
- LOTSTAT (status of the lots your process in your production - Active, Hold, Finished, Terminated etc).
- OWN (owner of the lot - whether production, R&D etc)
It also provides for two menu types (add options as the situation requires):
- ListBox (allows selection of multiple values)
- DropDownList (allows for only selecting one value).
Start with a ascx file as follows:
<%@ Control Language="VB" AutoEventWireup="false" CodeBehind="ucMenu.ascx.vb" Inherits="PrgLib.UserControl.ucMenu" %>
<asp:DropDownList ID="dlCtrl" runat="server">
</asp:DropDownList><asp:ListBox ID="lbCtrl" runat="server" SelectionMode="Multiple" Rows="5"></asp:ListBox>
<asp:Label ID="lblMsg" runat="server" />
Then the code behind maybe something like this:
' User Control for Report Filtering
' Author Strovek
' Date Sep 01, 2006
' Revised
'
' A usercontrol that can be shared by multiple reports to facilitate the use of a
' common cache (information is stored in system_lookup table)
' ==========================================================================
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Reflection
Imports System.Collections
Imports System.Web
<Assembly: AssemblyVersionAttribute("1.00.0")>
<Assembly: AssemblyCompanyAttribute("Programmer's Journal")>
<Assembly: AssemblyProductAttribute("Report Filter User Control")>
<Assembly: AssemblyFileVersion("1.0.0")>
Namespace PrgLib.UserControl
Public Class ucMenu
Inherits System.Web.UI.UserControl
#Region "Variables"
Protected WithEvents lbCtrl As System.Web.UI.WebControls.ListBox
Protected WithEvents dlCtrl As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblmsg As System.Web.UI.WebControls.Label
Dim vDataTypeOpt As String = "OWN"
Dim vSelOpt As String = "DL"
Dim vDateTypeLim As String = "N"
Dim vConnStrKey As String = Nothing
Dim vConnStr As String = Nothing
Dim vCache As System.Web.Caching.Cache
Dim ds As New DataSet
Dim dTab As New DataTable("MenuTab")
Dim Conn As New OleDbConnection
Const dataOptList As String = "OWN,LOTSTAT"
Const selOptList As String = "DL,LB" 'Drop Downlist or ListBox
Const cacheName As String = "ucMenuCache"
#End Region
#Region "Public Properties"
'Property for setting data type
Public Property dataTypeOpt() As String
Get
Return vdataTypeOpt
End Get
Set(ByVal value As String)
If dataOptList.IndexOf(UCase(value)) > -1 Then
vDataTypeOpt = UCase(value)
End If
End Set
End Property
'To decide if to use Dropdownlist or ListBox
Public Property selOpt() As String
Get
Return vSelOpt
End Get
Set(ByVal value As String)
If selOptList.IndexOf(UCase(value)) > -1 Then
vSelOpt = UCase(value)
End If
End Set
End Property
' Sets the Connection string to Connect to database
' Need to be done otherwise the user control will not work
Public Property ConnStrKey() As String
Get
Return vConnStrKey
End Get
Set(ByVal value As String)
vConnStrKey = value
End Set
End Property
Public Property selectedVal() As String
Get
Dim vVal As String = ""
Dim vTmpStr As String
Dim sb As New System.Text.StringBuilder
Dim vArrList As New ArrayList
If vSelOpt = "DL" Then
vVal = dlCtrl.SelectedValue
Else
For Each vTmpStr In lbCtrl.SelectedValue
If sb.Length > 0 Then
sb.Append(",")
End If
sb.Append(vTmpStr)
Next
vVal = sb.ToString
End If
Return vVal
End Get
Set(ByVal value As String)
If vSelOpt = "DL" Then
dlCtrl.SelectedValue = value
Else
Dim vStr As String
Dim vItem As System.Web.UI.WebControls.ListItem
Dim vArrayList As New ArrayList
For Each vStr In Split(value, ",", -1, CompareMethod.Text)
vArrayList.Add(vStr)
Next
For Each vItem In lbCtrl.Items
If vArrayList.Contains(vItem.Value) Then
vItem.Selected = True
Else
vItem.Selected = False
End If
Next
End If
End Set
End Property
#End Region
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
If IsPostBack = False Then
fillDS()
If vSelOpt = "DL" Then
lbCtrl.Visible = False
dlCtrl.Visible = True
Else
lbCtrl.Visible = True
dlCtrl.Visible = False
End If
bindCtrl()
End If
Catch ex As Exception
Throw New Exception("[ucFWRptFilter01]" & ex.Message)
End Try
End Sub
Private Sub bindCtrl()
Dim dView As New DataView
Select Case vDataTypeOpt
Case "OWN"
dView = New DataView(dTab, "Type = 'OWNCAT'", "minval", DataViewRowState.CurrentRows)
Case "LOTSTAT"
dView = New DataView(dTab, "Type = 'LOTSTATCAT'", "minval", DataViewRowState.CurrentRows)
End Select
doBindCtrl(dView, "opt")
End Sub
' Helper routine which decides which control to bind
Private Sub doBindCtrl(ByVal dView As DataView, ByVal colName As String)
If vSelOpt = "DL" Then
dlCtrl.DataSource = dView
dlCtrl.DataTextField = colName
dlCtrl.DataBind()
Else
lbCtrl.DataSource = dView
lbCtrl.DataTextField = colName
lbCtrl.DataBind()
End If
End Sub
' Fill the dataSet or get from cache
Public Sub fillDS()
Dim sql, preSQL, postSQL As String
Dim dbCmd As OleDbCommand
Dim dbAdapt As New OleDbDataAdapter
Dim cCache As New System.Web.Caching.Cache
Try
If IsNothing(vConnStrKey) Then
Throw New Exception("[fillDS01]Connection String not set")
Trace.Write("debug", "No connection string")
Else
Trace.Write("debug", "Conn string is set")
vConnStr = System.Configuration.ConfigurationSettings.AppSettings(vConnStrKey)
vCache = Page.Cache
If IsNothing(vCache.Get(cacheName)) Then
Conn = New OleDbConnection(vConnStr)
Conn.Open()
dbCmd = Conn.CreateCommand
sql = "select opt, type, minval, lookupkey from system_lookup where systemid = 'MENU' order by type, minval"
dbCmd.CommandText = sql
dbAdapt.SelectCommand = dbCmd
dbAdapt.Fill(dTab)
dbCmd.Dispose()
Conn.Close()
vCache.Add(cacheName, dTab, Nothing, _
DateTime.Now.AddSeconds(3600), TimeSpan.Zero, _
System.Web.Caching.CacheItemPriority.Normal, Nothing)
Else
dTab = vCache.Get(cacheName)
End If
End If
Catch ex As Exception
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
Throw New Exception("[fillDS02]" & ex.Message)
Finally
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
End Try
End Sub
End Class
End Namespace
In the above example, I created a table called System_Lookup to store all the menu values. Although the example used here is an Oracle Table, you can customize it to use any other database. Populate the menu options into the table. Add columns as the situation requires.
The table structure of the system_lookup is as follows:
SYSTEMID VARCHAR2(20 BYTE)
TYPE VARCHAR2(40 BYTE)
OPT VARCHAR2(40 BYTE)
ENTITLEMENT NUMBER
UPDTIME DATE
MODIFIER_ID VARCHAR2(10 BYTE)
MINVAL NUMBER
LOOKUPKEY VARCHAR2(60)
You can then use NANT to compile to above code into an independent .dll file and copy it together with the .ascx file to your various projects.
The build file will look like this:
<?xml version="1.0"?>
<project name="ucMenu" default="build" basedir=".">
<property name="project.name" value="User Control for Menu Options"/>
<property name="project.version" value="1.0.0"/>
<description>The ucMenu build files.</description>
<property name="debug" value="true" unless="${property::exists('debug')}" />
<property name="verbose" value="true" />
<target name="clean" description="remove all generated files">
<delete file="bin\ucMenu.dll" failonerror="false" />
<delete file="bin\ucMenu.pdb" failonerror="false" />
</target>
<target name="build" description="compiles the source code">
<vbc target="library" output="bin\ucMenu.dll" debug="${debug}">
<sources>
<include name="UserControl\ucMenu.ascx.vb" />
</sources>
<references>
<include name="System.dll" />
<include name="System.Web.dll" />
<include name="System.Web.RegularExpressions.dll" />
<include name="System.XML.dll" />
<include name="System.Web.Services.dll" />
<include name="System.Data.dll" />
</references>
</vbc>
</target>
</project>
You can then use it in your aspx pages as follows (where dbConn represents the ConnectionString from the project - provides more flexibility instead of hardcoding it to the usercontrol):
<%@ Register Src="UserControl/ucMenu.ascx" TagName="ucMenu" TagPrefix="uc1" %>
<uc1:ucMenu id="UcMenu_Own" dataTypeOpt="OWN" ConnStrKey="dbConn" selopt="LB" runat="server"></uc1:ucMenu>
Additional Notes:
- When ListBox option is used, the selectedVal property will return the values in comma delimited list. This is done to ease setting and retrieving selected value.
- Cache is used to improve performance
