Tag Archives: MVC

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

Configure MySQL with ADO.NET EF in VS 2013

MySQL, ADO.NET & MVC

Even though we are using a Microsoft framework (MVC) and ADO.NET Entity Framework (EF) we may not want to connect to a Microsoft Database.  In my case I am going to connect to a MySQL database which means I have to configure MySQL for use in my project.

I had to download the latest version of MySQL for Visual Studio installer and once downloaded run the installer and follow the on-screen prompts to install.  I went with the Typical install option.

I then downloaded the latest MySQL connector and ran the installer also using the Typical install option.

Once this is done you can open your project in Visual Studio and right click on the solution in the solution explorer to Manage NuGet Packages… as in Figure 1 below.

Figure 1: Manage NuGet Packages
Figure 1: Manage NuGet Packages

You then go to the Online section and search for MySQL.  You then want to install the MySQL.Data.Entities package, highlighted in Figure 2 below, by clicking install which will then install the NuGet package and it’s dependencies (Figure 3) and once it’s done a green check mark will show that the package installed correctly (Figure 4).

Figure 2: Search MySQL and Select
Figure 2: Search MySQL and Select
Figure 3: Installing MySQL and Dependencies
Figure 3: Installing MySQL and Dependencies
Figure 4: Shows Installed - Close NuGet
Figure 4: Shows Installed – Close NuGet

After you have MySQL configured you need to add the MySQL provider to your Web.config by replacing the Entity Framework tag with the following:

<entityFramework>
     <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6"/>
     <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
     </providers>
</entityFramework>

Then you save the Web.config file and add an ADO.NET Entity Model by adding a new item (Figure 5) to your solution.  The new item is an ADO.NET Entity Model which provides the connection to your database (Figure 6) by selecting Visual C# from the left menu followed by ADO.NET Entity Data Model, giving it a name (in my case TrackerDB) and clicking Add.

Figure 5: Add New Item to Solution
Figure 5: Add New Item to Solution
Figure 6: Select Visual C# ADO.NET Entity Data Model
Figure 6: Select Visual C# ADO.NET Entity Data Model

In the window that appears select the option EF Designer from Database and click Next (Figure 7).  In the window that appears, Figure 8, click the New Connection button to create a connection to your database and select MySQL Database from the list of options, Figure 9.  Make sure to deselect the option to always use this selection and click Continue.

 

Figure 7: Select EF Designer From Database
Figure 7: Select EF Designer From Database
Figure 8: New Connection
Figure 8: New Connection
Figure 9: Select MySQL Database & Uncheck Always use this selection
Figure 9: Select MySQL Database & deselect ‘Always use this selection’

The Connections Properties menu will open where you will need to provide the details for the database server your application is going to connect to, as in Figure 10.  In my case I entered the server name as an ip address since no DNS friendly name exists along with the username and password required to connect to the database server.  You can click the Test Connection button to ensure your information is correct; however, in my case I also noticed that you can attempt to select the database you want to.  If your credentials are incorrect an error message will appear, otherwise a list of databases the username/password combination has access to will appear.    If the connection succeeds click OK, returning you to the previous screen.

Figure 10: Enter MySQL Database Information
Figure 10: Enter MySQL Database Information

As an aside I find it best to create a username/password combination that is unique to each database, rather than one to rule them all, so that if the credentials get compromised only access to a particular database will be granted.  If you have finer grained control over database usernames it is best to only grant a user the rights to do the minimal tasks required.

After clicking OK I received an error message (Figure 11) about the XML declaration.  I clicked OK which brings you back to a screen like Figure 8 above, however, information will be populated under the connection string.  I determined this was caused by me having extra lines, which I had commented out as part of my experimentation, at the top of my Web.Config file.  After deleting these lines making the <?xml …> tag the first line in the file I was able to proceed (canceled, deleted the connection and started from the beginning) without error which brought me back to the screen as seen in Figure 12.  I stuck with the default name provided for the connection string in my Web.Config file and choose yes for storing my password since the account only has the writes required.  I may decide to change this later and how you wish to handle this is up to you.  You then want to click the Next button.

Figure 11: XML Error Message - Click OK
Figure 11: XML Error Message – Click OK
Figure 12: Yes/No to Password and Connection Setting
Figure 12: Yes/No to Password and Connection Setting

Update (Aug 22, 2014): You may want to provide a better name for your connection settings in your Web.Config file as that acts as your context.  That is if your database is called tracker then db_nameEntities could be TrackerContext.

On the next screen you will see “Retrieving database information, please wait..” followed by the various elements you can select to be included in your model.  In my case I selected only Tables as I currently have no Views or Stored Procedures and Functions for my database, as seen in Figure 13.  I left the default to pluralize or singularize generated object names as well as to include foreign keys since I want my model to include the foreign keys.  Click Finish to complete the Entity Data Model Wizard.

Update (Aug 22, 2014): You may want to provide a more friendly model name for your database model, again if database is called Tracker you could use TrackerModel.

Figure 13: Select Database Object and Finish
Figure 13: Select Database Object and Finish

You will receive the message “Running this text template can potentially harm your computer. Do not run if you obtained it from untrusted source.. ”, as a reminder that you are about to execute someone else’s code which you should only do if you trust the source of that code.  I am confident in this case that I can run this template so I click OK, which I had to do a couple of times.  If you want you can disable these warnings by going to Tools Options… Text Templating and setting the Show Security Message to False.

Figure 14: Click OK to Run Text Template
Figure 14: Click OK to Run Text Template

Under Data Connections in the Solution Explorer I able to see the database I added.  At first it showed as having a red ‘X’ next to the database icon, but once I expanded the connection to see the contents, Figure 15, the icon refreshed and the database contents were displayed which signifies that I now have a data connection established to my database for use with my application.

Figure 15: View Database Contents to Confirm
Figure 15: View Database Contents to Confirm

Finally now that everything is configured you are able to see an ERD diagram of you database open up as well as the database schema in the Solution Explorer that you can navigate, shown in Figure 16.

Figure 16: Tracker DB in Solution Explorer and Diagram
Figure 16: Tracker DB in Solution Explorer and Diagram

The connection string added to my Web.Config file under the <connectionStrings> tag looks similar to below, but with the actual values for server, user id, and database.  The reason for the connection string (with the space) and the &quot sections is because it is escaping the connection string generated for the database I am connecting to and putting it into the web configuration file under the connectionString attribute.  I will have to see if this works as generated by the wizard or if I will have to modify its results.

<add name="tracker_appEntities" connectionString="metadata=res://*/TrackerDB.csdl|res://*/TrackerDB.ssdl|res://*/TrackerDB.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=192.168.2.1;user id=user_name;password=db_password;persistsecurityinfo=True;database=db_name&quot;" providerName="System.Data.EntityClient" />

Note:  You may not run into this, however, there were times throughout when Visual Studio would go into a non-responsive state (sometimes flashing in and out of them) throughout the process.  I found that I just had to be patient and give the application time to complete the necessary tasks.