Just a few examples here.  First we’ll setup the basic architecture from the lowest common denominator – the database – bounce up to the first layer, and test in isolation and test in integration.  These examples are simply the easiest ways to test the basic data and data access layer using Stubs, Mocks, and general unit test practices.

First off here are the pieces of the architecture that you’ll NEED regardless.  No real coding in this entry (except for the T-SQL), just setting up the solution to put the tests in and have the initial database architecture setup.  The part 2 entry will have more on the testing, and part 3 will have actual functional code tests and implementations – after all, I’m working this up in a test driven development mentality – “mostly“.

The Beginning

Setup the following projects within a new VS.NET solution.  I broke out the projects as you see below. (Click to see the larger image)

I like to keep my unit and integration tests separate so that they’re easy to include or exclude from a continuous integration build.  Because really, the integration tests aren’t generally going to work in a regular build.  Unless of course you’ve scripted out some serious steps such as “Create a Setup MSI” and “install the software and run” etc.  Which in my mind, just seems a bit out of line with the build process.  Not to even mention extremely time consuming.  I however think that a good solid and separate CI build of the respective setup project should be kept in a different solution that when it does alter can have a build process and either an automated integration test run or have the integration tests manually run.  Either way, they get run, but in my humble opinion shouldn’t be run during the regular CI build of the working solution.

If you don’t have mbUnit installed, go grab a copy over at www.mbunit.com.

The next thing I did is setup the databases.  I have some odd requirements for the particular database schema and segmentation of various databases for the application components.  The Grand Central Database is used for the security, membership, roles, etc, the other databases are used for each of the application “components”.  Each of these “component” databases I’ll refer to as either “component” or “tsr” or “tmt” respectively.  These “component” databases I’ve setup for use once a user is authenticated.  So each of these databases has a Users Table that has a UserId Column that will be used for relating the respective user that has access to the particular component.

First I setup the tables:

tmtUsers

   1:  /****** Object:  Table [dbo].[tmtUsers]    Script Date: 07/06/2008 01:27:07 ******/
   2:  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmtUsers]') AND type in (N'U'))
   3:  DROP TABLE [dbo].[tmtUsers]
   4:  GO
   5:  /****** Object:  Table [dbo].[tmtUsers]    Script Date: 07/06/2008 01:27:07 ******/
   6:  SET ANSI_NULLS ON
   7:  GO
   8:  SET QUOTED_IDENTIFIER ON
   9:  GO
  10:  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmtUsers]') AND type in (N'U'))
  11:  BEGIN
  12:  CREATE TABLE [dbo].[tmtUsers](
  13:      [UserId] [uniqueidentifier] NOT NULL,
  14:   CONSTRAINT [PK_tmtUsers] PRIMARY KEY CLUSTERED
  15:  (
  16:      [UserId] ASC
  17:  )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  18:  ) ON [PRIMARY]
  19:  END
  20:  GO

   1:  /****** Object:  Table [dbo].[tsrUsers]    Script Date: 07/06/2008 01:50:30 ******/
   2:  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsrUsers]') AND type in (N'U'))
   3:  DROP TABLE [dbo].[tsrUsers]
   4:  GO
   5:  /****** Object:  Table [dbo].[tsrUsers]    Script Date: 07/06/2008 01:50:31 ******/
   6:  SET ANSI_NULLS ON
   7:  GO
   8:  SET QUOTED_IDENTIFIER ON
   9:  GO
  10:  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsrUsers]') AND type in (N'U'))
  11:  BEGIN
  12:  CREATE TABLE [dbo].[tsrUsers](
  13:      [UserId] [uniqueidentifier] NOT NULL,
  14:   CONSTRAINT [PK_tsrUsers] PRIMARY KEY CLUSTERED
  15:  (
  16:      [UserId] ASC
  17:  )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  18:  ) ON [PRIMARY]
  19:  END
  20:  GO

After creating the tables I went ahead and created a view that would link the UserId do the Grand Central Database and a respective stored procedure too.  I created these by simply dragging the tables onto the designer for creating the view from each of the respective databases. (Click to see the larger image)

The SQL for the views is below.  The TSR and TMT Databases both use basically the same exact SQL.

tmtUserListing

   1:  SELECT     dbo.tmtUsers.UserId, GrandCentral.dbo.aspnet_Users.UserName
   2:  FROM         dbo.tmtUsers LEFT OUTER JOIN
   3:                        GrandCentral.dbo.aspnet_Users ON dbo.tmtUsers.UserId = GrandCentral.dbo.aspnet_Users.UserId

