Implement joint query of two datatables

This is the basis of the original Union query.

For example, Method 1: append the sub-table array to the base table array. This achieves the effect similar to the view (single table.

How can left join (inner join) and right join (Outer Join) be implemented?

Let's take a closer look at the solution tomorrow.

Without any SQL statement

Method 1:

Public datatable join (datatable first, datatable second, datacolumn [] FJC, datacolumn [] SJC)
{

// Create a new able

Datatable table = new datatable ("join ");

// Use a dataset to leverage datarelation

Using (Dataset DS = new dataset ())
{

// Copy the DataTable to DataSet

Ds. Tables. AddRange (new DataTable [] {First. Copy (), Second. Copy ()});

DataColumn [] parentcolumns = new DataColumn [FJC. Length];

For (int I = 0; I <parentcolumns. Length; I ++)
{

Parentcolumns [I] = ds. Tables [0]. Columns [FJC [I]. ColumnName];

}

Datacolumn [] childcolumns = new datacolumn [SJC. Length];

For (INT I = 0; I <childcolumns. length; I ++)
{

Childcolumns [I] = Ds. Tables [1]. Columns [SJC [I]. columnname];

}

// Create an association

Datarelation r = new datarelation (string. Empty, parentcolumns, childcolumns, false );

DS. relations. Add (R );

// Create a column for The joined table

For (INT I = 0; I <first. Columns. Count; I ++)
{

Table. Columns. Add (first. Columns [I]. columnname, first. Columns [I]. datatype );

}

For (int I = 0; I <Second. Columns. Count; I ++)
{

// Check whether there are repeated columns. If there is a Column in the Second able, add _ Second

If (! Table. Columns. Contains (Second. Columns [I]. ColumnName ))

Table. Columns. Add (Second. Columns [I]. ColumnName, Second. Columns [I]. DataType );

Else

Table. Columns. Add (Second. Columns [I]. ColumnName + "_ Second", Second. Columns [I]. DataType );

}

Table. BeginLoadData ();

Foreach (DataRow firstrow in ds. Tables [0]. Rows)
{

// Obtain the row data

Datarow [] childrows = firstrow. getchildrows (R );

If (childrows! = NULL & childrows. length> 0)
{

Object [] parentarray = firstrow. itemarray;

Foreach (DataRow secondrow in childrows)
{

Object [] secondarray = secondrow. ItemArray;

Object [] joinarray = new object [parentarray. Length + secondarray. Length];

Array. Copy (parentarray, 0, joinarray, 0, parentarray. Length );

Array. Copy (secondarray, 0, joinarray, parentarray. Length, secondarray. Length );

Table. LoadDataRow (joinarray, true );

}

}

}

Table. EndLoadData ();

}

Return table;

}

////////////////////////////////////

Method 2:

This method is used to merge two tables.

/// <Param name = "dt1"> Table 1 to be merged </param>
/// <Param name = "dt2"> table 2 to be merged </param>
/// <Param name = "KeyColName"> key column names associated with dt1 and dt2 </param>

Public DataTable MergeDataTable (DataTable dt1, DataTable dt2, String KeyColName)
{
// Define temporary variables
DataTable dtReturn = new DataTable ();
Int I = 0;
Int j = 0;
Int k = 0;
Int colKey1 = 0;
Int colKey2 = 0;

// Set the table dtReturn name
DtReturn. TableName = dt1.TableName;
// Set the name of the dtReturn column.
For (I = 0; I <dt1.Columns. Count; I ++)
{
If (dt1.Columns [I]. ColumnName = KeyColName)
{
Colkey1 = I;
}

Dtreturn. Columns. Add (dt1.columns [I]. columnname );
}

For (j = 0; j <dt2.columns. Count; j ++)
{
If (dt2.columns [J]. columnname = keycolname)
{
Colkey2 = J;
Continue;
}

Dtreturn. Columns. Add (dt2.columns [J]. columnname );
}

// Create table Space
For (I = 0; I <dt1.Rows. Count; I ++)
{
DataRow dr;
Dr = dtReturn. NewRow ();
DtReturn. Rows. Add (dr );
}

// Write data of table dt1 and table dt2 to dtReturn
For (I = 0; I <dt1.Rows. Count; I ++)
{
Int m =-1;
// Copy row I of Table dt1 to dtReturn
For (j = 0; j <dt1.Columns. Count; j ++)
{
Dtreturn. Rows [I] [J] = dt1.rows [I] [J]. tostring ();
}

// Query the keycolname data in dt2, which is the same as that in dt1.
For (k = 0; k <dt2.rows. Count; k ++)
{
If (dt1.Rows [I] [colKey1]. ToString () = dt1.Rows [k] [colKey2]. ToString ())
{
M = k;
}
}

// Copy the data in Row m of table dt2 to dtReturn without the KeyColName (ID) Column
If (m! =-1)
{
For (k = 0; k <dt2.Columns. Count; k ++)
{
If (k = colKey2)
{
Continue;
}
DtReturn. Rows [I] [j] = dt2.Rows [m] [k]. ToString ();
J ++;
}
}
}

Return dtReturn;
}