How to Create Lookup Fields in C#
I started my (more or less) serious database development training many years ago in Microsoft Access. One of my favorite controls was the Combo Box, a nice little drop-down field that enabled the user to select one of many values but collapsed down onto a single line. The Combo Box control in Access was very configurable and one of the nice features was that you could display one value while actually saving another value to the field the control was bound to. For example, you could display the Customer Name while saving the associated CustomerID that the database would use to reference the customer record.
C# also has a Combo Box control that can do much the same thing but offers a lot more options through code. The .NET version maintains a collection of objects, any kind of objects, and displays whatever text is presented by the object’s ToString() method. The code can then use any other property of the object to carry out operations in the background.
The example above shows a small program with a combo box showing a list of vendors from the AdventureWorks2012 sample database. I want the drop-down to show the vendor name but when I select one, I want it to use the VendorID value to retrieve a list of products from that vendor and display it in the DataGrid behind the list. There are ways I could do this through data binding but I want to write the code myself for now.
The first thing I need is a custom class that encapsulates the vendor name and VendorID value and defines the value to be shown to the user.
public class cboObject { int vID; string vName; public cboObject(int VendorID, string VendorName) { vID = VendorID; vName = VendorName; } public string VendorName { get { return vName; } set { vName = value; } } public int VendorID { get { return vID; } set { vID = value; } } public override string ToString() { return this.VendorName; } }
That’s not so painful. It’s just a small class with a constructor that accepts the two values, the two public properties and then an override of the ToString() method that returns the current object’s VendorName property.
I have a stored procedure that returns a table with the VendorID and VendorName fields from AdventureWorks and after I use C# to read that stored procedure into DataTable, the following code in the form’s Load event will use the cboObject class to add the objects to the Combo Box.
sqlDa.Fill(dtVendors); foreach (DataRow drVendor in dtVendors.Rows) { VendorID = int.Parse(drVendor.ItemArray[0].ToString()); VendorName = drVendor.ItemArray[1].ToString(); cbVendors.Items.Add(new cboObject(VendorID, VendorName)); }
The DataTable itself is made up of a collection of DataRows so I can use the foreach loop to iterate through them.
Each DataRow has a zero-based ItemArray collection that holds the values for the row. The values are stored in the same order as returned by the stored procedure so referring to them is just a matter of using the right index value.
Selecting an Item
The next step is actually being able to select and use one of these objects. The SelectedIndexChanged event enabled me to retrieve the selected object. The cast to the cboObject type shown on the first line converted the list item back to the right type so the object’s properties are available.
private void cbVendors_SelectedIndexChanged(object sender, EventArgs e) { cboObject currentObject = (cboObject)cbVendors.SelectedItem; int VendorID = currentObject.VendorID; DataTable dtVendorProducts = new DataTable(); try { SqlCommand sqlComm = new SqlCommand("spVendorProducts", sqlConn); sqlComm.CommandType = CommandType.StoredProcedure; SqlParameter prmVendorID = new SqlParameter("@VendorID", VendorID); sqlComm.Parameters.Add(prmVendorID); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlComm); sqlDa.Fill(dtVendorProducts); dgVendorProducts.DataSource = dtVendorProducts; }
Normally, I wouldn’t put all that connection handling code into this event but it works for this demonstration. The command calls another stored procedure that accepts a VendorID parameter and returns the product list. When adding parameters to the command in this way, be sure to specify the CommandType as shown or the parameters won’t be passed properly and you’ll get an error.
Once the DataTable has been filled from the stored procedure, it can be used as the DataGrid’s data source. Now, whenever the user selects a different vendor, the grid is updated with the resulting records from the stored procedure.
Sign up for our newsletter to receive updates about new projects, including the upcoming book "Self-Guided SQL"!
We respect your privacy and will never share your information with third-parties. See our privacy policy for more information.
0