Home

Microsoft Visual C# ADO.NET Programming: Data Joins

   

Joins Fundamentals

 

Introduction

A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

  1. Selecting the tables that will be involved in the join
  2. Selecting a column that will create the link in each table
  3. Writing or creating a SQL statement that will produce the records

Practical LearningPractical Learning: Introducing Joins

  1. Start Microsoft Visual Studio and create a new Windows Application named SouthernEnergyCorp1
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type SouthernEnergyCorp.cs and press Enter

The Tables of a Join

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table.

When creating the child table, remember to create a column that would serve as the link to the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column is the same as the primary key of the parent table.

Practical LearningPractical Learning: Creating the Tables

  1. Double-click the middle of the form and change the file as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace SouthernEnergyCorp1
    {
        public partial class SouthernEnergyCorp : Form
        {
            string[] strCode;
    
            public SouthernEnergyCorp()
            {
                InitializeComponent();
            }
    
            void CreateDatabase()
            {
                using (SqlConnection cntSEC =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security=Yes"))
                {
                    SqlCommand cmdSEC =
                        new SqlCommand("IF  EXISTS (" +
                                       "SELECT name " +
                                       "FROM sys.databases " +
                                       "WHERE name = N'SouthernEnergyCorp1')" +
                                       "DROP DATABASE SouthernEnergyCorp1; " +
                                       "CREATE DATABASE SouthernEnergyCorp1;", cntSEC);
                    cntSEC.Open();
                    cmdSEC.ExecuteNonQuery();
    
                    MessageBox.Show("The SouthernEnergyCorp1 database has been created.",
                                    "Southern Energy Corporation",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cntSEC =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='SouthernEnergyCorp1';" +
                                      "Integrated Security=Yes"))
                {
                    SqlCommand cmdSEC = new SqlCommand("CREATE TABLE Customers(" +
                        "AccountNumber nvarchar(10) not null, CustomerName nvarchar(50) not null," +
                        "ResidenceType nvarchar(40), Address1 nvarchar(60) not null, " +
                        "Address2 nvarchar(50), City nvarchar(50), State nvarchar(40), " +
                        "ZIPCode nvarchar(20), Constraint PK_Customers Primary Key(AccountNumber));", cntSEC);
                    cntSEC.Open();
                    cmdSEC.ExecuteNonQuery();
    
                    MessageBox.Show("The Customers table has been created.",
                                    "Southern Energy Corporation",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cntSEC =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='SouthernEnergyCorp1';" +
                                      "Integrated Security=Yes"))
                {
                    SqlCommand cmdSEC = new SqlCommand("CREATE TABLE MetterReadings(" +
                        "ReadingID int identity(1, 1) not null, " +
                        "CustomerAccount nvarchar(10) not null, " +
                        "PreviousReadingDate date," +
                        "PreviousReadingNumber int, NewReadingDate date not null, " +
                        "NewReadingNumber int not null," +
                        "Consumption AS (NewReadingNumber - PreviousReadingNumber), " +
                        "Constraint PK_MetterReadings Primary Key(ReadingID));", cntSEC);
                    cntSEC.Open();
                    cmdSEC.ExecuteNonQuery();
    
                    MessageBox.Show("The MetterReadings table has been created.",
                                    "Southern Energy Corporation",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cntSEC =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='SouthernEnergyCorp1';" +
                                      "Integrated Security=Yes"))
                {
                    SqlCommand cmdSEC = new SqlCommand("INSERT INTO Customers VALUES" +
                        "('27397405', 'John Melmann', 'Single Family', '12005 Belcrest Ave', NULL, " +
                        "'Takoma Park', 'MD', '20904')," +
                        "('80940696', 'Daniel Fletcher', 'Apartment', '808 Solthan Drive N.E.', '#206', " +
                        "'Washington', 'DC', '20012'), " +
                        "('20499507', 'Frank Donaldson', 'Single Family', '2466 Nicholson Crt', NULL, " +
                        "'Alexandria', 'VA', '22234')," +
                        "('92824975', 'Gabriela Dawson', 'Townhouse', '1602 Salomons Ave', NULL, " +
                        "'Silver Spring', 'MD', '20906');", cntSEC);
                    cntSEC.Open();
                    cmdSEC.ExecuteNonQuery();
    
                    MessageBox.Show("A few records have been added to the Customers table.",
                                    "Southern Energy Corporation",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (SqlConnection cntSEC =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='SouthernEnergyCorp1';" +
                                      "Integrated Security=Yes"))
                {
                    SqlCommand cmdSEC = new SqlCommand("INSERT INTO MetterReadings(CustomerAccount, " +
                        "PreviousReadingDate, PreviousReadingNumber, NewReadingDate, NewReadingNumber) VALUES" +
                        "('27397405', '20110415', 2488, '20110512', 2646), " +
                        "('80940696', '20110415', 602, '20110512', 648), " +
                        "('20499507', '20110415', 4155, '20110513', 4402), " +
                        "('92824975', '20110415', 1904, '20110513', 2006), " +
                        "('27397405', '20110512', 2646, '20110616', 2960), " +
                        "('80940696', '20110512', 648, '20110616', 669), " +
                        "('20499507', '20110513', 4402, '20110616', 4881);", cntSEC);
                    cntSEC.Open();
                    cmdSEC.ExecuteNonQuery();
    
                    MessageBox.Show("A few records have been added to the MetterReadings table.",
                                    "Southern Energy Corporation",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
    
            private void SouthernEnergyCorp_Load(object sender, EventArgs e)
            {
                CreateDatabase();
            }
        }
    }
  2. Execute the application to create the database
  3. Click OK for each message box
  4. Close the form and return to your programming environment
  5. Change the Load event as follows:
    private void SouthernEnergyCorp_Load(object sender, EventArgs e)
    {
        // CreateDatabase();
    
        strCode = new string[]
        {
    
        };
    }
  6. Return to the form and design it as follows:
     
  7. Southern Energy Corporation
    Control Name Text Other Properties
    Group Box Group Box   Metter Reading & Account Consumption  
    RadioButton RadioButton      
    RadioButton RadioButton      
    RadioButton RadioButton      
    RadioButton RadioButton      
    RadioButton RadioButton btnSubmit Submit  
    RichTextBox RichTextBox rtbSEC   Anchor: Top, Left, Right
    DataGridView DataGridView dgvEnergy   Anchor: Top, Bottom, Left, Right
    Button Button btnClose Close Anchor: Bottom, Right

    Visually Creating a Join

    Equipped with the necessary tables and their columns, you can create the join. To visually create a join, in the Server Explorer, right-click one of the tables involved in the relationship and click New Query. This would display the Add Table dialog box.

    To select a table from the Add Table dialog box:

    • Click the table's name and click Add
    • Double-click a table

    From there, you can click each table and click Add. After adding the tables, you can click Close. Because the foundation of a join lies on at least two tables, if you had selected only one table or if you need a table that is not yet selected, you can add a table any time. To do this:

    • On the main menu, click Query Designer -> Add Table...
    • On the Query Designer toolbar, click the Add Table button Add Table
    • Right-click any part of the Query window and click Add table...

    Any of these actions would display the Add Table dialog box. Alternatively, instead of using Add Table, you can drag the child table from the Object Explorer and drop it in the Diagram section.

    Here is an example of two tables that have been added:

    Joins

    The Sections of a Query Window

    To create and manage a query, you can use various sections in the Query window. To display a section:

    • On the main menu, click Query Designer, position the mouse on Pane, and click one of the options
    • On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, the Show SQL Pane button Show SQL Pane, and the Show Result Pane button Show Result Pane
    • Right-click anywhere in the Query window, position the mouse on Pane, and click one of the options

    Panes

    If you don't want a particular section or you want to hide some sections:

    • On the main menu, click Query Designer, position the mouse on Pane, and click the selected option
    • On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, the Show SQL Pane button Show SQL Pane, and the Show Result Pane button Show Result Pane
    • Right-click anywhere in the Query window, position the mouse on Pane, and click one of the options

    You can drag the title bars of the tables to move them and position them to your liking. After selecting the table(s), on the Add Table dialog box, you can click Close.

    If a relationship was already established between the tables, a joining line would show it.

    Practical LearningPractical Learning: Preparing a Join

    1. If the Server Explorer is not visible, on the main menu, click View -> Server Explorer.
      In the Server Explorer, right-click Data Connections and click Add Connection...
    2. In the Server Name combo box, type (local) (if you are planning to store the database in a particular server or in a computer, enter its name)
    3. In the Select combo box, select SouthernEnergyCorp1
    4. Click OK
    5. Still in the Server Explorer, expand the SouthernEnergyCorp1.dbo node (although not necessary, if you want, expand the Tables node)
    6. In the Server Explorer, under SouthernEnergyCorp1.dbo, right-click the Tables node and click New Query
    7. In the Add Table dialog box, click Customers and click Add
    8. Double-click MetterReadings
    9. Click Close.
      (If there is an check box in the tables, uncheck it/them)
    10. To show all sections, right-click any part of the window, position the mouse on Pane and click any item that is not clicked. Do that until all sections are displaying

    Programmatically Creating a Join

    In SQL code, the basic formula to create a join is:

    SELECT WhatColumn(s)
    FROM ChildTable
    TypeOfJoin ParentTable
    ON Condition

    The ChildTable factor specifies the table that holds the records that will be retrieved. It can be represented as follows:

    SELECT WhatColumn(s)
    FROM Persons
    TypeOfJoin ParentTable
    ON Condition

    The ParentTable factor specifies the table that holds the column with the primary key that will control what records, related to the child table, that will display. This factor would be represented as follows:

    SELECT WhatColumn(s)
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    The Condition factor is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

    SELECT WhatColumn(s)
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    The WhatColumn(s) factor of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

    SELECT *
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

    SELECT LastName, FirstName, Gender
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name of the column by preceding it with the name of its parent table followed by a period. Here are examples:

    SELECT LastName, FirstName, Persons.GenderID,
                 Genderes.GenderID, Gender
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here are examples:

    SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
                 Genderes.GenderID, Genderes.Gender
    FROM Persons
    TypeOfJoin Genderes
    ON Persons.GenderID = Genderes.GenderID

    Cross and Inner Joins

     

    Introduction

    When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.

    Cross Joins

    A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.

    To create a cross join, you can replace the TypeOfJoin factor of our formula with CROSS JOIN or CROSS OUTER JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
                 Genderes.GenderID, Genderes.Gender
    FROM Persons
    CROSS JOIN Genderes
    GO

    If you are working visually on a table, by default, after you have just added a table to another one (if no relationship was already established between both tables), the query would be automatically made a cross join. All you have to do is to select the needed columns. After selecting the columns, you can click the Execute button Execute to see the result. Here is an example:

    Join

    Practical LearningPractical Learning: Creating a Cross Join

    1. If there is a line between the tables, rght-click that line and click Remove.
      In the tables, click the check boxes of the following columns: AccountNumber (in the Customers table), CustomerName, ResidenceType, City, PreviousReadingDate, PreviousReadingNumber, NewReadingDate, NewReadingNumber, and Consumption
      Southern Energy Corporation
    2. On the Query Designer toolbar, click the Execute button Execute
    3. Access the form and change the properties of the top radio button as follows:
      (Name): rdoCrossJoin
      Text: Show all accounts associated with all metter readings
    4. Return to the Query window

    Inner Joins

    Imagine you have two tables that can be linked through one's primary key and another's foreign key. Some records in the child table may not have an entry for the foreign key column and would be marked with NULL by the database engine. When creating a query of records of the child table, if you want your list to include only records that have an entry, you can create it as inner join.

    By default, when creating a new query, if a relationship was already established between both tables, the query is made an inner join. If there was no relationship explicitly established between both tables, you would have to edit the SQL statement. Consider the following:

    Join

    Notice that, because no relationship was previously established between both tables, the join is crossed.

    To create an inner join, you have two options. You can drag the primary key from the parent table and drop it on the foreign key in the child table. Here is an example:

    Dragging a field to create a join

    Alternatively, you can edit the SQL statement manually to make it an inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, Persons.GenderID,
           Genderes.GenderID AS [Gender ID], Genderes.Gender
    FROM   Persons INNER JOIN Genderes ON Persons.GenderID = Genderes.GenderID

    After creating the join, in the Diagram section, a line would be created to join the tables. You can then execute the query to see the result. This would produce:

    Join

    We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the Gender of each Persons record, we would not need the GenderID column from the Genders table. Here is an example:

    Join

    As mentioned earlier, notice that the result includes only records that have an entry (a non-NULL entry) in the GenderID foreign key column of the Persons table.

    An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
           Genderes.Gender
    FROM Persons
    JOIN Genderes
    ON Persons.GenderID = Genderes.GenderID

    To destroy a join between two tables, if you are working in the Query window, you can right-click the line that joins the tables and click Remove. In SQL, you must modify the expressions that make up the join (the JOIN and the ON expressions).

    Practical LearningPractical Learning: Creating an Inner Join

    1. To create an inner join, from the Customers table, drag AccountNumber and drop it on the CustomerAccount field of the MetterReadings table:
       
      Southern Energy Corporation
    2. Release the mouse
    3. On the Query Designer toolbar, click the Execute button to see the result
      Southern Energy Corporation
    4. On the Query Designer toolbar, click the Execute button Execute
    5. Access the form and change the properties of the second radio button from the top and change it as follows:
      (Name): rdoInnerJoin
      AutoSize: False
      Text: Show accounts summary including customer name and consumption (int the order the metters were read)
      TextAlign: BottomLeft
    6. Return to the Query window

    Outer Joins

     

    Introduction

    Instead of showing only records that have entries in the child table, you may want your query to get all records, including those that are null. To get this result, you would create an outer join. You have three options.

    Left Outer Joins

    A left outer join produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column are marked as NULL.

    To create a left outer join, if you are working in the Query window, in the Diagram section, right-click the line that joins the tables and click the option that would select all records from the child table (in this case, that would be Select All Rows From Persons):

    Join

    Alternatively, you can replace the TypeOfJoin factor of our formula with either LEFT JOIN or LEFT OUTER JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
                 Genderes.GenderID, Genderes.Gender
    FROM Persons
    LEFT OUTER JOIN Genderes
    ON Persons.GenderID = Genderes.GenderID
    GO

    In both cases, the button in the middle of the line would be added an arrow that points to the parent table. You can then execute the query to see the result. Here is an example:

    Join

    Notice that the result includes all records of the Persons (also called the right) table and the records that don't have an entry in the GenderID column of the Persons (the right) table are marked with NULL.

    Practical LearningPractical Learning: Creating a Left Outer Join

    1. To create a left outer join, right-click the line between the tables and click the first option under Remove:
      Southern Energy Corporation
    2. On the Query Designer toolbar, click the Execute button Execute
    3. Access the form and check the properties of the top radio button as follows:
      (Name): rdoLeftOuerJoin
      Text: Show consumption for each customer (one customer per section)
    4. Return to the Query window

    Right Outer Joins

    A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genderes table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the GenderID column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

    To visually create a right outer join in the Table window, after establishing a join between both tables, if you had previously created a left outer join, you should remove it by right-clicking the line between the tables and selecting the second option under Remove. Then, you can right-click the line that joins them and click the option that would select all records from the parent table. In our example, you would click Select All Rows From Genderes.

    To create a right outer join in SQL, you can replace the TypeOfJoin factor of our formula with RIGHT JOIN or RIGHT OUTER JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
                 Genderes.GenderID, Genderes.Gender
    FROM Persons
    RIGHT OUTER JOIN Genderes
    ON Persons.GenderID = Genderes.GenderID
    GO

    In both cases, the button on the joining line between the tables would have an arrow that points to the child table. You can then run the query. Here is an example:

    Join

    Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Genderes table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next GenderID value. Also, notice that there are no NULL records in the Gender.

    Practical LearningPractical Learning: Creating a Right Outer Join

    1. Right-click the line between the tables and click the first option under Remove to de-select it
    2. To create a left outer join, right-click the line between the tables and click the second option under Remove to select it
       
      Southern Energy Corporation
    3. On the Query Designer toolbar, click the Execute button Execute
    4. Access the form and check the properties of the top radio button as follows:
      (Name): rdoRightOuterJoin
      Text: Show all accounts associated with all metter readings
      TextAlign: BottomLeft
    5. Return to the Query window

    Full Outer Joins

    A full outer join produces all records from both the parent and the child tables. If a record from one table does not have a value in the other value, the value of that record is marked as NULL.

    To visually create a full outer join, in the Table window, right-click the line between the tables and select each option under Remove so that both would be checked. To create a full outer join in SQL, replace the TypeOfJoin factor of our formula with FULL JOIN or FULL OUTER JOIN. Here is an example:

    SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
                 Genderes.GenderID, Genderes.Gender
    FROM Persons
    FULL OUTER JOIN Genderes
    ON Persons.GenderID = Genderes.GenderID
    GO

    The button on the line between the tables would now appear as a square. You can then execute the query. Here is an example:

    Join

    Just as we have involved only two tables in our joins so far, you can create a join that includes many tables.

    Practical LearningPractical Learning: Creating a Full Outer Join

    1. Right-click the line between the tables and click the first option under Remove to select it (that should select both options under the Remove menu item)
       
      Southern Energy Corporation
    2. On the Query Designer toolbar, click the Execute button Execute
    3. Access the form and check the properties of the top radio button as follows:
      (Name): rdoFullOuterJoin
      Text: Show all accounts associated with all metter readings
      TextAlign: BottomLeft
      Southern Energy Corporation
    4. Double-click the first radio button and change the file as follows:
      private void SouthernEnergyCorp_Load(object sender, EventArgs e)
      {
          // CreateDatabase();
      
          strCode = new string[]
          {
              "SELECT Customers.AccountNumber, " + Environment.NewLine + 
              "\tCustomers.CustomerName, " + Environment.NewLine + 
              "\tCustomers.ResidenceType, " + Environment.NewLine + 
              "\tCustomers.City, " + Environment.NewLine + 
              "\tMetterReadings.PreviousReadingDate, " + Environment.NewLine + 
              "\tMetterReadings.PreviousReadingNumber, " + Environment.NewLine + 
              "\tMetterReadings.NewReadingDate, " + Environment.NewLine + 
              "\tMetterReadings.NewReadingNumber, " + Environment.NewLine + 
              "\tMetterReadings.Consumption " + Environment.NewLine + 
              "FROM Customers CROSS JOIN MetterReadings;",
      
                      "SELECT Customers.AccountNumber, " + Environment.NewLine + 
                      "\tCustomers.CustomerName, " + Environment.NewLine + 
                      "\tCustomers.ResidenceType, " + Environment.NewLine + 
                      "\tCustomers.City, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.Consumption " + Environment.NewLine + 
                      "FROM Customers INNER JOIN MetterReadings " +
                      "ON Customers.AccountNumber = MetterReadings.CustomerAccount;",
      
                      "SELECT Customers.AccountNumber, " + Environment.NewLine + 
                      "\tCustomers.CustomerName, " + Environment.NewLine + 
                      "\tCustomers.ResidenceType, " + Environment.NewLine + 
                      "\tCustomers.City, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.Consumption " + Environment.NewLine + 
                      "FROM Customers LEFT OUTER JOIN MetterReadings " + Environment.NewLine + 
                      "ON Customers.AccountNumber = MetterReadings.CustomerAccount;",
      
                      "SELECT Customers.AccountNumber, " + Environment.NewLine + 
                      "\tCustomers.CustomerName, " + Environment.NewLine + 
                      "\tCustomers.ResidenceType, " + Environment.NewLine + 
                      "\tCustomers.City, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.Consumption " + Environment.NewLine + 
                      "FROM Customers RIGHT OUTER JOIN MetterReadings " + Environment.NewLine + 
                      "ON Customers.AccountNumber = MetterReadings.CustomerAccount;",
      
                      "SELECT Customers.AccountNumber, " + Environment.NewLine + 
                      "\tCustomers.CustomerName, " + Environment.NewLine + 
                      "\tCustomers.ResidenceType, " + Environment.NewLine + 
                      "\tCustomers.City, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.PreviousReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingDate, " + Environment.NewLine + 
                      "\tMetterReadings.NewReadingNumber, " + Environment.NewLine + 
                      "\tMetterReadings.Consumption " + Environment.NewLine + 
                      "FROM Customers FULL OUTER JOIN MetterReadings " + Environment.NewLine + 
                      "ON Customers.AccountNumber = MetterReadings.CustomerAccount;"
          };
      }
      
      void LoadDataGridView()
      {
          using (SqlConnection cntSEC =
              new SqlConnection("Data Source=(local);" +
                                "Database='SouthernEnergyCorp1';" +
                                "Integrated Security=Yes"))
          {
              SqlCommand cmdProperties =
                          new SqlCommand(rtbCode.Text,
                                         cntSEC);
              SqlDataAdapter sdaSEC = new SqlDataAdapter();
              DataSet dsProperties = new DataSet("StoreItems");
      
              cntSEC.Open();
      
              sdaSEC.SelectCommand = cmdProperties;
              sdaSEC.Fill(dsProperties);
      
              dgvEnergy.DataSource = dsProperties.Tables[0];
          }
      }
      
      private void rdoCrossJoin_CheckedChanged(object sender, EventArgs e)
      {
          rtbCode.Text = strCode[0];
          LoadDataGridView();
      }
    5. Return to the form and double-click the second radio button
    6. Implement the event as follows:
      private void rdoInnerJoin_CheckedChanged(object sender, EventArgs e)
      {
          rtbCode.Text = strCode[1];
          LoadDataGridView();
      }
    7. Return to the form and double-click the third radio button
    8. Implement the event as follows:
      private void rdoLeftOuerJoin_CheckedChanged(object sender, EventArgs e)
      {
          rtbCode.Text = strCode[2];
          LoadDataGridView();
      }
    9. Return to the form and double-click the fourth radio button
    10. Implement the event as follows:
      private void rdoRightOuterJoin_CheckedChanged(object sender, EventArgs e)
      {
          rtbCode.Text = strCode[3];
          LoadDataGridView();
      }
    11. Return to the form and double-click the fifth radio button
    12. Implement the event as follows:
      private void rdoFullOuterJoin_CheckedChanged(object sender, EventArgs e)
      {
          rtbCode.Text = strCode[4];
          LoadDataGridView();
      }
    13. Return to the form and double-click the Close button
    14. Implement the event as follows:
      private void btnClose_Click(object sender, EventArgs e)
      {
          Close();
      }
    15. Execute the application to see the result
    16. Click each of the radio buttons
      Southern Energy Corporation
      Southern Energy Corporation
      Southern Energy Corporation
      Southern Energy Corporation
      Southern Energy Corporation
    17. Close the form
     

    Home Copyright © 2007-2011 C#Key