Monday, October 27, 2008 -
I posted a couple of weeks back that I created a prototype Addin for Visual Studio that would scaffold your MVC site for you using Linq To Sql as the data access bits. If you didn't get a chance to watch the screencast - have a look.
Last week I updated the bits to Beta 1 and also fixed some things - namely:
There's a lot more that needs to be done, but I want to build this thing with your feedback so do let me know what you think.
Some Detail (Actually, TONS of Detail)
The concept of the "scaffold" comes from Rails and it's the idea that outputting some basic CRUD pages will help you get started on creating your site. You can jam in some test data and then begin changing the pages as you need to; locking down the Edit/New pages for admins only etc - it's up to you.
The Addin is designed to work with the project structure as set forth by the MVC Beta 1 template - in this case I've created on from scratch called:
The next thing you need is a Linq To Sql DBML class. The scaffold bits need this file so it knows what your data access bits look like. This is an important part of the equation here - the Addin doesn't look at your database, it uses your Linq To Sql definition file.
Yes, when SubSonic 3.0 gets a bit closer I'll build make sure to offer that as a choice, and yes I will (eventually) make this work with EF.
The next step, then, is to add some Linq To Sql:
... and as always I'm using Northwind. One day I'll change that addiction...
Once the DBML file is added (I'm using all the tables in Northwind - you don't have to do this... use whatever you like) I then right-click on a folder in my solution, or on the Project itself:
This brings up the Scaffold menu. You can choose the things you want SubSonic to generate for you as well (note that SubSonic will never overwrite anything):
Select your table and you're good to go! In this case, contrary to what the image says, I selected "Products" and clicked "go" - this is what's created:
This picture is a bit big - sorry about that - but I wanted to make sure you know what get's added to your project:
The Code
I've tried to make sure that what gets generated follows really simple, testable "conventions". These conventions are:
The Repository
To see this in action, let's take a look at the Data Access bits created for the Product class. The first thing that is created is the Interface:
namespace MvcApplication6.Data { /// <summary> /// And interface for mocking the Product Repository /// </summary> public interface IProductRepository { void Delete(Product item); void Delete(int itemID); IQueryable<Product> GetProducts(); IQueryable<Order_Detail> GetOrder_Details(); IQueryable<Category> GetCategories(); IQueryable<Supplier> GetSuppliers(); void Save(Product item); } }
Notice here that I'm using IQueryable<Product> as I do in the MVC Storefront. I've become a big fan of this pattern for its simplicity. Also notice that the namespace is set to "[APPLICATION].Data".
Next up - the Sql implementation, which uses Linq To Sql:
/// <summary> /// A Linq To Sql Repository implementation /// </summary> public class SqlProductRepository : IProductRepository { NorthwindDataContext _db; public SqlProductRepository() { _db = new NorthwindDataContext(); } #region Foreign Keys public IQueryable<Order_Detail> GetOrder_Details() { return from items in _db.Order_Details select items; } public IQueryable<Category> GetCategories() { return from items in _db.Categories select items; } public IQueryable<Supplier> GetSuppliers() { return from items in _db.Suppliers select items; } #endregion /// <summary> /// Returns all Products, which you can then requery. /// Note: this is an IQueryable list - it's not executed /// until you enumerate it. /// </summary> public IQueryable<Product> GetProducts() { return from items in _db.Products select items; } /// <summary> /// Saves the Product to the Database. /// </summary> public void Save(Product item) { //is this new or existing? var existingItem = (from x in _db.Products where x.ProductID == item.ProductID select x).SingleOrDefault(); if (existingItem == null) { _db.Products.InsertOnSubmit(item); } _db.SubmitChanges(); } /// <summary> /// Deletes the Product from the Database /// </summary> /// <param name="item"></param> public void Delete(Product item) { _db.Products.DeleteOnSubmit(item); _db.SubmitChanges(); } /// <summary> /// Deletes the Product from the Database, using the passed-in ID /// </summary> public void Delete(int itemID) { _db.Products.DeleteAllOnSubmit(from x in _db.Products where x.ProductID == itemID select x); _db.SubmitChanges(); }
Not much to add here - it's about as simple as you can get. Simplicity is my goal here as I don't want to get in your way!
The Services
The Business Logic is captured in the ProductService class, which builds on top of the IProductRepository. Notice the injectable constructor for testing purposes:
/// <summary> /// A class for holding Business Logic for Products /// </summary> public class ProductService:IProductService { IProductRepository _ProductRepository; /// <summary> /// Injectable constructor /// </summary> public ProductService(IProductRepository ProductRepository) { _ProductRepository = ProductRepository; } /// <summary> /// Gets a Product By ID /// </summary> /// <param name="id"></param> public Product GetProduct(int id) { return (from items in _ProductRepository.GetProducts() where items.ProductID==id select items).SingleOrDefault(); } /// <summary> /// Returns all Products /// </summary> public IList<Product> GetProducts() { return _ProductRepository.GetProducts().ToList(); } /// <summary> /// Executes a paged query using server-side paging /// </summary> /// <param name="pageIndex">Zero-based index of the current page</param> /// <param name="pageSize">Size of all pages</param> /// <returns>PagedList</returns> public PagedList<Product> GetProducts(int pageIndex, int pageSize) { int skipRecords = pageIndex * pageSize; var qry = from items in _ProductRepository.GetProducts() select items; return new PagedList<Product>(qry, pageIndex, pageSize); } /// <summary> /// Searches for Products by ProductName that start with the passed value /// </summary> public IList<Product> Search(string query) { return (from items in _ProductRepository.GetProducts() where items.ProductName.StartsWith(query) select items).ToList(); } /// <summary> /// Returns all Order_Details /// </summary> public IList<Order_Detail> GetOrder_Details() { return _ProductRepository.GetOrder_Details().ToList(); } /// <summary> /// Returns all Categories /// </summary> public IList<Category> GetCategories() { return _ProductRepository.GetCategories().ToList(); } /// <summary> /// Returns all Suppliers /// </summary> public IList<Supplier> GetSuppliers() { return _ProductRepository.GetSuppliers().ToList(); } /// <summary> /// Saves the item to the Repository /// </summary> /// <param name="item"></param> public void Save(Product item) { //validations go here... _ProductRepository.Save(item); } /// <summary> /// Deletes the item from the Repository /// </summary> /// <param name="id"></param> public void Delete(int id) { //validations go here... _ProductRepository.Delete(id); } }
The Controller
These things can then be passed into the Controller (or you can inject them yourself when testing):
[HandleError]
public class ProductController : Controller
{
IProductService _ProductService;
/// <summary>
/// Default constructor - creates SqlRepository
/// </summary>
public ProductController():this(new ProductService(new SqlProductRepository())) {}
/// <summary>
/// Constructor overload for testing
/// </summary>
public ProductController(IProductService ProductService)
{
_ProductService = ProductService;
ViewData["ClassName"] = "Product";
ViewData["Table"] = "Products";
}
#region Utility Methods
/// <summary>
/// Creates SelectLists for Foreign-keys
/// </summary>
void LoadForeignKeyLists(Product item)
{
var order_details = _ProductService.GetOrder_Details();
ViewData["ProductID"] = new SelectList(order_details, "ProductID", "ProductID", item.ProductID);
var categories = _ProductService.GetCategories();
ViewData["CategoryID"] = new SelectList(categories, "CategoryID", "Description", item.CategoryID);
var suppliers = _ProductService.GetSuppliers();
ViewData["SupplierID"] = new SelectList(suppliers, "SupplierID", "HomePage", item.SupplierID);
}
#endregion
/// <summary>
/// Default view
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
return View();
}
/// <summary>
/// Lists all Products
/// </summary>
public ActionResult List()
{
int pageSize = 20;
int currentPage = 1;
int totalPages = 1;
int totalCount = 0;
IList<Product> items;
if (Request.Form["pg"] != null)
{
int.TryParse(Request.Form["pg"], out currentPage);
}
//search support
if (Request.Form["q"] != null)
{
string search = Request["q"];
items = _ProductService.Search(search);
}
else
{
items = _ProductService.GetProducts(currentPage - 1, pageSize);
PagedList<Product> paged = (PagedList<Product>)items;
totalCount = paged.TotalCount;
totalPages = paged.TotalPages;
}
ViewData["TotalRecords"] = totalCount;
ViewData["TotalPages"] = totalPages;
ViewData["PageSize"] = pageSize;
ViewData["CurrentPage"] = currentPage;
return View(items);
}
/// <summary>
/// Shows an individual Product
/// </summary>
public ActionResult Show(int id)
{
var item = _ProductService.GetProduct(id);
return View(item);
}
/// <summary>
/// Default form view for New
/// </summary>
[AcceptVerbs("GET")]
//[Authorize(Roles="Administrator")]
public ActionResult New()
{
var item = new Product();
//initialize dates
item.DateCreated = DateTime.Now;
item.CreatedOn = DateTime.Now;
item.ModifiedOn = DateTime.Now;
LoadForeignKeyLists(item);
return View(item);
}
/// <summary>
/// Handles New post-back
/// </summary>
[AcceptVerbs("POST")]
//[Authorize(Roles="Administrator")]
public ActionResult New(FormCollection form)
{
var item = new Product();
try {
UpdateModel(item, new[] {
"ProductID",
"ProductName",
"SupplierID",
"CategoryID",
"QuantityPerUnit",
"UnitPrice",
"UnitsInStock",
"UnitsOnOrder",
"ReorderLevel",
"Discontinued",
"DateCreated",
"ProductGUID",
"CreatedOn",
"CreatedBy",
"ModifiedOn",
"ModifiedBy",
"Deleted"
});
_ProductService.Save(item);
TempData["Message"] = item.ProductName + " Created";
return RedirectToAction("New");
} catch(Exception x) {
TempData["ErrorMessage"] = "Oops! " + item.ProductName + " wasn't saved: "+x.Message;
LoadForeignKeyLists(item);
return View(item);
}
}
/// <summary>
/// Default form view for Edit
/// </summary>
[AcceptVerbs("GET")]
//[Authorize(Roles="Administrator")]
public ActionResult Edit(int id)
{
var item = _ProductService.GetProduct(id);
LoadForeignKeyLists(item);
return View(item);
}
/// <summary>
/// Handles post-back for Edit
/// </summary>
[AcceptVerbs("POST")]
//[Authorize(Roles="Administrator")]
public ActionResult Edit(int id, FormCollection from)
{
var item = _ProductService.GetProduct(id);
try {
UpdateModel(item, new[] {
"ProductID",
"ProductName",
"SupplierID",
"CategoryID",
"QuantityPerUnit",
"UnitPrice",
"UnitsInStock",
"UnitsOnOrder",
"ReorderLevel",
"Discontinued",
"DateCreated",
"ProductGUID",
"CreatedOn",
"CreatedBy",
"ModifiedOn",
"ModifiedBy",
"Deleted"
});
_ProductService.Save(item);
TempData["Message"] = item.ProductName + " Saved";
return RedirectToAction("Edit");
} catch(Exception x)
{
TempData["ErrorMessage"] = "Oops! " + item.ProductName + " wasn't saved - "+x.Message;
LoadForeignKeyLists(item);
return View(item);
}
}
/// <summary>
/// Handles post-back for Delete
/// </summary>
//[Authorize(Roles="Administrator")]
public ActionResult Delete(int id)
{
var item = _ProductService.GetProduct(id);
string itemName = item.ProductName;
try {
_ProductService.Delete(id);
TempData["Message"] = itemName + " Deleted";
return RedirectToAction("List");
} catch (Exception x) {
TempData["ErrorMessage"] = "Error deleting Product: " + x.Message;
LoadForeignKeyLists(item);
return View("Edit",item);
}
}
}
Customization
When you install the Addin a directory will be created in C:\Program Files\SubSonic\SubSonic Makai and in there will be the binary bits that need to run as well as a folder called "Templates". These are simple token-replacement templates (for now) that you can modify as needed to fit your project.
I don't have a way to modify the "orchestration" aspect yet - in other words allowing you to customize the process or where things go (simply because I don't know how to do this). The only thing you can change right now is the code that's created.
To do this, create a directory in your project root called "_Templates" and put your custom template in there. The name of this template matters!
Installation
Ideally everything should "just work" - but I've had some issues with the installer not including required binaries (like SubSonic 2.1 of all things). I've tested and re-tested it so "it works on my machine" but... well that hasn't served me so well :).
The installer assumes you're running Vista (yah I know - I had to make a choice) and so tries to put the binaries into your Addin folder, which it thinks should be at "[PersonalFolder]\Visual Studio 2008\Addins" ("C:\Users\Rob\Visual Studio 2008\Addins" for example). Obviously, if you're running XP, that won't work for you.
If you're having ANY issues with the installer - be sure you grab the binaries installer and drop them into your Visual Studio 2008\Addins folder (you might have to create this folder manually).
Summary and Download
As you can see this thing works best when you're doing things from scratch - that's what it's designed to do. I still consider this a "prototype" and I would really love to get your feedback!
Download the Installer Here (MSI - just click and run)
Download the binaries Here (DLL's and Addin file - unzip into your Visual Studio\Addins folder, wherever it is)
later this week - want to investigate some ideas...
Excellent work Rob, really enjoying all the stuff on your site and the videos are priceless (I've become a junkie).
in LinqController.aspx
void LoadForeignKeyLists(#CLASS# item)
{
#FKLOADERS#
}
Basically, i have a one to many table where the GroupType table with a primary key of "Id" is related to Group and the group column is GroupTypeId
Here is incorrect code that was generated:
var grouptypes = _GroupService.GetGroupTypes();
ViewData["Id"] = new SelectList(grouptypes, "Id", "OrganizationTypeName", item.Id);
and in View:
So i changed to this in controller and it works fine
var grouptypes = _GroupService.GetGroupTypes();
ViewData["GroupTypeId"] = new SelectList(grouptypes, "Id", "OrganizationTypeName", item.Id);
I think the issue is that Group has primary key named "Id" and so does GroupType, but the foreign key column on Group is name GroupTypeId
I could not find the source for this in the template, the part that generates #FKLOADERS#. I would love to see the source on this project as i could use this and it feels a little bit more in control that Dynamic Data, although Dynamic Data has many features that kick ass.
thanks and great work!
Jim
I Reinstall but this solution don´t work for me...
And maybe update this to work with the technology you've included in your template, structuremap, etc.
Talking about that, would be nice to see a post of yours about the new StructureMap 2.5, since you use it in your template and in Storefront, there were really nice changes. I would to see your comments on that.
Regards!
I reinstall ASP.NET MVC Beta 1 and the pluggin starts works fine ! Now I will dive into it.
Cool!
Bruno Caimar
2 - I would love to know more too :)
Two questions:
1. Will the add-in work if your project is structured more akin to the proposed changes you made in an earlier blog post ... where controllers, models and views were under an app directory? If not, is there a way to customize it so it will? Maybe even adding an option for locating those folders instead of assuming a given structure???
2. As I'm building out a new project on mvc + subsonic ... I'd love to know more about when subsonic 3 will be out (in any form) so I can use your scaffolding generator here.
Thanks again
When I tried it on one of my tables that is without a primary key (no good excuse for that) I got an error message that was not very descriptive: Index was out of range.
Apart from that: really great stuff!
The bug i found was, i had table in my database with a column called "PostId" and it also exist as "PostId" in my Linq to Sql DBML file. The problem was, that fieldname was called "PostID" in the generated "SqlPostRepository.cs" file.
///
/// Saves the Post to the Database.
///
public void Save(Post item)
{
//is this new or existing?
var existingItem = (from x in _db.Posts
where x.PostID == item.PostId
select x).SingleOrDefault();
if (existingItem == null)
{
_db.Posts.InsertOnSubmit(item);
}
_db.SubmitChanges();
}
The project wont compile in the beginning but that bug was an easy fix.
As always Rob, thanks for everything.
I got an error when I tried to generate the scaffold. The message is:
---------------------------
There was an error generating the bits - very sorry. The error is: Object reference not set to an instance of an object.
---------------------------
Is there anything that I can do to investigate the problem ? I've checked the files and looks like evething is there.
My environment is:
VS 2008 SP1
WinXP PRO - Portuguese-Brazil
SQL 2005
Tks.
[]'s Bruno Caimar
On XP, VS SP1, ReSharper (Latest), I ran the installer. No addin. Dropped the binaries into the Addins folder. On VS launch, got test version error and renamed. Restarted VS and the addin is enabled. Created a new MVC project (Beta 1), added the DBML class to the model folder and set the connection string. Then right clicked to run Subsonic addin, and BANG. A quick flash of a dialog box, and VS disappears. No Dr. Watson, just the desktop. I tried it on my workstation and my laptop. Same results. I'll admit it could be me, but not sure what I did. BTW, I tried to attach the debudder to the vs process to see if I could catch the exception, but the debugger didn't catch anything.
Not sure how to figure out what's wrong.
PC
Keys to Subsonic joy:
* Add TABLES to the dbml. (That's what hosed VS; no tables added.)
* Create an App folder in VS on the root of your project. (That's the null object reference issue)
* Rename the 'SubSonic.VS - For Testing.AddIn' file you added to the Addins folder to 'SubSonic.VS - For Testing.AddIn__' and relaunch VS. (Rob, it's in the binary distro zip)
Thanks for the great work.
PC
* Any inheritance defined in the dbml results in an empty Select Table list in the Scaffold dialog.
* The templates assume that the primary keys are always an Int type.
* I would add an optional check box to the Scaffold that forces an overwrite of existing files.
All-in-all, I like it very much.
PC
This an awesome tool and I hope Microsoft implement this when they release MVC.
I have used your tool and am wondering if you could give some guidelines on how the database tables should be setup. I have already guessed that all foreign keys should be created like a relationship in the DBML.
I have spotted in the search method that it searches for one of the columns in the table. Is this the first string column it finds or the second column in the table?
Just some overall pointers on how your program expects the database would be gratefully received.
Thanks
trying with edmx but I got an error when trying to add "MVC scaffold" I got an error telling me it's not an MVC Prject ???
Could you help me please ?