Chris McKenzie's Blog

I've been reading about and applying Test Driven Development and Design Patterns over the last few months. Today I saw the culmination of that effort in the design of new module of ASP .NET software that was added to an existing project on Monday.

Early on I had created a generic object for leafing through pages of records in the database.  I called the object the RecordCursor, and implemented basic cursor and search functionality (MoveFirst, MoveNext, etc.). Separately, I had designed a generic object for managing records in the database--I called this a RecordManager. It has 3 basic functions (Save, SaveNew, and Delete), and sometimes has others (ListAll).

Of course, there are also the Business Objects themselves.

For any Business Object in the system, there are basically 3 parts--the business object itself, the Manager, and the Browser.  This gives me a good separation of the Business Layer from the Data Access Layer.

I wanted to make the UI solely dependent on Interfaces and Abstract classes. When building my DataAccess components, I made sure that they only returned Interfaces from any API function. I further made sure that none of the concrete objects were visible to the UI.

This is where it starts to get cool. The RecordManager classes receive only interfaces to their Save, SaveNew, and Delete functions. The idea I had for the UI was to develop a set of UserControls that implemented the interface of the object they were intended to edit. Thus, if I have an interface IPerson, then I'll have a PersonEditorControl UserControl that implements IPerson. Instead of creating property declarations with internal variables, I'm redirecting the properties to the server controls embedded on the UserControl.

For Example:

Public Property FirstName as String Implements IPerson.FirstName

Get

return Me.txtFirstName.Text

End Get

Set (value as String)

Me.txtFirstName.Text = value

End Set

End Property

'etc...

Thus, the editor becomes an instance of the object it is intended to edit.  When I'm done editing, I simply pass the control in its entirety to RecordManager.Save(p as IPerson).

I've been writing code for about 5 years now, and haven't seen anybody doing this in any existing code, nor in any books. It's worked out pretty well for the project I'm working on, so I thought I'd share.

"The report server cannot decrypt the symmetric key."

Follow these instructions:

http://support.microsoft.com/kb/842421

I have a client-server application that uses SQL Server 2k as the backend. One of the things that I have done successfully is to require that all applications access the database via stored procedures. I don't have any grant permissions on any tables, views, or functions. I have already blogged about the problems with having a dbo as a user and enforcing row-level security, so none of my application user contexts are dbo's--at least until today.

I want members of a certain role to be able to manage which users are able to access the database via the application. I've built an ASP .NET Page that provides the UI for adding/removing users, setting their roles, etc.. The problem is that since my application-level 'admin' role is not in fact a dbo, it cannot execute the SQL 2k stored procedures to add/remove login/users to the database.

For now I've created a special login and added it to the securityadmin server role and set it up as a dbo to get around this problem. My question for the community is this: is there a way to allow my app to add and remove users using SQL Server Security and without knowing the login of a dbo, perhaps through a stored procedure? Can I create a role or something with those kind of permissions that is still not a dbo?

The VS Data Team has a new blog on Database Projects.  I've written before about things I'd like to see made better in the database projects and Visual Data Tools, but here is how I'm leveraging them now to manage our projects now.

The projects I've been working on for the last couple of years were intra net based systems that were developed with a view toward deploying on a client's intra net. We needed to be able to manage the versioning of the database schema as tightly as we managed the versioning of the application. Database projects are the way we did that. In this type of application, we have total control over the database schema, and it is tightly bound to our specific suite of applications for use.

My approach:

I have 3 folders in my database projects: Create Scripts, Change Scripts, Shared Scripts. I use the Visual Data Designer generate my initial schema for a new database. When I'm done, I create several files under Change Scripts--one each for Security, Tables, Functions, Views, and Stored Procedures.

All change to the database tables happen in the Change Scripts folder. I usually prefix my change scripts with a two digit sequence number, the version number of the database under development, and finally the name of the object being modified. Rule 1 is "one script file per table being modified." All modifications to a particular table are done in that table's script file, and those steps must be sequenced correctly. Rule 2 is that every change script should be able to be run against the same database multiple times without breaking. This means that all operations should be encapsulated in the appropriate "IF [NOT] EXISTS(...)" statements.

Security, Functions, Views, and Stored Procedures all go in Shared scripts. The reasoning here is that these objects are easily destroyed and rebuilt without impacting the underlying data. To keep database bloat to a minimum, it's easiest to simply drop all of these objects (except the security objects) with each revision of the database, and recreate the ones needed by the application. I usually start with a "00 1.x.x DROP OBSOLETE OBJECTS.sql" script that just reads the sysobjects table removing any lightweight objects it finds. Then I recreate functions, views, and finally stored procedures. I usually have a single script for each type of object.

