Join Datatables from a loop and merge in one Dataset

Question

I have this requirement that the user request one or more partnumbers.And i need to display all the result in one Data Set

Ex: Request SerialNumbers with

PartNumber  Required Quantity
   A               2  
   B               1
   C               3

Code:

//Loop Request

foreach (DataRow a in request.Rows)
{
   //select top(Quantity) SerialNumber,PartNumber where PartNumber=@PartNumber
   var dt = new DataTable();
}

//1. PartNumber A

     SerialNumber  Partnumber
1                A
2                A

+

//2. PartNumber B

     SerialNumber  Partnumber
1                B

//3. PartNumber C

     SerialNumber  Partnumber
1                C
2                C
3                C

//Load to DataSet

And the result will be something like:

SerialNumber  Partnumber
1                A
2                A
1                B   
1                C
2                C
3                C

Thanks in Regards

score 1 · Answer 1 · edited Jun 30 '11 at 07:14

1

I am not sure what two tables you wish to join here and on what key but regadless of that, the simplest way will be to use LINQ to Dataset. For example,

var query = from a in table1
            join
            b in table2 
            on
            a.FkColumn = b.PKColumn select a,b;

edited Jun 30 '11 at 07:14

painotpi

6,894
1
37
70

answered Jun 30 '11 at 05:54

VinayC

47,395
5
59
72

agree. if your question was of the form "how do i use this hammer to drive in this screw?", this answer is along the lines of "use a screwdriver instead". – John Gardner Jun 30 '11 at 23:39

score 0 · Accepted Answer · answered Jun 30 '11 at 06:06

Please check below logic:

DataTable myDataTable = new DataTable();
        DataColumn PartNumber = new DataColumn("PartNumber");
        DataColumn RequiredQuantity = new DataColumn("RequiredQuantity");
        myDataTable.Columns.Add(PartNumber);
        myDataTable.Columns.Add(RequiredQuantity);

        DataRow dataRowPN1 = myDataTable.NewRow();
        DataRow dataRowPN2 = myDataTable.NewRow();
        DataRow dataRowPN3 = myDataTable.NewRow();


        dataRowPN1["PartNumber"] = "A";
        dataRowPN2["PartNumber"] = "B";
        dataRowPN3["PartNumber"] = "C";
        dataRowPN1["RequiredQuantity"] = "2";
        dataRowPN2["RequiredQuantity"] = "1";
        dataRowPN3["RequiredQuantity"] = "3";
        myDataTable.Rows.Add(dataRowPN1);
        myDataTable.Rows.Add(dataRowPN2);
        myDataTable.Rows.Add(dataRowPN3);
        int  i = myDataTable.Rows.Count;

        DataTable joinDataTable = new DataTable();
        DataColumn SerialNumber = new DataColumn("SerialNumber");
        DataColumn JoinPartNumber = new DataColumn("PartNumber");
        joinDataTable.Columns.Add(SerialNumber);
        joinDataTable.Columns.Add(JoinPartNumber);
        foreach (DataRow dr in myDataTable.Rows)
        {
            for (int count = 1; count <= Convert.ToInt16(dr["RequiredQuantity"]); count++)
            {
                DataRow joindataRow = joinDataTable.NewRow();
                joindataRow["SerialNumber"] = count.ToString().Trim();
                joindataRow["PartNumber"] = dr["PartNumber"].ToString().Trim();
                joinDataTable.Rows.Add(joindataRow);
            }
        }
        Response.Write(joinDataTable.Rows.Count);

Join Datatables from a loop and merge in one Dataset

2 Answers2