Add rows to the datagrid using foreach loop with dynamically returned query

Question

I am trying to add rows to a datagrid where a condition is passed to query the data and get the results returned from that query to add to a datagrid. Below is the code:

private void FetchAllJobStatus(int regionID)
    {
        OleDbConnection con = new OleDbConnection(Constring);
        String strQu1;
        strQu1 = "SELECT LOGPATH, TWSID FROM JOB_DETAILS_TEST WHERE REGIONID = " + regionID + " ORDER BY TWSID";
        OleDbDataAdapter dapt1 = new OleDbDataAdapter(strQu1, con);
        DataTable dt1 = new DataTable();
        dapt1.Fill(dt1);

        foreach (DataRow row in dt1.Rows)
        {
            string twsIDName = row.ItemArray[1].ToString();
            string startTime = GetStartTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
            string endTime = GetEndTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
            DateTime dat1 = DateTime.Parse(startTime);
            DateTime dat2 = DateTime.Parse(endTime);
            string endingTime;

            if (endTime == string.Empty || endTime == null || dat2 < dat1)
            {
                endingTime = "";
            }
            else
            {
                endingTime = endTime.Remove(0, 3).ToString();
            }
            string startingTime = startTime.Remove(0, 3).ToString();
            String strQu2;
            strQu2 = "SELECT JOBNAME, TWSID, HIGHPRIORITY, DAY, TIME, '"+ startingTime + "' as StartTime, '"+ endingTime + "' as EndTime FROM JOB_DETAILS_TEST WHERE TWSID = " + int.Parse(twsIDName) + " AND REGIONID = " + regionID + " ORDER BY JOBNAME";
            OleDbDataAdapter dapt2 = new OleDbDataAdapter(strQu2, con);
            DataSet ds = new DataSet();
            dapt2.Fill(ds, "dt2");
            dgJobStatusAll.AutoGenerateColumns = true;
            dgJobStatusAll.DataSource = ds;
            dgJobStatusAll.DataMember = "dt2";
        }

    }

Here only the last data gets appended in the grid. Thanks in advance

Answer 1

write a single query using union and fill the datatable out side the loop. Take

String strQu2; 

out of the loop.And in loop use

if(string.IsNullOrEmpty(strQu2 ))
{
strQu2 ="SELECT JOBNAME, TWSID, HIGHPRIORITY, DAY, TIME, '"+ startingTime + "' as StartTime, '"+ endingTime + "' as EndTime FROM JOB_DETAILS_TEST WHERE TWSID = " + int.Parse(twsIDName) + " AND REGIONID = " + regionID ;
}
else
{
strQu2 =strQu2 +" Union All "+ "SELECT JOBNAME, TWSID, HIGHPRIORITY, DAY, TIME, '"+ startingTime + "' as StartTime, '"+ endingTime + "' as EndTime FROM JOB_DETAILS_TEST WHERE TWSID = " + int.Parse(twsIDName) + " AND REGIONID = " + regionID ;
}

And now After the loop write

strQu2 =strQu2 + " Order by REGIONID,JOBNAME "

And fill datatable and bind to grid

Answer 2

Your finding is obvious because you bind the grid each time inside the foreach, hence every time its overwritten. Try to do it like this. This is not complete but should get you on track.

private void FetchAllJobStatus(int regionID)
{
    OleDbConnection con = new OleDbConnection(Constring);
    String strQu1;
    strQu1 = "SELECT LOGPATH, TWSID FROM JOB_DETAILS_TEST WHERE REGIONID = " + regionID + " ORDER BY TWSID";
    OleDbDataAdapter dapt1 = new OleDbDataAdapter(strQu1, con);
    DataTable dt1 = new DataTable();
    dapt1.Fill(dt1);

    DataSet ds = new DataSet(); // Move this out
    DataTable dt = new DataTable("MyTable"); 
    dt.Columns.Add(new DataColumn("yourColumnName",typeof(int))); // Create columns as you want (your structure)
    dt.Columns.Add(new DataColumn("yourColumnName2", typeof(string)));
    foreach (DataRow row in dt1.Rows)  // Now inside foreach create row and add it to  the table
    {
        string twsIDName = row.ItemArray[1].ToString();
        string startTime = GetStartTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
        string endTime = GetEndTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
        DateTime dat1 = DateTime.Parse(startTime);
        DateTime dat2 = DateTime.Parse(endTime);
        string endingTime;

        if (endTime == string.Empty || endTime == null || dat2 < dat1)
        {
            endingTime = "";
        }
        else
        {
            endingTime = endTime.Remove(0, 3).ToString();
        }
        string startingTime = startTime.Remove(0, 3).ToString();
        String strQu2;
        strQu2 = "SELECT JOBNAME, TWSID, HIGHPRIORITY, DAY, TIME, '"+ startingTime + "' as StartTime, '"+ endingTime + "' as EndTime FROM JOB_DETAILS_TEST WHERE TWSID = " + int.Parse(twsIDName) + " AND REGIONID = " + regionID + " ORDER BY JOBNAME";
        OleDbDataAdapter dapt2 = new OleDbDataAdapter(strQu2, con);
        // dapt2.Fill(ds, "dt2");
        DataRow dr = dt.NewRow(); // create row
        dr["yourColumnName"] = // fill these
        dr["yourColumnName2"] = // fill these
        dt.Rows.Add(dr); // add row to dt
    }
    ds.Tables.Add(dt); // add table to your dataset
    dgJobStatusAll.AutoGenerateColumns = true;
    dgJobStatusAll.DataSource = ds;
    dgJobStatusAll.DataMember = "dt2";

}

Answer 3

You can do it with DataTable.Merge(). take a DataTable instead of DataSet ds and before the for loop and bind the grid after the loop:

 ............................................
 ................................................
        DataTable dt1 = new DataTable();
        dapt1.Fill(dt1);
   DataTable dtMain=new DataTable();

 foreach (DataRow row in dt1.Rows)
    {
        string twsIDName = row.ItemArray[1].ToString();
        string startTime = GetStartTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
        string endTime = GetEndTime(row.ItemArray[0].ToString(), int.Parse(twsIDName));
        DateTime dat1 = DateTime.Parse(startTime);
        DateTime dat2 = DateTime.Parse(endTime);
        string endingTime;

        if (endTime == string.Empty || endTime == null || dat2 < dat1)
        {
            endingTime = "";
        }
        else
        {
            endingTime = endTime.Remove(0, 3).ToString();
        }
        string startingTime = startTime.Remove(0, 3).ToString();
        String strQu2;
        strQu2 = "SELECT JOBNAME, TWSID, HIGHPRIORITY, DAY, TIME, '"+ startingTime + "' as StartTime, '"+ endingTime + "' as EndTime FROM JOB_DETAILS_TEST WHERE TWSID = " + int.Parse(twsIDName) + " AND REGIONID = " + regionID + " ORDER BY JOBNAME";
        OleDbDataAdapter dapt2 = new OleDbDataAdapter(strQu2, con);
        DataTable dtTemp=new 
        dapt2.Fill(dtTemp);

       if(dtTemp.Rows.Count>0)
       {
          dtMain.Merge(dtTemp);
       }
    }
        dgJobStatusAll.AutoGenerateColumns = true;
        dgJobStatusAll.DataSource = dtTemp;
        //dgJobStatusAll.DataMember = "dt2";

}

or you can write single query with inner join which will be best this case .