CST272 Project No. 7

SQL Server and the SqlDataSource Control

Part 1

In a new ASP.NET project named "Movies" create a SQL Server database named "Movies" with the following tables and related properties:

Director (table)
Name Data type Allow nulls Other
DirectorID int   Primary key;
Indentification specification: True
Identity increment: 1; Identity seed: 7
DirectorName varchar(25)    
DirectorBorn int    
DirectorDied int ✓  

Movie (table)
Name Data type Allow nulls Other
MovieID int   Primary key;
Indentification specification: True
Identity increment: 1; Identity seed: 24
MovieTitle varchar(50)    
YearMade int    
DirectorID int   Foreign key into "Director" table
Stars int ✓  
Rating varchar(5) ✓  
MovieType varchar(10) ✓  

 
Star (table)
Name Data type Allow nulls Other
StarID int   Primary key;
Indentification specification: True
Identity increment: 1; Identity seed: 25
StarName varchar(25)    
Birthplace varchar(25)    
StarBorn int    
StarDied int ✓  

 
MovieStar (table)
Name Data type Allow nulls Other
MovieID int   Concatenated primary key with StarID;
Foreign key into "Movie" table
StarID int   Concatenated primary key with MovieID;
Foreign key into "Star" table

 

Part 2

Enter the following data for the tables of the "Movies" database:

Director (data)
DirectorID DirectorName DirectorBorn DirectorDied
7 John Ford 1894 1973
8 Henry Hathaway 1898 1985

 
Movie (data)
MovieID MovieTitle YearMade DirectorID Stars Rating MovieType
24 Grapes of Wrath 1940 7 4 NR Drama
25 Fort Apache 1948 7 4 NR Drama
26 True Grit 1969 8 3 G Western

 
Star (data)
StarID StarName Birthplace StarBorn StarDied
25 Henry Fonda Grand Island, NE 1905 1982
26 Jane Darwell Palmyra, MO 1879 1967
27 John Carradine New York, NY 1906 1988
28 John Wayne Winterset, IA 1907 1979
29 Shirley Temple Santa Monica, CA 1928 2014
30 Kim Darby Los Angeles, CA 1947  
31 Glen Campbell Delight, AR 1936 2017

 
MovieStar (data)
MovieID StarID
24 25
24 26
24 27
25 25
25 28
25 29
26 28
26 30
26 31

 

Part 3

Create three Web Forms each with SqlDataSource and GridView controls as follows:

  1. A Form that shows the Star table in a GridView (complete Parts 1 and 2 above, as well as this query and GridView control for a maximum grade of 8 out of 10 points)
  2. A Form that shows the MovieTitle, YearMade, Stars, Rating and MovieType from the Movie table and the DirectorName from the Director table in a GridView (complete Parts 1 and 2 as well as these first two queries and GridView controls for a maximum grade of 9 out of 10 points)
  3. A Form that shows the MovieTitle from the Movie table, the DirectorName from the Director table, and the StarName from the Star table for all movies in a GridView (this query requires all four tables be included in the SQL INNER JOIN statement) (complete all elements of this assignment including all three queries and GridView controls for a maximum grade of 10 points)

Hint:  Possibly the easiest way to complete the second and third SqlDataSource/GridView assignments above which merge columns from two or more tables might be to use the "Query Builder" window from the "Configure Data Source" wizard for the SqlDataSource control

The completed project, a ZIP file that contains the folder with all files for the website, is due to Prof. Struck as an attachment to a Brightspace dropbox by 11:59 p.m. on the date specified in the course outline.