asked on C# Asp.net Bind Listview to Dataset with Multiple Tables
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.
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();
C#ASP.NET.NET Programming
Last Comment
kruegerste
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.
"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.
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.
"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.Dat
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.
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.Dat
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.
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.
Then when you bind it to LV it is just normal access to any other property you are accessing on CustomerData object.
ASKER
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.
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.
ASKER
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.
Could you elaborate a bit?
Thanks.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
ASKER
Perfect, thanks.
Thanks to you for the points... was quite pleasantly surprised at it!!! We didnt get off to a very good start on this one :)
ASKER
No we didn't, but you did finally answer the question and it was a good solution. That is all that matters :)