In our data access layer, we have many strongly-typed datasets which contain multiple tables and their relationships. We set them up very close to what our actual database schema is.
We use to use strongly-type class objects for our schemas but moved to datasets recently. So our issue is that we are trying to understand how to take our current schema and effectively bind this data to a listview without flattening the entire dataset into some easy to use view.
1. So first, we have elow in the code section a very scaled down example of the table structure in one of our datasets. We removed address and email tables and are left with 3 tables: customer, customerphone and phone.
As you can see, Phone and Customer are both one to many in relation to CustomerPhone. The CustomerPhone table is the linking table since each customer can have multiple phone numbers. This is just our preference for query performance in place of putting customerxid or any other identifier directly in phone table.
2. Secondly, the second portion of the code snippet below shows a few example records for each table in the dataset. As you can see, we have 3 customers but they each have at least 2 phone number records with the last one having 3.
3. And lastly, the final part of the code snippet shows a simple output that we desire. This display is via a ListView on .aspx page.
So with all that said, what is the best way to accomplish our desire? Flattening all the tables into one view is not desired, the structure is complex and many times, we dynamically want to determine which of the many address, emails, phones, etc.... we want to display.
We currently display the data from the customer table in a ListView with no issues. It has sorting, paging and filtering too. We basically bind the whole dataset to the ListView and then on the ItemDataBound, we programatically get the values from each ListViewDataItem > DataRowView and populate the controls in the ItemDataTemplate. Below is a small code snippet of this method as it works today, does not have any address, phone or email information since that is the issue we are trying to figure out.
(Note: this is an enterprise application, we have many layers and do not use sqldatasources, inline SQL or any other non-real world application of .net code. I note this cause most examples on the web use these methods which don't help)
But it seems that in the ItemDataBound event, we only have access to the customer table. Even though we bind the whole dataset, it seems it takes the customer table by default and uses it.
We need to figure out how to gain access to the other tables in the dataset after binding in this event? And more importantly, just the records that pertain to the current customer's row since the ItemDataBound event fires on each and every row record in the binding table.
Or would we have to take the whole customerphone/phone tables and dynamically select the rows that pertain to the current customer's xid during this event? This seems like a lot of overhead and should be simplier.
As a reminder, we do have relationships setup on these tables. They mirror those in the database.
Please let me know if there are any questions. Your help is very much appreciated. I wish I could give 10K points for this solution.
xid int (PK)
xid int (PK)
customerxid int (FK)
phonexid int (FK)
xid int (PK)
Sample Table Records
1 Kobe Bryant 1324341
2 Kevin Garnett 3545434
3 Ray Allen 4539949
1 1 1 cell
2 1 2 home
3 2 3 cell
4 2 4 home
5 3 5 cell
6 3 6 home
7 3 7 work
xid name accountnumber address phone email
1 Kobe Bryant 65451321 123 Main St, Los Angeles, CA 43434 654-568-8943 firstname.lastname@example.org
ListView sample code
protected void Page_Load(object sender, EventArgs e)
//get all customers
CustomerData customerData = new CustomerData();
CustomerBF customerBF = new CustomerBF();
customerData = customerBF.GetCustomersAll();
//bind customers to listview
ltvCustomers.DataSource = customerData;
catch (HandledException ex)
protected void ltvCustomers_ItemDataBound(object sender, ListViewItemEventArgs e)
if (e.Item.ItemType == ListViewItemType.DataItem)
ListViewDataItem listViewDataItem = (ListViewDataItem)e.Item;
DataRowView dataRowView = (DataRowView)listViewDataItem.DataItem;
Label lblXID = (Label)e.Item.FindControl("lblXID");
Label lblBrandXID = (Label)e.Item.FindControl("lblBrandXID");
Label lblUserXID = (Label)e.Item.FindControl("lblUserXID");
Label lblName = (Label)e.Item.FindControl("lblName");
Label lblCustomerCode = (Label)e.Item.FindControl("lblCustomerCode");
Label lblDOB = (Label)e.Item.FindControl("lblDOB");
Label lblIsActive = (Label)e.Item.FindControl("lblIsActive");
Label lblDateCreated = (Label)e.Item.FindControl("lblDateCreated");
//set column values for this row of data
lblXID.Text = dataRowView["xid"].ToString();
lblBrandXID.Text = dataRowView["brandxid"].ToString();
lblUserXID.Text = dataRowView["userxid"].ToString();
lblName.Text = dataRowView["firstname"].ToString() + " " + dataRowView["lastname"].ToString();
lblCustomerCode.Text = dataRowView["customercode"].ToString();
lblDOB.Text = dataRowView["dob"].ToString();
lblIsActive.Text = dataRowView["isactive"].ToString();
lblDateCreated.Text = dataRowView["datecreated"].ToString();