Solved

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

Posted on 2010-09-10
14
4,160 Views
Last Modified: 2013-05-25
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();

Open in new window

0
Comment
Question by:kruegerste
  • 6
  • 6
  • 2
14 Comments
 
LVL 30

Expert Comment

by:MlandaT
ID: 33647913
I am not sure why you want to do this "without flattening the entire dataset into some easy to use view". Typically, your gain lots of performance by doing your data manipulation and processing using the database engine, which is highlyu optimised for doing this kind of work. For example, processing multiple JOINs within SQL Server is more efficient than manipulating relationships within datasets in .NET code. Using indexes and foreign keys, you can do lots of data filtering quickly and efficiently. So the best design guideline is really that you should perhaps revise your current approach and loosen your data structures a little bit. You will also do your developers a huge favour. You can build typed datasets that pull data from views in the database, you will still have lots of different filtering mechanisms and all. I write and maintain enterprise-grade systems used all over the globe in various industries such as contruction, healthcare and CRM and there are certain patterns which are in fact anti-patterns... Look at Object-oriented design & Programming anti-patterns here: http://en.wikipedia.org/wiki/Anti-pattern. So while you are sticking, somewhat strictly, to the typed-datasets model, you are in fact going against commonly accepted and proven design precepts.

0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33647956
"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"

Use database objects such as views, indexes, stored procedures, and functions (inline, table valued) and so on. These will help you NOT to put SQL code directly in the application.
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33648036
"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"

Indeed, when using databinding it only binds to one datatable. It cannot bind ot multiple datatables, so the DataItem will be from only a single datatable. However, you can still gain access to the other datatables in the original dataset. Say your orig dataset is called dsAllData and it contains several datatables, tblCustomers and tblPhones. And you bind to tblCustomers. You can in the ItemDataBound event navigate back to the dataset this way... Ctype(e.Item.DataItem, DataRowView).Row.Table.Dataset at which point you are back to dsAllData. I'm sure you can then cast this into your typeddataset or otherwise just navigate that dataset as you see fit.

Note, by using a database view, you would already have all the information you need in this listview all in one datarow, and this would in fact be easier for your developers and result in a more efficient application. Choice is yours... but I most certainly advise that you 'rethink' certain restrictions you are placing on your data access methodologies.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 33648084
This is what I would do... CustomerData object will have all Fields which what you want display as it's properties and you write a Storeproc with some complex joins to return all related data.
Then when you bind it to LV it is just normal access to any other property you are accessing on CustomerData object.
0
 
LVL 4

Author Comment

by:kruegerste
ID: 33648242
Mlanda - thanks for the response, here are a couple notes.

Notes on your first post:

While I appreciate your insight, your post doesn't help me really.  Sounds like somebody on their high horse preaching to me.  I apologize if that wasn't your intentions.  Addressing my actual dilemma and providing an specific solution would be much appreciated.

I understand that flattening the dataset tables is the ideal situation for my dilemma but I raised several points on why we don't think we can do this and you did not address any of them.

Mainly, how do you flatten the tables of a dataset when the relationship is one to many.  For example, while we have a customer table with separate address, email and phone tables we could flatten this since we do have a defined number of instances that we can have for address, email and phones.  

But what about the other examples where there isn't a defined number of child records?  Customer/orders, customer/payments, order/orderitems, order/payments, etc......  In these instances, I don't know how we would effectively flatten these, seems we would still run into the issue of querying a dataset.    

Lets take an example:  We want to display orders and their orderitems in a ListView.  

Flattened Solution:

If the dataset was flattened I'm assuming that we would have multiple order records then, one for each orderitem in that order with the orderitem data at the end of the order record.  The datatable would like the same as this results set:

SELECT *
FROM Order o
INNER JOIN OrderItem oi ON o.xid = oi.orderxid

It would create multiple records for each order instance, one for each orderitem record.  Then with this structure, when you bind it to any control, it is going to display a listview row for every record, which duplicates the order information.  

Any suggestions to this issue would be appreciated.  


Alternative Solution:

With having two tables in the dataset, we would then bind only the orders Table to the ListView creating one listview row per order.  Then we would have some repeater inside the listview that would then load all the order items under each order.  This last part is what this question is about, not sure how to do that effectively.  

I was hoping that when binding a dataset Table that had relationships setup, that you could somehow get all the child rows from each of the other tables quite easily.  This doesn't seem possible.  


I also didn't see any anti-pattern pertaining to Object Oriented Design and my dilemma.  In addition, your comment...... "So while you are sticking, somewhat strictly, to the typed-datasets model, you are in fact going against commonly accepted and proven design precepts." didn't really make any sense to me.

Typed-datasets has nothing to do with the issue.  Whether its a flattened dataset or mirrors our database structure, it can still be strongly-typed.  And I'm not sure what "commonly accepted and proven design precepts" you are referring to.  There are dozens of design and data patterns out there.


Notes on your second post:

I stated we don't use sqldatasources and inline SQL.  We do use stored procs, views, functions, etc..... very much.  We have over 1800 stored procs and views.  I totally understand the benefits of SQL, trust me.  This is not the issue or dilemma I should say.  

