The DropDownList Web control enables users to select from a single-selection drop-down list (for example, a combo box). You can control the look of the DropDownList control by setting its height and width in pixels, but you cannot control the number of items displayed when the list drops down. The SelectedIndex and SelectedItem properties provide details about the currently selected element. The DropDownList control supports data binding by using the following five properties: DataSource, DataMember, DataTextField, DataValueField, and DataTextFormatString. As shown in the following code, of these five properties, only DataSource, DataTextField, and DataValueField have a corresponding ASP.NET attribute you can declare:
<asp:DropDownList id="DropDownList1" runat="server" DataSource="<%# databindingexpression %>" DataTextField="DataSourceField" DataValueField="DataSourceField">
You can also use a data-binding expression to set the DataSource property. The expression you use must evaluate to a .NET object that exposes the ICollection interface. You cannot use expressions to set the other properties. The values for DataTextField and DataValueField each must match the name of one field in the data source, so you cannot assign DataTextField by combining two or more fields in the data source. Let’s see how to work around this limitation.
Suppose you want a drop-down list to display both the first name and the last name of each employee in the company. You could ask SQL Server to return a calculated column that has the required format. In this case, you would use a query string, as shown in the following code, and set DataTextField to the name of the precalculated field:
SELECT lastname + ', ' + firstname AS 'EmployeeName' FROM Employees
You can obtain the same result, however, without the involvement of SQL Server. After you hold a DataTable object that contains the result of the query, you can add a new column on the fly. The content of the column is determined by the expression you use. The following code uses this approach to generate the drop-down list shown in Figure 1-2:
String strConn, strCmd; strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; strCmd = "SELECT employeeid, firstName, lastname FROM Employees"; SqlDataAdapter oCMD = new SqlDataAdapter(strCmd, strConn); DataSet oDS = new DataSet(); oCMD.Fill(oDS, "EmployeesList"); DataTable dt = oDS.Tables["EmployeesList"]; dt.Columns.Add("EmployeeName", typeof(String), "lastname + ', ' + firstname");
The next code example shows how to bind the drop-down list control named EmpList to the dynamically created EmployeeName field:
EmpList.DataSource = oDS.Tables["EmployeesList"].DefaultView; EmpList.DataTextField = "EmployeeName"; EmpList.DataValueField = "employeeid";
DataTextField links the Text property of any individual item in the list with the EmployeeName field. DataValueField ensures that the Value property of each item is set with the value stored in the corresponding record of the employeeid field. The full source code for the DropDown.aspx application is available on the companion CD.
An expression-based column does not have to be filled explicitly. Whenever the program needs to read the value of one of its rows, it just evaluates the expression as it processes data and then takes the result. Note that expressions can reference other expression columns. Circular references, though, are not allowed. The ADO.NET run time promptly detects them and raises an exception.
Which of the two approaches is preferable? Should you ask SQL Server to return a made-to-measure column, or should you create the extra column you need when you need it? The final result of both approaches is identical, but the cost of using each is not. SQL Server is not as efficient. It returns a new column of potentially duplicated data. Processing data to produce a column creates overhead, especially when the expression is complex. Unless the precalculated column is needed for further processing, you should avoid using SQL Server for this type of operation.