Filtering on multiple DataTables with First DataTable

Question

I am trying to export to database tables details into Multiple Worksheets Inside a Single Excel File and I got export working correct, but the issue with multiple DataTable Filtering with First DataTable Email Id.

Below is my C# Code:

  protected void Button1_Click(object sender, EventArgs e)
{
     DataSet ds = new DataSet();
    DataTable dt = new DataTable("Registration Details");

    DataTable dt1 = new DataTable("Education Details");

    dt = bl.Get_Registrationdetailsbydate1(bo);

    dt1 = bl.Get_Registrationdetailsbydate2(bo);

    ds.Tables.Add(dt);

    ds.Tables.Add(dt1);

    ExcelHelper.ToExcel(ds, "Users.xls", Page.Response);   

}

I have two tables Registration_table and Education_table and also have two DataTable Registration details and Education details.

I need result base on email id's of first DataTable(dt) that first DataTable emails id's users only should come into second DataTable that means education details based on email ids.

Suppose below is first DataTable details.

sno    email            mobile        city

 1     [email protected]     123456789    hyderabad

I need result of second DataTable details like below single users.

sno       email              education 

 1        [email protected]       MBA

And I am using two stored procedures for first getting registration details and second Education Details.

You can simply modify your Stored Procedure to fetch data based on email present in Registration_table. — Rahul Singh
– Rahul Singh, Commented Jan 21, 2016 at 9:33
How to pass first DataTable Email Ids, Above I used first datatable method : Get_Registrationdetailsbydate1(bo);, Can you update answer. @Rahul Singh — Mr doubt
– Mr doubt, Commented Jan 21, 2016 at 9:36
What I mean is just pass the parameter to your Stored Procedure which is fetching educational details and in SP simply filter the data with WHERE clause. — Rahul Singh
– Rahul Singh, Commented Jan 21, 2016 at 9:40
I think, its simple but getting confused, I used this method for getting registration details: dt = bl.Get_Registrationdetailsbydate1(bo); and this method is use for getting education details. dt1 = bl.Get_Registrationdetailsbydate2(bo);. but after filling of first datatable, how to pass parameter to second procedure means how to get email ids from first datatable dt to second dt1. @Rahul Singh — Mr doubt
– Mr doubt, Commented Jan 21, 2016 at 9:47

Rahul Singh · Accepted Answer · 2016-01-21 10:39:43Z

2

IMHO the best approach would be to do this at the database end itself, i.e. when you fetch the registration details based on those details query the education details too, but if your logic is very complicated rather than simple tables then you will have to loop through your Registration DataTable and pass the email address as parameter to fetch Education details. Altough it will be compilcated approach since you will have to store all he emails in Registration table and then fetch Education details.

foreach(DataRow row in  dtRegistration.Rows)
{
    string Email = row.Field<string>("Email");
    //store it in a collection or comma separeted string and pass it to
    //bl.Get_Registrationdetailsbydate2(bo); table.
}

If you don't have huge data and you are fine with current code on how both datatable are populated then let the code be as it is and simply use LINQ to filter you Education datatable like this:-

DataTable filteredEducation = dtEducation.AsEnumerable()
             .Where(x => dtRegistration.AsEnumerable()
                           .Any(z => z.Field<string>("Email") == x.Field<string>("Email")))
             .CopyToDataTable();

You will have to import System.Linq namespace.

Update:

Finally your button clcik handler should look like this:-

dt = bl.Get_Registrationdetailsbydate1(bo);
dt1 = bl.Get_Registrationdetailsbydate2(bo);
DataTable filteredEducation = dt1.AsEnumerable()
                                 .Where(x => dt.AsEnumerable()
                           .Any(z => z.Field<string>("Email") == x.Field<string>("Email")))
                           .CopyToDataTable();
ds.Tables.Add(dt);
ds.Tables.Add(filteredEducation);
ExcelHelper.ToExcel(ds, "DangoteUsers.xls", Page.Response);

edited Jan 21, 2016 at 10:39

answered Jan 21, 2016 at 10:03

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Mr doubt Over a year ago

Can you update loop code also, as you said, i have to loop through your Registration DataTable and pass the email address as parameter to fetch Education details. bo.para1 is passing parameters, I am not aware of linq or update answer as above code. @Rahul Singh

Rahul Singh Over a year ago

@zahed - Updated but as I said it will be compicated, try LINQ approach its very easy and straighforward.

Mr doubt Over a year ago

Actual i have thousand of data, then how to pass parameter through linq, the above your linq answer getting confused to me, can you update clearly according to my code, please dont mind. @Rahul Singh

Rahul Singh Over a year ago

@zahed - No need to pass any parameter through LINQ that's what I said. You already have both datatables right? You want to filter second table based on first. Exactly that we are doing with LINQ, Where caluse will filter with those email which are present in Registration DataTable.

Mr doubt Over a year ago

Thank you very much, but some 5 to 6 records is missing according to searching date wise. @Rahul Singh

|

Collectives™ on Stack Overflow

Filtering on multiple DataTables with First DataTable

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related