Merge 2 datatables based on column value

Question

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

Answer 1

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:

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