CODEWITHSUNDEEP
c#asp.netdatatable
chipbug
chipbug

Reputation: 19

How to fill a datatable from another datatable depending on field values

I teach a class of 9 units, students can pass each unit with a 'pass', 'merit' or 'distinction'. A P gets 70 points, M gets 80 and D gets 90. The overall result for the year is a sum of the points for all units.

They need to pass all the 'P' criteria before 'M' criteria are allowed, and they need all 'M' criteria before 'D' criteria are allowed. The number of P/M/D criteria varies depending on the unit. The following student has achieved a 'pass' (70 points) for this unit because he failed an M even though he got all D criteria:

Unit 1 - brain surgery

P1 - know where the brain is - pass
P2 - know which way is up - pass
M1 - explain what the brain does - pass
M2 - is there a difference between an male and female brain - FAIL
D1 - figure out why my brain can't work this out - pass
D2 - invent a drug that allows a brain to figure out logic immediately - pass
D3 - find a way of making lots of money with just brainpower - pass

I've a SqlDataReader that fills a DataTable with these columns: unitNo, criterionName, criteriontype (ie P/M/D), passedOrFailed. The query already selects by individual student so the DataTable only contains one student's records. It looks like this:

UnitNo   Citerion   Type   passed?  
1        know etc     P       Yes  
1        know etc     P       Yes  
1        explain etc  M       Yes  
1        is there etc M       No  
1        Figure etc   D       Yes  
1        invent etc   D       Yes  
1        find etc     D       Yes  
2        blah etc     P       Yes  
2        wot etc      P       Yes  
2        so etc       M       No

And I'm after a DataTable that looks like this:

UnitNo  P    M   D   Points  
1       Y    N   Y     70  
2       Y    Y   N     80  
3       N    N   N      0  
4       N    Y   Y      0  
5       Y    Y   Y     90

...
...
...........................

And finally total the points - student has 240 points.

I'm absolutely fine with DataTables, c# etc it's the blasted logic that's doing me in. Every bit of code I write looks like spaghetti after about 2 lines.

Does anyone have some pointers for sorting this logic out? It's like a scene from the school in Ghormenghast... if you can solve it then I shall personally send the wifey around to cook you a Sunday roast.

PC

