Strongly-Typed Binding for List Controls
 
Published: 05 May 2004
Unedited - Community Contributed
Abstract
This article briefly covers the implementation of two types that can be used to bind list controls, such as the DropDownList, in a strongly-typed manner to a SQL Server data source using stored procedures.
by J. Ambrose Little
Feedback
Average Rating: 
Views (Total / Last 10 Days): 26116/ 74

Introduction

[Download Sample Project]

This article briefly covers the implementation of two types that can be used to bind list controls, such as the DropDownList, in a strongly-typed manner to a SQL Server data source using stored procedures.  I think the main application of this concept, as demonstrated by the sample code, is for menu and list controls that usually provide a friendly display value (name or text) and a hidden value, but there may be more.  It could be used in any situation where you need a name-value pair collection. 

The usefulness of this architecture is up for debate--it may be more work than it's worth--but I hope you'll find it is at least an interesting exercise.  Please send me feedback on whether or not you think it is useful, whether you end up using it in a real application, and please rate the article using the rating system that is located at the bottom of the article pages.

There are basically two core types involved in this architecture, the NameValuePair and the NameValuePairCollection.  The NameValuePair type has only two properties--Name and Value.  It is the so-called "business" or "domain" object that represents an individual name-value pair (menu option). 

The second type is, more or less, your standard collection type (the need for which will be replaced with generics in Whidbey), but it does have some custom functionality and data that give this a bit of umph.  There are really three core additions to the basic collection functionality:  FillFromDb, BindToListControl, and the BuildingParameters event (and related argument structure, delegate, and raising method).

 

Loading From the Database

[Download Sample Project]

Listing 1 shows the code for the FillFromDb method.  This method is fairly straightforward; it takes a stored procedure name and a connection string (to where that procedure is located), creates a SqlCommand using that information, executes a SqlDataReader, and creates a NameValuePair instance for each row returned, assuming that the zero column is the "value" and the one column is the "name." 

Obviously, this means that the provided stored procedure needs to return data with the assumed row schema, and it does not care how or where the data is stored on the SQL Server as long as this is the schema of the result set.

Listing 1

1:     public void FillFromDb(string storedProcedure, 
string connectionString)
2: {
3: SqlDataReader dr;
4: NameValuePair pair;
5: using (SqlCommand cmd = new SqlCommand(storedProcedure,
6: new SqlConnection(connectionString)))
7: {
8: cmd.CommandType = CommandType.StoredProcedure;
9: this.OnBuildingParameters(
10: new BuildingParametersArgs(storedProcedure,
cmd.Parameters));
11: cmd.Connection.Open();
12: dr = cmd.ExecuteReader();
13: while (dr.Read())
14: {
15: pair = new NameValuePair();
16: pair.Value = dr[0];
17: pair.Name = dr.GetString(1);
18: this.Add(pair);
19: }
20: }
21: }

Note that simply calling this method also assumes that there are no parameters to be added.  Since this is often not the case, we have to enable users to add parameters to the command somehow. 

There are many ways to do this, but I chose to simply raise an event when ready to add parameters to enable users to hook into my code at the appropriate point to do so.  The reason I chose this over other options is that I am assuming that the normal and simple usage (with no database parameters) will be the most-often used, so I wanted to simplify the interface for using this functionality by reducing the number of method parameters.

However, I did want to enable users who need to add parameters to do so in a familiar way, i.e., using the event handler paradigm.  In order to do this, I created the BuildingParametersArgs class that has two properties (CommandName and Parameters) to be used to pass needed information and references to the subscribing code.  I created a BuildingParametersHandler delegate to specify the desired method signature and declared the BuildingParamters event to provide the hook.  Finally, I wrote the raising method (OnBuildingParameters) to call from within my code when it was time to raise the event.  Listing 2 provides the code for these elements.

Listing 2

1:     #region BuildingParameters Event

2: /// <summary>
3: /// Event arguments for the BuildingParameters event.
4: /// </summary>
5: public class BuildingParametersArgs
6: {
7: #region Fields and Properties
8: string commandName;
9: /// <summary>
10: /// The stored procedure command name.
11: /// </summary>
12: public string CommandName
13: {
14: get
15: {
16: return this.commandName;
17: }
18: }
19:
20: IDataParameterCollection parameters;
21: /// <summary>
22: /// Gets the parameters collection.
23: /// </summary>
24: public IDataParameterCollection Parameters
25: {
26: get
27: {
28: return this.parameters;
29: }
30: }
31: #endregion
32:
33: #region Constructors
34: /// <summary>
35: /// Default Constructor
36: /// </summary>
37: public BuildingParametersArgs() {}
38: /// <summary>
39: /// Constructor providing setting of all properties.
40: /// </summary>
41: /// <param name="commandName"><see cref="CommandName"/></param>
42: /// <param name="parameters"><see cref="Parameters"/></param>
43: public BuildingParametersArgs(string commandName,
IDataParameterCollection parameters)
44: {
45: this.commandName = commandName;
46: this.parameters = parameters;
47: }
48: #endregion
49: }
50: /// <summary>
51: /// Delegate to use if wanting to add parameters to the procedure
52: /// used to retrieve the items in the collection.
53: /// </summary>
54: public delegate void BuildingParametersHandler(object source,
BuildingParametersArgs e);
55: /// <summary>
56: /// Event raised when ready for handlers to add parameters.
57: /// </summary>
58: public event BuildingParametersHandler BuildingParameters;
59: /// <summary>
60: /// Called when ready to raise the <see cref="BuildingParameters"/>
event.

