 |
Microsoft Visual C#: Topics on Data Relationships |
|
|
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
Learning: Introducing Join Options
|
|
- Start Microsoft Visual Studio
- To start a new applicaiton, on the main menu, click File -> New
Project...
- In the middle list, click Windows Forms Application and set the Name
to MonsonUniversity2
- Click OK
- In the Solution Explorer, right-click Form1.cs and click Rename
- Type MonsonUniversity.cs and press Enter
- 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();
}
}
}
- Press Ctrl + F5 to execute
- Click OK on each message box
- Close the form and return to your programming environment
- To create a dialog box, on the main menu, click Project -> Add
Windows Form...
- Set the name to UndergraduateMajor and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
 |
Major: |
|
|
| TextBox |
 |
|
txtMajor |
Modifiers: Public |
| Label |
|
Dean: |
|
|
| ComboBox |
 |
|
cbxDeans |
Modifiers: Public |
| Button |
 |
OK |
btnOK |
DialogResult: OK |
| 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 |
- To create a dialog box, on the main menu, click Project -> Add
Windows Form...
- Set the name to UndergraduateMajors and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
| DataGridView |
 |
|
dgvMajors |
| Button |
|
New Undergraduate Major... |
btnClose |
| Button |
|
Close |
btnClose |
|
- To create a dialog box, on the main menu, click Project -> Add
Windows Form...
- Set the name to Employees and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
| DataGridView |
|
|
dgvEmployees |
| Button |
|
Close |
btnClose |
|
- To create a dialog box, on the main menu, click Project -> Add
Windows Form...
- Set the name to UndergraduateStudents and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
| DataGridView |
|
|
dgvStudents |
| Button |
|
Close |
btnClose |
|
- To create a dialog box, on the main menu, click Project -> Add
Windows Form...
- Set the name to UndergraduateRegistrations and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
| DataGridView |
|
|
dgvRegistrations |
| Button |
|
Close |
btnClose |
|
- Design the form as follows:
 |
| Control |
Text |
Name |
| Button |
|
Undergraduate Students... |
btnUndergraduateStudents |
| Button |
|
Graduate Students... |
btnGraduateStudents |
| Button |
|
Undergraduate Registrations... |
btnUndergraduateRegistrations |
| Button |
|
Graduate Registrations... |
btnGraduateRegistrations |
| Button |
|
Undergraduate Majors... |
btnUndergraduateMajors |
| Button |
|
Graduate Majors... |
btnGraduateMajors |
| Button |
|
Employees... |
btnEmployees |
| Button |
|
Close |
btnClose |
|
- 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();
}
- Return to the form
- 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();
}
- Return to the form
- 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();
}
- Return to the form
- 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();
}
- Return to the form
- Double-click the Close button and implement its event as follows:
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
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
Learning: Joining the Tables
|
|
- In the Server Explorer, right-click Data Connections and click Add
Connection...
- In the Server Name combo box, type (local)
- In the Select Or Enter A Database Name combo box, select
MonsonUniversity2
- Click OK
- In the Server Explorer, right-click MonsonUniversity2.dbo and click
New Query
- In the Add Table dialog box, double-click UndergraduateMajors
(Academics) and Employees (Administration)
- Click Close
- Click the Major check box
- Click the box under Major in the Criteria section
- Type LastName + ', ' + FirstName and press Tab
- Type Dean
- Click the check box of Title
- Select the code in the SQL section and press Ctrl + C
- Display the Undergraduate Majors form
- 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();
}
}
}
- Return to the form and double-click the New Undergraduate Major
button
- 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();
}
- Return to the form and double-click the Close button
- Implement its event as follows:
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
- Display the Query window
- In the Diagram section, right-click each table and click Remove
- Right-click the Diagram section and click Add Table...
- In the Add Table dialog box, double-click Employees (Administration)
and Departments (Administration)
- Click the following check boxes: EmployeeNumber, FirstName,
MiddleName, LastName, Name, and Title
- Select the code in the SQL section and press Ctrl + C
- Display the Employees form
- 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();
}
}
}
- Return to the form and double-click the Close button
- Implement its event as follows:
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
- Display the Query window
- In the Diagram section, right-click each table and click Remove
- Right-click the Diagram section and click Add Table...
- In the Add Table dialog box, double-click UndergraduateMajors
(Academics), UndergraduateStudents (Admissions), and Minors(Academics)
- Click the following check boxes: StudentNumber, FirstName,
MiddleName, LastName, Major, and Minor
- Select the code in the SQL section and press Ctrl + C
- Display the Undergraduate Students form
- 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();
}
}
}
- Return to the form and double-click the Close button
- Implement its event as follows:
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
- Display the Query window
- In the Diagram section, right-click each table and click Remove
- Right-click the Diagram section and click Add Table...
- In the Add Table dialog box, double-click UndergraduateStudents
(Admissions), Semesters (Admissions),
UndergraduateRegistrations(Admissions), and
UndergraduateCourses(Academics)
- Click the StudentNumber check boxes
- Click the first box under Column in the Criteria section and type
LastName + N', ' + FirstName
- Press Tab and type StudentName
- In the Diagram section, click the following check boxes: Semester,
CourseCode, CourseName (from one of the tables), CourseName, and,
Credits
- Select the code in the SQL section and press Ctrl + C
- Display the Undergraduate Registrations form
- 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();
}
}
}
- Return to the form and double-click the Close button
- Implement its event as follows:
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
- Execute the application
- Access each of the forms:
- 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
Learning: Introducing Joins and Data Analysis
|
|
- Display the Undergraduate Registrations form
- Change its design as follows:
 |