ok, here's the actual code with CSharper's answer incorporated (also edited to add his solution for the extra column name). It's clearly the right solution, I'm just not quite getting the numbers to appear (I spotted his omission of the brackets after the DataRow cast but that's not the problem). I've hooked a couple of test grid on at the end to show the results. Also I'm not a linq guy so I don't know how to add the unit name from the first table to the second, or total the points:

(Note, I'm using 1= not marked, 2 = fail, 3 = pass in the passFail field)

//create the sql to extract the data
        String sqlString = "SELECT unitNo, unitName, criterionPMD, passFail FROM unitDetails INNER JOIN unitStudentRecord ON unitDetails.id = unitStudentRecord.unitDetailsId INNER JOIN unitSummary ON unitDetails.unitSummaryId = unitSummary.id WHERE studentID = '" + ((String)Session["studentID"]).Trim() + "'";
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["studentTutorialsConnectionString"].ConnectionString);
        SqlCommand sqlComm = new SqlCommand(sqlString, conn);
        conn.Open();
        SqlDataReader sqlTotalMarks = sqlComm.ExecuteReader();

        //create the workings out datatable and fill it
        DataTable dtWorkings = new DataTable();
        dtWorkings.Load(sqlTotalMarks);
        conn.Close();

        // create the results datatable
        DataTable dtResults = new DataTable();
        dtResults.Columns.Add("units", typeof(int));
        dtResults.Columns.Add("name", typeof(string));
        dtResults.Columns.Add("P", typeof(bool));
        dtResults.Columns.Add("M", typeof(bool));
        dtResults.Columns.Add("D", typeof(bool));
        dtResults.Columns.Add("points", typeof(int));

        //fill the results table
        foreach (var units in dtWorkings.Rows.Cast<DataRow>().GroupBy(r => r["unitNo"]))
        {
            var p = units.Where(r => r["criterionPMD"] == "P").All(r => r["passFail"] == "3");
            var m = units.Where(r => r["criterionPMD"] == "M").All(r => r["passFail"] == "3");
            var d = units.Where(r => r["criterionPMD"] == "D").All(r => r["passFail"] == "3");
            dtResults.Rows.Add(
                 units.Key,//UnitNo
                 units.Select(r => r["unitName"]).First(),//UnitName                    
                 p,
                 m,
                 d,
                 p ? (m ? (d ? 90 : 80) : 70) : 0);//Points
        }

        gvTemp.DataSource = dtWorkings;
        gvTemp.DataBind();
        gvSummary.DataSource = dtResults;
        gvSummary.DataBind();

Here's the grid for gvTemp:

   unitNo   unitName       criterionPMD passFail
    2     Computer systems  P     3
    2     Computer systems  P     1
    3     Web rubbish           P     3

Here's the grid for gvSummary:

units   name               P     M  D   points
2           Computer systems            90   
3           Web rubbish                             90

In the gvSummary grid ticked checkboxes appear for all PMD criteria, but no totals. I was expecting zero for unit 2, 70 points for unit 3...

It's oh-so-close...

Upvotes: 0

Views: 5680

Answers (1)

CSharper
CSharper

Reputation: 534

Doing that with C# classes might help structure the problem into smaller parts and getter better maintainable solution. However surely that's also possible with a DataTable. Another disadvantage of data tables is that the data is not easily being implemented typesafe. In the first version of this answer the casts of r["criterionPMD"] to string was missing so there was a reference comparison instead of string comparison.

            //create the workings out datatable and fill it
            DataTable dtWorkings = new DataTable();
            dtWorkings.Columns.Add("unitNo", typeof(int));
            dtWorkings.Columns.Add("unitName", typeof(string));
            dtWorkings.Columns.Add("criterionPMD", typeof(string));
            dtWorkings.Columns.Add("passFail", typeof(int));

            dtWorkings.Rows.Add(2, "Computer systems", "P", 3);
            dtWorkings.Rows.Add(2, "Computer systems", "P", 2);
            dtWorkings.Rows.Add(3, "Web rubbish", "P", 3);

            // create the results datatable
            DataTable dtResults = new DataTable();
            dtResults.Columns.Add("units", typeof(int));
            dtResults.Columns.Add("name", typeof(string));
            dtResults.Columns.Add("P", typeof(bool));
            dtResults.Columns.Add("M", typeof(bool));
            dtResults.Columns.Add("D", typeof(bool));
            dtResults.Columns.Add("points", typeof(int));

            //fill the results table
            foreach (var units in dtWorkings.Rows.Cast().GroupBy(r => r["unitNo"]))
            {
                var p = units.Where(r => (string)r["criterionPMD"] == "P").All(r => (int)r["passFail"] == 3);
                var m = units.Where(r => (string)r["criterionPMD"] == "M").All(r => (int)r["passFail"] == 3);
                var d = units.Where(r => (string)r["criterionPMD"] == "D").All(r => (int)r["passFail"] == 3);
                dtResults.Rows.Add(
                     units.Key,//UnitNo
                     units.Select(r => r["unitName"]).First(),//UnitName                    
                     p,
                     m,
                     d,
                     p ? (m ? (d ? 90 : 80) : 70) : 0);//Points
            }

With the complex conditional expression you assure that the 90 points are only given if all, p, m and d criteria are fulfilled.

This will give the correct result even in a unit with only pass and distinction criteria so only 90, 70 or 0 points are possible. Don't know if that's a requirement for you.

In your example with only "P" criteria it's difficult to decide if 70 or 90 points are the correct result. This code snippet checks if all P, M and D criteria are fulfilled and in this case gives 90 points even if there is no M or D criteria and all P criteria were passed. I think in all your real-world scenarios you won't have this edge case but anyway it's worth thinking about this.

Upvotes: 1

Related Questions