0
 
LVL 4

Author Comment

by:kruegerste
ID: 33648275
Guru sami - thanks for the response but I don't think that works for my needs.  I may be wrong, its a little vague so there is a good chance I'm misunderstanding it.  

Could you elaborate a bit?

Thanks.
0
 
LVL 4

Author Comment

by:kruegerste
ID: 33648307
MlandaT - thanks for the response.

Notes in reference to your 3rd post:

Thank you, that does solve one of the issues.  I didn't know how to gain access to all the dataset records, it turns out they are binded in a way.  Thanks.  

On the database view issue, see my questions in the previous post to you.  I would be open to this but I just don't know how to get around a couple problems I see with it.  If you have insight into those, this might be the answer.

Thanks.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 30

Expert Comment

by:MlandaT
ID: 33648452
Ha ha ha... interesting...

1. No intention to sound like I'm sitting on high horse or to preach. Just stating. Your initial question seems to suggest that the only thing you want to use is typed datasets and navigating through the datarelations to retrieve data... I thought of myself as the developer having to work with that model ONLY and I got goosebumps. It is perhaps a question of data access architecture vs delivering on application objectives and I strongly believe that a data architecture that uses typed datasets alone is not ideal for any application.

2. Flattened solution: There are many considerations for how you retrieve data from the database into your dataset. Of course the display plays a big part. In the original question, you indicate that you want to read values from the other tables in the dataset, perhaps for the purpose of displaying additional info in the same listview row (or at least that's how it comes across). In this case, it is far better to have a SQL query (whether in inline sql code, a view, a stored proc, a function, or whatever) that already does the necessary JOINs and you just databind to the already combined resultset. Where you want to display orders maybe in a heirarchical grid or subgrids, it does not help to already have all the data in one big query which denormalises the data and repeats the order multiple times. So it is all dependant on the context. There just needs to flexibility on the data access architecture to allow ad-hoc queries and/or views.

3. Typed Datasets: Like I said, the scenario you first depicted coupled with the impression you painted that you only use tpyed datasets and are not open to alternative approaches to the data access. The idea that you are being somewhat inflexible about your dataaccess architecture probably came from undertones in these statements:

- "bind this data to a listview without flattening the entire dataset into some easy to use view"
- "We set them up very close to what our actual database schema is."
- "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"

:D (rofl) No offence implied in my comms with you on this... and none taken whatsoever.
0
 
LVL 4

Author Comment

by:kruegerste
ID: 33648672
MlandaT -

thanks for the response but it seems we haven't really gotten anywhere.  You haven't actually provided any specific solutions.  Most of what you have stated is already understood.  I'm just looking for a solution.   I feel like I'm in a college course when reading your responses.  It definitely does seem like you want to preach rather than address the actual question and provide a specific solution.

And you are reading way to much into typed text.  When somebody writes 4 paragraphs in 45 seconds,  it isn't always all that thought out.  And the 3 statements you reference seem pretty tame to me.  

- "bind this data to a listview without flattening the entire dataset into some easy to use view"

i say this because we have tried flattening and came up with some issues, one of which i specified in detail and you agreed with me in #2 that flattening wouldn't be appropriate in this context.  But yet haven't provided a solution.

- "We set them up very close to what our actual database schema is."

just a statement of fact, telling you how and why they are setup up that way.  says nothing about us not being open to change.

- "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"

this is just so they understand our application, many examples and solutions provided deal with sqldatasources and inline SQL which are not helpful, no enterprise application I have ever come across uses them.  


Please respond if you have a solution for this question.  Thanks.

0
 
LVL 30

Accepted Solution

by:
MlandaT earned 500 total points
ID: 33648820
"...and are left with 3 tables: customer, customerphone and phone ... 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."

Have you tried this?

Ctype(e.Item.DataItem, DataRowView).Row.Table.Dataset

The assumption I made is that once you have managed to get back to the dataset from the bound rows dataitem, you can in fact then navigate your datarelations on the dataset to get the data that you want from the other datatables...?

Ctype(e.Item.DataItem, DataRowView).Row.GetChildRows(relationName) 'to get the rows from one of the other 2 datatables in your dataset associated with the row currently being bound
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 33648868
Sorry I know it was vauge and don't have real sample.
But I saw your alternative solution in your previous comment and yes, you can include a repeater control in the  phone column. Then in the ItemDataBound, you Find this repeater in the item, get the Phone numbers for that customer using the customerid from dataItem. And bind the result to the repeater.

Here is sample of nested ListView not exactly you are looking for but see if it helps:
http://mattberseth.com/blog/2008/01/building_a_grouping_grid_with.html
0
 
LVL 4

Author Closing Comment

by:kruegerste
ID: 33675491
Perfect, thanks.
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33675946
Thanks to you for the points... was quite pleasantly surprised at it!!! We didnt get off to a very good start on this one :)
0
 
LVL 4

Author Comment

by:kruegerste
ID: 33676044
No we didn't, but you did finally answer the question and it was a good solution.  That is all that matters :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now