DevelopMENTAL Madness

SQL Server add column with DEFAULT CONSTRAINT: The Dumb Little Things

2010-04-29T12:39:36Z

Sometimes you think you know and then when you go and open your big mouth and make a fool of yourself.

I generally script all my database changes by hand. Not 100%, if it’s a big change I’ll right-click the object and use the script menu command to generate a script I can paste into my script editor. But often if I’m creating new objects I’ll write the script by hand from scratch.

Last week I needed to add a column to three separate tables and the column was not nullable. So as I have done so many times in the past, I proceeded to drop all dependent objects, create a temp table with the new table structure, copy the data, drop the old table, rename the temp table and proceed to recreate all dependent objects (triggers, constraints, indexes). The script for each table was pretty long.

But yesterday the deployment script I wrote wouldn’t run in the QA environment. And someone asked why I didn’t just use the ALTER TABLE [tablename] ADD [column] syntax and do it all in one line.

My response was the column wasn’t nullable. The answer was, “use a default constraint”. To which I responded that default constraints don’t add the default data to existing rows, only on new inserts. Hence I couldn’t use that technique.

I was so sure, I went back to my desk to write a script to prove I was right. I wrote the following script:

CREATE TABLE ColumnTest (

    Id INT IDENTITY(1,1),

    Name VARCHAR(10)

INSERT INTO ColumnTest(Name) VALUES ('Mark')

INSERT INTO ColumnTest(Name) VALUES ('eSteve')

INSERT INTO ColumnTest(Name) VALUES ('Tyler')

INSERT INTO ColumnTest(Name) VALUES ('Jason')

INSERT INTO ColumnTest(Name) VALUES ('Chad')

INSERT INTO ColumnTest(Name) VALUES ('Mike')

INSERT INTO ColumnTest(Name) VALUES ('Nick')

ALTER TABLE ColumnTest ADD AreaCode CHAR(3) DEFAULT('801')

SELECT * FROM ColumnTest

DROP TABLE ColumnTest

The result of the SELECT proved that the column “AreaCode” was null. But then I noticed I hadn’t set the column to “NOT NULL”. So I modified the script like this:

CREATE TABLE ColumnTest (

    Id INT IDENTITY(1,1),

    Name VARCHAR(10)

INSERT INTO ColumnTest(Name) VALUES ('Mark')

INSERT INTO ColumnTest(Name) VALUES ('eSteve')

INSERT INTO ColumnTest(Name) VALUES ('Tyler')

INSERT INTO ColumnTest(Name) VALUES ('Jason')

INSERT INTO ColumnTest(Name) VALUES ('Chad')

INSERT INTO ColumnTest(Name) VALUES ('Mike')

INSERT INTO ColumnTest(Name) VALUES ('Nick')

ALTER TABLE ColumnTest ADD [State] CHAR(2) NOT NULL DEFAULT('UT')

ALTER TABLE ColumnTest ADD AreaCode CHAR(3) DEFAULT('801')

SELECT * FROM ColumnTest

DROP TABLE ColumnTest

Boy was I surprised! I can’t believe I never knew this. Could you imagine the time I could have saved. I hate adding not nullable columns!

My theory is that this was not always the case and that somewhere along the line (2005, 2008) this was added because of the pain involved in adding columns which could not be null.

Anyway, it was a humbling experience and not one I’ll forget.

tags: SQL Server, T-SQL, DDL

Domain-Driven Design: Protect your persistence required methods

2010-04-16T12:55:49Z

I’ve been reading up on Domain-Driven Design (DDD) lately and I’ve checked out Jimmy Nilsson’s book Applying Domain-Driven Design and Patterns: With Examples in C# and .NET. I was reading Ch 5, “Moving Further with Domain-Driven Design” and Jimmy is talking about reconstituting your entity from persistence.

Specifically, he’s talking about the situation where your entity has read-only fields that must be provided by your persistence layer, but that you don’t want to make public. For example, the entity’s ID field. This is definitely a read-only field, but you have to set it at some point when querying the data from your data store.

In recent projects I have provided a separate constructor and documented it (read: comments) as “reserved for persistence only”. Also, in some cases, instead of providing a set property method for the ID I have created a method which allows the Id to be set. Something like SetIdFromPersistence() to make it clear that Id is intended as a read-only field.

public class Customer {

    ///<summary>

    /// This is reserved for use by the repository

    ///</summary>

    public Customer(int id, string name){

    ///<summary>

    /// This is open for public use to create a new customer

    ///</summary>

    public Customer(string name){

    public int Id { get; private set; }

    ///<summary>

    /// This is reserved for use by the repository,

    /// Id is read-only and using this inappropriately

    /// could result in data corruption issues.

    ///</summary>

    public void SetIdFromRepository(int id){

        Id = id;

This is because, my persistence layer and my model are in separate assemblies. I’ve considered using reflection to allow me to make the setters and ctors I’ve provided private. But I’ve never really liked this. So I’ve just tried to make it clear what the use cases are for these methods.

But then something Jimmy said triggered and idea for me and I want to see what everyone else thinks.

Explicit Interface Implementation

I haven’t run into many developers who’ve heard of this, but you may have noticed this when using some of Visual Studio 2008’s code editing tools (I don’t know if this exists in previous versions of VS, I only discovered it in v. 2008). But if you’re editing a class which implements an interface and you select the interface with your cursor you’ll see that below the interface is an “auto correct” icon/sprite. If you click it or hit ALT+SHIFT+F10 you’ll get a couple options:

Implement interface ‘ISomeInterface’
Explicitly implement interface ‘ISomeInterface’

If you select the first option, you’ll get public method/property stubs created in your class for you with the correct method signatures. If you click the second option you’ll see something similar, but you may have to look closer to see the difference (go ahead, I’ll wait)… Ok, you back? Did you notice the difference? Here’s what you should have seen. Given the following interface:

public interface IMyInterface {

    void MyMethod(int id);

    int MyProperty { get; set; }

The first option would generate the following implementation:

public MyClass : IMyInterface {

    public void MyMethod(int id){

        throw new NotImplementedException();

    public int MyProperty{

        get{

            throw new NotImplementedException();

        set{

            throw new NotImplementedException();

And the second option (explicit) would generate this implementation:

public MyClass : IMyInterface {

    void IMyInterface.MyMethod(int id){

        throw new NotImplementedException();

    int IMyInterface.MyProperty{

        get{

            throw new NotImplementedException();

        set{

            throw new NotImplementedException();

Notice the differences in the second implementation? Let me help:

Not public members
each member name is prefixed with IMyInterface

These members are only visible when the class is explicitly used/cast as IMyInterface. Here’s two examples:

public void SomeMethod(IMyInterface obj){

    obj.MyMethod(2);

public void SomeMethod2(MyClass obj){

    int x = ((IMyInterface)obj).MyProperty;

So the explicit members will never show up in the public interface. I first ran into this years ago, when I was working with a FCL class which I knew implemented a specific interface, but when I tried calling a member of the interface I couldn’t compile the application. An example of this is IConvertable on the Int32 type:

int i = 33;

i.ToInt32();                    //compile error

((IConvertable)i).ToInt32();    //compiles

CREDIT: I couldn’t remember any FCL examples of this, so I had to search and found the above example on Brad Abram’s blog.

The guidlines for this can also be found on the same post by Brad:

Do use explicit members to hide implementation details
Do use explicit members to approximate private interface implementations.

For reference, here are some links on Explicit Interface Implementation:

Exposing Private Members

Now back to our discussion on preventing the methods needed by our persistence layer from being called by consumers of our entities.

Explicit interfaces can be used to hide members that aren’t intended to be called directly by consumers of the object, eg. “Not intended for public use”.

Can you use this method to prevent consumers from calling your methods at all? No. The interface will be public so you can use it from your persistence layer and it will need to follow your entities around. Most likely it will be a public interface located in the same assembly as your model. However, marking the members as private doesn’t keep consumers from calling them using reflection. This is the solution Jimmy uses to access the private members in his example. He might use something different later on, I get the impression he might use another technique once I read further into the book.

But this technique will be more efficient and more clean than using reflection. Also, if you are running in a medium-trust environment where calling private members is blocked this technique will still work.

CAVEAT: When I read Brad Abram’s post I noticed a comment below indicating that if you use this technique with a struct instead of a class you will encounter boxing/unboxing issues. This is an important consideration to keep in mind. Does boxing cost more than using reflection? I don’t know, I’ll leave that exercise to the user.

tags: DDD, Domain-Driven Design, Explicit Interface Implementation

Utah Code Camp: Spring 2010

2010-03-22T15:49:53Z

Just got confirmation today, I’ll be speaking at the upcoming Utah Code Camp. The topic is ASP.NET MVC: A First Look. I’m hoping to introduce MVC to the uninitiated. If you’re curious or even if you’re a skeptic, come and join me and I’ll walk you through many of the features of MVC that make it such a great framework (including some of the newer versions in the recently released MVC 2).