Assignment

The objectives of this assignment are as follows:

• Create a fully functioning Windows Forms desktop application.
• The application’s forms will allow a user to interact with the Movies table from a SQL Server database.
• This application retrieves data from the database while also demonstrating the ability to handle the typical CRUD operations (Create, Read, Update, and Delete).

Skills Required

To properly complete this assignment, you will need to apply the following skills:

• Construct Windows desktop GUI application using Windows Forms. This application will use all the following controls:
  • DataGridView to display all the movies available in the database. In your code-behind, you will populate the data grid control with a list of movies using a class object for each movie retrieved from the database.
  • ComboBox to display nine different genre selections to associate with a given movie.
  • RichTextBox to display information on the About form.
  • MenuStrip to provide a menu for the Main form.
• Work with ADO.NET to access SQL Server database housed in AWS.
• Retrieve data from a table.
• Add, Update, and Delete data in the table.
• Implement Try/Catch block logic.
• Display multiple forms.
• Construct dialogue windows.

Supplementary Information

Everything you need to understand accessing a database using ADO.NET can be found in chapter 2 of the book entitled, SQL Server for C# Developers: Succinctly, found in Blackboard.

Note: There are no shortcuts; you must read chapter 2 from this book to begin to gain an understanding of what ADO.NET is and how to use it.

Other materials specific to the controls that you will be using and how to code these will be provided to you in Blackboard.

Assignment User Story

As a user, I want a new Windows application written using Windows Forms that is capable of obtaining basic information from a SQL Server database related to movies that are entered into a database by programming students. Further, I want the ability to add, update, and delete existing movies in the database.

Assignment Requirements

The work you will be doing is all part of a larger project. The application that results from this project, when completed, will be capable of finding a movie by its title and displaying some related information about the movie including the director, the year the movie was released, its genre category, the Rotten Tomatoes score, and the total box office earnings.

ADO.NET

The technology that we will be using to access our database is called ADO.NET. This is a data access technology that provides communication to and from data systems through a common set of software components. ADO.NET provides a bridge between your application and a back-end database; it is an interface to data. For a fairly complete walk-through of ADO.NET you must read the second chapter in the book SQL Server for C# Developers: Succinctly provided as a PDF in this class.

The process of working with ADO.NET involves the following steps:

• Create a connection string that contains the information needed to successfully connect to the server and specific database.
• Establish a connection to the database.
• Form the SQL query string that will be sent to the database.
• Establish a command that will execute the query.
• Open the connection.
• Execute a SqlDataReader against the open connection that will send the command to the database and return any results to the reader instance.
• Process the results from the reader.
• Close the connection.

The above steps are completely covered by the book as well as with the sample console-based application provided in Blackboard.

The Database and Connection String

Our database is hosted in AWS. Of course, where the database is hosted is not consequential; we simply need a connection string to the instance of our database. The following connection string is properly formatted for this assignment:

• Data Source=coursemaster1.csbchotp6tva.us-east-2.rds.amazonaws.com,1433;Initial Catalog=CSCI1630;User ID=rw1630;Password=Project!;

Ideally, the connection string should be placed in and accessed from the application’s App.config file. However, it can just as easily be constructed through string interpolation. Examples of how to construct the connection string will be provided.

What is of importance here is knowing that the above string represents exactly what the connection must contain.

Important! You must not copy the string from above to your code. You will get additional spaces in the pasted version that will cause your connection to the database to fail.

The Movies Table and Class

Within the CSCI1630 database we have a table called Movies. We will access this table to retrieve information about a given movie based on the Title the user provides to the application’s interface. The schema of our table is shown to the left.

You will need to construct a class in your application called Moviecontaining properties that match each of the columns in the table.

Warning: for your application to work properly, the names and data-types of your class’s properties must match the names and data-types of the columns shown in our table.

The varchar(number) data-type in SQL Server simply maps to a string data-type in C#. The number in the parenthesis, example varchar(50) is the maximum length of the string in the table. This factors into our code in three meaningful ways: first, we can use our knowledge of the length of a given column to properly provide enough length for the fields on the form; second, we need the length of each column when we add parameters to the SQL command passed to the database. For a full discussion of parameters, you must read chapter 2 from the book SQL Server for C# Developers: Succinctly; And, finally, knowing the maximum length of a column should factor into our error handling for the application. For example, we want to prevent the user from entering either a title or director where the length of the string is greater than 50 characters.

SQL Commands

The SQL commands that you will use throughout the application will be provided to you.

Null Values

Two of the columns in our table: RottenTomatoesScore and TotalEarned are marked as allowing null values. This means that any individual row in the table might have no values for these two columns. Your program must protect against this. If you don’t specifically code to check for nulls then when the user tries to find a movie where one or both of these values are null your program will end in an execution error. This is shown in the section Querying your database on pages 22 through 24 of the SQL Server for C# Developers: Succinctly book.

Example Output

You do not need to format your output to look like these examples. You simply need to ensure that you have met the technical requirements for this application.

Following is example of the main page for this application:

The following example shows the Maintenance menu expanded with its three options:

The following example shows the Add Movie Screen:

The following example shows the Update Movie Screen with data loaded:

The following example shows the Delete Movie Screen with the delete prompt:

In this final example, the About form is shown: