Group and update multiple records into one gridview row

Question

I need to enhance the application and the table structure cannot be amend.

Table structure:

ID      StaffID     Type
========================
1       1           1
2       1           2   
3       1           3
4       2           2
5       2           3
6       3           1

I want to group same StaffID into one gridview row and then databind the checkbox (Type 1, Type 2, Type3) based on the value of "Type" column.

Proposed Gridview :

StaffID     StaffName   Type 1  Type 2  Type 3
==============================================
1           Amy         [X]     [X]     [X]
2           John        [ ]     [X]     [X]
3           Chris       [X]     [ ]     [ ]

Moreover, How can i insert/delete record if user check/uncheck the checkbox in gridview ? Thanks.

Tom Troughton Tom Troughton · Accepted Answer · 2013-08-08T08:46:56

This looks to me like more of a SQL question so I will answer it accordingly. If you aren't looking for a SQL-based answer please be more clear in the question. And if you are please amend your tags.

As long as the number of types is fixed and known, the following SQL can form your underlying SQL query (assuming the source is a SQL database):

SELECT a.StaffID, Name, 
    CASE WHEN b.ID IS NOT NULL THEN 1 ELSE 0 END AS Type1,
    CASE WHEN c.ID IS NOT NULL THEN 1 ELSE 0 END AS Type2,
    CASE WHEN d.ID IS NOT NULL THEN 1 ELSE 0 END AS Type3
FROM dbo.Staff a
LEFT JOIN dbo.StaffType b ON a.StaffID = b.StaffID AND b.TypeID = 1
LEFT JOIN dbo.StaffType c ON a.StaffID = c.StaffID AND c.TypeID = 2
LEFT JOIN dbo.StaffType d ON a.StaffID = d.StaffID AND d.TypeID = 3

In your GridView each checkbox will know its StaffID (from the row) and TypeID (from the column) so simply handle its OnChanged event and call something like:

DECLARE @StaffID INT = 1, @TypeID INT = 1

IF EXISTS (SELECT * FROM dbo.StaffType WHERE StaffID = @StaffID AND TypeID = @TypeID)
    DELETE FROM dbo.StaffType WHERE StaffID = @StaffID AND TypeID = @TypeID
ELSE
    INSERT INTO dbo.StaffType ( StaffID, TypeID )
    VALUES ( @StaffID, @TypeID )

Updated with LINQ version:

var q2 =
    from staff in staffList
    from type1 in typesList
        .Where(t => t.StaffID == staff.StaffID && t.TypeID == 1)
        .DefaultIfEmpty()
    from type2 in typesList
        .Where(t => t.StaffID == staff.StaffID && t.TypeID == 2)
        .DefaultIfEmpty()
    from type3 in typesList
        .Where(t => t.StaffID == staff.StaffID && t.TypeID == 3)
        .DefaultIfEmpty()
    select new
    {
        StaffID = staff.StaffID,
        Name = staff.Name,
        Type1 = (type1 != null),
        Type2 = (type2 != null),
        Type3 = (type3 != null)
    };

Group and update multiple records into one gridview row

1 Answers