Tracking SQL Server procedure and function changes

This Stack Overflow question inspired me to create a couple batch files today:

1. gen_all.cmd (creates one text file for each stored procedure and function in a database)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION')" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

2. gen_since.cmd (same as gen_all.cmd, but takes a date/time parameter and only generates files for procs/funcs that were created or altered since the specified date/time)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION') AND (CREATED >= convert(datetime,'%1') OR LAST_ALTERED >= convert(datetime,'%1'))" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

Usage (to generate any files altered on or after Jun 5th):

gen_since 06/05/2010

I know there are some slick database change management tools out there, both OSS (Tarantino) and commercial (Redgate SQL Compare)… but I’m going to try using this simple batch file for a while as a nice, mindless way to save my DB objects as files that can be tracked in source control.

Test driving the TekPub ASP.Net MVC2 Starter Site

I like starter sites… we use one I put together at work (I called it BlankSolution).  They make it really fast to get up and running on a new project, AND to carry over improvements and best practices from project to project.  They are also great for learning.

Rob Conery released an MVC 2 starter site recently which looks really interesting.  Go check it out to see all the goodies it includes… then come back here to read about my experience using it to build up a real project (the NCharlie Task Board of course!)

Here we go, step by step (I’m following Rob’s blog post announcing the 0.5 release):

1. Download the bits from CodePlex, extract the zip and open the solution.  Screeeech… error opening in VS 2008.  Something about a bad pointer in the Web.csproj file to MSBuild “v10.0” WebApplicationTargets.  A quick edit to the csproj file (changed v10.0 to v9.0) fixed that (I won’t be forking the starter project for fixes if I find them… just want to see how quickly I can get up and running tonight.)

2. Set up the app to use SubSonic (the ORM tool Rob wrote).  Using Subsonic is a “for now” choice, to quickly build up the database from POCO domain classes.

- Global.asax.cs needs one change, on line 135.  Change “SiteEFSession” to “SubSonicSimple”.

- SubSonicSimple.cs needs one change, on line 18, add the name of the connection string the app will use, “SiteData”.

- The Web.config file doesn’t need to change, as long as your local database instance name is “SQLEXPRESS”.

3. Add a new Class Library project to the solution to hold the domain classes.  I called it “Core”.  Make sure to add the “Core” project to the “Web” project’s references.

(Side note: If you read through the comments on Rob’s getting started post, he ripped some guy a new one for suggesting he put his domain entities in a separate project, and add separate “Model” classes that map between the Core and the UI… so proceed at your own risk!)

- Add the domain classes:

4. Modify HomeController.cs:

- Add ISession as a private, injected member.

- Grab all the Board records from the database, ordered by Board name, and paged

public class HomeController : Controller {
    private ISession _session;

    public HomeController(ISession session)
    {
        _session = session;
    }

