Lab 10: Using Databases

Learning Objectives for lab 10

After completion of today's lab, you should be able to

Create Visual Studio Projects containing multiple projects
Know how to use namespaces and assemblies to reference projects
Use 3-tier architecture to separate
- Windows User Interface View
- Business Rules and Control Logic
- Database model details
Use XML comments to document your program
Use Unit Testing to test and debug methods separately before integration with the solution

Real-World Solutions

CS 446 deals with developing throw-away prototypes of user interfaces. In most cases, the wizards and auto-generated code is quick and appropriate for prototype construction.

With production code, some problems are inherent from using Visual Studio's wizards and auto-generated code:

Debugging auto-generated code is difficult
User Interface code, business logic code, and database access code become tightly-coupled (intermingled)
- Migration to different user interface (e.g. Web) requires a major rewrite
- Changes in business logic or database design require a major rewrite
- Migration to different data store (e.g. Oracle or XML) requires a major rewrite
Teams of programmers working on individual parts independently is hard
Unit and Integration testing is hard
Porting to other non-Microsoft platforms or languages methods is difficult
Program understandability and documention is difficult
Program maintenance (often much more an issue than initial development) is hard
Reusing the code in other applications is hard

Three-Tier Architectures

Three-tier architectures are one of the established architectural designs when dealing with databases. The goals of three-tier architectures are long-term flexibility, ease of maintenance, and reuse of components.

Three-tier architectures strive to separate into three separate tiers (modules).

Persistent Data Model Tier - generally classes and methods to model the database
Business Logic Tier
Presentation View Tier - the user interface

Another name for this type of classic three tier architecture is Model-View-Controller or MVC for short.

In a Visual Studio Solution, each of these three tiers is a separate project, each coded and unit-tested by separate programmers

Activity: Create a Three-Tier Solution

Activity 1 - Create a project named WindowsUI in three-tier solution named Lab10

Add a new Windows Form named MainForm to your WindowsUI project
Download and save the database file CwuCourses.mdb in your U:\Lab10\WindowsUI\bin\Debug folder

Activity 2 - Add the BusinessLogicTier and DataAccessTier projects to your solution

Download and extract file Lab10.zip that contains two project folders: BusinessLogicTier and DataAccessTier
Copy and paste both folders inside your U:\Lab10 folder
Add the existing project file U:\Lab10\BusinessLogicTier\BusinessLogicTier.vsproj to your solution
Add the existing project file U:\Lab10\DataAccessTier\DataAccessTier.vsproj to your solution

Activity 3 - Add two References and a namespace to the WindowsUI project

Build the solution to generate the three project assemblies
Add the project references BusinessLogicTier and DataAccessTier to your WindowsUI references
Since both the Business LogicTier and the DataAccessTier projects use Cwu.Cs446.Db as their project namespace, add the using directive using Cwu.Cs446.Database; to your Program and MainForm classes
Rebuild your solution and make sure it compiles OK

Data Access Tier

The DataAccessTier contains two base classes: MicrosoftAccessDatabase and MicrosoftAccessTable.

Business Logic Tier

The BusinessLogicTier contains Business Objects are derived from the two base classes of the Data Access Tier.

It is sometimes useful to use the Class View window in Visual Studio to help understand classes written by others.

Presentation Tier

The Presentation Tier in our application is our Windows User Interface. However, the goal of a three-tier design is to be able to change the presentation tier, for example to a Web User Interface, and not have to make any changes to the other two tiers.

XML Comments

Once programmers begin using code written by others, documentation becomes important as a way to communicate how and why to use methods.

Visual Studio and C# use XML comments to automatically generate HTML or XML documentation from code.

