Drag and Drop Categorized Item List with jQuery and ASP.NET MVC – Part 2

Database and Model Classes

This is part two of a series. In this part I’ll describe the database structure and create the model classes needed for basic database interaction.

To keep things simple, I went with a SQL Server 2005 Express database as it’s free and easy to create within Visual Studio 2008. To create one, simply right-click on the App_Data folder and select SQL Server database. I named mine ListDemo.mdf.

After the database has been created, double-click on the new .mdf file to open it up. I created three tables: Lists, Sections and Items. Here’s the final database structure:

Make sure to set the keys and relationships appropriately. The first ID field of each table should be the primary key and set to auto-increment. I also recommend turning on cascade delete to make deleting whole sections or lists easier. I’ll get into tying the lists table to a users table later.

In the Sections table, the ColumnNum field is to indicate which of the four DIV columns each section resides in. Values must be between 1 and 4. Once narrowed down to a column, sections are sorted by their SortOrder value. Likewise, in the Items table items are sorted within sections by their SortOrder value.

For now I manually created one record in the List table and named it “Demo List 1”.

On to the “M” (models) in MVC. Again to keep it simple and quick, I chose to use LINQ to SQL even though you could argue I should use LINQ to Entities. I added a “LINQ to SQL Classes” item to the Models folder and named it ListDemo.dbml to match the database file. At this point the LINQ to SQL designer should pop open. Just drag the Sections, Lists and Items tables from the database view in the Server Explorer to the designer. When you click Save the LINQ to SQL code is generated for you behind behind the scenes. To view it look at the code-behind file for ListDemo.dbml.

Following a simple MVC convention, I created a model class for each table with the name ending in “Repository.” Specifically, ListRepository.cs, SectionRepository.cs and ItemRepository.cs (saved to the Models folder). Declared at the top of each class is a variable that references the data context. In each of the three classes I needed a method to retrieve a single record by ID. I called this GetById and used the Lambda/Method syntax of LINQ. It’s virtually the same in all three classes, but here’s the code for SectionRepository.cs:

public class SectionRepository
{
    private ListDemoDataContext db = new ListDemoDataContext();

    public Section GetById(long id)
    {
        return db.Sections.Single(s => s.SectionId == id);
    }
}

For now I’m going to ignore the ListRepository class since I’m just dealing with the single List record for now. In the Section and Item classes, next I coded up the Insert, Delete and Save methods. In all 3 methods I’m referencing the DataContext object, which has the already generated InsertOnSubmit(), DeleteOnSubmit() and SubmitChanges() methods (among others) in the LINQ to SQL auto-generated code. Here’s the code in SectionRepository.cs, but again it’s very similar in ItemRepository.cs:

public void Insert(Section sec)
{
    sec.SortOrder = GetNextSectionSortOrderValueByListIdColumnNum(sec.ListId, sec.ColumnNum);
    db.Sections.InsertOnSubmit(sec);
}

public void Delete(Section sec)
{
    //Items deleted via SQL cascade delete
    db.Sections.DeleteOnSubmit(sec);
}

public void Save()
{
    db.SubmitChanges();
}

When a Section or Item record is inserted, it needs to have a SortOrder value that is one higher than the max SortOrder value that already exists within it’s group (all items within a section, or all sections within a column). Using LINQ I created a private method in each of these two classes to retrieve this value before saving the record. In SectionRepository.cs:

private int GetNextSectionSortOrderValueByListIdColumnNum(long listId, int colNum)
{
    //Max value is null if no items yet
    int? highestSortValue = (from s in db.Sections
                             where (s.ListId == listId) && (s.ColumnNum == colNum)
                             select (int?)s.SortOrder).Max();

    return (highestSortValue ?? 0) + 1;
}

When we drag sections or items around, we are basically re-ordering the SortOrder value (and possibly changing the ColumnNum value for sections). jQuery can pass a string of separated ID values when posting to the server via AJAX, so I created the method UpdateSortOrder with a ColumnNum (for Sections) or SectionId (for Items) parameter along with a string array parameter in the two repositories. In SectionRepository.cs:

public void UpdateSortOrder(int columnNum, string[] sectionIds)
{
    for (int i = 0; i < sectionIds.Length; i++)
    {
        Section section = GetById(long.Parse(sectionIds[i]));
        section.ColumnNum = columnNum;
        section.SortOrder = i + 1;
    }
}

To see the final code for all three repository classes, download the sample project at the end of this post.

I know there’s no UI updates in this part of the series, but it’s important to get the database and model classes established before moving on.