61: /// </summary>
62: /// <param name="BuildingParametersArgs">BuildingParameters event
arguments.</param>

63: protected virtual void OnBuildingParameters(BuildingParametersArgs e)
64: {
65: if (this.BuildingParameters != null)
66: BuildingParameters(this, e);
67: }
68: #endregion
69:

 

Binding to List Controls

[Download Sample Project]

Another feature that I thought to add is to simplify binding the collection to list controls, such as the DropDownList, RadioButtonList, etc.  In order to do this, I added a method called BindToListControl that takes a reference to the list control the user wants to bind, sets its DataSource,  DataTextField, and DataValueField properties appropriately, and calls the DataBind method on the control to bind the collection to the control.

Listing 3


1:     /// <summary>

2: /// Bind the given <i>list</i> to this item collection.
3: /// </summary>
4: /// <param name="list">List control to bind to.</param>
5: public void BindToListControl(System.Web.UI.WebControls.ListControl list)
6: {
7: list.DataSource = this;
8: list.DataTextField = "Name";
9: list.DataValueField = "Value";
10: list.DataBind();
11: }



 

Sample Usage

In the sample project (Download Here), you can see two sample usages of this architecture, a simple example without adding paramters (Listing 4) and the more complex usage subscribing to the BuildingParameters event (Listing 5).  Observe how the LoadParameters method is added as an event handler for the BuildingParameters event in Listing 5.  It is a bit cumbersome to create a method when we could just have one line of code (in this case); doing this in-line will be possible with anonymous methods coming in Whidbey.

Listing 4

1:     private void LoadStates()

2: {
3: // Simple Retrieval -- No Parameters Needed
4: NameValuePairCollection states =
5: new NameValuePairCollection();
6: // Fill From Db
7: states.FillFromDb(MenuCommands.StateMenu,
8: MenuCommands.DefaultConnectionString);
9: // Bind to List Control
10: states.BindToListControl(this.DropDownList1);
11: }

Listing 5

1:     private void LoadAuthors()

2: {
3: // Complex Retrieval -- Need to Add Parameters
4: NameValuePairCollection authors =
5: new NameValuePairCollection();
6: // Add Event Handler for Building Parameters
7: // (Anonymous methods will help here)
8: authors.BuildingParameters +=
9: new NameValuePairCollection.BuildingParametersHandler(
this.LoadParameters);
10: // Fill From Db
11: authors.FillFromDb(MenuCommands.AuthorByStateMenu,
12: MenuCommands.DefaultConnectionString);
13: // Bind to List Control
14: authors.BindToListControl(this.DropDownList2);
15: }
16:
17: private void LoadParameters(object sender,
NameValuePairCollection.BuildingParametersArgs e)
18: {
19: // Compare on the command name to enable reuse of this method
for more than one

20: // command/stored proc
21: if (e.CommandName == MenuCommands.AuthorByStateMenu)
22: {
23: // Create the Parameters for the dbo.GetAuthorMenuByState
procedure

24: // State Paramter
25: e.Parameters.Add(
26: new System.Data.SqlClient.SqlParameter("@State",
27: this.DropDownList1.SelectedValue));
28: }
29: }
 
You may notice the use of the MenuCommands type.  This is just an extra I added on at the last minute to give me strongly-typed options for specifying the different stored procedure names and database connections to be used in conjunction with with the LoadFromDb method calls.

Also note that for the sample to run, you'll probably need to update the connection string.  It is currently set to use integrated authentication to the default local SQL Server instance, and the application is set to use Windows authentication with impersonation (with all the gotchas that this implies).

Conclusion

As mentioned, I'm not entirely certain whether or not going to all this trouble is worth the effort, particularly when just using the BindToListControl method (since something like that can easily be implemented without strong-typing).  It may be that only hard-core middle-tier purists will appreciate the architecture; however it does illustrate (albeit quietly) the ease with which you can bind custom business object collections to data controls, which I think is a really great feature of .NET.

It also offers, as you might expect, a fairly decent separation of UI from the data.  This could easily be abstracted to use other data sources using the IDb interfaces.  And it would not be hard to offer simpler parameter addition if it's found that the event handler option is not usable.  It also lends itself easily to plopping the collection into a cache of some sort for reuse and ease of binding.

Again, let me know what you think, either by feedback, rating, or at the related post on my blog.  I appreciate any insight on how this concept might be expanded or modified to be more usable in day-to-day application development.



User Comments

Title: Well done. Very useful   
Name: Leo
Date: 2009-02-24 8:31:10 AM
Comment:
Hi J. Ambrose Little,

Thanks for your code. I found it very useful indeed.
I used it in a scenario where I needed to create a NameValuePair Collection to concatenate two fields before displaying it on a DropDownList. Without your approach I would have to retrieve an concatenate column field just for my business object in one specific dropdown.

I am using CSLA as framework for the Business Objects. I am looking forward to make it generic with my objects as is not suitable for me use your FillFromDb, because this data is already manipulated by my objects in a business layer.
Unfortunatelly I could not understand the BuildingParamters as I found quite hard to understand events with delegate, etc.

If you have experience with CSLA and have any suggestion to make the Fill up in a generic manner, please let me know.

Thanks a lot.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-21 1:12:54 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search