Click here to Skip to main content
15,887,585 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
II have two data tables as follows
dtOne
-------------------------
  ID  |   Name
--------------------------
 101  |  ABC
 102  |  XYZ
 103  |  MNO
 104  |  PQR
--------------------------

dtTwo
-------------------------
  ID  |   Name
--------------------------
 101  |  FGH
 102  |  XYZ
 104  |  GPS
--------------------------

I just want the result as data which is `in dtOne` and `not in dtTwo` (dtOne-dtTwo)
dtResult
-------------------------
  ID  |   Name
--------------------------
 103  |  MNO
--------------------------

How can i achieve this .

What I have tried:

I have used except method of LINQ but that is giving the result like this
101  |  ABC
103  |  MNO
104  |  PQR
101  |  FGH
104  |  GPS

Thats means matching both column in except method
Posted
Updated 20-Dec-18 22:16pm
v2

try this

C#
DataTable dt1 = new DataTable();
      dt1.Columns.Add("ID");
      dt1.Columns.Add("Name");
      dt1.Rows.Add(101, "ABC");
      dt1.Rows.Add(102, "XYZ");
      dt1.Rows.Add(103, "MNO");
      dt1.Rows.Add(104, "PQR");

      DataTable dt2 = new DataTable();
      dt2.Columns.Add("ID");
      dt2.Columns.Add("Name");
      dt2.Rows.Add(101, "FGH");
      dt2.Rows.Add(102, "XYZ");
      dt2.Rows.Add(104, "GPS");


     DataTable dtOutput = dt1.Rows.OfType<DataRow>().Where(a=>  dt1.Rows.OfType<DataRow>().Select(k=>  Convert.ToInt32(k["ID"])).Except( dt2.Rows.OfType<DataRow>().Select(k=>  Convert.ToInt32(k["ID"])).ToList()).Contains( Convert.ToInt32( a["ID"]))).CopyToDataTable();
 
Share this answer
 
Comments
kumari567 1-Jun-16 1:51am    
this code throw an exception.... source table does not contains no row
Karthik_Mahalingam 1-Jun-16 2:19am    
validate it

if ((dt1 != null && dt1.Rows.Count > 0) && (dt2 != null && dt2.Rows.Count > 0))
{
DataTable dtOutput = dt1.Rows.OfType<datarow>().Where(a => dt1.Rows.OfType<datarow>().Select(k => Convert.ToInt32(k["ID"])).Except(dt2.Rows.OfType<datarow>().Select(k => Convert.ToInt32(k["ID"])).ToList()).Contains(Convert.ToInt32(a["ID"]))).CopyToDataTable();
}
kumari567 1-Jun-16 5:24am    
still I have exception
An exception of type 'System.InvalidOperationException' occurred in System.Data.DataSetExtensions.dll but was not handled in user code

Additional information: The source contains no DataRows.
kumari567 1-Jun-16 5:28am    
my code is this
if ((dt != null && dt.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
{
dtExtraRowOfXml = 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"]))).CopyToDataTable();
}
Karthik_Mahalingam 1-Jun-16 5:43am    
try this..

DataTable dtOutput = new DataTable();

if ((dt1 != null && dt1.Rows.Count > 0) && (dt2 != null && dt2.Rows.Count > 0))
{
var temp = dt1.Rows.OfType < DataRow >().Where(a => dt1.Rows.OfType < DataRow > ().Select(k => Convert.ToInt32(k["ID"])).Except(dt2.Rows.OfType < DataRow > ().Select(k => Convert.ToInt32(k["ID"])).ToList()).Contains(Convert.ToInt32(a["ID"])));
if(temp != null && temp.Count() >0)
dtOutput = temp.CopyToDataTable();
}
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 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"]))).CopyToDataTable().AsEnumerable();
              if (temp != null && temp.Count() > 0)
                  dtExtraRowOfXml = temp.CopyToDataTable();

          }

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

          else
          {
              dtfinalXml = myDataTable.Clone();
          }


          ds.Tables.Add(dtfinalXml);

         // 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
 
Share this answer
 
C#
public DataTable getDiffRecords(DataTable dtDataOne, DataTable dtDataTwo)
        {
            DataTable returnTable = new DataTable("returnTable");

            using (DataSet ds = new DataSet())
            {
                ds.Tables.AddRange(new DataTable[] { dtDataOne.Copy(), dtDataTwo.Copy() });

                DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
                for (int i = 0; i < firstColumns.Length; i++)
                {
                    firstColumns[i] = ds.Tables[0].Columns[i];
                }

                DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
                for (int i = 0; i < secondColumns.Length; i++)
                {
                    secondColumns[i] = ds.Tables[1].Columns[i];
                }

                DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
                ds.Relations.Add(r1);

                DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
                ds.Relations.Add(r2);

                for (int i = 0; i < dtDataOne.Columns.Count; i++)
                {
                    returnTable.Columns.Add(dtDataOne.Columns[i].ColumnName, dtDataOne.Columns[i].DataType);
                }

                returnTable.BeginLoadData();
                foreach (DataRow parentrow in ds.Tables[0].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r1);
                    if (childrows == null || childrows.Length == 0)
                        returnTable.LoadDataRow(parentrow.ItemArray, true);
                }

                foreach (DataRow parentrow in ds.Tables[1].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r2);
                    if (childrows == null || childrows.Length == 0)
                        returnTable.LoadDataRow(parentrow.ItemArray, true);
                }
                returnTable.EndLoadData();
            }
            return returnTable;
        }
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900