使用第一个DataTable过滤多个DataTable

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.

我试图将数据库表的详细信息导出到一个Excel文件中的多个工作表中，我得到了导出工作正确，但是第一个DataTable电子邮件Id存在多个DataTable过滤的问题。

Below is my C# Code:

下面是我的c#代码:

  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.

我有两个表Registration_table和Education_table，还有两个可数据的注册细节和教育细节。

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.

我需要基于第一个DataTable(dt)的电子邮件id的结果基础，第一个DataTable邮件id的用户只应该进入第二个DataTable，这意味着基于电子邮件id的教育细节。

Suppose below is first DataTable details.

假设下面是第一个DataTable细节。

sno    email            mobile        city

 1     [e-mail]     123456789    hyderabad

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

我需要第二个数据细节的结果，如下面的单个用户。

sno       email              education 

 1        [e-mail]       MBA

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

我正在使用两个存储过程，首先获得注册信息和第二个教育细节。

1 个解决方案

#1

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.

IMHO这样做最好的方法是在数据库本身,即当你获取登记细节基于这些细节查询教育细节,但是如果你的逻辑非常复杂,而不是简单的表,那么你将必须遍历你的登记DataTable和通过电子邮件地址作为参数获取教育细节。由于您必须将所有的电子邮件存储在注册表中，然后获取教育详细信息，因此它将被编译。

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的代码没有问题，那么就让代码保持原样，使用LINQ对您的Education datatable进行如下过滤:-

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.

您需要导入系统。Linq命名空间。

Update:

更新:

Finally your button clcik handler should look like this:-

最后，您的按钮clcik处理程序应该如下所示:-

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);