The change scripts can be run by any team-member on any local or shared copy of the database by any co-developer at any time, and be assured that s/he is coding against the latest schema.  The scripts are source-controlled, so changes are immediately available and implicit in any database context you happen to be working in while developing.

When it comes time to release, the change scripts are bundled up into our deployment programs (we use the built-in .NET setup projects, as well as InstallShield) and executed against our customer's current systems. As part of the release cycle, the Create Scripts are updated to reflect the schema of the database at the time of the release--thus the change scripts are "posted" to the create scripts, archived, and a new set of change scripts are started for the next release. If we may encounter problem data that has to be corrected prior to upgrade, we'll generate a stored procedure that identifies the problem data, where to find it, and what to do about it for the customer. We make this available to them via SQL Server Reporting Services. When that report is empty, they're ready for upgrade.

As far as versioning goes, I haven't found a great way to handle this in SQL Server yet. Our system is a suite of programs with one "master" program that governs the whole system. The best I've been able to come up with so far is to create a table called tblModule consisting of a ModuleID, Name, Description, Major, Minor, Build, and Revision columns. Basically, as new modules for the system go into development, they are assigned a hard-coded ModuleID at the system level. The master module is always 1. The master module must exist in the system before any other child module can be added. Each module has an independent version number that must be at least equal to that of the master module.

These are the things that I've thought of and implemented for our team over the last year. I'm curious, what do you think I could be doing differently or better? Do you think any of the stuff I've written here would be helpful to you in your projects?

Click here. It's a column by Robert C. Martin that illustrates how TDD improves the quality of code.

This line relates directly to my last post, "Tests are users, too. The needs of the tests are often the same as the needs of the real users."

I've recently begun using Test Driven Development as my method for developing and maintaining applications. A worry I had early on was that I have inherited several poorly designed ASP .NET applications (read "poorly designed" as "not designed") and that it would be difficult to use TDD in that environment.

I was mistaken.

Today I had an issue with a browse page. The code to browse the database was slammed behind the page in a 250-300 line sub routine. Additionally, the same code was being used by 5 different buttons on the page--btnFirst, btnPrevious, btnNext, btnLast, and btnSearch. Given that the page is supposed to allow the user to browse the records a "page" (a set of x number of records) at a time, I thought the ITERATOR pattern most appropriate. I wrote a unit-test for the MoveFirst(pageSize) functionality of the RecordBrowser class, which didn't yet exist.

After implementing the MoveFirst() method, I then went on and wrote tests for--and coded--the MoveNext(), MovePrevious(), MoveLast(), and MoveToSpecificRecord() methods.  After writing the tests and verifying that they worked as expected, it was pretty simple to plug the new class behind the page and start using it instead of the existing code. I watched 250+ lines of code in the Code-Behind dwindle to less than 25 lines.

Because I needed to write a test to capture the failure (which ultimately was a failure in a stored procedure call), I had to create an object that I could test in isolation from the UI and surrounding logic.  This took a ltitle bit longer up front, but because I had separated out the actual db browsing logic from the UI, I could just work on getting that part to work correctly without having to worry about whether other stuff in the page was messing up the needs of the browing class. I also ended up with a set of new interfaces and abstract classes that I'll be able to re-use when I have to fix errors with other variants of the browser class, which means that the same fix will go much faster next time around.

When it came time to integrate the new class into the existing page, it took hardly any time at all. I had a couple of initial errors, but since I had already tested the browsing object, I knew that these errors were related to things on the page--not the browsing class. Once I got those straightened out, it was a snap to run the program and watch the page browse the db flawlessly.

So now I have a poorly designed web application with one lone piece of well-designed code. It took less than a morning to add the db browser class, and now I'll know that it's still functional every time I work on this program. The reason that I was able to seamlessly insert a piece of well-designed code into the system is because of the nature of TDD--the whole point is to test functionality in isolation. This means that design changes (and additions) are incremental, and have little impact on the larger system (or non-system in this case).  BUT--and this is the key point--over time, as bits of quality design are added here and there, the overall quality of the system incrementally improves.  As more and more features and issues are added or fixed, the overall system will begin to take on a definite structured design.  TDD causes the design to spread and overtake the system.

I am so looking forward to new enhancements on these old projects I inherited.