Merge 2 datatables based on column value

Padmanabhan, Venkatesh 120 Reputation points
2023-04-14T05:27:33.73+00:00

Hi. I have 2 datatables. Datatable1 contains columns username, class Datatable2 contains columns USER_NAME, mark1, mark2,total I want to merge these 2 datatables using the column username. I tried using the merge, but I am getting datatable which has data from both datatables in different rows. How to fix this merge based on a column value? Thanks

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,411 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Hui Liu-MSFT 44,471 Reputation points Microsoft Vendor
    2023-04-17T09:20:28.95+00:00

    Hi,@ Padmanabhan, Venkatesh. Merge requires primary key to match the records. You could refer to the following code. MainWindow.xaml:

     <StackPanel>
            <DataGrid x:Name="grid1" Height="100" ItemsSource="{Binding dt1.DefaultView}" />
            <DataGrid x:Name="grid2" Height="100" ItemsSource="{Binding dt2.DefaultView}" />
            <Button x:Name="btn" Height="30" Click="btn_Click" Content="merge" />
            <DataGrid x:Name="grid3" Height="100" />
        </StackPanel>
    
    

    MainWindow.xaml.cs:

     public partial class MainWindow : Window
        {
            public DataTable dt1 { get; set; } = new DataTable("dt1");
            public DataTable dt2 { get; set; } = new DataTable("dt2");
            public DataTable dt3 { get; set; }   = new DataTable("dt3");
            public MainWindow()
            {
                InitializeComponent();
                CreateDataTable();
                 DataContext = this;
            }
            private void CreateDataTable()
            {
                dt1.Columns.Add("SchoolName", typeof(string));
                dt1.Columns.Add("Area", typeof(string));
                dt1.Columns.Add("Speciality", typeof(string));
                dt1.Columns.Add("UserName", typeof(string));
               
               dt1.PrimaryKey = new DataColumn[] { dt1.Columns["UserName"] };
                dt1.Rows.Add("School1","Area1","Speci1","John");
                dt1.Rows.Add("School2","Area2","Speci2","Ron");
                dt1.Rows.Add("School3","Area3","Speci3", "Max");
    
                dt2.Columns.Add("User_Name", typeof(string));
                dt2.Columns.Add("Mark1", typeof(string));
                dt2.Columns.Add("Mark2", typeof(string));
                dt2.Columns.Add("Total", typeof(string));
    
                dt2.PrimaryKey = new DataColumn[] { dt2.Columns["User_Name"] };
                dt2.Rows.Add("John", "10", "10", "20");
                dt2.Rows.Add("Ron", "20", "10", "30");
                dt2.Rows.Add("Max", "30", "10", "40");
               
            }
          
        
        }
    
    

    Update: You could do this using Join .

     private void btn_Click(object sender, RoutedEventArgs e)
            {
                dt3.Columns.Add("SchoolName", typeof(string));
                dt3.Columns.Add("Area", typeof(string));
                dt3.Columns.Add("Speciality", typeof(string));
                dt3.Columns.Add("UserName", typeof(string));
                dt3.Columns.Add("Mark1", typeof(string));
                dt3.Columns.Add("Mark2", typeof(string));
                dt3.Columns.Add("Total", typeof(string));
    
                var results = from row1 in dt1.AsEnumerable()
                              join row2 in dt2.AsEnumerable() on row1.Field<string>("UserName") equals row2.Field<string>("User_Name")
    
                              select dt3.LoadDataRow(new object[]
                              {
                                  row1.Field<string>("SchoolName"),
                                  row1.Field<string>("Area"),
                                  row1.Field<string>("Speciality"),
                                  row1.Field<string>("UserName"),
                                  row2.Field<string>("Mark1"),
                                  row2.Field<string>("Mark2"),
                                  row2.Field<string>("Total"),
                                  
                              },false);
                 dt3 = results.CopyToDataTable();
    
                this.grid3.ItemsSource = dt3.DefaultView;
    
            }
        
    

    The result: User's image

    If the response is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our [documentation][5] to enable e-mail notifications if you want to receive the related email notification for this thread. [5]: https://docs.microsoft.com/en-us/answers/articles/67444/email-notifications.html