ADO.NET Data Joins

	Data Joins

Introduction

When studying relationships, we reviewed techniques of making data from one table available to the records of another table. This demonstrated to reduce data duplication and mistakes. Another issue that involves the combination of tables consists of creating records from more than one table and making the result into a single list. This is the basis of data joins.

A data join is a technique of creating a list of records from more that 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:

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

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. Here is an example of such a table:

Sexes

If needed, you can then create the necessary records for the table. Here is an example:

Sexes - Records

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 are the same as the primary key of the parent table. Here is an example of a child table that would be joined to the above parent table:

Persons

Once again, if necessary, you can add the needed records to the table. Here is an example:

List of persons

Practical Learning: Introducing Joins

Start Microsoft Visual C# and create a new Windows Application named AltairRealtors7
In the Solution Explorer, right-click Form1.cs and click Rename
Type AltairRealtors.cs and press Enter

Double-click the middle of the form and implement the Load event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace AltairRealtors7
{
    public partial class AltairRealtors : Form
    {
	public AltairRealtors()
        {
	    InitializeComponent();
	}

	void CreateDatabase()
	{
	    string csAltairRealtors = "Data Source=(local);" +
				      "Database='AltairRealtors2';" +
				      "Integrated Security='SSPI';";
	    string strAltairRealtors = "";
	    SqlConnection cnnAltairRealtors = null;
	    SqlCommand cmdAltairRealtors = null;

	    using (cnnAltairRealtors =
		new SqlConnection("Data Source=(local);" +
				  "Integrated Security='SSPI';"))
	    {
		strAltairRealtors = "CREATE DATABASE AltairRealtors2;";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
		cnnAltairRealtors);

		cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

	MessageBox.Show("The AltairRealtors2 database has been created");
	    }

	    using (cnnAltairRealtors =
		new SqlConnection(csAltairRealtors))
	    {
		strAltairRealtors =
		    "CREATE TABLE PropertyTypes" +
		    "(" +
		    "PropertyTypeID int identity(1,1) NOT NULL," +
		    "PropertyType varchar(20), " +
		"CONSTRAINT PK_PropertyTypes PRIMARY KEY (PropertyTypeID));";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
						   cnnAltairRealtors);

		cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

		MessageBox.Show("The PropertyTypes table has been created");
	    }

	    using (cnnAltairRealtors = new SqlConnection(csAltairRealtors))
	    {
		strAltairRealtors =
		    "INSERT INTO PropertyTypes(PropertyType) " +
		    "VALUES('Condominium'); " +
		    "INSERT INTO PropertyTypes(PropertyType) " +
		    "VALUES('Single Family'); " +
		    "INSERT INTO PropertyTypes(PropertyType) " +
		    "VALUES('Townhouse'); " +
		    "INSERT INTO PropertyTypes(PropertyType) " +
		    "VALUES('Unknown');";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
						   cnnAltairRealtors);

		cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

MessageBox.Show("Some records have been created in the Property Types table.");
	   }

	    using (cnnAltairRealtors = new SqlConnection(csAltairRealtors))
 	    {
		strAltairRealtors =
		    "CREATE TABLE Conditions " +
		    "( " +
		    "ConditionID int identity(1,1) NOT NULL, " +
		    "Condition varchar(20), " +
		    "CONSTRAINT PK_Conditions PRIMARY KEY (ConditionID));";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
						   cnnAltairRealtors);

	 	cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

		MessageBox.Show("The Conditions table has been created");
	    }

	    using (cnnAltairRealtors = new SqlConnection(csAltairRealtors))
	    {
		strAltairRealtors =
		    "INSERT INTO Conditions(Condition) " +
		    "VALUES('Excellent'); " +
		    "INSERT INTO Conditions(Condition) " +
		    "VALUES('Good'); " +
		    "INSERT INTO Conditions(Condition) " +
		    "VALUES('Bad Shape'); " +
		    "INSERT INTO Conditions(Condition) " +
		    "VALUES('Unknown');";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
						   cnnAltairRealtors);

		cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