tsrUserListing

   1:  SELECT     dbo.tsrUsers.UserId, GrandCentral.dbo.aspnet_Users.UserName, GrandCentral.dbo.aspnet_Users.LoweredUserName, GrandCentral.dbo.aspnet_Users.MobileAlias,
   2:                        GrandCentral.dbo.aspnet_Users.IsAnonymous, GrandCentral.dbo.aspnet_Users.LastActivityDate
   3:  FROM         dbo.tsrUsers INNER JOIN
   4:                        GrandCentral.dbo.aspnet_Users ON dbo.tsrUsers.UserId = GrandCentral.dbo.aspnet_Users.UserId

   1:  /****** Object:  StoredProcedure [dbo].[tmtUserListingSelect]    Script Date: 07/06/2008 01:36:33 ******/
   2:  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmtUserListingSelect]') AND type in (N'P', N'PC'))
   3:  DROP PROCEDURE [dbo].[tmtUserListingSelect]
   4:  GO
   5:  /****** Object:  StoredProcedure [dbo].[tmtUserListingSelect]    Script Date: 07/06/2008 01:36:33 ******/
   6:  SET ANSI_NULLS ON
   7:  GO
   8:  SET QUOTED_IDENTIFIER ON
   9:  GO
  10:  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmtUserListingSelect]') AND type in (N'P', N'PC'))
  11:  BEGIN
  12:  EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.tmtUserListingSelect
  13:
  14:  AS
  15:      SELECT     dbo.tmtUsers.UserId, GrandCentral.dbo.aspnet_Users.UserName
  16:  FROM         dbo.tmtUsers LEFT OUTER JOIN
  17:                        GrandCentral.dbo.aspnet_Users ON dbo.tmtUsers.UserId = GrandCentral.dbo.aspnet_Users.UserId
  18:
  19:      RETURN
  20:  '
  21:  END
  22:  GO

   1:  /****** Object:  StoredProcedure [dbo].[tsrUserListingSelect]    Script Date: 07/06/2008 01:50:38 ******/
   2:  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsrUserListingSelect]') AND type in (N'P', N'PC'))
   3:  DROP PROCEDURE [dbo].[tsrUserListingSelect]
   4:  GO
   5:  /****** Object:  StoredProcedure [dbo].[tsrUserListingSelect]    Script Date: 07/06/2008 01:50:38 ******/
   6:  SET ANSI_NULLS ON
   7:  GO
   8:  SET QUOTED_IDENTIFIER ON
   9:  GO
  10:  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsrUserListingSelect]') AND type in (N'P', N'PC'))
  11:  BEGIN
  12:  EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.tsrUserListingSelect
  13:
  14:  AS
  15:  SELECT     dbo.tsrUsers.UserId, GrandCentral.dbo.aspnet_Users.UserName, GrandCentral.dbo.aspnet_Users.LoweredUserName, GrandCentral.dbo.aspnet_Users.MobileAlias,
  16:                        GrandCentral.dbo.aspnet_Users.IsAnonymous, GrandCentral.dbo.aspnet_Users.LastActivityDate
  17:  FROM         dbo.tsrUsers INNER JOIN
  18:                        GrandCentral.dbo.aspnet_Users ON dbo.tsrUsers.UserId = GrandCentral.dbo.aspnet_Users.UserId
  19:      RETURN
  20:  '
  21:  END
  22:  GO

Now your server explorer should look like the image below.  (Click to see the larger image)

My database projects appear as below (all checked in). (Click to see the larger image)

Next we’re going to setup the Data Access Layer (DAL).  Yes, this is somewhat crude, but really, we’re only trying to get the basic objects and setup the business entities.  I’ve used SQL to LINQ for the DAL.  I dragged the three database objects from each “component” database over to the respective *.dbml files.

After that add the testing files to the various mbUnit test projects that have been created.  I create one for each of the objects that will be tested from the database, mainly based on the idea that they will generate entities or objects of some type, and this will keep them fairly organized.  A little bit more about this in part 2.

I’ll have the next part up soon.  Until then this will get one setup for the initial tests and core data access layer.

