Solved

C# Dataset with multiple tables

Posted on 2010-09-09
26
1,187 Views
Last Modified: 2012-05-10
Hello,

We have many datasets that we use to bind our controls and these datasets contain multiple tables with relationships.

One example dataset has 3 tables - customer, customerphone and phone.  Customerphone is the linking table between the other two and is many to one relationship with both.  

First, how do you bind a control with this dataset (like LIstview) and obtain access to all tables in a binding event like OnItemDataBound?  Seems that it just binds to the primary table every time.  

Secondly and more importantly, in some event like a OnItemDataBound if we were to have access to all table records, how do we find the phone records that pertain to the current ListViewDataItem?  Not sure if just those records that pertain are available or if we would have to select on the remaining tables?

Please provide specific details, code samples preferred.  Thanks.
0
Comment
Question by:kruegerste
  • 10
  • 9
  • 6
  • +1
26 Comments
 
LVL 1

Expert Comment

by:revteam
Comment Utility
You can assign them directly  by checking for count of dataset and rows.
If you want to filter the data then use data view row filter.
0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
you can use childrows from relation to bind the data.
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
Since we don't how the table structure and the relation between them its hard to guess how you need the output  to be.
  1. How the output should be:
    • A flat table with repeated customer names.
    • Customer row followed by Customer Phone Rows
  2. The Attached sample files have the output as the 2nd option (Customer row ...)
    • Repeater Item DataBind
      • Accessing Controls in Main Repeater.
    • Child Repeater Item DataBind
      • Accessing Controls in Child Repeater.
    • PostBack
      • How to find a control and retrieve its value on postback.
  • If you have controls which are modified and you want to access the control values on postback "submit" you can do as such.

    • public void Submit()
      {
      foreach (RepeaterItem item in rptCustomer.Items)
      {
      TextBox txt = item.FindControl("txt")as TextBox;
      string text = txt.Text;
      Repeater rptCustomerPhone = item.FindControl("rptCustomerPhone") as Repeater;
      foreach (RepeaterItem itemCustomerPhone in rptCustomerPhone.Items)
      {
      TextBox txt2 = itemCustomerPhone.FindControl("txt") as TextBox;
      /*
      ...
      */
      }
      }
      }
Good Luck!

Default.aspx
Default.aspx.cs
Result.PNG
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
Sorry, looks like I wasn't specific enough.

revteam & puru1981 - more details would be much appreciated, not sure I understand what you mean.  The detail below hopefully helps clear it up.

cyrusjoudieh - great example, thanks for the detail.  unfortunately, it isn't quite what I'm looking for.  Your example left out the linking table - customerphone.  I don't flatten out the table because I only want to bind each singular customer record.  If I flatten, then for phone or something like orders, I would have to have multiple customer X records for each phone or order that customer has.

Here are some more details that might help.  This example has been simplified for ease of use in discussion.

So here are the 3 tables - customer, customerphone and phone that are in my dataset.

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

As you can see, Phone and Customer are both one to many in relation to CustomerPhone.  CustomerPhone is the linking table since each customer can have multiple phone numbers.  

