How to update and add new row in a datatable from another datatable using linq

Question

0.00/5 (No votes)

See more:

II have two data tables as follows

dtOne
-------------------------
  ID  |   Name
--------------------------
 101  |  AA
 102  |  BB
 103  |  CC
 104  |  DD
 105  |  EE
 106  |  FF
--------------------------

dtTwo
-------------------------
  ID  |   Name
--------------------------
  101  |  AA
  102  |  BBAA
  103  |  CCBB
  104  |  DD
  107  |  GG
  108  |  HH

--------------------------

I just want the result as data which is `in dtOne` and `not in dtTwo` (dtOne-dtTwo)

dtResult
-------------------------
  ID  |   Name
--------------------------
 101  |  AA
 102  |  BB
 103  |  CC
 104  |  DD
 107  |  GG
 108  |  HH
 105  |  EE
 106  |  FF
--------------------------

How can i achieve this .

What I have tried:

C#

DataSet ds = new DataSet();
            DataSet ds1 = new DataSet();

            ds1.ReadXml(HttpContext.Current.Server.MapPath("XML/myFile.xml"));
            DataTable dt1 = ds1.Tables[0];


            var query = (from p in dt.AsEnumerable()
                         join t in dt1.AsEnumerable()
                             on p.Field<string>("Station") equals t.Field<string>("Station")
                         select new
                        {
                            Station = p.Field<string>("Station"),
                            Max_Temp = p.Field<string>("Max_Temp"),
                            Min_Temp = p.Field<string>("Min_Temp"),
                            Weather_Detail = p.Field<string>("Weather_Detail"),
                            DateTime = p.Field<string>("DateTime")

                        });

            DataTable myDataTable = new DataTable();
            myDataTable.Columns.Add("DateTime", typeof(string));
            myDataTable.Columns.Add("Station", typeof(string));
            myDataTable.Columns.Add("Max_Temp", typeof(string));
            myDataTable.Columns.Add("Min_Temp", typeof(string));
            myDataTable.Columns.Add("Weather_Detail", typeof(string));



            foreach (var element in query)
            {
                var row = myDataTable.NewRow();
                row["DateTime"] = element.DateTime;

                row["Station"] = element.Station;
                row["Max_Temp"] = element.Max_Temp;

                row["Min_Temp"] = element.Min_Temp;

                row["Weather_Detail"] = element.Weather_Detail;


                myDataTable.Rows.Add(row);
            }




            DataTable dtExtraRow = new DataTable();

            if ((dt1 != null && dt1.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
            {
                var temp1 = dt1.Rows.OfType<datarow>().Where(a => dt1.Rows.OfType<datarow>().
                 Select(k => Convert.ToString(k["Station"])).Except(myDataTable.Rows.OfType<datarow>().Select(k => Convert.ToString(k["Station"])).ToList()).Contains(Convert.ToString(a["Station"]))).AsEnumerable();
                if (temp1 != null && temp1.Count() > 0)
                    dtExtraRow = temp1.CopyToDataTable();

            }
            if (dtExtraRow.Rows.Count > 0)
                myDataTable = myDataTable.AsEnumerable().Union(dtExtraRow.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();

          
            DataTable dtExtraRowOfXml = new DataTable();

            if ((dt != null && dt.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
            {
                var temp = dt.Rows.OfType<datarow>().Where(a => dt.Rows.OfType<datarow>().
                 Select(k => Convert.ToString(k["Station"])).Except(myDataTable.Rows.OfType<datarow>().Select(k => Convert.ToString(k["Station"])).ToList()).Contains(Convert.ToString(a["Station"]))).AsEnumerable();
                if (temp != null && temp.Count() > 0)
                    dtExtraRowOfXml = temp.CopyToDataTable();

            }

if (dtExtraRowOfXml.Rows.Count > 0)
                myDataTable = myDataTable.AsEnumerable().Union(dtExtraRowOfXml.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();

           
            ds.Tables.Add(myDataTable);
            string strXml = ds.GetXml();
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml(strXml);
            xDoc.Save(HttpContext.Current.Server.MapPath("XML/myFile.xml")); //XML is the folder name and myFile.xml is ur new file name

Posted 3-Jun-16 1:41am

kumari567

Updated 6-Jun-16 8:56am

Garth J Lancaster

v2

Add a Solution

Comments

kumari567 3-Jun-16 7:44am

tHE ABOVE CODE IS GIVING CORRECT RESULT..BUT I want to direct update datatable from another datatable .

Maciej Los 6-Jun-16 1:53am

Whcich datatable you want to update? dt1 or dt2? I don't get you!

kumari567 6-Jun-16 1:55am

dt2

Maciej Los 6-Jun-16 4:03am

Seems, you're wrong. Based on the example you provided, you want to update dt1 instead of dt2, which means you want to get all data from dt1 and only those data from dt2 which does not exist in dt1. Am i right?

kumari567 6-Jun-16 5:31am

I want to update dt2 with match row with dt1 and also add new row from dt1 into dt2 which is not in dt2

2 solutions

Solution 2

Assumming that you want to join data, try this:

C#

//get result list by using Union method together with IEqualityComparer
var result = dt1.AsEnumerable()
                .Union(dt2.AsEnumerable(), new MyComparer())
                .OrderBy(x=>x.Field<int>("ID"));</int>

And the definition of comparer class:

C#

public class MyComparer : IEqualityComparer<datarow>
{
    // rows are equal if their ID are equal.
    public bool Equals(DataRow x, DataRow y)
    {

        //Check whether the compared objects reference the same data.
        if (Object.ReferenceEquals(x, y)) return true;

        //Check whether any of the compared objects is null.
        if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            return false;

        //Check whether the rows are equal.
        return x.Field<int>("ID") == y.Field<int>("ID");
    }

    // If Equals() returns true for a pair of objects 
    // then GetHashCode() must return the same value for these objects.

    public int GetHashCode(DataRow dr)
    {
        //Check whether the object is null
        if (Object.ReferenceEquals(dr, null)) return 0;

        //Get hash code for the ID field.
        return  dr.Field<int>("ID").GetHashCode();

    }
}</int></int></int></datarow>

Result:

ID  Name
101 AA 
102 BB 
103 CC 
104 DD 
105 EE 
106 FF 
107 GG 
108 HH

More at: Enumerable.Union(TSource) Method (IEnumerable(TSource), IEnumerable(TSource), IEqualityComparer(TSource)) (System.Linq)[^]
You may want to use: Enumerable.Intersect(TSource) Method (IEnumerable(TSource), IEnumerable(TSource), IEqualityComparer(TSource)) (System.Linq)[^] too.

Posted 6-Jun-16 8:56am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kumari567 · Accepted Answer · 2016-06-05T20:03:00

C#

private void  UpdateXml(DataTable dt1, DataTable dt2)
       {

-------matching row should be updated and nomatching row should be added in dt2
           DataTable Match = dt1.AsEnumerable().Where(ra => dt2.AsEnumerable().Any(rb => rb.Field<string>("Station") == ra.Field<string>("Station"))).AsDataView().ToTable();

           foreach (DataRow dRNew in Match.Rows)
           {
               DataRow row = dt2.AsEnumerable().Where(r => r.Field<string>("Station").Equals(dRNew["Station"])).First();

               row["DateTime"] = dRNew["DateTime"];
               row["Station"] = dRNew["Station"];
               row["Max_Temp"] = dRNew["Max_Temp"];

               row["Min_Temp"] = dRNew["Min_Temp"];

               row["Weather_Detail"] = dRNew["Weather_Detail"];

           }



           DataTable NoMatch = dt1.AsEnumerable().Where(ra => !dt2.AsEnumerable().Any(rb => rb.Field<string>("Station") == ra.Field<string>("Station"))).AsDataView().ToTable();

           NoMatch.Merge(dt2);
           //dt1.Merge(NoMatch);

           NoMatch.DefaultView.Sort = "Station";
           NoMatch = NoMatch.DefaultView.ToTable();

        
       }