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: