Member Avatar for Tank50

HI

Can I join two data tables without any database connection,Please let me know if there way to do this.Iam using C#

Thanks
Tank50

  • 3 Contributors
  • 4 Replies
  • 130 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

A small summary and a practical option that solves the "outer join" need shown in this thread.

As pointed out, a DataRelation links two DataTables in-memory but does not produce an outer-joined result table for you. Since is loading from Excel (asked earlier to ), column types and DBNulls are common pitfalls—so normalize types first. For true outer-join results, one simple, safe approach is to use LINQ to DataSet to perform a left (or full) outer join and then materialize a new DataTable.

Example: left outer join (project into a new result DataTable)

DataTable result = new DataTable();
result.Columns.Add("No", typeof(int));
result.Columns.Add("Name", typeof(string));
result.Columns.Add("Data", typeof(int));

var left = dt1.AsEnumerable();
var right = dt2.AsEnumerable();

var q = from l in left
        join r in right
          on l.Field<int>("No") equals r.Field<int>("No") into grp
        from r in grp.DefaultIfEmpty()
        select new {
            No = l.Field<int>("No"),
            Name = l.Field<string>("Name"),
            Data = r == null ? (int?)null : r.Field<int>("Data")
        };

foreach (var item in q)
{
    var nr = result.NewRow();
    nr["No"] = item.No;
    nr["Name"] = item.Name ?? string.Empty;
    nr["Data"] = (object)item.Data ?? DBNull.Value;
    result.Rows.Add(nr);
}

Notes and quick tips

  • For a full outer join, union the left-join rows with rows from dt2 whose keys are not in dt1 (use Except/Contains or a keyed Dictionary).
  • Be explicit about column types when calling Field<T>; Excel import often yields strings or nulls—convert/trim before joining.
  • For large tables, build a Dictionary keyed on the join column for O(1) lookups instead of repeated LINQ searches.
  • If you keep the DataRelation approach, check row.GetParentRow(...)/GetChildRows(...) for nulls rather than assuming a match.

For background on the LINQ-to-DataSet approach see the Microsoft docs: .

Recommended Answers

All 4 Replies

Member Avatar for extofer

Where are you getting the Data From??

Member Avatar for Tank50

HI

Iam reading from excel file and I store values into data tables.

Thanks
Tank50

Member Avatar for kvprajapati

HI

Iam reading from excel file and I store values into data tables.

Thanks
Tank50

You say that you want to store data from Excel file and store into two DataTable instances - You are going to split your data.

If you want to add relationship between two tables - one table must have a primarykey and another table must have a same datatype field in which relationship exists.

.....
 .....
 // Two DataTable instances
 DataTable dt1=new DataTable("Table1");
 DataTable dt2=new DataTable("Table2");

 dt1.Columns.Add("No",typeof(int));
 dt1.Columns.Add("Name");

 dt2.Columns.Add("No",typeof(int));
 dt2.Columns.Add("Data",typeof(int));

 // Adding a primary key
 dt1.PrimaryKey=new DataColumn[]{dt1.Columns[0]};

 // Create an instance of DataSet
 DataSet ds=new DataSet("DB");
 ds.Tables.Add(dt1);
 ds.Tables.Add(dt2);
 ds.Relations.Add("Relation1",dt1.Columns[0],dt2.Columns[0]);
 ...
 ...
Member Avatar for Tank50

Hi

I did what is adatapost is mentioned but problem its equi join,I want to join it as outer join ,coz if thre is no match record in dt1 table for dt2.Columns[0] then it give me exception.I want to join it as outer join.

Thanks
Tank50

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.