MessageBox.Show("Some records have been created in the Conditions table.");
	    }

	    using (cnnAltairRealtors =
		new SqlConnection("Data Source=(local);" +
				  "Database='AltairRealtors2';" +
				  "Integrated Security='SSPI';"))
	    {
		strAltairRealtors =
		    "CREATE TABLE Properties" +
		    "(" +
		    "PropertyID int identity(1,1) NOT NULL," +
		    "PropertyNumber char(6)," +
		    "Address varchar(100)," +
		    "City varchar(50)," +
		    "State char(2)," +
		    "ZIPCode varchar(12)," +
		    "PropertyTypeID varchar(40)," +
		    "ConditionID varchar(32)," +
		    "Bedrooms smallint," +
		    "Bathrooms float," +
		    "FinishedBasement bit," +
		    "IndoorGarage bit," +
		    "Stories smallint," +
		    "YearBuilt smallint," +
		    "MarketValue money, " +
		    "CONSTRAINT PK_Properties PRIMARY KEY (PropertyID));";

		cmdAltairRealtors = new SqlCommand(strAltairRealtors,
						   cnnAltairRealtors);

		cnnAltairRealtors.Open();
		cmdAltairRealtors.ExecuteNonQuery();

		MessageBox.Show("The Properties table has been created");
	    }

	    using (cnnAltairRealtors = new SqlConnection(csAltairRealtors))
	    {
		strAltairRealtors =
"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('524880', '1640 Lombardo Ave', 'Silver Spring', 'MD'," +
"'20904', 2, 2, 4, 2.5, 3, 1, 3, 1995, 495880.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('688364', '10315 North Hacht Rd', 'College Park', 'MD'," +
"'20747', 2, 1, 4, 3.5, 3, 1, 2, 2000, 620724.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, FinishedBasement," +
"Stories, MarketValue)" +
"VALUES('611464', '6366 Lolita Drive', 'Laurel', 'MD'," +
"'20707', 2, 2, 1, 2, 422625.00);" +

"INSERT INTO Properties(Address, City, PropertyTypeID," +
"Bedrooms, MarketValue)" +
"VALUES('9002 Palasko Hwy', 'Tysons Corner'," +
"1, 2, 422895.00);" +

"INSERT INTO Properties(PropertyNumber, State," +
"ZIPCode, Bedrooms, YearBuilt, MarketValue)" +
"VALUES('420115', 'DC', '20011', 2, 1982, 312555);" +

"INSERT INTO Properties(PropertyNumber, City, ZIPCode," +
"PropertyTypeID, Bedrooms, YearBuilt, MarketValue)" +
"VALUES('917203', 'Alexandria', '22024'," +
"2, 3, 1965, 345660.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"PropertyTypeID, ConditionID, Bedrooms, Bathrooms, MarketValue)" +
"VALUES('200417', '4140 Holisto Crt', 'Germantown', 'MD'," +
"1, 1, 2, 1, 215495.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('927474', '9522 Lockwood Rd', 'Chevy Chase', 'MD'," +
"'20852', 3, 3, 3, 2.5, 3, 0, 3, 1992, 415665.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('207850', '14250 Parkdoll Rd', 'Rockville', 'MD'," +
"'20854', 3, 2, 3, 2.5, 2, 1, 2, 1988, 325995.00);" +

"INSERT INTO Properties(City, PropertyTypeID, Bedrooms," +
"YearBuilt, MarketValue)" +
"VALUES('Washington', 3, 4, 1975, 366775.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"YearBuilt, MarketValue)" +
"VALUES('288540', '10340 Helmes Street #408', 'Silver Spring', 'MD'," +
"'20906', 1, 2, 1, 1, 2000, 242775.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('247472', '1008 Coppen Street', 'Silver Spring', 'MD'," +
"'20906', 2, 1,3, 3, 3, 1, 3, 1996, 625450.00);" +

"INSERT INTO Properties(City, ZIPCode, PropertyTypeID, " +
"Stories, YearBuilt, MarketValue)" +
"VALUES('Chevy Chase', '20956', 2, 3, 2001, 525450.00);" +

"INSERT INTO Properties(Address, City, State," +
"PropertyTypeID, ConditionID, Bedrooms, MarketValue)" +
"VALUES('686 Herod Ave #D04', 'Takoma Park', 'MD'," +
"1, 1, 2, 360885.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('297446', '14005 Sniders Blvd', 'Laurel', 'MD'," +
"'20707', 3, 4," +
"4, 1.5, 3, 1, 2, 2002, 412885.00);" +

"INSERT INTO Properties(City, ZIPCode, ConditionID, Bedrooms," +
"Stories, YearBuilt)" +
"VALUES('Silver Spring', '20905', 2," +
"4, 2, 1965);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('924792', '680 Prushia Rd', 'Washington', 'DC'," +
"'20008', 2, 2, 5, 3.5, 3, 0, 3, 2000, 555885.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('294796', '14688 Parrison Street', 'College Park', 'MD'," +
"'20742', 2, 1, 5, 2.5, 2, 1, 2, 1995, 485995.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"YearBuilt, MarketValue)" +
"VALUES('811155', '10340 Helmes Street #1012', 'Silver Spring'," +
"'MD', '20906', 1, 2, 1, 1, 2000, 252775.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('447597', '4201 Vilamar Ave', 'Hyattsville', 'MD'," +
"'20782', 3, 1, 3, 2, 2, 1, 3, 1992, 365880.00);" +

