Category Archives: Tracker

Tracker is an MVC5 application for tracking various things (i.e. golf scores & novels read) and presenting information in a dashboard, like your handicap for a golf course, number of books in a year, etc.

Database Design v3 – Quilt

Tracker Database v3 - Quilting
Tracker Database v3 – Quilting

I’m back!  This project has been put on the back burner for a while, but now that it is fall and winter is soon to follow I can’t think of a better time to ramp up development again, especially since a new requirement for my tracker application has arisen!  In chatting with my girlfriend, who is an avid quilter, would like a place to track the quilts she has completed.  Currently this is done by taking a picture of the quilt and storing just the image on her laptop.  She would like to keep track of other information as well, which lead to the addition of a Quilt table (first eight rows) and a Quilt Image Path table (remaining rows) including the following columns:

QuiltIDint(11)Primary Key of Quilt Table, Auto Increment (Also FK of QuiltImagePath table)
UserIDint(11)Foreign Key from User table
Lengthdouble(5,2)Will use inches as unit
Widthdouble(5,2)Will use inches as unit
Patternvarchar(255)The name of the pattern used in creating the quilt
Recipientvarchar(200)Who the quilt was made for
CompletedOndateWhen quilt was finished
Costdecimal(6,2)Total cost of quilt, includes materials, etc.
ImagePathvarchar(255)Path, with file name, to the quilt relative to project
ImagePathIDint(11)Primary Key of Quilt Image Path table, Auto Increment

I thought this would be a nice addition, as it also adds in another technical element I haven’t had to deal with in any of the other items I am tracking.  This is the addition of being able to upload, store, reference, and display an image related to the content.  Also have to implement the functionality to delete a record along with corresponding images.  I believe, without attempting an implementation yet, I am going to accomplish this by:

  • Storing the images in a folder on disk (this folder will be contained within my VS project so I know how to properly reference the folder)
  • Rename the file on upload after record is created to include primary key in file name for if need to cross reference for any reason, also ensures unique names are being stored
  • Storing the relative path (from project perspective) with file name in the database (FilePath column)
  • Implement deleting of a record to also include deleting of the file, on missing files skip and continue with deletion of record

My goal for the winter is to have an initial implementation for all the features I currently have slated for my tracker application.

Separation of Concerns: View Model & Database Model

I noticed how when I used scaffolding to generate my MVC5 controller with views using Entity Framework this would only allow me to specify a model representing a table in my database, which is not what I want.  In order to have true separation of concerns I wanted to:

  • Use view models for my views that get validated prior to any database updates
  • Use database models, auto generated, to represent my database and contain the results of queries

I accomplished this by implementing various services which handle the communication with the database.  The controller contains an instance of the service it requires to communicate with the database.  The service queries the database and returns the results to the controller, which then sends the information to the view utilizing the appropriate format.

When querying (performing  select) the database:

  • Resulting database model(s) are converted to the corresponding view model(s), and
  • Returned to the controller, where
  • They are passed through to the view for display

When updating an entry:

  • View Model is passed from the view to the controller
  • Validated using an appropriate validator, I built with Fluent Validation
  • If invalid, error message is returned to the view and displayed
  • If valid, view model is passed to the service to update the corresponding tables in the database
  • In either case if errors occur then the model state is updated and the default view for the action is returned

An example of this is how I manage reading lists.  I have my reading list controller (ReadingListController class) which handles user interactions and manipulates the model.  You can see below that the controller class contains minimal logic and utilizes helper classes to perform the logic. A validator class is used for validating data being passed from the view, when creating/editing a reading list, using Fluent Validation.  This ensures valid data is being entered into the database rather than relying on exceptions being returned on insert/updating of the database due to ill formatted values.

To provide separation of concerns services are used to interact with the database model.  Utilizing a service also means that the controller does not need to know the implementation of the service and how to interact with the database so by extending the particular service interface you can change the implementation, i.e. if I decided to not utilize the Entity Framework anymore and instead use NHibernate.

In the case of reading lists two services are used, the reading list service and the author service. The author service is utilized here so that we do not duplicate code for retrieving authors who have books and specific books for that author based on selection.  I am still working on the view implementation of this.  The reading list service contains the implementation for retrieving:

  • List of reading lists for a particular user (List title, number of novels in the list)
  • A specific reading list (for editing/deleting)
  • Book information for a book in a particular reading list
  • Details for a reading list (Book titles, year, whether they are read, and date finished)

Also an implementation for editing, creating, and deleting a reading list.

[Authorize]
public class ReadingListController : Controller
{
	private readonly IReadingListService _service;
	private readonly IAuthorService _aService;
 
	public ReadingListController() : this(new ReadingListService()) { }
 
	public ReadingListController(IReadingListService service)
	{
		_service = service;
	}
 
	// GET: ReadingList
	public ActionResult Index()
	{
		int userId = GetUserId();
 
		var readingLists = _service.GetReadingListsForUser(userId);
 
		if(readingLists == null)
		{
			ModelState.AddModelError("", "An error occurred in retrieving your reading lists.");
			return View();
		}
 
		return View(readingLists);
	}
 
	// GET: ReadingList/Details/5
	public ActionResult Details(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var readingListDetails = _service.GetReadingListDetails(id ?? 0);
 
		if (readingListDetails == null)
		{
			return HttpNotFound();
		}
 
		return View(readingListDetails);
	}
 
	// GET: ReadingList/Create
	public ActionResult Create()
	{
		return View();
	}
 
	// POST: ReadingList/Create
	// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
	// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
	[HttpPost]
	[ValidateAntiForgeryToken]
	public ActionResult Create([Bind(Include = "ListTitle")] ReadingListModel readingListModel)
	{
		readingListModel.UserId = GetUserId();
		var rlValidator = new ReadingListValidator();
		var results = rlValidator.Validate(readingListModel);
 
		if (results.IsValid)
		{
			var success = _service.CreateReadingList(readingListModel);
 
			if (!success)
				return RedirectToAction("Failed", new FailedModel { Message = "Unable to create reading list, please try again.", Action = "Index" });
 
			return RedirectToAction("Index");
		}
 
		return View(readingListModel);
	}
 
	public ActionResult Failed(FailedModel fm)
	{
		return View(fm);
	}
 
	// GET: ReadingList/Edit/5
	public ActionResult Edit(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var readingList = _service.GetReadingList((int)id);
 
		if(readingList != null)
			readingList.UserId = GetUserId();
 
		//ViewBag.UserID = new SelectList(db.Users, "UserID", "Username", readingList.UserID);
		return View(readingList);
	}
 
	// POST: ReadingList/Edit/5
	// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
	// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
	[HttpPost]
	[ValidateAntiForgeryToken]
	public ActionResult Edit([Bind(Include = "ReadingListID,ListTitle,UserID")] ReadingListModel readingListModel)
	{
		var rlValidator = new ReadingListValidator();
		var results = rlValidator.Validate(readingListModel);
 
		if (results.IsValid)
		{
			var success = _service.EditReadingList(readingListModel);
 
			if (!success)
				return RedirectToAction("Failed", new FailedModel { Message = "Unable to edit reading list, please try again.", Action = "Index" });
 
			return RedirectToAction("Index");
		}
 
		return View(readingListModel);
	}
 
	// GET: ReadingList/Delete/5
	public ActionResult Delete(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var rlm = _service.GetReadingList((int)id);
 
		if (rlm == null)
		{
			return HttpNotFound();
		}
 
		return View(rlm);
	}
 
	// POST: ReadingList/Delete/5
	[HttpPost, ActionName("Delete")]
	[ValidateAntiForgeryToken]
	public ActionResult DeleteConfirmed(int id)
	{
		_service.DeleteReadingList(id);
		return RedirectToAction("Index");
	}
 