    public ActionResult Index(int? page) {
        var boards = _session.All<Board>().OrderBy(x => x.Name);
        var list = new List<Board>(boards);

        return View(list);
    }

5. Control-F5 to build and run the site… it builds, but:

6. I had some problems.

- First, my SQL Server instance is version 2005… Rob’s Site.mdf file in the App_Data folder is v655 (2008 I think), and it would not open.  I created a new “SiteData” blank database on my local instance, modify the connection string in Web.config, and tried again.  Same beautiful error screen I got before.

- Second (I looked in the Event Viewer for this exception), I found this in the exception detail:

Exception information:
    Exception type: InvalidOperationException
    Exception message: Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute

…so, I added an “ID” property to by Board class, and tried again.  Error screen.

- This time the “Index.aspx” view is expecting the model to be “Web.Model.User”, and I’m passing it a list of Board entities to choke on… that makes sense.  So I changed the “Inherits” directive at the top of Index.aspx to expect IList<Core.Domain.Board>, and removed the one “EditorFor” statement on line 35, and…

 

It’s alive!  It took me almost 2 hours to follow Rob’s getting started post to this point… I didn’t get to the part about switching SubSonic out for EF or LinqToSql.  I’ll leave that, and making this into an actual Task Board, for next time.

NCharlie Task Board – MVC Version

It’s unfortunate that paid work has to take precedence over fun blog projects like this… but that’s life!  I have an hour or so tonight, so I’m jumping back in to this task board project.

Last time (February, ‘10!) I wrote about the Webforms implementation of the task board.  That was a good exercise for me, because I’ll be able to take many of the patterns and tools from that little project directly into my “for pay” projects, which are still all Webforms.

This MVC version will take up more than one post; I will try to keep the posts a bit shorter, and I want to record the implementation details with enough depth that I’ll be able to use these notes later to quickly ramp up my team on MVC projects when we get there.

This (image at right) is the initial project tree from Solution Explorer.  You can see some of the same elements from the Webforms version (AutoMapper, StructureMap, a Master page, an NHibernate configuration file and TransactionBoundaryModule that implements the “Session per Request” pattern for data access operations.)  This UI.Mvc project references the same “Core” project that the Webforms project references.

To quickly get started with the “Model-View-Controller” pattern in this project, I created the TaskBoardController class and added one ActionResult method named “Index”:

public ActionResult Index()
{
    var board = new Board("My hard coded test board", 1, 1);
    var column = new Column("Col1", board);
    var task = new Task("task1", column);

    var model = new[] { Mapper.Map<Board, BoardViewModel>(board) };
    return View(model);
}

You can see it just creates a new, hard-coded task board entity, maps it to the BoardViewModel class, and returns it.

To quickly create a View page that will accept this ActionResult, I right-click on the method name “Index” and choose “New View”:

This brings up the Add View wizard, in which I can create a new ASPX view file that is strongly typed to my BoardViewModel class, uses the Master page for my project, and puts in some initial code to display the data:

I chose “List” as the View content type, because that selection assumes I will be returning an enumerable list of models, and puts a “foreach” loop into the ASPX page (I’ll use that code later as I build up the task board… where I used the ASP Repeater control in the Webforms project, I’ll use “foreach” to create the correct HTML in this MVC view page.  This is pure code-generated quick and dirty programming… which I think is dangerous if you don’t use it correctly, but valuable for quickly understanding the ingredients in an MVC web app and getting started with a screen quickly.  Even though this view of the BoardViewModel doesn’t look anything like a task board yet, I think it is useful as an exercise:

To see the generated code, check out the HTML in the Index.aspx file that creates this view.  I tagged this code in source control as “MVC quick start”.  Next, I’ll do things right and create some tests that define a set of actions and expected behavior for the TaskBoardController.

NCharlie Task Board – Webforms version

…in which the task board UI comes alive.

I’ve got my domain objects and simple persistence finished.  Now I will implement the user interface first using ASP.Net Webforms.  I created screen mockups in an earlier post, and this view model class is a representation of that screen:

public class BoardViewModel
{
    public string Name { get; set; }
    public int CommunityId { get; set; }
    public int PageId { get; set; }

    public ColumnViewModel[] Columns { get; set; }
    public class ColumnViewModel
    {
        public string Id { get; set; }
        public string Name { get; set; }

        public TaskViewModel[] Tasks { get; set; }
        public class TaskViewModel
        {
            public string Id { get; set; }
            public string Name { get; set; }
            public bool InFirstColumn { get; set; }
            public bool InLastColumn { get; set; }
        }
    }
}

The user interface for this model is a “board” with a variable number of columns, each of which has a variable number of tasks in it that can jump forward or back to other columns.  For the Webforms version I use nested Repeater controls to accomplish this.  This is how it turned out:

I’m keeping this post short, but there is actually plenty to talk about behind the scenes that I may cover in more detail in later posts.  For now, here’s a list of elements that in my opinion made this little project fun to work on:

• Uses a master page, NCharlie.master



• Uses jQuery for showing/hiding textboxes for user input



• Uses Automapper to simplify mapping entities retrieved from the database to the view model for display



• Uses the StructureMap IoC container for injection of IBoardRepository into the page constructor



• Web.config modification: added HttpModule reference for the TransactionBoundaryModule class (implements the Unit of Work pattern using one NHibernate Session Per Http Request)

There are some things I need to improve… for example, I don’t have StructureMap configured to automatically inject the repository class.  Rather, I’m asking StructureMap for it explicitly in the constructor… simply because I’m still weak in that area and need to learn how.  Also, I skipped writing tests for this bit of code… mainly because I find it hard to write tests for Webform controller classes.  Hopefully I will be able to fix that when I implement the MVC version.

Check it out in the Webform branch of the NCharlie source.

NCharlie – Simple Persistence with Fluent NHibernate

Now that I have the domain objects (Board, Column and Task) built up and tested, it is time to wire them up to a database.  I want to create them, click Save, go home and play Wii with my boys… then come back the next day to find them persisted, waiting for me to work with them again.

I’m going to keep this part really simple… because it should be.  The days of writing stored procedures, or ADO.Net connection, command, execution and mapping code are dead to me.  At least here in the NCharlie project they are!

First, I’ll give credit where credit is due… The folks at Headspring have contributed lots of code to the community through open source projects like CodeCampServer.  The following simple data access code, and most of the NCharlie starter project, comes from the example project Jimmy Bogard used at his MVC boot camp course in January.  Check out the NCharlie source to see the configuration files and base classes that make the following simple mapping code possible.

Here’s a test showing that I want to persist a Board entity to a database:

Here’s a mapping class that makes the test pass:

That’s all that’s needed to get the Board entity saved.  I really like the simple syntax used here… no strings like “Name” or “CommunityId” in this code.

I wanted to keep it short and simple here… I did add some other persistence features (cascading updates/deletes, joins.)  You can check out the code (which includes mapping code for the other entities) in the source branch tagged 2010-0218-persistence.

NCharlie Project Mockup, Domain Model

Here are the mockups of the two project screens I will focus on in the next few posts.  First, here is a the page that allows users to create a new task board:

I will want this application to allow users to set up multiple task boards for different teams/groups.  Since I’d like to use this software at work someday, each task board will have a unique CommunityId and PageId so they can reside in a portal application.  Once a new task board is created, columns and tasks may be added to it:

A task can move forward and backward through the columns.  Columns and tasks may be deleted.

The problem domain contains Boards, Columns and Tasks.

This set of user stories describes the functionality of the task board:

• When adding a new Board, the Board Name, CommunityId and PageId should be specified.
• When adding a new Column to a Board, the Column name should be specified.
• When adding a new Task, a Task description should be specified.
• When displaying a Board, all the Board’s columns should be shown.
• A new Column should be added to the end of the Board’s list of columns.
• A Column may be deleted.
• When displaying a Column on a Board, all the Column’s Tasks should be shown.
• A Task may Task should be added to the end of the Column’s list of Tasks.
• A Task may be moved from its Column to an adjacent Column.

I wrote unit tests for the non-trivial stories above, and created domain objects and methods to make them pass:

Here is the class diagram for this simple domain:

I tagged the code for this post in source control, you can check it out here.  Next I’ll walk through the data access code… Or I’ll create view models and UI elements… Or I’ll step back and explain some patterns in the NCharlie starter project… I’ll have to surprise you because I don’t know yet!

Resurrecting NCharlie

We delivered some software at my day job last week that has been keeping me pretty busy for the past few weeks.  To celebrate, I am revving up the NCharlie project again to buzz out some things I picked up in Austin last month.

I’ve already spent some time integrating StructureMap, Automapper, and Fluent NHibernate into a task manager Webforms application that uses the patterns I learned last month in bootcamp.  Here’s what I’ve got so far:

We’ve been using a “task board” like this at work to move our development tasks through to deployment.

I know, the CSS needs some work… but the thing is working!  Here’s what I’ve done so far:

1. Started a new blank solution for NCharlie in Visual Studio 2008.
2. Added .Net 3.5 projects for the Core NCharlie project, a Webforms UI project, an MVC UI project (using ASP.Net MVC 2 RC 2), Integration and Unit Test projects.
3. I put the blank NCharlie starter project (no entities, tests, views… just the architecture) into a branch tagged ncharlie-starter.

As of tonight, the version in the trunk of the NCharlie repository matches the starter project.  My plan is to build it up using a branch per feature, and blog about it as I go.  I plan to mirror the functionality using Webforms and the MVC framework.  I think it will be a good way for me to cut my teeth on MVC, plus it will be interesting to compare the two when it is done.