Developed by
Renaissance Computer Systems Ltd.
Important: This starter kit requires that the Report Viewer control be installed on your development and target computers. If you only have VB Express or C# Express installed, you must download and install the Report Viewer control separately. More details and download information can be found below.
Contents
- Introduction
- Overview
- Database Schema
- SQL Server Express Database Files and Visual Studio 2005
- Using the Application
The Teacher
Starter Kit is a starter kit that shows how easy it is to develop usable
database applications with SQL Server 2005 Express and Visual Basic 2005 (or C#
2005).
This application is targeted at school teachers, who may also program, and
provides an automated record of assessment grades for students in multiple
courses that may be taught by the teacher. The application comes as a
Visual Studio 2005 Starter Kit template and is ready to be compiled and
run. You have full access to all of the source code, so that you can
learn how the different features and programming techniques are
implemented. You can also modify and extend the VB or C# source code to meet
your own specific needs. A list of possible ideas for extending this
application is provided later in this document.
In addition to being a simple, but fully functional, desktop application, the Teacher Starter Kit application also attempts to showcase the use of various techniques and technologies in SQL Server 2005 Express and Visual Studio 2005. The following is a partial list of the techniques and technologies that are utilized:
- One-to-Many, Many-to-Many, and lookup database tables
- SQL Outer Joins
- Typed DataSets
- TableAdapter Object
- Winforms data-binding
- DataGridView Control
- Single-record view
- ReportViewer Control
- Loading objects (instead of simple data) into list/combobox
- Search/Filter capabilities
The
database schema consists of 6 tables, including one many-to-many mapping table (CourseStudents) and one simple lookup table (Assessment
types). The tables, along with their columns, primary keys, and
relationships are show in the diagram below.
SQL Server Express Database Files
and Visual Studio 2005 
It is important to understand the way in which Visual Studio 2005 handles SQL Server 2005 Express database files. There are actually several pairs of mdf/ldf files (Teachers.mdf and Teachers_log.ldf). Teachers.mdf is the file that contains the actual database data and Teachers_log.ldf is the file that is the associated log file for the database (used by the SQL Server engine). In order to simplify deployment , so that all you need to do is copy the project files without having to modify any connection strings, the application is configured so that it looks for the database files in the same directory as the executable (teacher.exe) – the Output Directory. Therefore, there is one set in the \debug directory. If you also compile for a release configuration, there will be another set in the \release directory.
In additional, there is always a set in the same directory as the other source code files, since the database files are being treated by Visual Studio as project items. It is important to remember that Visual Studio will normally be accessing this last set of database files in the IDE, even thought they are not the ones that are accessed by the application when the application teacher.exe runs. This means that if you run the application and modify data in the database, you will not see these changes if you look at the default database files in Visual Studio, since they are actually different physical files. When in doubt, be sure to verify the path of the database files that you are looking at, by checking the path in the Properties window.
If you
select the Teachers.mdf file in the Solution Explorer
and look at its properties in the Properties window, you will see that
"Copy to Output Directory" is set to Copy if newer. This means
that if the mdf/ldf files in the source files
directory are modified in any way (i.e. via the Server Explorer in Visual
Studio) they will be copied to the \debug (and/or \release) directory the next
time the application is run – overwriting the existing files. Be
sure to keep this behavior in mind and to exercise care once you have used the
Teacher application to save data in the database.
In order to actually use the application, SQL Server 2005 Express must be installed and running on your computer.
The Teacher
application is designed around a single, tabbed main form for most of the
day-to-day functionality. In addition, there are ancillary forms for
adding/deleting courses and students to/from the application, and for selecting
students and assigning them to a specific course. The main form is
shown below:
The application is designed to be course-centric. That is, everything that is displayed or done from the application is based on the selected course (in the drop-down combobox). All of the tabs relate to the currently selected course. The Manage Courses button displays a dialog box that allows you to view and/or modify all currently defined courses, or to add a new one.
The
functionality of the system is divided into five (5) areas, each one with its
own tab and UI. The tabs are ordered according to the typical progression
of activities. The following is a summary of the functionality available
on each tab:
(1)
Students – This is the default view of the application and
displays a DataGridView of the students currently
registered in the current course.
You can click on the columns in the grid to set the sorting of the data. The toolstrip allows you to search for a student in the course, by (partial) last name. One or more existing students may be added to the course via the Select Students dialog. A student may be removed from the course by clicking the Remove Students button. New students may be added to the system via the Manage Students dialog.
(2)
Assessments – This tab allows you to browse/modify/delete
existing assessments, as well as to add new ones.
(3)
Enter Grades – This is where you enter or modify a student's grade for a
defined assessment. The grade is validated to ensure that it is numeric
and falls within the min/max range defined for that assessment (displayed as a
hint).
(4) View Grades – This displays a DataGridView
of the existing grades currently assigned for all students and for all of the
assessments in the current course.
The columns in the grid may be clicked
to set the sorting of the data.
The View
Report dialog displays the report and allows for standard viewing, paging,
sizing, zooming, print preview, and printing.
Issues With the ReportViewer Control
The ReportViewer shown above is a powerful control for
previewing, printing, and exporting reports based on data in your SQL Express
database. This control is not part of
the .NET framework, but is an add-on control that is part of Visual Studio. This fact results in
2 main issues:
(1) If you deploy your application on a computer that does not have Visual Studio 2005 installed, you cannot necessarily assume that the ReportViewer control is available on that computer – even after the .NET Framework 2.0 is installed. This issue is easily addressed by downloading and installing the Report Viewer Redistributable (http://www.microsoft.com/downloads/details.aspx?FamilyID=8a166cac-758d-45c8-b637-dd7726e61367) onto the target computer. Alternatively, you can build a setup program from within Visual Studio (Professional or better) to deploy your application on other computers. Once it is detected that your application uses the ReportViewer control, it will automatically be included as part of the setup program for your application.
(2) The VB Express and C# Express versions of Visual Studio do not include the ReportViewer control. This means that if you are using VB Express or C# Express to develop an application based on the Teacher Starter Kit, you must first download and install the Report Viewer Redistributable described above onto your development computer in order to compile the starter kit. You must also must download and install the Report Viewer Redistributable onto any target machines onto which you are deploying your application.
In addition, VB Express and C# Express do not include a designer for designing reports.
Enhancements, Additions, and
Wish-List Items
It is important to note that this application is designed and implemented to be as simple and easy to follow as possible. The emphasis is on correct, functional code, rather than more complex code that might be appropriate for large-scale applications. This means that while the design and implementation is appropriate for a simple desktop application (and as a learning tool), things would have been done differently if this were meant to be a commercial and/or enterprise-class application. Some of the things which you may want to improve/implement if you plan on modifying the existing code include the following:
- Moving more of the code out of the actual event handlers into separate functions. This was done often, but not always, in order to make the code easier to follow.
- Moving code out of the forms into separate classes.
- Making the overall application more event-driven.
- Writing code to have the system remember its context in more situations. The current implementation will often “reset” its context, rather than remember it. This was done in order to avoid adding a lot of code to add this functionality, which would obscure the essential code of the application. An example of this is the fact that the current course combobox is reset after you display and return from the Manage Courses dialog, rather than remembering what is was displaying beforehand.
- Have the exception handlers in Try-Catch blocks check for more specific exception types.
The following is a list of ideas on how you can extend the functionality of the Teacher Starter Kit application:
- Categorize courses by department.
- Extend the application to support multiple teachers, with each one requiring their own user name and password and only having access to information for courses that they teach.
- Calculate a student’s final grade based on assessment grades.
- Calculate and display student and course statistics.
- Color-code assessment types.
- Modify the views of assessment grades so that you show grades for only a single assessment or for a single student across multiple courses.
Online Resources
- SQL Server 2005 Express on MSDN
- Visual Basic on MSDN
- Visual C# on MSDN
- SQL Server Reporting Services Home