Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- private void PerformLeftOuterJoin()
- {
- try
- {
- //Prepaar Left DataTable.
- DataTable dtContacts = new DataTable("Contacts");
- dtContacts.Columns.Add("ContactID", typeof(int));
- dtContacts.Columns.Add("FirstName", typeof(string));
- dtContacts.Columns.Add("LastName", typeof(string));
- //Prepaar Right DataTable.
- DataTable dtContactAddresses = new DataTable("Addresses");
- dtContactAddresses.Columns.Add("AddressID", typeof(int));
- dtContactAddresses.Columns.Add("AddressLine1", typeof(string));
- dtContactAddresses.Columns.Add("AddressLine2", typeof(string));
- dtContactAddresses.Columns.Add("AddressLine3", typeof(string));
- dtContactAddresses.Columns.Add("City", typeof(string));
- dtContactAddresses.Columns.Add("State", typeof(string));
- dtContactAddresses.Columns.Add("Country", typeof(string));
- dtContactAddresses.Columns.Add("ContactID", typeof(int));
- //Prepare Final Output DataTable
- DataTable dtContactResult = new DataTable("Contact_Address");
- dtContactResult.Columns.Add("FirstName", typeof(string));
- dtContactResult.Columns.Add("LastName", typeof(string));
- dtContactResult.Columns.Add("AddressLine1", typeof(string));
- dtContactResult.Columns.Add("AddressLine2", typeof(string));
- dtContactResult.Columns.Add("AddressLine3", typeof(string));
- dtContactResult.Columns.Add("City", typeof(string));
- dtContactResult.Columns.Add("State", typeof(string));
- dtContactResult.Columns.Add("Country", typeof(string));
- DataRow dr = null;
- //Add Rows In Left DataTable.
- dr = dtContacts.NewRow();
- dr.ItemArray = new object[] { 1, "Jevis", "Reshamwala" };
- dtContacts.Rows.Add(dr);
- dr = dtContacts.NewRow();
- dr.ItemArray = new object[] { 2, "Mehul", "Jariwala" };
- dtContacts.Rows.Add(dr);
- dr = dtContacts.NewRow();
- dr.ItemArray = new object[] { 3, "Dhaval", "Desai" };
- dtContacts.Rows.Add(dr);
- //Add Rows In Right DataTable.
- dr = dtContactAddresses.NewRow();
- dr.ItemArray = new object[] { 1, "3/456", "Navapura Karwa Road,", "Near Bhagal.", "Surat", "Gujarat", "India", 1 };
- dtContactAddresses.Rows.Add(dr);
- dr = dtContactAddresses.NewRow();
- dr.ItemArray = new object[] { 2, "4/123", "Ghanchi Street,", "Near Patel Wadi", "Surat", "Gujarat", "India", 2 };
- dtContactAddresses.Rows.Add(dr);
- //Get Contact along with the their address.
- var query = (from DataRow drLeft in dtContacts.Rows
- join DataRow drRight in dtContactAddresses.Rows
- on drLeft["ContactID"] equals drRight["ContactID"]
- select new
- {
- FirstName = drLeft["FirstName"],
- LastName = drLeft["LastName"],
- AddressLine1 = drRight["AddressLine1"],
- AddressLine2 = drRight["AddressLine2"],
- AddressLine3 = drRight["AddressLine3"],
- City = drRight["City"],
- State = drRight["State"],
- Country = drRight["Country"]
- });
- //Prepare Final Output.
- foreach (var item in query)
- {
- dr = dtContactResult.NewRow();
- dr.ItemArray = new object[]
- { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
- item.City,item.State,item.Country};
- dtContactResult.Rows.Add(dr);
- }
- //As above result shown 3rd contact "Dhaval" is missing because it has not entered an address.
- //So we can go for "Left Outer Join"
- //Let's see how to do it.
- //Clear Rows from Existing Result.
- dtContactResult.Rows.Clear();
- //Get Contact along with the their address.
- query = (from DataRow drLeft in dtContacts.Rows
- join DataRow drRight in dtContactAddresses.Rows
- on drLeft["ContactID"] equals drRight["ContactID"]
- into rightRow
- from rw in rightRow.DefaultIfEmpty()
- select new
- {
- FirstName = drLeft["FirstName"],
- LastName = drLeft["LastName"],
- AddressLine1 = rw == null ? "Not Avilable" : rw["AddressLine1"],
- AddressLine2 = rw == null ? "Not Avilable" : rw["AddressLine2"],
- AddressLine3 = rw == null ? "Not Avilable" : rw["AddressLine3"],
- City = rw == null ? "Not Avilable" : rw["City"],
- State = rw == null ? "Not Avilable" : rw["State"],
- Country = rw == null ? "Not Avilable" : rw["Country"]
- });
- //Prepare Final Output.
- foreach (var item in query)
- {
- dr = dtContactResult.NewRow();
- dr.ItemArray = new object[]
- { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
- item.City,item.State,item.Country};
- dtContactResult.Rows.Add(dr);
- }
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement