Adding to Data Tables as part of For Each

[voot1989]

I am using the below code initially to go through multiple files, and adding the values to 3 data tables in a data set. However, the issue is, the data tables keep getting overwritten by the next file that's parsed through, but what I want is for the data tables to keep being added to for each file that's run through. What might I be doing wrong? It's ultimately pushed out to a Workbook, and there is a Worksheet for each Data Table, but as I say, it's only recording the latest file to be parsed through the process.

The file is run through here initially:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DatasetExtensions;
using MessageLogging;


namespace ILRValidation
{
public static partial class Validation
{
static MessageLogging.Msgs msgs = Msgs.Instance;
static MessageLogging.Progress progress = Progress.Instance;

/// <summary>
/// Takes ILR Converted dataset, and checks for null values and outputs to separate validation dataset
/// </summary>
/// <param name="ds_NullChecks">ILR XML Import Dataset</param>
/// <returns></returns>
public static DataSet NullChecks(DataSet ds_NullChecks)
{
return Checks(ds_NullChecks);
}

public static DataSet NullChecks(string xmlPath)
{

DataSet ds_xmlDataset = new DataSet();
FileInfo fileInfo = new FileInfo(xmlPath);

ds_xmlDataset.ReadXml(fileInfo.FullName, XmlReadMode.Auto);

return Checks(ds_xmlDataset);
}
private static DataSet Checks(DataSet dataset)
{
msgs.AddMsg("Entering Checks");
progress.ResetValue();

if (ds_Validation.Tables.Contains("LearningDelivery_NullValues"))
{
ds_Validation.Tables.Remove("LearningDelivery_NullValues");
}

if (ds_Validation.Tables.Contains("LearnerDeliveryFAM_NullValues"))
{
ds_Validation.Tables.Remove("LearnerDeliveryFAM_NullValues");
}

if (ds_Validation.Tables.Contains("Learner_NullValues"))
{
ds_Validation.Tables.Remove("Learner_NullValues");
}


msgs.AddMsg("Adding Extra columns to LearningDelivery & LearningDeliveryFAM");
dataset.Tables["LearningDelivery"].Columns.Add("LearnRefNumber").SetOrdinal(0);
dataset.Tables["LearningDelivery"].Columns.Add("Comments");
dataset.Tables["LearningDeliveryFAM"].Columns.Add("LearnRefNumber").SetOrdinal(0);
dataset.Tables["LearningDeliveryFAM"].Columns.Add("Comments");
dataset.Tables["Learner"].Columns.Add("Comments");

DataTable dt_LearnDelivery = new DataTable();
dt_LearnDelivery.TableName = "LearningDelivery_NullValues";



foreach (DataColumn dataColumn in dataset.Tables["LearningDelivery"].Columns)
{
dt_LearnDelivery.Columns.Add(dataColumn.ColumnName);
}



//Make a table to hold the issues with LearnerDeliveryFAM
DataTable dt_LearnDelFAM = new DataTable();
dt_LearnDelFAM.TableName = "LearnerDeliveryFAM_NullValues";

foreach (DataColumn dataColumn in dataset.Tables["LearningDeliveryFAM"].Columns)
{
dt_LearnDelFAM.Columns.Add(dataColumn.ColumnName);
}

//Make a table to hold the issues with Learner
DataTable dt_Learner= new DataTable();
dt_Learner.TableName = "Learner_NullValues";

foreach (DataColumn dataColumn in dataset.Tables["Learner"].Columns)
{
dt_Learner.Columns.Add(dataColumn.ColumnName);
}

progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(LearnAimRef,'') = ''").Count());
progress.SetMessage("Checked for issues 1 of 9");

//Add "No Aim" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(LearnAimRef,'') = ''"))
{
dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "LearnAimRef - No Aim Ref Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(AimType,'') = ''").Count());
progress.SetMessage("Checked for issues 2 of 9");

//Add "No Aim Type" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(AimType,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "AimType - No Aim Type Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(AimSeqNumber,'') = ''").Count());
progress.SetMessage("Checked for issues 3 of 9");

//Add "No Aim Sequence Number" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(AimSeqNumber,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "AimSeqNumber - No Aim Sequence Number Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}
progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(LearnStartDate,'') = ''").Count());
progress.SetMessage("Checked for issues 4 of 9");

//Add "No Learner Start Date" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(LearnStartDate,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "LearnStartDate - No Learner Start Date Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(LearnPlanEndDate,'') = ''").Count());
progress.SetMessage("Checked for issues 5 of 9");

//Add "No Learner Planned End Date" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(LearnPlanEndDate,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "LearnPlanEndDate - No Learner Planned End Date Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(FundModel,'') = ''").Count());
progress.SetMessage("Checked for issues 6 of 9");

//Add "No Funding Model" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(FundModel,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "FundModel - No Funding Model Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(DelLocPostCode,'') = ''").Count());
progress.SetMessage("Checked for issues 7 of 9");

//Add "No Delivery Location Postcode" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(DelLocPostCode,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "DelLocPostCode - No Delivery Location Postcode Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDelivery"].Select("Isnull(CompStatus,'') = ''").Count());
progress.SetMessage("Checked for issues 8 of 9");

//Add "No Completion Status" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDelivery"].Select("Isnull(CompStatus,'') = ''"))
{

dr["LearnRefNumber"] = dr.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "CompStatus - No Completion Status Supplied";

dt_LearnDelivery.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["Learner"].Select("Isnull(LearnRefNumber,'') = ''").Count());
progress.SetMessage("Checked for issues 9 of 9");

//Add "Learner Reference Number" issues to new table
foreach (DataRow dr in dataset.Tables["Learner"].Select("Isnull(LearnRefNumber,'') = ''"))
{
dr["Comments"] = "LearnRefNumber- No Learner Reference Number Supplied";

dt_Learner.Rows.Add(dr.ItemArray);

progress.IncProgress();
}

progress.int_Progress = 0;
progress.SetMax(dataset.Tables["LearningDeliveryFAM"].Select("Isnull(LearnDelFAMCode,'') = '' and LearnDelFAMType = 'SOF'").Count());
progress.SetMessage("Checked for issues 10 of 10");

//Add "No SOF" issues to new table
foreach (DataRow dr in dataset.Tables["LearningDeliveryFAM"].Select("Isnull(LearnDelFAMCode,'') = '' and LearnDelFAMType = 'SOF'"))
{
DataRow AimRow = dr.GetParentRow("LearningDelivery_LearningDeliveryFAM");

dr["LearnRefNumber"] = AimRow.GetParentRow("Learner_LearningDelivery")["LearnRefNumber"].ToString();
dr["Comments"] = "LearnDelFAMCode is null where LearnDelFAMType is SOF";

dt_LearnDelFAM.Rows.Add(dr.ItemArray);

progress.IncProgress();
}


ds_Validation.Tables.Add(dt_LearnDelivery);
ds_Validation.Tables.Add(dt_LearnDelFAM);
ds_Validation.Tables.Add(dt_Learner);

return ds_Validation;
}
}
}



Then it's handed over to the file creation:


using InfExcelExtension;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ILRValidation;


namespace ILRChecks
{
internal static partial class ILRReport
{
internal static void NullChecks()
{
Global.Progress.ResetValue();

foreach (string str_FileLocation in Global.fileNames)
{
FileInfo fileInfo = new FileInfo(str_FileLocation);



DataSet ds_NullValue = ILRValidation.Validation.NullChecks(str_FileLocation);
ds_NullValue.AddToWorkBook(Global.output);



Global.Progress.SetMax(Global.fileNames.Count());
Global.Progress.IncProgress();

}


Global.Progress.ResetValue();




}
}
}

Continue reading...