Technorati Tags: TDD,Test,Unit Tests,LINQ,SQL,DAL,DataAccessLayer,Architecture,Software,mbUnit,Rhino,Mock,Stub,ReSharper,Isolation

del.icio.us Tags: TDD,Test,Unit Tests,LINQ,SQL,DAL,DataAccessLayer,Architecture,Software,mbUnit,Rhino,Mock,Stub,ReSharper,Isolation

I’m working on getting things into and out of Excel.  In one scenario I copy an Excel Range or Rows from one Excel Worksheet to a Worksheet in another Workbook.  This Workbook then acts as a storage repository for the Range of Rows and is saved and closed.  Each of these Range of Rows are saved with a unique identifier on the row above the copied in Range of Rows.

I then want to retrieve the Range of Rows to insert back into that Workbook’s Worksheet in another location.  I have an Excel Addin that provides a dialog box.  This dialog box goes through the Workbook’s Worksheet that is acting as a repository and retrieves each Range of Rows and provides a list based on the unique name in a dialog box.  The user can then click on the unique Range of Rows unique name and the idea is then to open the Range of Rows and copy them into the Worksheet the user is actually working in.

I had created a nice clean class object to put the “Range of Rows” in, but I’ve stumbled into a problem.  I can’t assign the Range object to a Range object in my domain class.  The problem being is the Range object is a bloody piece of @#$% COM object and won’t do a deep copy of the object itself.  So now I need to find some way to do a deep copy of the COM object or I have to open the Workbook’s Worksheet back up again and actually find again each Range of Rows that need to be pulled into the user’s Worksheet.

Overall, this is really ticking me off, the lack of operability and integration with the managed code base Microsoft works with .NET now is the polar opposite of the archaic and frustrating COM derived API of the Office Model.

If any Microsoftie’s are reading this, please, somebody get off the laurels and get this mess fixed.  The Office Model is impossible for all but the crudest of implementations and doing things “right” is almost completely removed from the options list.  Integrating the Office Model, especially with Microsoft’s Tech Stack of WCF, WF, WPF, and all that, is almost difficult to the point that you wouldn’t use Office for anything except if absolutely necessary.  Microsoft needs to fix this, open it up, get some clean API integration and improve the operability.

…and for the love of sanity Mr. Microsoft, practice what you preach and toss that COM @#$%.  It’s beyond frustrating dealing with unreliable COM objects and… well, I could rant for days about COM objects.

If anybody has any ideas on the best way to work around this, shoot some comments at me.  Eventually I’ll figure out something ideal for this, but for now I’m stuck with a really nasty way to get back at the Range of Rows.

Technorati Tags: COM,Microsoft,Office,Excel,Object,Range,Row,Worksheet,Workbook

del.icio.us Tags: COM,Microsoft,Office,Excel,Object,Range,Row,Worksheet,Workbook

So the build broke based on a unit test that failed.  Is the general rule of thumb that the build breaking check in person fixes the test?  What if they did something that broke someone else’s test for something?

…hmm, the philosophy of project handling and management.

Today I have nothing to proffer or offer the world of the blogosphere, merely that I’m exhausted and going to gladly jump on my bike and high tail it out of the office.

Tomorrow is implement functionality and dependency injection on the UI day.  At least get started, as it seems our UI has gotten rather dirty the last two weeks of coding.  Time for the refactor.

I was running three virtual machines on my laptop and couldn’t help but think, this is awesome.  I couldn’t have imagined doing this 6-7 years ago.

That is three operating systems running in virtual machines.  Windows 2003, Windows XP, and of course Windows Vista.  All running hosted on a Windows Vista Machine – my laptop.  2+ Ghz and 4 GB RAM on dual 7200 RPM 200GB Drives.  Awesome to be able to work with such an environment.

…and I hate it.

Excel has a model, that seems to be broken all the time, and appears that there are a zillion ways to do things that are not a preferred way.  The most common situation seems that Excel either breaks, crashes hard, eats up on the memory, or some other monstrous thing happens to cause a day to go south.

But the reason I love Excel, and maybe it is the pack rat collector mentality I have sometimes is – you can put stuff in it.  It isn’t just putting stuff in it, but arranging, formatting, calculating, and crunching the numbers is interesting to me.  For some reason, this almost seems odd, as if I should be bored silly.  But in all seriousness I really dig working with Excel.

That’s my epiphany of the day.  Later I’ll probably write something more useful for myself, but alas, I was thinking…