Com And Hosting

Sometimes you might want to create a dynamic drop-down list to bind data from database table instead of hard coding. In my recent application development I wanted to create a custom user control for dynamic drop-down list with data bind from table and re-use it through out the pages. This will reduce lot of hard coding for drop-down list values and quite easy to change them down the track instead of going through each page. So I have created a parameter table that stores all the parameters data and retrieve them using code.

Here is the table structure for creating a param table in SQL server –

USE [test]
GO

/****** Object:  Table [dbo].[rtt_param]    Script Date: 07/27/2012 10:40:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[rtt_param](
	[pId] [int] IDENTITY(1,1) NOT NULL,
	[CODE] [nvarchar](50) NULL,
	[description] [nvarchar](120) NULL,
	[returnVal] [nvarchar](60) NULL,
 CONSTRAINT [PK_rtt_param] PRIMARY KEY CLUSTERED 
(
	[pId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

In the above param table the idea is to use the code in select statement to retrieve specific values. I have added two more columns for display value and return value so that it will give me the flexibility use different text for description and actual value behind the scene. Here is the snapshot of some table data.

I have actually created a custom aspx page to add or delete parameters so that I do not have to interact with the database directly.

Once I have some parameters ready to go, I have created a custom user control for drop-down list and bind data from this param table. Here is the code for custom user control. My user control is called dynlov.ascx. Here is the code for that –

<%@ Control Language="C#" ClassName="dynlov" %>

<script runat="server">

public string dynSelected
{
get
{
    return dynDDL.SelectedValue;

}
set
{
    dynDDL.SelectedValue = value;
}
}

 public string code  
{
    get
    {
        return code;
    }
    set
    {
        dsCode.SelectParameters.Add("CODE", value);
    }

}

</script>

<asp:DropDownList ID="dynDDL" runat="server" DataSourceID="dsCode" DataTextField="description"
    DataValueField="returnVal" AppendDataBoundItems="true">
    <asp:ListItem Value="" Text="%" />
</asp:DropDownList>
<asp:SqlDataSource ID="dsCode" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
    SelectCommand="SELECT [description], [returnVal] FROM [rtt_param] WHERE ([CODE] = @CODE) order by code asc">
</asp:SqlDataSource>

You notice I am using basic set and get functionality to set the value of the dropdownlist.  First string dynSelected gets the dropdown list value and set the value to DDL property. Now as I want to bind the dropdown list with database table I have used simple SqlDataSource control with simple select statement. In the where clause I have added the parameter but I want to get the parameter dynamically from user control. So I have used another public string to get the condition value for CODE.

So far so good, lets try now to implement the custom user control in our aspx page. To be honest with you this reduced my hard coding in each page a lot. Now I can bind the custom drop down list in one line where before I had to write SqlDataSource and ddl for each dropdown list. In my application I had over 15 drop-down-list sourcing data from the param table. Here is the code to include that in aspx page –

First register the custom user control in the header section of the page –

<%@ Page Title="" Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="false" CodeFile="siteDetails.aspx.vb" Inherits="siteDetails" %>
<%@ Register Src="~/controls/dynlov.ascx" TagName="lov" TagPrefix="dl"  %>

Once you register the control, it will be easily accessible from the page. Now include the control in the page. I have used it inside the formview so that I can use it for two way data bind.

 <dl:lov ID="fsd" runat="server" code="UNIVERSAL_ACCESS" dynSelected='<%# Bind("UNIVERSAL_ACCESS") %>' />

I have just included only the relevant user control tag for this custom user control. From the above code you can see I have added code value to select in SqlDataSource control and two way data bind inside the formview. See the screen shot below when rendered –

Now it works good and I can use only one line to retrieve data using different param code. Now I realize this dropdown list does not have any validation what about if you want to make any of these dropdown list mandatory. Ok good point, to make it more flexible I have included validation control so that when can set validation as we like. Here is the final code for the custom user control –

<%@ Control Language="C#" ClassName="dynlov" %>

<script runat="server">

public string dynSelected
{
get
{
    return dynDDL.SelectedValue;

}
set
{
    dynDDL.SelectedValue = value;
}
}

 public string code  
{
    get
    {
        return code;
    }
    set
    {
        dsCode.SelectParameters.Add("CODE", value);
    }

}

 public Boolean validate
 {
     get
     {
       return  validate;
     }
     set
     {
         if (value == true)
         {
             RequiredFieldValidator1.Enabled = true;
         }
     }
 }

</script>

<asp:DropDownList ID="dynDDL" runat="server" DataSourceID="dsCode" DataTextField="description"
    DataValueField="returnVal" AppendDataBoundItems="true">
    <asp:ListItem Value="" Text="%" />
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="This field required." ForeColor="Red" ControlToValidate="dynDDL" Enabled="false" ></asp:RequiredFieldValidator>

<asp:SqlDataSource ID="dsCode" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
    SelectCommand="SELECT [description], [returnVal] FROM [rtt_param] WHERE ([CODE] = @CODE) order by code asc">
</asp:SqlDataSource>

As you can see I have included a RequiredFieldValidator and the enabled property to false so that I can enable or disable programmatically as I want. I have included another get and set function boolean which can be accessed from the aspx page. So when I want to enable the validation I just use like this –

<dl:lov ID="dlCampingFees" runat="server" code="CAMPING_FEES" validate="true" dynSelected='<%# Bind("CAMPING_FEE") %>' />

Now it will not allow user to enter null or blank field for this dropdown list. This can be extended to much more functionality like adding validation error message etc. Here is the screen capture for above user control in action.

Now if I select nothing for this field, it block the user to proceed. Hope this helps.

 

Happy programming.

Leave a Reply

Your email address will not be published.