troubleshooting Question

C# Asp.net Bind Listview to Dataset with Multiple Tables

Avatar of kruegerste
kruegersteFlag for United States of America asked on
.NET ProgrammingC#ASP.NET
14 Comments1 Solution5226 ViewsLast Modified:
Hello,

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.

Thanks.

==============================
Table Structure
==============================

Customer Table
xid int (PK)
name varchar
accountnumber varchar

CustomerPhone Table
xid int (PK)
customerxid int (FK)
phonexid int (FK)
phonetype varchar

Phone Table
xid int (PK)
number int

===========================
Sample Table Records
===========================

Customer Table
1      Kobe Bryant          1324341
2      Kevin Garnett        3545434
3      Ray Allen            4539949
      

CustomerPhone Table
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


Phone Table
1      9525653212
2      6545234512
3      9855632145
4      8742151563
5      6548156113
6      8945415312
7      5645611565


===============================
Sample Display
================================

xid 	name		accountnumber	address                        		phone  		email
1      	Kobe Bryant	65451321       	123 Main St, Los Angeles, CA 43434      654-568-8943 	kobe.bryant@lakers.com


===========================
ListView sample code
===========================
 protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!IsPostBack)
                {
                    //get all customers
                    CustomerData customerData = new CustomerData();
                    CustomerBF customerBF = new CustomerBF();
                    customerData = customerBF.GetCustomersAll();

                    //bind customers to listview
                    ltvCustomers.DataSource = customerData;
                    ltvCustomers.DataBind();
                }
            }
            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();
ASKER CERTIFIED SOLUTION
MlandaT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros