Strongly-Typed Binding for List Controls
page 2 of 5
by J. Ambrose Little
Average Rating: 
Views (Total / Last 10 Days): 27366/ 93

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,
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: }
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
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"/>

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

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


View Entire Article

User Comments

Title: Well done. Very useful   
Name: Leo
Date: 2009-02-24 8:31:10 AM
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-2018  |  Page Processed at 2018-08-15 8:57:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search