| Control |
Text |
Name |
Anchor |
| DataGridView |
|
|
dgvRegistrations |
Top, Bottom, Left, Right |
| Label |
|
Show courses registered for student #: |
|
Bottom, Left |
| TextBox |
|
|
txtStudentNumber |
Bottom, Left, Right |
| Button |
|
Show |
btnStudentNumber |
Bottom, Right |
| Button |
|
Show All |
btnShowAll |
Bottom, Right |
| Label |
|
Show student(s) whose name is/contains |
|
Bottom, Left |
| TextBox |
|
|
txtStudentName |
Bottom, Left, Right |
| Button |
|
Show |
btnStudentName |
Bottom, Right |
| Label |
|
Show students registered in |
|
Bottom, Left |
| TextBox |
|
|
txtSemester |
Bottom, Left, Right |
| Label |
|
semester |
|
Bottom, Right |
| Button |
|
Show |
btnSemester |
Bottom, Right |
| TextBox |
|
|
|
Bottom, Left, Right |
| Label |
|
Show students who have registered for this course
code: |
|
Bottom, Left |
| TextBox |
|
|
txtCourseCode |
Bottom, Left, Right |
| Button |
|
Show |
btnCourseCode |
Bottom, Left, Right |
| Label |
|
Show students who have registered for a course
named: |
|
Bottom, Left |
| TextBox |
|
|
txtCourseName |
Bottom, Left, Right |
| Button |
|
Show |
btnCourseName |
Bottom, Right |
| Button |
|
Close |
btnClose |
Bottom, Right |
|
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;
}
}
|
Practical
Learning: Analyzing Data With Joins
|
|
- Double-click the Show All button and implement its event as follows:
private void btnShowAll_Click(object sender, EventArgs e)
{
ShowRegistrations();
}
- 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];
}
}
- Return to the form and double-click the second Show button
- 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];
}
}
- Return to the form and double-click the third Show button
- 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];
}
}
- Return to the form and double the fourth Show button
- 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];
}
}
- Return to the form and double the fifth Show button
- 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];
}
}
- Execute the application
- Click the Undergraduate Registrations and test the options. Here are
examples:
- Close the forms and return to your programming environment
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).
|
|