(Now we could go ahead and flatten this since we have a defined number of phone types a customer can have but this would not work for something like orders or payments where this is unknown so we don't want to go down that road.)

Here are some example records for these tables:

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


So as you can see, we have 3 customers but they each have at least 2 phone number records with the last one having 3.  This table structure is the same as our database.  

And the output we desire is to bind this dataset with a Listview and then in an ItemDataBound event, we programatically will get the values from the customer table row and then somehow get the corresponding phone numbers for that customer's row from the customerphone/phone tables.  

Currently it seams that when we bind the whole dataset to the listview, it defaults to just using the customer table and the other table row values are inaccessible.  

We have relationships setup on these tables in the dataset.  So I was hoping that when we bind the whole dataset and access the ItemDataBound event which fires on each customer row, that the corresponding customerphone/phone records would be attached to that dataitem somehow cause of the relationships.  Or at the very least, having all of them available so we could somehow query them using the customer's row xid.  Reason we want it this way is cause its more dynamic, we have business logic to execute on what information for a customer is displayed and it needs to be determined on the fly.

It seems that this would be common practice but I can't find a solution for it.  But if this isn't possible or we aren't doing it the right way, the only other solution I can think of would be to sort of flatten out the customerphone/phone tables and then binding these to some child control inside the listview.  This flattened table would basically add customerxid and phonetype to the phone table.  My issue here though would be, how do we make sure that the phone records that are binded, correspond with the cusotmer records that are binded?  The whole "managing relationships" is sort of the messy part in all of this.

And to keep in mind, just a side note, this is an enterprise application with business logic and data access layers.  We don't do anything with sqldatasources in the .aspx page or any silly stuff like that.  For this dataset, a method is called in business facade layer which calls a method in business logic layer which then calls a method in the data access layer which makes the call to the stored procedure which populates the strongly-typed dataset from the schema layer.

Hopefully this helps clear things up a little and it didn't make it more puzzling.  Please let me know if there are any questions.  



0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
Another note, this example can basically be duplicated for customer emails, addresses, orders, payments, etc....

Here is an example of the display we are shooting for, contains header with one record::

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

We don't know which address, phone or email to display per customer when binding so we like to programatically do this in the ItemDataBound event.
0
 
LVL 2

Accepted Solution

by:
cyrusjoudieh earned 500 total points
Comment Utility
For you to be able to display one row for each customer you must have predefined columns Work, Cell, and Home and retrieve the value for each type on each databound.
Modifications:
  1. Added Phone Types to repeater Customer
  2. New function (string) GetPhone(CustomerId, PhoneTypeEnum) to retreive phone number for each Predefined Column. Assuming that you only have only those three per client. Which i mean that the maximum entries for each client in CustomerPhone is 3 records.
  3. Another Method using List Collection (Recommended) for performance. but you need to populate the phone into the phonebook list first.
Good Luck!.

Result.PNG
Default.aspx
Default.aspx.cs
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
Very thorough example, thank you very much.  We aren't quite there though......

Some notes:

Ok, so basically your solution to my main dilemma here is to make the dataset (or List<>Collection in your other example) a global variable in the code-behind of the .aspx page.  So then on ItemDataBound firing, I'm not actually getting the other Table values from the ListViewDataItem but from the original dataset Tables and they are now accessible by making them global for all methods in the page's class.  This makes sense but.......

So that now brings us to the other dilemma I see with this solution which is performance.  So if I'm displaying 100 customers, then the ItemDataBound will fire 100 times which means that I would have to do a select on the CustomerPhone/Phone tables 100 times to get each phone number for each customer.  And if we add in email, addresses, orders, etc..... That is a lot of selects on the original dataset which could be pretty big in of itself, maybe 500K records.

And with the other solution of using a collection, this is the method that we are getting away from.  We use to use strongly-type collection classes for all our schemas but don't want to bother with them anymore, datasets are suppose to make things easier.  In addition, we would still need to loop through the entire dataset and create these PhoneBookCollection objects which would still seem like a performance issue, just at a different time in the page life cycle.  And the state of these objects would be lost on refresh so you would need to do it every page request.

Any thoughts on this?  Are we off at all in our observations?  

So what is the main purpose then of multiple tables in a dataset with relationships then?  I don't see any difference between having the one dataset with 3 tables as we have versus just having 2 separate datasets each with one table.  

If we go with either of your solutions, it almost seems that two separate smaller datasets would almost be better.  Just have to add customerxid and phonetype to the phone table dataset and I can query it just the same.

I guess we were under the impression that when binding a dataset to a control that the related records in other tables of that dataset would be somehow attached or available without querying.  That would be the ideal situation.

Thoughts?  Your help is much appreciated.
0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
hmm...

create a relation between the table customer and customerPhone say RelCustNamePhoneMap. (left table Customer, right table customerPhone)
create another relation between the table Phone and customerPhone say RelCustPhoneMap. (left Table Phone, right table customerPhone)

then iterate through customerPhone and get parent row from the relation to get the data from customer and phone.

add to a new table and bind with the datasource.

Below is a link where you can find something on data relation.
http://www.emmet-gray.com/Articles/DataRelations.htm

hope it helps.

0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
The way the data is structured and are located in DataSets there are no way without looping nor filtering.
  1. Another method is to do an SQL that  fetches those records into a flat table.
  2. I don't know why you have them separated from the Customers table.

    From my point of view those information can be stored into one table  "customers" no need for two tables, if you are able to redesign your Table structure to the below sample it would be great and makes your code even more simpler.

    Customer Table:
    pkClientId
    Name
    Account
    WorkPhone
    HomePhone
    CellPhone
    Email
    AlternateEmail
    Address1
    Address2
    Photo

    Customer Orders:
    fkCustomerId
    ....
  3. If you are using a .Net 3.5 framework or higher you can benefit from using LINQ to SQL which is better and faster and easier to access and extend your classes on the fly to fit your needs rather than using DataSets.
  4. Refreshing the Page you have to reload the repeater, so if the repeater is integrated in the aspx. and not dynamically generated a postback is enough to handle the values of all the records but again think how much information would go and come between the server and the client.
Dynamic Controls which are created on runtime their values cannot be retreived unless they are reloaded on postbacks on the Page_Init cycle and  then you can access them to get the values you need.
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
cyrus - thanks for the feedback, some more notes.......

1 & 2.  We have them as separate tables because that is how we have them in our database, we were trying to mirror the database, thought it would make the most sense.

And the reason we have the database structure this way is mainly for performance and scalability.  Addresses, phones, emails, etc... are only inserted into the database once, then linked up with the customer or record its related to.  Prevents duplicate data being inserted.  And when you have 22 columns in the address table cause of address standardization, its nice not to have these fields repeated multiple times in a customer table.

As for flattening them to your suggestion, while it may work for customer address, phone and email since there is a defined number of instances for these, I don't know how you would do this for something like orders where there is an unknown number of them.  And if we couldn't do it for orders and other examples like this, we were trying to keep them all the same.  

So yes, I could use your customer table suggestion and I think it would work.  But I would be right back here when I got to customers/orders, orders/orderitems, orders/payments, customers/payments, etc.....

3. LINQ is being replaced by entity-framework, not really wanting to go down the path of implementing a bunch of LINQ when Microsoft has stopped development on it.  They obviously kept it in 4.0 but not advancements will ever be made, I'm assuming it will eventually die.

4. I didn't mean dynamic controls, it is integrated in the .aspx page.  I just meant, whether you query the dataset or populate a collection List, both seem like a lot of overhead when you do the math.  Say the ItemDataBound event is fired off 100 times for 100 customers, you have to query the dataset 3 separate times for address, phone and email tables.  That is a lot of stuff going on for every page request.  Was just hoping there was a better way without flattening the dataset.


0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
I hope it would be better when you use SQL Server for joining the tables and flattening the records(can use Views).

Since doing joins on and relations in dataset is not as efficient as in SQL Server. if you want to work with two or three tables then it is useful to work with dataset but for more tables it will become cumbersome through Dataset.
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
Customer -> Customer Orders Rows

Two repeaters needed if phones are moved to the customers table.
  1. To make it much more easier you can store the ID of the row as Customer ID in  a LinkButton.CommandArgument value or a hidden field when databinding, the databinding you do it once only, on Postbacks, no need to Render the list again, so when you click it you sent this to an event with the Command Arguments and it would be  ((LinkButton)sender).CommandArguement and no need to loop on all the records since you have that id that you want to look for.
  2. List all the customers you want with the information you need for that customer:
    • Do a link to open the result of the customers' order in a new window
    • using UpdatePanel (nice feature and neat)
      • Your Html window can be broken into two parts upper part for Customers list with scrollable overflow division. And the lower part the orders view of the customer selected from the upper part list.
      • To Achieve this you need Ajax Controls. So this method cuts down the traffic between the client and server and less select queries needed, one query per each click.
  3. Table Design, you need to find out what are the most common fields used. Like the phone numbers you are having from 1 to 3 rows in the CustomerPhone and Phone tables which makes up to six records and two tables used, while you can store them in 3 columns in the customer table. Regarding the Addresses, you can store the address in the orders list Shipping and Billing columns. and not in the Customers Table. The address specified in the Customer table can be used for Mailing Address for example.
  4. If you don't want to change anything with the structure then the only way left is the SQL fetching a flat table or use Ajax in point 2
For each Customer row there is a databind. you can't cut this down.
 
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
puru1981 - yes, I entirely understand the benefits of SQL Server doing these operations, that is why I haven't accepted querying a dataset as a reasonable solution quite yet.  

My problem with flattening the dataset is that it doesn't seem that it would work in most of the instances that I can think of.  And nobody has offered any suggestions to these concerns I have about doing it this way.  

Lets take another example, displaying the orders and their orderitems in a ListView.

If the dataset was flattened I'm assuming that we would combine the order and orderitem tables.  Since the relationship is one to many (many orderitems per order), we would have multiple order records then, one for each orderitem.  

The flattened datatable would like the same as this results set:

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

So if we had order 1234 with orderitems 2, 13 and 44.  Then the datatable would look like::

orderxid        ordername    blah,blah,blah         orderitemxid        orderitemname         blah,blah,blah
1234              xxxxx                                          2                          yyyyyyy
1234              xxxxx                                          13                        yyyyyyyyy
1234              xxxxx                                          44                        yyyyyy

So now we try to bind this datatable to a ListView, we end up with the order information repeating.  This is not desired.  What would be the solution to this issue?

We would only want to display one row record for each order and then each orderitem below it.  I just don't see how this could be accomplished with flattening out the record.  Especially now try to show each payment for this order in which there could be multiple payments methods so now the payment information is tacked onto each of these records but we only want to display the payment info for each order once.

Solution to this issue?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
If you are willing to list all the customers and orders in one page. then two repeaters will do the job.
Table Customer (Info)
   Table Customer Orders (Select Order Where Customer Id)

Sample Output:

Customer X   Info
Orders:
orderxid        ordername
1234              xxxxx
1234              xxxxx
 
______________________________

Customer Y  Info
Orders:
orderxid        ordername
2345              yyyyyy
 
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
cyrusjoudieh - thanks for the response.

Please see my last post to puru1981, not sure any of your 4 suggestions address this dilemma.  Which is the main dilemma I have with flattening the dataset.  I'm not opposed to flattening, I just don't know how to get around the issue in my last post.

And this issue is relevant to every other scenario I can think of outside of customer.  Customer was a bad example cause address, email  and phone all have finite instances.  But customer/order, order/orderitems, order/payments, etc.... all have an infinite number of child records, per say.

And your #2 suggestion has been thought of, might be a possibility.  Would rather have it all on one screen though.  

Thanks.
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
cyrusjoudieh -

in your last suggestion, "Table Customer Orders (Select Order Where Customer Id)" where is this happening and how?  Is there a way to select on the dataset with a variable?  Or is this happening in the ItemDataBound event for each record?
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
What i suggest if you want to have a falt table output then probable you do looping and build an HtmlTable Dynamically without the help of the repaters.

1- RowSpan Customers on Orders Count
2- RowSpan  Phone: Do columns phone on Distinct Phone Types

output would be

Customer   Cell Work Phone Orders  Qty Shipping Address
X                    12    34        56       1          2     Europe
                                                       2          1     Canada
Y                    11    22         33      4          5     USA
Z ....

Yes on each databind of customer record filter the orders table like the example of the PhoneBook, by Customer Id.
0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
in this scenario i think you need to work with a master child repeaters. one would be master where your distinct orders are there. second will be to display the order information where order items are.

if you want to work with flatten dataset then you need to use repeaters itemDatabound event where you can use a variable to set the value of say orderno and check on each iteration if the value is same. if it changes write the whole row other wise write only partial row i.e. order items.
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
customer/order, order/orderitems, order/payments,
  1. Click on Customer -> Updates Order Panel (and you can display the order payments in the same panel)
  2. Click on Order  -> Updates Order Items Panel
Conclusion your page is broken into 3 parts.
  1. Customer
  2. Orders/Payments
  3. Order Items

Can be acheived in Dynamic HtmlTable too, Attached a sample in Excel.

Book.xlsx
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
Regarding the Phone Table you can get rid of it and merge it with Customer phone table
CustomerPhone Table
xid int (PK)
customerxid int (FK)
phonetype varchar  
phone varchar

Like this a customer can have more than one work, home or cell phones defined all in one table.
Regarding the phonetype i suggest you use a constant defined enumerator like
ex: (int)PhoneTypeEnum.Work

If you are concerned about the format to be displayed here is a sample function to do it. And also you can validate it depending on the type.


public string GetPhoneStringFormat(string Phone, int type)
{
string phone = Phone;
PhoneTypesEnum pbtype = (PhoneTypesEnum)Enum.Parse(typeof(PhoneTypesEnum), type.ToString());
switch (pbtype)
{
case PhoneTypesEnum.Home:
case PhoneTypesEnum.Work:
phone = string.Format("0:00 000 000", phone);
break;
case PhoneTypesEnum.Cell:
phone = string.Format("0:000-00000", phone);
break;
}
return phone;
}
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
Found my answer, I used GetChildRows(Relation) to get the records associated with a customer table row.  This was basically two additional lines of code to get this to work from what I already had.

Thanks for all the efforts, it was appreciated.
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
GetChildRows(Relation) is good to use when the repeaters are seperated ex:(displaying the output on different rows and not in columns)
What if no records fetched on this method, how are you going to build the output  of the table foreach Home, Cell and Work phones on a single row.
Client,  Account, Cell, Work, Home...
0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
i have already advised this solution her ID: 33647609
0
 
LVL 4

Author Comment

by:kruegerste
Comment Utility
puru1981 - yes you did, but in the next response I asked for details or examples and you never elaborated on it.  I did forget you mentioned it at least when giving points, sorry.  

cyrusjoudieh - seems you are a little hung up on repeaters, I am binding to a ListView programmatically using ItemDataBound event method.  You may be confused on how this can be done since we are using different controls.  ListView is awesome.  IIf data doesn't exist for address, phone or email, then its just not displayed of course. It evens has templates for EmptyItem and EmptyData to handle these situations.

I'm binding using the Customer table which will always have data to display the base customer information.  And if it doesn't for some reason cause of filter or whatever, then no data is displayed.  

My original question was how can I easily access the data rows that are associated to each customer in the other data tables of my dataset?.?.  With relations, I was able to do this with about 5 lines of code and it works great.  I have posted a snippet for other to see for getting address information.  You loop through the returned rows, so if no rows are returned, it just puts String.Empty in the column and continues.  

This solution is much easier and faster than trying to Select or Filter the original dataset.
 protected void ltvCustomers_ItemDataBound(object sender, ListViewItemEventArgs e)

        {

            if (e.Item.ItemType == ListViewItemType.DataItem)

            {

                ListViewDataItem listViewDataItem = (ListViewDataItem)e.Item; 

                DataRowView dataRowView = (DataRowView)listViewDataItem.DataItem;

                CustomerData customerData = (CustomerData)dataRowView.Row.Table.DataSet;



                //get billing address from address table 

                string addressLine1 = "";

                string city = "";

                string state = "";

                string zipcode = "";



                foreach (DataRow row in dataRowView.Row.GetChildRows("FK_customer_address"))

                {

                    CustomerData.addressRow customerAddressRow = (CustomerData.addressRow)(row);



                    if (customerAddressRow.addresstypexid == (int)AddressType.Billing)

                    {

                        addressLine1 = customerAddressRow.addressline1;

                        city = customerAddressRow.city;

                        state = customerAddressRow.statecode;

                        zipcode = customerAddressRow.zipcode.ToString();

                    }

                }

Open in new window

0
 
LVL 9

Expert Comment

by:puru1981
Comment Utility
Never mind... i am not looking for points here...

what i have tried is generalized concept of using relations and datatable because as you have used getchildrows and i was saying to retrieve the parent because for me base table was mapping table to bind the data...

Happy coding :)
0
 
LVL 2

Expert Comment

by:cyrusjoudieh
Comment Utility
Glad that you found a solution.

Will check out with the ListView control and how to play with it.
Thank you for sharing.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

15 Experts available now in Live!

Get 1:1 Help Now