[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:
3:
4:
5: public class BuildingParametersArgs
6: {
7: #region Fields and Properties
8: string commandName;
9:
10:
11:
12: public string CommandName
13: {
14: get
15: {
16: return this.commandName;
17: }
18: }
19:
20: IDataParameterCollection parameters;
21:
22:
23:
24: public IDataParameterCollection Parameters
25: {
26: get
27: {
28: return this.parameters;
29: }
30: }
31: #endregion
32:
33: #region Constructors
34:
35:
36:
37: public BuildingParametersArgs() {}
38:
39:
40:
41:
42:
43: public BuildingParametersArgs(string commandName,
IDataParameterCollection parameters)
44: {
45: this.commandName = commandName;
46: this.parameters = parameters;
47: }
48: #endregion
49: }
50:
51:
52:
53:
54: public delegate void BuildingParametersHandler(object source,
BuildingParametersArgs e);
55:
56:
57:
58: public event BuildingParametersHandler BuildingParameters;
59:
60:
61:
62:
63: protected virtual void OnBuildingParameters(BuildingParametersArgs e)
64: {
65: if (this.BuildingParameters != null)
66: BuildingParameters(this, e);
67: }
68: #endregion
69:
