This blog post chronicles the development of a new version of my web site to manage my wine inventory. For years I’ve actually managed my wine inventory on the web and allowed others to see that I had… Not the same details I get, but some information.
I’d like to revamp the Zachry’s Heritage site I maintain, but I’d like to learn on something less critical first. So I’m going to start with my wine inventory… Only used by me, but still used frequently so it will get tested.
Moving My Database Locally
4/17/2009
My database actually sits on the machines at the hosting site. Therefore, if I want to refactor that some, I need to pull the data over. I’m not as impressed with the latest version of DB Pro (GDR). That’s the capability to work with databases inside Visual Studio 2008. In the early releases I could pull over the database without any issues. I know they worked on performance, but now it seems the task is left to me to pull the tables over in the right order. In other words, if the table that has a foreign key relationship is moved before its parent table it will give an error. That didn’t happen in previous versions. Hopefully I can finish this quickly.
4/18/2009
I did get all the data except two tables moved over. I’m not sure why I get an error on one, and I don’t need the “Hits” table (that’s too much data).
Starting The Wine Model
My current wine model developed from a spreadsheet… then lived in Microsoft Access for several years… the then was ported to Microsoft SQL Server. However, when I ported, I did not revisit the data model. Today I did and hopefully it will make it easier to work with in the future. Here is a comparison of the two models:
Old Model
New Model
So now I can actually have two wine coolers (aka, racks), and then there are locations for each. With this model it will also be easier to find empty slots as I can remove the bottle from a slot when it is consumed. I was tempted to make the various names a lookup as well (Region, SubRegion, Vineyard) but those lists will get too long to be useful and some vineyards actually change their names slightly over time (Colorado Mountain Vineyards became Colorado Vineyards for example… ). I’m actually interested in what the name was on the bottle.
Challenges over the past two weeks (5/2/2009)
MVC on IIS 6.0
MVC works great on my Vista machine… And works great on IIS 7.0 Integrated Mode. But IIS 7.0 Classic or IIS 6.0 is a different matter. I spent about 8 hours investigating making the site work on IIS 6.0 in a subdirectory. I finally decided to try against IIS 7.0. Since my current hosting company doesn’t provide that, I noticed that Go Daddy does. So, I created a new site at Go Daddy and it was working in pretty short order. I did still work to make the site functional in a subdirectory. So from the time I called Go Daddy until the time I had my site up and running was less than 4 hours!
MVC in a Sub Directory
There is a great post that provides code about getting the proper path : http://forums.asp.net/p/1240199/2271109.aspx#2271109 . (I of course ported it to C#) I do wish that MVC could more easily detect the right paths. For now I’ll have to do some extra work every time I need to access a file like the css files or any images.
Editing Relationships
There appears to be no easy way to edit relationships in MVC/EF! Here’s the scenario: I want to add a new bottle of wine to the inventory had have Varietal (Cabernet Sauvignon, Merlot, Zinfandel, etc) and the Location as dropdown lists. Of course you don’t have access to the IDs of the navigation properties, so you can’t just set that. I could not find any decent answers to this on the web… Seems that everyone has stopped short of that problem. Here is my current HACK:
In the partial class WineBottle.cs I have these properties:
// Entity Framework/MVC Note: Since the EF does not have a property
// for the Id or key of a Navigation Property, the MVC pattern does
// not really have a way to set the Navigation Property. They only way
// that I’ve found to “trick this” is to have my own version that
// MVC can bind to. Then I get the new value and manually change the
// Navigation Property.
public int? LocationIdValue { get; set; }
public int? VarietalIdValue { get; set; }
In the View AddBottle.aspx I have this:
<p>
<label for=”VarietalIdValue”>
Varietal:</label>
<{f073afa9b3cad59b43edffc8236236232bb532d50165f68f2787a3c583ed137f}= Html.DropDownList(“VarietalIdValue”,
(SelectList)ViewData[“VarietalList”],
“– select varietal –“){f073afa9b3cad59b43edffc8236236232bb532d50165f68f2787a3c583ed137f}>
</p>
And finally, in the WineController I have this code:
public
ActionResult AddBottle()
{
ViewData[“EmptyLocationList”] = GetEmptyLocationList(-1);
ViewData[“VarietalList”] = GetVarietalList(-1);
return View();
}
[AcceptVerbs(HttpVerbs.Post)]
public
ActionResult AddBottle(WineBottle bottleToCreate)
{
if (!ModelState.IsValid)
return View();
try
{
// Now set the navigation properties using a trick…
WineLocation editLocation =
_dbContext.WineLocationSet.FirstOrDefault<WineLocation>(loc => loc.LocationId == bottleToCreate.LocationIdValue);
bottleToCreate.WineLocation = editLocation;
WineVarietal editVarietal =
_dbContext.WineVarietalSet.FirstOrDefault<WineVarietal>(v => v.VarietalId == bottleToCreate.VarietalIdValue);
bottleToCreate.WineVarietal = editVarietal;
_dbContext.AddToWineBottleSet(bottleToCreate);
_dbContext.SaveChanges();
// Repopulate the DropDownLists
ViewData[“EmptyLocationList”] = GetEmptyLocationList(-1);
ViewData[“VarietalList”] = GetVarietalList(-1);
return View();
}
catch
{
return View();
}
}
Some things that didn’t for example was to include the Navigation Property of Varietal in the view so that I had Html.DropDownList(“WineVarietal.VarietalId”, (SelectList)ViewData[“VarietalList”], “– select varietal –“. If someone finds a better HACK – or perhaps the right way to do it I would sure like to know. I think that changes are required to at least the Entity Framework, if not both EF and MVC in order for this to be practical.
Next Step
Next I need to sort out the permissions issue. I don’t see an easy way to leverage the Security Trimming of the ASP.NET XmlSiteMapProvider…