"INSERT INTO Properties(Address, ZIPCode, Bathrooms)" +
"VALUES('1622 Rombard Str', 20904, 2.5);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('297415', '980 Phorwick Street', 'Washington', 'DC'," +
"'20004', 2, 2, 4, 3.5, 3, 3, 1, 2004, 735475.00);" +

"INSERT INTO Properties(PropertyNumber, Address, City, State," +
"ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms," +
"FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" +
"VALUES('475974', '9015 Marvin Crow Ave', 'Gaithersburg', 'MD'," +
"'20872', 2, 4, 4, 2.5, 3, 1, 1, 1965, 615775.00);";

cmdAltairRealtors =
new SqlCommand(strAltairRealtors,
cnnAltairRealtors);

cnnAltairRealtors.Open();
cmdAltairRealtors.ExecuteNonQuery();

MessageBox.Show("Some records have been created in the Properties table.");
	}
    }

	private void AltairRealtors_Load(object s}er, EventArgs e)
	{
	    CreateDatabase();
	}
    }
}

Execute the application to actually create the database and its tables
Close the form and return to your programming environment
To create a data source, on the main menu, click Data -> Add New Data Source...
In the first page of the wizard, make sure Database is selected and click Next
In the combo box
1. If you see a AltairRealtors2, select it
2. If you do not have AltairRealtors2, click New Connection... In the Server combo box, select the server or type (local). In the Select Or Enter A Database Name combo box, select AltairRealtors2. Click Test Connection. Click OK twice. In the Data Source Configuration Wizard, make sure the new connection is selected and click Next. Change the Connection String to csAltairRealtors and click Next. Expand the Tables node and click the check box of Properties. Change the DataSet Name to dsAltairRealtors
Click Finish
In the Data Sources window, drag the Properties node and drop it on the form
Under the form, click propertiesBindingNavigator and press Delete
Using the Properties window, change the names of the other two objects as follows:

Object Name

propertiesBindingSource bsProperties

propertiesTableAdapter taProperties

Design the form as follows:

Control	Text	Name	Other Properties
DataGridView		dgvProperties	Anchor: Top, Bottom, Left, Right
Label	Show:		Anchor: Bottom, Right
ComboBox	All		Anchor: Bottom, Right Items: All Townhouses Only Condominiums Only Single Families Only
Button	Close	btnClose	Anchor: Bottom, Right

Double-click the Close button and implement its even as follows:

private void btnClose_Click(object s}er, EventArgs e)
{
     Close();
}

Save the form

Join Creation

Equipped with the necessary tables and their columns, you can create the join. To do this in the Microsoft SQL Server Management Studio, you can right-click one of the tables involved and click Open Table. This would display the Table window. You should then display the Diagram and the SQL sections. Because the foundation of a join lies on at least two tables, you should add one. To do this

On the main menu, you can click Query Designer -> Add Table...
On the Query Designer toolbar, you can click the Add Table button
You can right-click the Diagram section of the window and click Add table...

Any of these actions would display the Add Table dialog box. To select a table:

You can click the table's name and click Add
You can double-click a table

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

Remember that 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.

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 Sexes
ON Persons.SexID = Sexes.SexID

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 Sexes
ON Persons.SexID = Sexes.SexID

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 Sexes
ON Persons.SexID = Sexes.SexID

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, Sex
FROM Persons
TypeOfJoin Sexes
ON Persons.SexID = Sexes.SexID

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.SexID,
             Sexes.SexID, Sex
FROM Persons
TypeOfJoin Sexes
ON Persons.SexID = Sexes.SexID

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.SexID,
             Sexes.SexID, Sexes.Sex
FROM Persons
TypeOfJoin Sexes
ON Persons.SexID = Sexes.SexID

Practical Learning: Preparing a Join

In the Data Source window, right-click dsAltair and click Edit Dataset with Designer
In the designer, right-click the title bar of the Properties table and click Configure
In the TableAdapter Configuation Wizard, click Query Builder...

Right-click an (any) area of the Query Builder window and click Add Table

In the Add Table dialog box, double-click PropertyTypes

Click Close

Uncheck all the columns

Published on Sunday 10 February 2008


Home	Copyright © 2008-2010 FunctionX, Inc.	Next