How to update table using group by

Question

0.00/5 (No votes)

See more:

I am using visual studio 2010 C# language, please help me

in the following table : every family has familyid i.e members having same familyid is a family. The relationcode '01'= father, '05'=son, '06'=daughter

Familyid  Memberid  SerialNumber   Name      Age   Relationcode   Snofather fathername                        
101           1         1          Jame       30      01            -           -
101           2         2          John       10      05            -           -
101           3         3          mary        5      06            -           -
102           1         1          Lawrence    45     01            -           -
102           2         2          Linda       15     06            -           -
102           3         3          milie       13     06            -           -
102           4         4          sam         10     05            -           -
103           1         1          Joe         34     01            -           -
103           2         2          kate        10     06            -           -

i want to update the columns snofather (SerialNumber of father) and fathername of every family for e.g - in the first family jame is father of john and mary, so snofather is 1 and fathername is jame for john and mary and the next family so on.
The output should look like this ;

Familyid  Memberid  SerialNumber   Name      Age   Relationcode   Snofather fathername                        
101           1         1          Jame       30      01            -           -
101           2         2          John       10      05            1         Jame
101           3         3          mary        5      06            1         Jame
102           1         1          Lawrence    45     01            -           -
102           2         2          Linda       15     06            1        Lawrence 
102           3         3          milie       13     06            1        Lawrence    
102           4         4          sam         10     05            1        Lawrence 
103           1         1          Joe         34     01            -           -
103           2         2          kate        10     06            1           Joe

<br/>

Posted 16-Nov-14 6:28am

Muanzova

Updated 16-Nov-14 11:26am

Nelek

v2

Add a Solution

Comments

BillWoodruff 16-Nov-14 13:21pm

"Update" and "GroupBy" mean different things to me; I think what you describe here is you want to update a DataTable so all children in each family have their 'Snofather and 'fatherName fields in the DataTable filled. Correct ?

Muanzova 16-Nov-14 15:32pm

yes corrrect

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BillWoodruff · Answer 1 · 2014-11-16T08:49:00

Solution 2

Assume: your DataTable is named 'Families

C#

// required
using System.Data;

private void UpdateFamiles()
{
    string currentParentName = "";

    foreach (DataRow row in Families.Rows)
    {
        if (Convert.ToInt32(row.Field<int>("RelationCode")) == 1)
        {
            currentParentName = row.Field<string>("Name");
        }
        else
        {   
            row.SetField<int>("snoFather", 1);
            row.SetField<string>("fathername", currentParentName);
        }
    }
}

You may wonder if you could use Linq here, and I imagine you could, but I would not use Linq, because: as I understand it, Linq is not meant to be used to modify the value of fields. If someone reading this thinks Linq could/should be used, I'd be very curious to have your opinion as a comment here.

Posted 16-Nov-14 8:49am

BillWoodruff

Updated 16-Nov-14 9:38am

v2

Comments

Manas Bhardwaj 16-Nov-14 14:54pm

+5.

However, You might want to sort the rows with familyId, serial number before iterating.

EDIT: Changing my vote to 4.

The concept would work but you always assume that SNoFather is 1. It might be correct for this dataset but isn't universal.

BillWoodruff 16-Nov-14 15:07pm

I appreciate your vote, Manas ! The DataTable structure looks pretty weird to me, as I think it might look to you: since, 'snoFather is always going to be #1 for every "child." I think the data, as shown by the OP, is sorted right now: do you think so ?

Do you recommend sorting because you are thinking of a future use case where any Row in the DataTable could refer to any "Family:" i.e., the Rows are unordered ?

cheers, Bill

Manas Bhardwaj 17-Nov-14 3:21am

Hi Bill,

Personally, I don't rely on the sorting on datalayer and remove any assumptions being made for sorting etc. in datalayer if I am using the logic in application layer.

Yes, agree that the datatable structure is not very clear.

Manas Bhardwaj · Answer 2 · 2014-11-16T07:37:00

I don't think you need this to be recurring task and the tags don't make much sense to me.

Although, you could do something in SQL like this:

Look here for Common Table Expressions[^].

XML

WITH Family_Father (FamilyId, SerialNumber, Name)
AS
(
    SELECT FamilyId, SerialNumber, Name
    FROM #Temp
    WHERE RelationCode = 1
)

SELECT
    #Temp.FamilyId,
    #Temp.MemberId,
    #Temp.SerialNumber,
    #Temp.Name,
    #Temp.Age,
    #Temp.RelationCode,
    CASE WHEN (RelationCode <> 1) THEN Family_Father.SerialNumber ELSE 0 END As SNoFather,
    CASE WHEN (RelationCode <> 1) THEN Family_Father.Name ELSE '' END AS FatherName
FROM
    #Temp INNER JOIN Family_Father ON
        #Temp.FamilyId = Family_Father.FamilyId

How to update table using group by

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How to update table using group by

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us