	//Get: ReadingList/DeleteBook/5/4
	public ActionResult DeleteBook(int? readingListId, int? bookId, string bookTitle)
	{
		if (readingListId == null || bookId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var bm = _service.GetReadingListBookInfo((int)bookId, (int)readingListId);
 
		if (bm == null)
		{
			return HttpNotFound();
		}
 
		return View(bm);
	}
 
	// POST: ReadingList/DeleteBook/5
	[HttpPost, ActionName("DeleteBook")]
	[ValidateAntiForgeryToken]
	public ActionResult DeleteBookConfirmed(int readingListId, int bookId)
	{
		_service.DeleteBookFromList(bookId, readingListId);
		return RedirectToAction("Details", new { id = readingListId });
	}
 
	// Get: ReadingLis/AddBook/5
	public ActionResult AddBook(int? readingListId, string rlTitle)
	{
		if(readingListId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var rlam = new ReadingListAuthorsModel
		{
			Authors = _aService.GetAuthorsWithBooks().ToList(),
			ListTitle = rlTitle,
			ReadingListId = (int)readingListId
		};
 
		return View(rlam);
	}
 
	public ActionResult Books(int authorId)
	{
		var books = _aService.GetBooksForAuthor(authorId);
 
		return Json(books, JsonRequestBehavior.AllowGet);
	}
 
	//Get: ReadingList/MoveBook/5/4
	public ActionResult MoveBook(int? readingListId, int? bookId, string bookTitle)
	{
		if (readingListId == null || bookId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var bm = _service.GetReadingListBookInfo((int)bookId, (int)readingListId);
		var usersReadingLists = _service.GetReadingListsForUser(GetUserId()).Where(x => x.ReadingListId != readingListId);  //Can't move book to itself
 
		if (bm == null || usersReadingLists == null)
		{
			return HttpNotFound();
		}
 
		bm.ReadingLists.ToList().AddRange(usersReadingLists);
 
		return View(bm);
	}
 
	private int GetUserId()
	{
		int userId;
		userId = int.TryParse(User.Identity.Name.Split(':')[0], out userId) ? userId : 0;
		return userId;
	}
}

Validation for creating a new reading list is shown below.  I chose this example since it is a very simplistic validator, to implement.  The only criteria is that the list title has to be 1-255 characters long and the returned result for the number of novels in a list should be greater than or equal to zero, the highlighted rows.

using FluentValidation;
using tracker.Models.View.ReadingList;
 
namespace tracker.Validators.Novels
{
    public class ReadingListValidator : AbstractValidator<ReadingListModel>
    {
        public ReadingListValidator()
        {
            RuleFor(a => a.ListTitle).Length(1, 255).WithMessage("List name needs to be between 1 and 255 characters.");            RuleFor(a => a.NumberOfNovels).GreaterThanOrEqualTo(0).WithMessage("Number of novels was less than 0, please contact admin.");        }
    }
}

In order to utilize my view model instead of the database model for each view I changed the @model parameter to be my view model instead of the database model.   This ensures that the model is populated within the database session prior to being passed back to the view.  An example is the details view for a reading list:

@model tracker.Models.View.ReadingList.ReadingListDetailsModel

MySql.Data Reference

I previously wrote a post about configuring MySQL for use with a Microsoft MVC application.  I noticed after publishing that my application could not connect to the database and returned the following exception:

System.ArgumentException: The ADO.NET provider with invariant name ‘MySql.Data.MySqlClient’ is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details. —> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.

In order to solve this I had to do two things:

  1. In the Solution Explore under ‘References’ set Copy Local to true for the MySql.data reference
  2. Add the database provider configuration to your Web.config as seen below, with your particular version
<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" 
         invariant="MySql.Data.MySqlClient" 
         description=".Net Framework Data Provider for MySQL"  
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

The reason for #2 is that I am trying to run my code on a machine where the provider is not installed, so I have to specify the provider in my configuration file and then installation adds it to the machine.config registering the provider on the remote server.

Tracker Database Design

Tracker Database v.1

What is an application without some sort of storage mechanism behind the scenes?  Above represents my database design which I am going to go into more details about.

At the heart of the database is the User table which stores user information and associates a reading list, measurement, or golf round with a corresponding user such that users can only see their data when logged in.  This also makes it easy to track additional information by adding the corresponding tables which will then get associated with a user if I choose to expand the application at a later time.  The other tables which stores, what I will call public data, is visible to all users to avoid duplication of data.  This includes golf course information (Golf Course, Address, Region and Course Par Information tables) and book information (Author, Book, and what authors are associated with which book). The measurement and course par info tables each have a ‘special’ column.  The measurement table contains a column called Type which takes a number from 1-4.  Since this never changes the application can handle the numbers, rather than creating an additional static look-up table in the database.  The number representation is as follows:

  1. Weight
  2. Neck
  3. Waist
  4. Hips

The course par information table contains a column called IsCNine to accommodate courses that have more than 18 holes with a format of: (Course Color)*( )(Front|Back).  Where there can be 0 or more colors   Examples of this would be:

  • White Front
  • Blue Front
  • Blue Back
  • Front
  • Back

This format allow for courses which provide 27 holes of golf as they typically have a white, blue, and red course, each with 9 wholes.  I’d imagine both would also have a front and back nine.  I have never played at a 27 whole golf course but I try to develop for future (or potential) possibilities.

I’ll explain the user profile dynamic content and dashboards text boxes in the diagram in my next post about the dynamic content and dashboards I plan to provide in my application.

Why Develop a Tracker Web Application?

Ahh, it is so nice to have completed my Masters and be able to focus on some personal projects I have been pondering.  The first was this website and the second is a tracker application.  I have been using an Excel spreadsheet I store on Google Drive for tracking various things, which does work pretty well for just maintaining one long list of, for instance, the novels I have read and the novels I’d like to read.  When it comes to tracking items with more complex items like rounds of golf played and at which course they were played dealing with the relationships becomes a little more cumbersome though I could also implement this in a spreadsheet; however, I thought this would be a great opportunity to learn more about web applications.

The technologies I will be using to develop my web app are C#.NET, Entity Framework, and MVC for the main framework of the application.  I will also be utilizing CSS to style my application forms and views when necessary.  I am going to attempt utilizing a MySQL back-end, since my web host provides more storage for MySQL databases, which requires additional configuration from utilizing MS SQL when using Microsoft technologies.

Security is an important aspect of any application making it necessary to implement a login system for my web app.  The login form will be the first page a user sees when accessing the site.  The web app will test, when access to other pages are attempted, if a valid user is logged in prior to granting access to any other page and redirect to the login page for unauthorized users.

Once the basic create, read, update, and delete (CRUD) functionality is implemented I am going to generate various dashboards for the users to provide statistics and further information at a glance based on database records relevant to the user.  This will include elements like what was the last novel you read (and completion date) and the number of novels you read over a specified time frame.  Related to golfing this would include calculating your overall golf handicap, average score for 9 and 18 whole games, and a further drill down to information per course will also be available.  I’m thinking at least 5 rounds will have to be played for a handicap to be calculated for a given category and averages will always show as only courses you have played will be presented as options.  I may also show the number of different courses you have played and a map of where they are located.  Additional technologies I plan to use in accomplishing these tasks are Google Charts (Google Visualization API) and Google Maps API.