Home

Microsoft Visual C#: Topics on Data Relationships

   

Options on Joins

 

Joining More Than Two Tables

A join statements can involve only two tables. Actually, you can use more than that. The basic formula to join three tables is:

SELECT WhatColumn(s) FROM FirstTable

FirstJoinType SecondTable ON Condition1
SecondJoinType ThirdTable ON Condition2

You start the expression by joining the first to the second table, which means that both tables should share a column in a primary key-foreign key type of relationship. In the same way, you can create the second join. of course, the second and the third table should have a common column. In most cases, there should be a column that all three tables share. Most of the time, the relationship starts with a primary column from the first table. That column is then represented as a foreign key in the other two tables.

Practical LearningPractical Learning: Introducing Join Options

  1. Start Microsoft Visual Studio
  2. To start a new applicaiton, on the main menu, click File -> New Project...
  3. In the middle list, click Windows Forms Application and set the Name to MonsonUniversity2
  4. Click OK
  5. In the Solution Explorer, right-click Form1.cs and click Rename
  6. Type MonsonUniversity.cs and press Enter
  7. 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 MonsonUniversity2
    {
        public partial class MonsonUniversity : Form
        {
            public MonsonUniversity()
            {
                InitializeComponent();
            }
    
            internal void CreateDatabase()
            {
                SqlConnection cntMonsonUniversity = null;
                SqlCommand    cmdMonsonUniversity = null;
    
                using (cntMonsonUniversity = new SqlConnection("Data Source=(local);" +
                                                               "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("IF EXISTS (" +
                                       "SELECT name " +
                                       "FROM sys.databases " +
                                       "WHERE name = N'MonsonUniversity2')" +
                                       "DROP DATABASE MonsonUniversity2; " +
                                       "CREATE DATABASE MonsonUniversity2;", cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The MonsonUniversity2 database has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE SCHEMA Academics;", cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("A new schema named Academics has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using(cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE SCHEMA Admissions;", cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("A new schema named Admissions has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using(cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE SCHEMA Administration;", cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("A new schema named Administration has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Administration.Departments( " +
                                       "DepartmentCode nchar(4) not null, " +
                                       "Name nvarchar(50) not null, " +
                                       "Constraint PK_Departments Primary Key(DepartmentCode));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The Departments table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Administration.Employees(" +
                                       "EmployeeNumber nchar(8) not null," +
                                       "FirstName nvarchar(20)," +
                                       "MiddleName nvarchar(20)," +
                                       "LastName nvarchar(20) not null," +
                                       "DepartmentCode nchar(4)" +
                                       "    Constraint FK_Departments " +
                                       "    References Administration.Departments(DepartmentCode)," +
                                       "Title nvarchar(50)," +
                                       "Constraint PK_Employees Primary Key(EmployeeNumber));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The Employees table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.UndergraduateMajors(" +
                                       "MajorID int identity(1001, 1) not null," +
                                       "Major nvarchar(60)," +
                                       "Dean nchar(8) not null" +
                                       "    Constraint FK_Deans " +
                                       "    References Administration.Employees(EmployeeNumber)," +
                                       "Constraint PK_UndergraduateMajors Primary Key(MajorID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The UndergraduateMajors table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.Minors(" +
                                       "MinorID int identity(1001, 1) not null," +
                                       "Minor nvarchar(60)," +
                                       "Constraint PK_Minors Primary Key(MinorID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The Minors table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.GraduateMajors(" +
                                       "MajorID int identity(5001, 1) not null," +
                                       "Major nvarchar(60)," +
                                       "Dean nchar(8)," +
                                       "Constraint PK_GraduateMajors Primary Key(MajorID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The GraduateMajors table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.Certificates(" +
                                       "CertificateID int identity(1001, 1) not null," +
                                       "Certificate nvarchar(60)," +
                                       "Constraint PK_Certificates Primary Key(CertificateID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The Certificates table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Admissions.Semesters(" +
                                       "SemesterID int identity(10001, 1) not null, " +
                                       "Semester nvarchar(40), " +
                                       "Constraint PK_Semesters Primary Key(SemesterID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The Semesters table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.UndergraduateCourses(" +
                                       "CourseCode nchar(8) not null, " +
                                       "CourseName nvarchar(100), " +
                                       "Credits smallint not null, " +
                                       "CourseDescription nvarchar(max), " +
                                       "Prerequisite1  nchar(8) " +
                                       "    Constraint FK_Prerequisites1  " +
                                       "    References Academics.UndergraduateCourses(CourseCode), " +
                                       "Prerequisite2  nchar(8) " +
                                       "	Constraint FK_Prerequisites2 " +
                                       "    References Academics.UndergraduateCourses(CourseCode), " +
                                       "Prerequisite3  nchar(8) " +
                                       "	Constraint FK_Prerequisites3 " +
                                       "    References Academics.UndergraduateCourses(CourseCode), " +
                                       "Constraint PK_UndergraduateCourses Primary Key(CourseCode));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The UndergraduateCourses table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Academics.GraduateCourses(" +
                                       "CourseCode nchar(8) not null, " +
                                       "CourseName nvarchar(100), " +
                                       "Credits smallint not null, " +
                                       "CourseDescription nvarchar(max), " +
                                       "Prerequisite1 nchar(8), " +
                                       "Constraint PK_GraduateCourses Primary Key(CourseCode));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The GraduateCourses table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Admissions.UndergraduateStudents(" +
                                       "StudentNumber nchar(8) not null, " +
                                       "FirstName nvarchar(20), " +
                                       "MiddleName nvarchar(20), " +
                                       "LastName nvarchar(20), " +
                                       "MajorID int " +
                                       "    Constraint FK_StudentsMajors " +
                                       "    References Academics.UndergraduateMajors(MajorID), " +
                                       "MinorID int " +
                                       "    Constraint FK_StudentsMinors " +
                                       "    References Academics.Minors(MinorID), " +
                                       "Constraint PK_UndergraduateStudents Primary Key(StudentNumber));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The UndergraduateStudents table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Admissions.GraduateStudents(" +
                                       "StudentNumber nchar(8) not null, " +
                                       "FirstName nvarchar(20), " +
                                       "MiddleName nvarchar(20), " +
                                       "LastName nvarchar(20), " +
                                       "MajorID int " +
                                       "    Constraint FK_GraduateMajors " +
                                       "    References Academics.GraduateMajors(MajorID), " +
                                       "Constraint PK_GraduateStudents Primary Key(StudentNumber));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The GraduateStudents table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                
                using (cntMonsonUniversity =
                         new SqlConnection("Data Source=(local);" +
                                           "Database='MonsonUniversity2';" +
                                           "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Admissions.UndergraduateRegistrations(" +
                                       "RegistrationID int identity(10000001, 1) not null, " +
                                       "StudentNumber nchar(8) " +
                                       "    Constraint FK_UndergraduateRegistrations " +
                                       "    References Admissions.UndergraduateStudents(StudentNumber), " +
                                       "SemesterID int " +
                                       "    Constraint FK_UndergraduateSemesters " +
                                       "    References Admissions.Semesters(SemesterID), " +
                                       "CourseCode nchar(8) " +
                                       "    Constraint FK_UndergraduateCourses " +
                                       "    References Academics.UndergraduateCourses(CourseCode), " +
                                       "Constraint PK_UndergraduateRegistrations Primary Key(RegistrationID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The UndergraduateRegistrations table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                         new SqlConnection("Data Source=(local);" +
                                           "Database='MonsonUniversity2';" +
                                           "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("CREATE TABLE Admissions.GraduateRegistrations(" +
                                       "RegistrationID int identity(20000001, 1) not null," +
                                       "StudentNumber nchar(8) " +
                                       "    Constraint FK_GraduateRegistrations " +
                                       "    References Admissions.GraduateStudents(StudentNumber)," +
                                       "SemesterID int not null " +
                                       "    Constraint FK_GraduateSemesters " +
                                       "    References Admissions.Semesters(SemesterID)," +
                                       "CourseCode nchar(8) " +
                                       "    Constraint FK_GraduateCourses " +
                                       "    References Academics.GraduateCourses(CourseCode)," +
                                       "Constraint PK_GraduateRegistrations Primary Key(RegistrationID));",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("The GraduateRegistrations table has been created.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("INSERT Administration.Departments " +
    "VALUES(N'HRMN', N'Human Resources - Personnel'), " +
    "      (N'ADMS', N'Admissions - Students Affairs'), " +
    "      (N'FINA', N'Finances - Accounting'), " +
    "      (N'ITEC', N'Information Technology'); " +
    "INSERT INTO Administration.Employees " +
    "VALUES(N'27922702', N'Donald', N'Henry', N'Leighton', N'HRMN', N'President'), " +
    "      (N'50249441', N'Anthony', N'Robert', N'Parrish', N'HRMN', N'Provost'), " +
    "      (N'19302484', N'Jeannette', N'Veronica', N'Holms', N'HRMN', N'Vice President for Government Relations'), " +
    "      (N'20485052', N'Simon', NULL, N'Lew', N'FINA', N'Vice-President and Chief Financial Officer'), " +
    "      (N'27559475', N'Kellie', N'Joan', N'Tierney', N'ADMS', N'Vice-President and Dean of Undergraduate Studies'), " +
    "      (N'38188248', N'Charles', NULL, N'McAhan', N'ITEC', N'Vice-President and Chief Technology Officer'), " +
    "      (N'90804792', N'Ann', N'Laura', N'Tenney', N'FINA', N'Cashier'), " +
    "      (N'79700429', N'Judith', N'Suzie', N'London', N'ADMS', N'Dean of Business Studies'), " +
    "      (N'16113841', N'Laura', N'Fannie', N'Joansen', N'ADMS', N'Dean of Litterary Studies'), " +
    "      (N'11395822', N'Richard', N'Matthew', N'Little', N'ITEC', N'IT Support'), " +
    "      (N'30840724', N'Fatima', N'Georgia', N'Williams', N'FINA', N'Accountant'), " +
    "      (N'16173974', N'Veronica', N'Bethanie', N'Pitts', N'ADMS', N'Dean of Commercial and Financial Studies'), " +
    "      (N'97417315', N'Eleanor', N'Virginia', N'Pearlman', N'ITEC', N'Webmaster'), " +
    "      (N'20000582', N'Catherine', NULL, N'Lehmann', N'ADMS', N'Intern'), " +
    "      (N'24759135', NULL, NULL, N'Hawthorne', N'ADMS', N'Dean of History and Geography'), " +
    "      (N'64020757', N'Kimberly', N'Carlette', N'Edelman', N'ADMS', N'Dean of Socioly and Psychology'), " +
    "      (N'94273941', N'Martin', N'Andrew', N'Schweinstenman', N'FINA', N'Cashier'), " +
    "      (N'79384795', N'Seraphine', N'Angie', N'Roeper', N'ADMS', N'Dean of Mathematical Studies'), " +
    "      (N'92748695', N'Robert', N'John', N'Preston', N'ADMS', N'Dean of Computer Studies'); " +
    "INSERT INTO Academics.UndergraduateMajors(Major, Dean) " +
    "VALUES(N'Accounting', N'79384795'),(N'Business Administration', N'79384795'), " +
    "      (N'English', N'16113841'),(N'History', N'24759135'),(N'Finance', N'16173974'), " +
    "      (N'Computer Information Technology', N'92748695'),(N'Computer Science', N'92748695'), " +
    "      (N'Marketing', N'16173974'), (N'Criminal Justice', N'16113841'), " +
    "      (N'Information Systems Management', N'92748695'),(N'Psychology', N'64020757'); " +
    "INSERT INTO Academics.GraduateMajors(Major, Dean) " +
    "VALUES(N'Accounting and Financial Management', N'79384795'), " +
    "      (N'Biotechnology Studies: Bioinformatics', N'92748695'), " +
    "      (N'Management: Human Resource Management', N'79384795'), " +
    "      (N'Information Technology: Database Systems Technology', N'92748695'), " +
    "      (N'Management: International Financial Management', N'79384795'); " +
    "INSERT INTO Academics.Certificates(Certificate) " +
    "VALUES(N'English as a Foreign Language'), (N'Introductory Accounting'), " +
    "      (N'Information Assurance'), (N'Teaching English as a Foreign Language'); " +
    "INSERT INTO Academics.Minors(Minor) " +
    "VALUES(N'Accounting'),(N'African American Studies'),(N'Art History'),(N'English'), " +
    "      (N'Business Administration'),(N'Computing'),(N'Criminal Justice'),(N'Forensics'), " +
    "      (N'Economics'),(N'Finance'),(N'Mathematical Sciences'),(N'Marketing'),(N'Philosophy'), " +
    "      (N'Political Science'),(N'Psychology'),(N'Sociology'),(N'Speech Communication'), " +
    "      (N'Women''s Studies'); " +
    "INSERT INTO Admissions.Semesters(Semester) " +
    "VALUES(N'FALL 2010'),(N'SUMMER 2010'),(N'SPRING 2010'), " +
    "      (N'FALL 2011'),(N'SUMMER 2011'),(N'SPRING 2011'), " +
    "      (N'FALL 2012'),(N'SUMMER 2012'),(N'SPRING 2012'); " +
    "INSERT INTO Academics.UndergraduateCourses " +
    "       (CourseCode, CourseName, Credits, Prerequisite1, Prerequisite2, Prerequisite3) " +
    "VALUES(N'BMGT 110', N'Introduction to Business and Management', 3, NULL, NULL, NULL), " +
    "      (N'WRTG 101', N'Introduction to Writing', 3, NULL, NULL, NULL), " +
    "      (N'ACCT 220', N'Principles of Accounting I', 3, NULL, NULL, NULL), " +
    "      (N'ACCT 221', N'Principles of Accounting II', 3, N'BMGT 110', N'ACCT 220', NULL), " +
    "      (N'ACCT 310', N'Intermediate Accounting I', 3, N'ACCT 221', NULL, NULL), " +
    "      (N'ACCT 311', N'Intermediate Accounting II', 3, N'ACCT 310', NULL, NULL), " +
    "      (N'ACCT 320', N'Fraud Detection and Deterrence', 3, NULL, NULL, NULL), " +
    "      (N'BEHS 220', N'Diversity Awareness', 3, NULL, NULL, NULL), " +
    "      (N'BEHS 365', N'Individuals, Society and Environmental Sustainability', 3, NULL, NULL, NULL), " +
    "      (N'BMGT 304', N'Managing E-Commerce in Organizations', 3, NULL, NULL, NULL), " +
    "      (N'BMGT 312', N'Women in Business', 3, NULL, NULL, NULL), " +
    "      (N'CMIS 102', N'Introduction to Problem Solving and Algorithm Design', 3, NULL, NULL, NULL), " +
    "      (N'CMIS 170', N'Introduction to XML', 3, N'CMIS 102', NULL, NULL), " +
    "      (N'CMIS 320', N'Relational Databases', 3, N'CMIS 102', NULL, NULL), " +
    "      (N'CMIS 420', N'Advanced Relational Databases', 3, N'CMIS 320', NULL, NULL), " +
    "      (N'CMST 306', N'Introduction to Visual Basic Programming', 3, N'CMIS 102', NULL, NULL), " +
    "      (N'CMST 385', N'Internet and Web Design', 3, N'CMIS 102', NULL, NULL), " +
    "      (N'ENGL 240', N'Introduction to Fiction, Poetry, and Drama', 3, N'WRTG 101', NULL, NULL), " +
    "      (N'ENGL 454', N'Modern World Drama', 3, N'WRTG 101', NULL, NULL), " +
    "      (N'HIST 104', N'Introduction to Archaeology', 3, NULL, NULL, NULL), " +
    "      (N'HIST 115', N'World History I', 3, NULL, NULL, NULL), " +
    "      (N'HIST 116', N'World History II', 3, NULL, NULL, NULL), " +
    "      (N'PSYC 100', N'Introduction to Psychology', 3, NULL, NULL, NULL), " +
    "      (N'PSYC 306', N'Psychology of Happiness', 1, NULL, NULL, NULL), " +
    "      (N'PSYC 307', N'Parapsychology', 1, NULL, NULL, NULL), " +
    "      (N'PSYC 308', N'Introduction to Black Psychology', 1, NULL, NULL, NULL), " +
    "      (N'WRTG 288', N'Standard English Grammar', 3, N'WRTG 101', NULL, NULL), " +
    "      (N'WRTG 388', N'Advanced Grammar and Style', 3, N'WRTG 101', NULL, NULL), " +
    "      (N'WRTG 394', N'Advanced Business Writing', 3, N'WRTG 101', NULL, NULL); " +
    "INSERT INTO Academics.GraduateCourses(CourseCode, CourseName, Credits) " +
    "VALUES(N'ACCT 608', N'Fraud Examination and Accounting Ethics', 3), " +
    "      (N'ACCT 610', N'Financial Accounting', 3), " +
    "      (N'BIOT 630', N'Introduction to Bioinformatics', 3), " +
    "      (N'BIOT 645', N'Bioprocessing and the Business of Biotechnology', 3), " +
    "      (N'MGMT 610', N'Organizational Theory', 3), " +
    "      (N'MGMT 615', N'Intercultural Communication and Leadership', 3), " +
    "      (N'MGMT 640', N'Financial Decision Making for Managers', 3), " +
    "      (N'MGMT 650', N'Statistics for Managerial Decision Making', 3), " +
    "      (N'IMAN 615', N'Strategic Investment and Partnering', 3), " +
    "      (N'IMAN 625', N'International Trade and Economic Policy', 3), " +
    "      (N'FINM 610', N'Financial Management in Organizations', 3), " +
    "      (N'FINM 620', N'Long-term Financial Management', 3), " +
    "      (N'FINM 640', N'Multinational Financial Management', 3), " +
    "      (N'FINM 660', N'Strategic Financial Management', 3); " +
    "INSERT INTO Admissions.UndergraduateStudents " +
    "VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', 1003, 1003), " +
    "      (N'24795711', N'Roger', N'Dermot', N'Baker', 1005, 1002), " +
    "      (N'18073572', N'Patrick', NULL, N'Wisne', 1001, 1004), " +
    "      (N'97394285', N'Jessica', N'Danielle', N'Shepard', 1007, 1001), " +
    "      (N'94708257', N'Christopher', N'Sheldon', N'Jones', 1002, 1005), " +
    "      (N'48009520', N'Diane', NULL, N'Rossi', 1006, 1009), " +
    "      (N'29480759', N'Maxwell', N'Peter', N'Carlson', 1007, 1007), " +
    "      (N'72938479', N'Marc', N'Kenny', N'Dickson', 1009, 1005), " +
    "      (N'61824668', N'Stephen', N'David', N'Kramer', 1006, 1002), " +
    "      (N'27582647', N'Kimberly', N'Julie', N'Wise', 1008, 1013), " +
    "      (N'92847957', N'Emmanuel', NULL, N'Orenstein', 1007, 1001), " +
    "      (N'24928472', N'Albert', N'Kevin', N'Thorne', 1002, 1006), " +
    "      (N'27114857', N'Michael', N'Alexander', N'Horns', 1001, 1005), " +
    "      (N'71513159', N'Berthe', N'Henriette', N'Essimbi', 1003, 1001), " +
    "      (N'28374957', N'Billie', N'Judith', N'Cannon', 1006, 1008), " +
    "      (N'82580947', N'Steve', N'Bruce', N'Maxwell', 1002, 1004), " +
    "      (N'20409220', N'Jasmine', NULL, N'Campino', 1010, 1005), " +
    "      (N'92584668', N'Jeoseph', N'David', N'Callahan', 1007, 1009), " +
    "      (N'79272413', N'Steve', N'Alan', N'Philbrick', 1011, 1015), " +
    "      (N'20488400', N'Joseph', NULL, N'Beal', 1004, 1006), " +
    "      (N'20204862', N'James', NULL, N'Kennan', 1006, 1010); " +
    "INSERT INTO Admissions.GraduateStudents " +
    "VALUES(N'24795711', N'Roger', N'Dermot', N'Baker', 5001), " +
    "      (N'37495884', N'Daniel', N'Joseph', N'Wiser', 5003), " +
    "      (N'31741957', N'Joel', N'Alexander', N'Elliott', 5005), " +
    "      (N'82475364', N'Heidy', N'Judith', N'Cooke', 5002), " +
    "      (N'92084157', N'Daniella', N'Helen', N'Politanoff', 5003), " +
    "      (N'97013268', N'Lucy', N'Andrea', N'Harding', 5002), " +
    "      (N'20947085', N'Linette', N'Jeanne', N'Robin', 5005), " +
    "      (N'48009520', N'Diane', NULL, N'Rossi', 5004), " +
    "      (N'71513159', N'Berthe', N'Henriette', N'Essimbi', 5003), " +
    "      (N'97394285', N'Jessica', N'Danielle', N'Shepard', 5002), " +
    "      (N'20946681', N'Becky', NULL, N'Wilkopf', 5005), " +
    "      (N'61824668', N'Stephen', N'David', N'Kramer', 5004);",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("Records have been added to tables.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using( cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes") )
                {
                    cmdMonsonUniversity =
                        new SqlCommand("INSERT INTO Admissions.UndergraduateRegistrations" +
                                       "(SemesterID, StudentNumber, CourseCode) " +
                                       "VALUES(10001, N'24795711', N'CMIS 102'), (10003, N'94708257', N'ACCT 220')," +
                                       "      (10001, N'20409220', N'BMGT 312'), (10001, N'71513159', N'PSYC 306')," +
                                       "      (10002, N'94708257', N'BEHS 220'), (10002, N'88130480', N'CMIS 170')," +
                                       "      (10004, N'29480759', N'CMIS 170'), (10003, N'82580947', N'ENGL 240')," +
                                       "      (10001, N'71513159', N'HIST 104'), (10001, N'20409220', N'CMIS 320')," +
                                       "      (10002, N'94708257', N'CMIS 320'), (10001, N'61824668', N'BEHS 220')," +
                                       "      (10004, N'94708257', N'WRTG 288'), (10002, N'71513159', N'CMST 306')," +
                                       "      (10001, N'94708257', N'CMIS 420'), (10004, N'29480759', N'WRTG 388')," +
                                       "      (10002, N'20409220', N'CMST 306'), (10002, N'71513159', N'CMST 306')," +
                                       "      (10003, N'82580947', N'CMIS 420'), (10002, N'24795711', N'BEHS 220')," +
                                       "      (10002, N'61824668', N'CMST 306'), (10004, N'94708257', N'ACCT 220');",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("A few undergraduate students have been registered for the courses.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (cntMonsonUniversity =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='MonsonUniversity2';" +
                                      "Integrated Security=Yes"))
                {
                    cmdMonsonUniversity =
                        new SqlCommand("INSERT INTO Admissions.GraduateRegistrations" +
                                       "(StudentNumber, SemesterID, CourseCode) " +
                                       "VALUES(N'24795711', 10002, N'ACCT 608'), (N'82475364', 10001, N'BIOT 630')," +
                                       "      (N'31741957', 10001, N'FINM 610'), (N'82475364', 10001, N'BIOT 645')," +
                                       "      (N'37495884', 10001, N'MGMT 610'), (N'31741957', 10001, N'FINM 640')," +
                                       "      (N'92084157', 10001, N'MGMT 610'), (N'24795711', 10002, N'ACCT 610')," +
                                       "      (N'31741957', 10002, N'FINM 620'), (N'37495884', 10001, N'MGMT 615')," +
                                       "      (N'92084157', 10001, N'MGMT 615'), (N'31741957', 10002, N'FINM 660')," +
                                       "      (N'97013268', 10001, N'BIOT 630'), (N'97013268', 10001, N'BIOT 645');",
                                       cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    cmdMonsonUniversity.ExecuteNonQuery();
    
                    MessageBox.Show("A few graduate students have been registered for the courses.",
                                    "Monson University",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
    
            private void MonsonUniversity_Load(object sender, EventArgs e)
            {
                CreateDatabase();
            }
        }
    }
  8. Press Ctrl + F5 to execute
  9. Click OK on each message box
  10. Close the form and return to your programming environment
  11. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  12. Set the name to UndergraduateMajor and click Add
  13. Design the form as follows:
     
    Monson University - New Undergraduate Major
    Control Text Name Other Properties
    Label Label Major:    
    TextBox TextBox   txtMajor Modifiers: Public
    Label Label Dean:    
    ComboBox ComboBox   cbxDeans Modifiers: Public
    Button Button OK btnOK DialogResult: OK
    Button Button Cancel btnCancel DialogResult: Cancel
    Form Property Value
    FormBorderStyle FixedDialog
    Text Make Editor
    StartPosition CenterScreen
    AcceptButton btnOK
    CancelButton btnCancel
    MaximizeBox False
    MinimizeBox False
    ShowInTaskbar False
  14. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  15. Set the name to UndergraduateMajors and click Add
  16. Design the form as follows:
     
    Monson University - Undergraduate Majors
    Control Text Name
    DataGridView DataGridView   dgvMajors
    Button Button New Undergraduate Major... btnClose
    Button Button Close btnClose
  17. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  18. Set the name to Employees and click Add
  19. Design the form as follows:
     
    Monson University - Employees
    Control Text Name
    DataGridView DataGridView   dgvEmployees
    Button Button Close btnClose
  20. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  21. Set the name to UndergraduateStudents and click Add
  22. Design the form as follows:
     
    Monson University - Students
    Control Text Name
    DataGridView DataGridView   dgvStudents
    Button Button Close btnClose
  23. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  24. Set the name to UndergraduateRegistrations and click Add
  25. Design the form as follows:
     
    Monson University - Undergraduate Registrations
    Control Text Name
    DataGridView DataGridView   dgvRegistrations
    Button Button Close btnClose
  26. Design the form as follows:
     
    Monson University
    Control Text Name
    Button Button Undergraduate Students... btnUndergraduateStudents
    Button Button Graduate Students... btnGraduateStudents
    Button Button Undergraduate Registrations... btnUndergraduateRegistrations
    Button Button Graduate Registrations... btnGraduateRegistrations
    Button Button Undergraduate Majors... btnUndergraduateMajors
    Button Button Graduate Majors... btnGraduateMajors
    Button Button Employees... btnEmployees
    Button Button Close btnClose
  27. Double-click the Undergraduate Students button and implement its event as follows:
    private void btnUndergraduateStudents_Click(object sender, EventArgs e)
    {
        UndergraduateStudents us = new UndergraduateStudents();
        us.ShowDialog();
    }
  28. Return to the form
  29. Double-click the Undergraduate Registrations button and implement its event as follows:
    private void btnUndergraduateRegistrations_Click(object sender, EventArgs e)
    {
        UndergraduateRegistrations ur = new UndergraduateRegistrations();
        ur.ShowDialog();
    }
  30. Return to the form
  31. Double-click the Undergraduate Majors button and implement its event as follows:
    private void btnUndergraduateMajors_Click(object sender, EventArgs e)
    {
        UndergraduateMajors um = new UndergraduateMajors();
        um.ShowDialog();
    }
  32. Return to the form
  33. Double-click the Employees button and implement its event as follows:
    private void btnEmployees_Click(object sender, EventArgs e)
    {
        Employees empls = new Employees();
        empls.ShowDialog();
    }
  34. Return to the form
  35. Double-click the Close button and implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }

Joining the Tables

We learned already how to join the tables, both visually and programmatically. when it comes to joining tables, you don't have to memorize the formulas and combinations of creating joins. As you know already, Microsoft Visually Studio can create the SQL statement for you. You can then simply copy it and paste it in your code.

Practical LearningPractical Learning: Joining the Tables

  1. In the Server Explorer, right-click Data Connections and click Add Connection...
  2. In the Server Name combo box, type (local)
  3. In the Select Or Enter A Database Name combo box, select MonsonUniversity2
  4. Click OK
  5. In the Server Explorer, right-click MonsonUniversity2.dbo and click New Query
  6. In the Add Table dialog box, double-click UndergraduateMajors (Academics) and Employees (Administration)
  7. Click Close
  8. Click the Major check box
  9. Click the box under Major in the Criteria section
  10. Type LastName + ', ' + FirstName and press Tab
  11. Type Dean
  12. Click the check box of Title
  13. Select the code in the SQL section and press Ctrl + C
     
    Joining the Tables
  14. Display the Undergraduate Majors form
  15. Double-click an unoccupied area of the form and implement its event 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 MonsonUniversity2
    {
        public partial class UndergraduateMajors : Form
        {
            public UndergraduateMajors()
            {
                InitializeComponent();
            }
    
            void ShowUndergraduateMajors()
            {
                using (SqlConnection cntMonsonUniversity =
                            new SqlConnection("Data Source=(local);" +
                                              "Database='MonsonUniversity2';" +
                                              "Integrated Security=Yes"))
                {
                    SqlCommand cmdMonsonUniversity =
                        new SqlCommand("SELECT Academics.UndergraduateMajors.Major, " +
                            "Administration.Employees.LastName + N', ' + Administration.Employees.FirstName AS Dean, " +
                            "Administration.Employees.Title " +
                            "FROM Academics.UndergraduateMajors INNER JOIN Administration.Employees " +
                            "ON Academics.UndergraduateMajors.Dean = Administration.Employees.EmployeeNumber;",
                                   cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    SqlDataAdapter sdaMonsonUniversity = new SqlDataAdapter(cmdMonsonUniversity);
                    DataSet dsMonsonUniversity = new DataSet("RegistrationsSet");
    
                    sdaMonsonUniversity.Fill(dsMonsonUniversity);
                    dgvMajors.DataSource = dsMonsonUniversity.Tables[0];
                }
            }
    
            private void UndergraduateMajors_Load(object sender, EventArgs e)
            {
                ShowUndergraduateMajors();
            }
        }
    }
  16. Return to the form and double-click the New Undergraduate Major button
  17. Implement its event as follows:
    private void btnNewMajor_Click(object sender, EventArgs e)
    {
        UndergraduateMajor um = new UndergraduateMajor();
    
        // Fill the combo box with the semesters
        using (SqlConnection cntMonsonUniversity =
            new SqlConnection("Data Source=(local);" +
                              "Database='MonsonUniversity2';" +
                              "Integrated Security=Yes"))
        {
            SqlCommand cmdEmployees =
                new SqlCommand("SELECT EmployeeNumber + N': ' + LastName + N', ' + FirstName + N' - ' + Title " +
                               "FROM Administration.Employees " +
                               "ORDER BY LastName;",
                               cntMonsonUniversity);
    
            cntMonsonUniversity.Open();
            SqlDataReader sdrEmployees = cmdEmployees.ExecuteReader();
    
            while(sdrEmployees.Read())
            {
                if( !string.IsNullOrEmpty(sdrEmployees[0].ToString()) )
                    um.cbxDeans.Items.Add(sdrEmployees[0].ToString());
            }
        }
    
        if (um.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            using (SqlConnection cntMonsonUniversity =
                new SqlConnection("Data Source=(local);" +
                                  "Database='MonsonUniversity2';" +
                                  "Integrated Security=Yes"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("INSERT INTO Academics.UndergraduateMajors(" +
                                   "Major, Dean) " +
                                   "VALUES('" + um.txtMajor.Text + "', '" +
                                   um.cbxDeans.Text.Substring(0, 8) + "');",
                                   cntMonsonUniversity);
                cntMonsonUniversity.Open();
                cmdEmployees.ExecuteNonQuery();
    
                MessageBox.Show("The major has been created.",
                                "Monson University",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    
        ShowUndergraduateMajors();
    }
  18. Return to the form and double-click the Close button
  19. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  20. Display the Query window
  21. In the Diagram section, right-click each table and click Remove
  22. Right-click the Diagram section and click Add Table...
  23. In the Add Table dialog box, double-click Employees (Administration) and Departments (Administration)
  24. Click the following check boxes: EmployeeNumber, FirstName, MiddleName, LastName, Name, and Title
  25. Select the code in the SQL section and press Ctrl + C
  26. Display the Employees form
  27. Double-click an unoccupied area of the form and implement its event 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 MonsonUniversity2
    {
        public partial class Employees : Form
        {
            public Employees()
            {
                InitializeComponent();
            }
    
            // The reason we are writing this code is a separate function is in
            // case we may need the same functionality in many events, 
            // such as when creating a new employee
            void ShowEmployees()
            {
                using (SqlConnection cntMonsonUniversity =
                            new SqlConnection("Data Source=(local);" +
                                              "Database='MonsonUniversity2';" +
                                              "Integrated Security=Yes"))
                {
                    SqlCommand cmdEmployees =
                        new SqlCommand("SELECT Administration.Employees.EmployeeNumber, " +
                                       "       Administration.Employees.FirstName," +
                                       "       Administration.Employees.MiddleName," +
                                       "       Administration.Employees.LastName," +
                                       "       Administration.Departments.Name, " +
                                       "       Administration.Employees.Title " +
                                       "FROM   Administration.Employees " +
                                       "INNER JOIN Administration.Departments " +
                                       "ON Administration.Employees.DepartmentCode = Administration.Departments.DepartmentCode;",
                                   cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
                    DataSet dsEmployees = new DataSet("RegistrationsSet");
    
                    sdaEmployees.Fill(dsEmployees);
                    dgvEmployees.DataSource = dsEmployees.Tables[0];
                }
            }
    
            private void Employees_Load(object sender, EventArgs e)
            {
                ShowEmployees();
            }
        }
    }
  28. Return to the form and double-click the Close button
  29. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  30. Display the Query window
  31. In the Diagram section, right-click each table and click Remove
  32. Right-click the Diagram section and click Add Table...
  33. In the Add Table dialog box, double-click UndergraduateMajors (Academics), UndergraduateStudents (Admissions), and Minors(Academics)
  34. Click the following check boxes: StudentNumber, FirstName, MiddleName, LastName, Major, and Minor
  35. Select the code in the SQL section and press Ctrl + C
     
    Joining the Tables
  36. Display the Undergraduate Students form
  37. Double-click an unoccupied area of the form and implement its event 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 MonsonUniversity2
    {
        public partial class UndergraduateStudents : Form
        {
            public UndergraduateStudents()
            {
                InitializeComponent();
            }
    
            internal void ShowStudents()
            {
                using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
                {
                    SqlCommand cmdStudents =
                        new SqlCommand("SELECT Admissions.UndergraduateStudents.StudentNumber, " +
                                       "       Admissions.UndergraduateStudents.FirstName, " +
                                       "       Admissions.UndergraduateStudents.MiddleName,  " +
                                       "       Admissions.UndergraduateStudents.LastName, " +
                                       "       Academics.UndergraduateMajors.Major, " +
                                       "       Academics.Minors.Minor " +
                                       "FROM   Admissions.UndergraduateStudents " +
                                       "INNER JOIN Academics.UndergraduateMajors " +
                                       "       ON Admissions.UndergraduateStudents.MajorID = Academics.UndergraduateMajors.MajorID " +
                                       "INNER JOIN Academics.Minors " +
                                       "       ON Admissions.UndergraduateStudents.MinorID = Academics.Minors.MinorID;",
                                   cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
                    DataSet dsStudents = new DataSet("StudentsSet");
    
                    sdaStudents.Fill(dsStudents);
                    dgvStudents.DataSource = dsStudents.Tables[0];
                }
            }
    
            private void UndergraduateStudents_Load(object sender, EventArgs e)
            {
                ShowStudents();
            }
        }
    }
  38. Return to the form and double-click the Close button
  39. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  40. Display the Query window
  41. In the Diagram section, right-click each table and click Remove
  42. Right-click the Diagram section and click Add Table...
  43. In the Add Table dialog box, double-click UndergraduateStudents (Admissions), Semesters (Admissions), UndergraduateRegistrations(Admissions), and UndergraduateCourses(Academics)
  44. Click the StudentNumber check boxes
  45. Click the first box under Column in the Criteria section and type LastName + N', ' + FirstName
  46. Press Tab and type StudentName
  47. In the Diagram section, click the following check boxes: Semester, CourseCode, CourseName (from one of the tables), CourseName, and, Credits
    Joining the Tables
  48. Select the code in the SQL section and press Ctrl + C
  49. Display the Undergraduate Registrations form
  50. Double-click an unoccupied area of the form and implement its event 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 MonsonUniversity2
    {
        public partial class UndergraduateRegistrations : Form
        {
            public UndergraduateRegistrations()
            {
                InitializeComponent();
            }
    
            internal void ShowRegistrations()
            {
                using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
                {
                    SqlCommand cmdStudents =
                        new SqlCommand("SELECT Admissions.UndergraduateStudents.StudentNumber, " +
                                       "       Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                                       "       Admissions.Semesters.Semester, " +
                                       "       Academics.UndergraduateCourses.CourseCode, " +
                                       "       Academics.UndergraduateCourses.CourseName, " +
                                       "       Academics.UndergraduateCourses.Credits " +
                                       "FROM   Admissions.UndergraduateRegistrations " +
                                       "INNER JOIN Admissions.Semesters " +
                                       "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                                       "INNER JOIN Admissions.UndergraduateStudents " +
                                       "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                                       "INNER JOIN Academics.UndergraduateCourses " +
                                       "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode;",
                                   cntMonsonUniversity);
                    cntMonsonUniversity.Open();
                    SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
                    DataSet dsRegistrations = new DataSet("StudentsSet");
    
                    sdaRegistrations.Fill(dsRegistrations);
                    dgvRegistrations.DataSource = dsRegistrations.Tables[0];
                }
            }
    
            private void UndergraduateRegistrations_Load(object sender, EventArgs e)
            {
                ShowRegistrations();
            }
        }
    }
  51. Return to the form and double-click the Close button
  52. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  53. Execute the application
  54. Access each of the forms:
     
    Monson University - Students
    Monson University - Undergraduate Registrations
    Monson University - Undergraduate Majors
    Monson University - Employees
  55. Close the forms and return to your programming environment

Introduction to Joins and Data Analysis

As seen already, the main reason for creating queries is to isolate records. This is done using conditions and criteria. Joins enhance this capability because they allow you to consider records from different tables and include them in a common SQL statement.

In the joins we have created so far, we considered all records and let the database engine list them using only the rules of joins built-in the SQL. To make such a list more restrictive, you can pose your own conditions that should be respected to isolate records, like a funnel. As done in previous lessons, to include a criterion in a SELECT statement, you can create a WHERE clause.

Practical LearningPractical Learning: Introducing Joins and Data Analysis

  1. Display the Undergraduate Registrations form
  2. Change its design as follows:
     
    Monson University - Undergraduate Registrations
    Control Text Name Anchor
    DataGridView DataGridView   dgvRegistrations Top, Bottom, Left, Right
    Label Label Show courses registered for student #:   Bottom, Left
    TextBox TextBox   txtStudentNumber Bottom, Left, Right
    Button Button Show btnStudentNumber Bottom, Right
    Button Button Show All btnShowAll Bottom, Right
    Label Label Show student(s) whose name is/contains   Bottom, Left
    TextBox TextBox   txtStudentName Bottom, Left, Right
    Button Button Show btnStudentName Bottom, Right
    Label Label Show students registered in   Bottom, Left
    TextBox TextBox   txtSemester Bottom, Left, Right
    Label Label semester   Bottom, Right
    Button Button Show btnSemester Bottom, Right
    TextBox TextBox     Bottom, Left, Right
    Label Label Show students who have registered for this course code:   Bottom, Left
    TextBox TextBox   txtCourseCode Bottom, Left, Right
    Button Button Show btnCourseCode Bottom, Left, Right
    Label Label Show students who have registered for a course named:   Bottom, Left
    TextBox TextBox   txtCourseName Bottom, Left, Right
    Button Button Show btnCourseName Bottom, Right
    Button Button Close btnClose Bottom, Right

Using a Criterion

To create a criterion in a query you create from the SQL Server Management Studio, first select a column to display it in the Grid section. When creating the query, to specify a criterion, in the Criteria box corresponding to the column, type the condition using any of the operators we reviewed in previous lessons. Here is an example:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    DataGridView dgvPersons;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        dgvPersons = new DataGridView();
        dgvPersons.Location = new Point(12, 12);
        dgvPersons.Size = new System.Drawing.Size(450, 160);

        Controls.Add(dgvPersons);
        Text = "Record Selection";
        Load += new EventHandler(SelectRecords);
    }

    void SelectRecords(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='People';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, " +
                               "       Genders.GenderID, Genders.Gender " +
                               "FROM   Persons LEFT OUTER JOIN " +
                               "       Genders ON Persons.GenderID = Genders.GenderID " +
                               "WHERE  Genders.Gender = N'female';",
                connection);

            connection.Open();
            SqlDataAdapter sdaPeople = new SqlDataAdapter(command);
            DataSet dsPersons = new DataSet("PersonsSet");

            sdaPeople.Fill(dsPersons);
            dgvPersons.DataSource = dsPersons.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Join

Practical LearningPractical Learning: Analyzing Data With Joins

  1. Double-click the Show All button and implement its event as follows:
    private void btnShowAll_Click(object sender, EventArgs e)
    {
        ShowRegistrations();
    }
  2. Double-click the first Show button and implement its event as follows:
    private void btnStudentNumber_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                               "       Admissions.Semesters.Semester, " +
                               "       Academics.UndergraduateCourses.CourseCode, " +
                               "       Academics.UndergraduateCourses.CourseName, " +
                               "       Academics.UndergraduateCourses.Credits " +
                               "FROM   Admissions.UndergraduateRegistrations " +
                               "INNER JOIN Admissions.Semesters " +
                               "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                               "INNER JOIN Admissions.UndergraduateStudents " +
                               "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                               "INNER JOIN Academics.UndergraduateCourses " +
                               "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode " +
                               "WHERE Admissions.UndergraduateStudents.StudentNumber = '" + txtStudentNumber.Text + "';",
                           cntMonsonUniversity);
            cntMonsonUniversity.Open();
            SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
            DataSet dsRegistrations = new DataSet("StudentsSet");
    
            sdaRegistrations.Fill(dsRegistrations);
            dgvRegistrations.DataSource = dsRegistrations.Tables[0];
        }
    }
  3. Return to the form and double-click the second Show button
  4. Implement its event as follows:
    private void btnStudentName_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT Admissions.UndergraduateStudents.StudentNumber, " +
                               "       Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                               "       Admissions.Semesters.Semester, " +
                               "       Academics.UndergraduateCourses.CourseCode, " +
                               "       Academics.UndergraduateCourses.CourseName, " +
                               "       Academics.UndergraduateCourses.Credits " +
                               "FROM   Admissions.UndergraduateRegistrations " +
                               "INNER JOIN Admissions.Semesters " +
                               "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                               "INNER JOIN Admissions.UndergraduateStudents " +
                               "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                               "INNER JOIN Academics.UndergraduateCourses " +
                               "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode " +
                               "WHERE (Admissions.UndergraduateStudents.LastName LIKE '%" + txtStudentName.Text + "%') OR " +
                               "      (Admissions.UndergraduateStudents.FirstName LIKE '%" + txtStudentName.Text + "%');",
                           cntMonsonUniversity);
            cntMonsonUniversity.Open();
            SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
            DataSet dsRegistrations = new DataSet("StudentsSet");
    
            sdaRegistrations.Fill(dsRegistrations);
            dgvRegistrations.DataSource = dsRegistrations.Tables[0];
        }
    }
  5. Return to the form and double-click the third Show button
  6. Implement its event as follows:
    private void btnSemester_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT Admissions.Semesters.Semester, " +
                               "       Admissions.UndergraduateStudents.StudentNumber, " +
                               "       Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                               "       Academics.UndergraduateCourses.CourseCode, " +
                               "       Academics.UndergraduateCourses.CourseName, " +
                               "       Academics.UndergraduateCourses.Credits " +
                               "FROM   Admissions.UndergraduateRegistrations " +
                               "INNER JOIN Admissions.Semesters " +
                               "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                               "INNER JOIN Admissions.UndergraduateStudents " +
                               "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                               "INNER JOIN Academics.UndergraduateCourses " +
                               "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode " +
                               "WHERE  Admissions.Semesters.Semester LIKE '%" + txtSemester.Text + "%' " +
                               "ORDER BY Admissions.Semesters.Semester;",
                           cntMonsonUniversity);
            cntMonsonUniversity.Open();
            SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
            DataSet dsRegistrations = new DataSet("StudentsSet");
    
            sdaRegistrations.Fill(dsRegistrations);
            dgvRegistrations.DataSource = dsRegistrations.Tables[0];
        }
    }
  7. Return to the form and double the fourth Show button
  8. Implement its event as follows:
    private void btnCourseCode_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT Admissions.UndergraduateStudents.StudentNumber, " +
                               "       Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                               "       Admissions.Semesters.Semester, " +
                               "       Academics.UndergraduateCourses.CourseCode, " +
                               "       Academics.UndergraduateCourses.CourseName, " +
                               "       Academics.UndergraduateCourses.Credits " +
                               "FROM   Admissions.UndergraduateRegistrations " +
                               "INNER JOIN Admissions.Semesters " +
                               "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                               "INNER JOIN Admissions.UndergraduateStudents " +
                               "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                               "INNER JOIN Academics.UndergraduateCourses " +
                               "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode " +
                               "WHERE Academics.UndergraduateCourses.CourseCode LIKE '%" + txtCourseCode.Text + "%' " +
                               "ORDER BY Academics.UndergraduateCourses.CourseCode;",
                           cntMonsonUniversity);
            cntMonsonUniversity.Open();
            SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
            DataSet dsRegistrations = new DataSet("StudentsSet");
    
            sdaRegistrations.Fill(dsRegistrations);
            dgvRegistrations.DataSource = dsRegistrations.Tables[0];
        }
    }
  9. Return to the form and double the fifth Show button
  10. Implement its event as follows:
    private void btnCourseName_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntMonsonUniversity =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='MonsonUniversity2';" +
                                          "Integrated Security=Yes"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT Admissions.UndergraduateStudents.StudentNumber, " +
                               "       Admissions.UndergraduateStudents.LastName + N', ' + Admissions.UndergraduateStudents.FirstName AS StudentName, " +
                               "       Admissions.Semesters.Semester, " +
                               "       Academics.UndergraduateCourses.CourseCode, " +
                               "       Academics.UndergraduateCourses.CourseName, " +
                               "       Academics.UndergraduateCourses.Credits " +
                               "FROM   Admissions.UndergraduateRegistrations " +
                               "INNER JOIN Admissions.Semesters " +
                               "       ON Admissions.UndergraduateRegistrations.SemesterID = Admissions.Semesters.SemesterID " +
                               "INNER JOIN Admissions.UndergraduateStudents " +
                               "       ON Admissions.UndergraduateRegistrations.StudentNumber = Admissions.UndergraduateStudents.StudentNumber " +
                               "INNER JOIN Academics.UndergraduateCourses " +
                               "       ON Admissions.UndergraduateRegistrations.CourseCode = Academics.UndergraduateCourses.CourseCode " +
                               "WHERE Academics.UndergraduateCourses.CourseName LIKE '%" + txtCourseName.Text + "%' " +
                               "ORDER BY Academics.UndergraduateCourses.CourseName;",
                           cntMonsonUniversity);
            cntMonsonUniversity.Open();
            SqlDataAdapter sdaRegistrations = new SqlDataAdapter(cmdStudents);
            DataSet dsRegistrations = new DataSet("StudentsSet");
    
            sdaRegistrations.Fill(dsRegistrations);
            dgvRegistrations.DataSource = dsRegistrations.Tables[0];
        }
    }
  11. Execute the application
  12. Click the Undergraduate Registrations and test the options. Here are examples:

    Monson University - Data Analysis With Joins
     
    Monson University - Data Analysis With Joins
     
    Monson University - Data Analysis With Joins
     
    Monson University - Data Analysis With Joins
     
    Monson University - Data Analysis With Joins
     
    Monson University - Data Analysis With Joins
  13. Close the forms and return to your programming environment

A Parameterized Query

 

Introduction

A query is referred to as parameterized if it would expect an external value to perform its comparison. The statement starts like a normal SELECT operation with a WHERE clause. Here is an example:

SELECT * FROM Students WHERE Sex = N'male';

Instead of specifying the value of the column(s) in the WHERE clause, you can wait for the user to do so.

Creating a Parameterized Query

To visually create a parameterized statement, in the Object Explorer, right-click the table and click Edit Top 200 Rows. In the Criteria pane, click the box at the intersection of the column and Filter. Type @ followed by a variable name.

To create a parameterized query with code, open a Query window. Start a normal SELECT expression that contains a condition. In the WHERE expression, replace the value with the name of a variable starting with @. Here is an example:

SELECT StudentNumber, LastName, FirstName, City, State
FROM Students
WHERE StudentNumber = @StdNbr;

Executing a Parameterized Statement

After creating a parameterized statement, you can test and/or use it. When you run the query, the SQL interpreter would request a value for the column. When you execute the statement, a dialog box would come up, asking you to enter a value for the filtered field. You can then type the appropriate value and click OK (or press Enter).

 

Home Copyright © 2011 C#Key