XML comments are indicated by three forward slashes (///) in the code with recommended XML documenation tags to to markup the comments. These comments will now be used by Visual Studio 2005 for its intellisense feature.

You can set the project's build properties to automatically extract or update the XML comments everytime you build the project. Then you can handcode the XSLT or use a open source tool like NDoc to transform the XML to HTML

Using out Business Logic Layer

in Program.cs main method, add the code to instantiate a CwuDatabase object and pass it along to the contructor method for the MainForm class.

using Cwu.Cs446.Database;

   try
   {
      CwuDatabase d = new CwuDatabase("CwuCourses.mdb");
      Application.Run(new MainForm(d));
   }
   catch (Exception e)
   {
       MessageBox.Show(e.Message);
   }

In the MainForm constructor method, add a parameter and save the database in an instance variable

using Cwu.Cs446.Database;


   //instance variable
   private CwuDatabase db;

   public MainForm(CwuDatabase database)
   {
      InitializeComponent();
      db = database;
   }

For a quick use the the business logic layer, drag and drop a listbox onto the MainForm. Fill the listbox with the following code.

      System.Collections.ArrayList courses = db.Courses.GetCoursesByDepartment("CS");
      listBox1.DataSource = courses;
      listBox1.DisplayMember = Course.CourseNumberColumnName;

Unit Testing

Be separating the tiers from each other, they can be compiled, tested, and debugged independently.

Unit testing is best done with a tool; NUnit unit-testing framework is a open source tool that integrates well with Visual Studio.

To provide a feel for Unit Testing without a tool, here is some example code that can be used to test the MsAccessDb class.

    //test the constructor
      public void ConstructorTest()
      {
         string relativePath = @"U:\446\Lab10d\WindowsUI\bin\Debug\CwuCourses.mdb"; 
         MicrosoftAccessDatabase target = new MicrosoftAccessDatabase(relativePath);
         Assert.IsNotNull(target, "Constructor Failed");
      }
   
   //test ExecuteScalar
   int NumCourses = (int) db.ExecuteScalar("SELECT COUNT(*) FROM Courses");
   Console.WriteLine("NumCourses = " + NumCourses);

   //test Insert
   int numRowsInserted = db.Insert(
       "Courses", 
       "CourseNumber, CourseName, CourseDescription, Credits",
       "'CS 999', 'Computer Basics', 'Intro to ... ', 4");
   System.Diagnostics.Debug.Assert(numRowsInserted == 1, "Insert Failed");

   //test Update
   int numRowsUpdated = db.Update("Courses", "Credits=5", "CourseNumber='CS 999'");
   System.Diagnostics.Debug.Assert(numRowsUpdated == 1, "Update Failed");

   //test Delete
   int numRowsDeleted = db.Delete("Courses", "CourseNumber='CS 999'");
   System.Diagnostics.Debug.Assert(numRowsDeleted == 1, "Delete Failed");
   numRowsDeleted = db.Delete("Courses", "CourseNumber='CS 000'");
   System.Diagnostics.Debug.Assert(numRowsDeleted == 0, "Delete2 Failed");

   //test Fill
   System.Data.DataTable table = db.Fill("*", "Instructors", null, "Name DESC");
   System.Diagnostics.Debug.Assert(table != null, "Fill Failed");
   System.Diagnostics.Debug.Assert(table.Rows[0][1].ToString() == "Springer-Lund", "Fill Failed");

   //and so on

Testing the BusinessLogicLayer requires a bit more work. Since the methods are designed to work with data from the database,we need to "simulate" a database fill. Once the DataTable is filled, all methods use the in-memory cache of the database data for their work. Therefore to test the classes, we need to fill through code the data into the DataTable.

The code below shows how one can fill a DataTable without actually connecting to a database

   //create a data table, add 4 columns, and add the primary key constraint
   Table = new DataTable("Courses");
    courses.Columns.Add("CourseName", typeof (string));
   courses.Columns.Add("CourseDescription", typeof (string));
   courses.Columns.Add("Credits", typeof (int));
   courses.Constraints.Add(new UniqueConstraint("PrimaryKey", courses.Columns["CourseNumber"], true));

   //Add a row of data manually, column by column
   DataRow newRow = courses.NewRow();
   newRow["CourseNumber"] = "CS 446";
   newRow["CourseName"] = "User Interface Design";
   newRow["CourseDescription"] = "This course covers blah blah blah ";
   newRow["Credits"] = 4;
   courses.Rows.Add(newRow);

   //Or once the Course constructor and the InsertCourse() method are working
   InsertCourse(new Course("CS 101", "Basic Computing", "Description1", 4));
   InsertCourse(new Course("CS 105", "Logic of Programming", "Description2", 4));
   InsertCourse(new Course("MATH 330", "Discrete Math", "Description4", 5));
   InsertCourse(new Course("MATH 260", "Sets and Logic", "Description3", 5));