SQL Server: how to prohibit empty strings globally?

We'd like to prohibit empty strings by SQL Server means (not
programmatically on C# level); only NULLs (for nullable fields) should
be allowed.

We can use

ALTER TABLE TableName ADD CONSTRAINT ck_FieldName CHECK ((FieldName <> '') OR (FieldName IS NULL))

But how to do it globally on the level of a database (or SQL Server
instance), so that it wouldn't be necessary to remember to add the
above constraint to each and every table?

(We are using SQL Server 2008.)

.NET 3.5 SP1 breaks SQL Server 2008 (CTP5 Management Studio SQL)

I installed .NET 3.5 SP1 and Visual Studio 2008 SP1 beta just to find out that my SQL Server 2008 ((CTP5 Management Studio SQL)) doesn't work any more. Instead it displays "The Tabular Data Stream (TDS) version 0x730b0003 of the client library used to open the connection is unsupported or unknown. The connection has been closed." error message when I try to connect to a SQL Server. Searching on Google didn't help much.

A generic ListCollection<T> class

I blogged recently about using public Collection instead of public List. On The Visual Studio Code Analysis Team Blog they explained how to ensure that we could re-use List methods in our concrete instance of Collection generic class, if necessary. The catch there is that a developer has to remember to call base class constructor like

public class AddressCollection : Collection<Address> {
public AddressCollection() : base(new List<Address>()) { }
}

And I thought: why not to use generics to ensure that base class constructor is called? It works fine, I tested it:

using System;

using System.Collections.ObjectModel;

using System.Collections.Generic;

using System.Linq; 

namespace ProgrammerThoughts.Common {

  /// <summary>

  /// A generic Collection class which ensures that it actually contains generic List.

  /// This provides us with an ability to re-use any public method of List<T>

  /// with a simple shell function. Such a shell could be implemented in ListColection<T>.

  /// or in a concrete implermentation.

  /// </summary>

  public class ListCollection<T> : Collection<T>

    where T : class {

    public ListCollection()

      : base(new List<T>()) {

    } 

    /// <summary>

    /// You can implement such shell methods in your concrete implementations of this generic instead.

    /// </summary>

    /// <param name="match">An actual function of type Predicate&lt;T&gt;</param>

    /// <returns></returns>

    public T Find(Predicate<T> match) {

      List<T> items = (List<T>)Items;

      return items.Find(match);

    }

  }

}

C# block scoping rules for variables, anonymous methods and lambdas

I was reading Rick Strahl's blog post about "Variable Scoping in Anonymous Delegates in C#". I added a completely wrong comment there. That happens when studying too many languages at the same time. (My only excuse is that even Rick itself wasn't entirely right.)

JavaScript, Scala, and F# allow to hide outer variable by declaring another variable with the same name in a nested scope. C# does not allow to do that!

In C# lambdas and anonymous methods are treated as inline code placed into a nested block scope. It is consistent with the fact that they can access local variables from outer scope.
Behind the scenes compiler activities of creating actual delegate object and closure behavior are what they are: behind the scenes compiler activities. They should not and do not affect lexical rules.

And those lexical scoping rules for nested blocks, anoyimous methods, and lambdas are as followed:

namespace VariableScope {
/// <summary>

  /// C# 3.0 in a Nutshell, http://www.amazon.com/3-0-Nutshell-Desktop-Reference-OReilly/dp/0596527578/
  /// Page 46, "The scope of local or constant variable extends to the end of the current block.
  /// You cannot declare another local variable with the same name in the current block
  /// or in any nested blocks."
  /// That's an opposite to JavaScript and F# scoping rules.
  /// </summary>
  class Program {
    delegate int Adder();

    static void Main(string[] args) {
    int x;
    {
      int y;
      int z;
      int x;  // error, x already defined in outer scope (1*)
    }
    int y;  // error, y already defined in a child scope (2*)
    {
      int z;  // ok, no z in outer block
    }
    Console.WriteLine(z); // error, z is out of scope
  
    int t, u;
    Adder goodAdder = () => { return t++; };  //ok
    Adder badAdder = () => { int u; return t + u; };  // error, u already defined in outer scope
    Adder badToo = delegate { int u; return t + u; }; // the same error
  }
}
}
// 1*: Compiler errors are as followed
// 1    A local variable named 'x' cannot be declared in this scope because it would give
// a different meaning to 'x', which is already used in a 'parent or current' scope to denote something else
//
// 2*: Compiler error is as followed
// A local variable named 'y' cannot be declared in this scope because it would give
// a different meaning to 'y', which is already used in a 'child' scope to denote something else

Common patterns / practices for domain objects and object-relational impedance mismatch?

We're currently developing domain layer for our company's intranet. Basically, we follow a simple and well defined N-Layer pattern from series of articles by Imar Spaanjaars.
We are porting of an existing classic ASP application to ASP.NET 3.5 / C# / SQL Server 2008 environment. Because of that, both business logic and a database are pretty much defined. What we need, however, is some additional guidance on overcoming object-relational impedance mismatch, common design patterns / practices for domain objects which correspond to database objects (tables) with one-to-many, many-to-many, and look-up types of relationships.
Which books/articles would you recommend to read?

Using HttpModules and HttpHandlers under IIS7

IIS 7 uses by default a new Integrated type of Application Pool. Old style Pool called Classic is available as well. As I discover after some trouble, to use HttpModules and HttpHandlers in ASP.NET application running under Integrated Application Pool, one need to add additional lines to Web.config file.
Normally, you put HttpHandlers and HttpModules sections inside system.web section.
To work under Integrated Application Pool, Web.config should put modules and handlers sections inside system.webServer section:

<configuration>
  <!-- This is for Classic Application Pool -->
  <system.web>
    <httpModules>
      <add name="IntranetPageHttpModule" type="CoTs.Intranet.IntranetPageHttpModule" />
    </httpModules>
    <httpHandlers>
    </httpHandlers>
  </system.web>
  <!-- This is for Integrated Application Pool -->
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <modules>
      <add name="IntranetPageHttpModule" type="CoTs.Intranet.IntranetPageHttpModule" />
    </modules>
    <handlers>
    </handlers>
  </system.webServer> 
</configuration>

References:


ASP.NET Integration with IIS7 by Mike Volodarsky, page2.


HttpModule and HttpHandler sections in IIS 7 web.config files - Rick Strahl's Web Log

Design time support for custom properties of a custom ASP.NET base page?

I'm converting to ASP.NET an ASP project in which each page has its own ControlNumber string, used as a page unique identifier. The whole security model is build around using those ControlNumbers.
On ASP pages ControlNumber are set simply as a constants:

const ControlNumber = "blah-blah"

In ASP.NET I would like to implement a page ControlNumber as persistent BasePage public property, which could be set in VS at design time.

It's easy to inherit BasePage from System.Web.UI.Page, but how to add persistent property, visible in VS at design time?

Did C# borrow ideas from JavaScript?

I have just read a good article by Scott Hanselman about internals of Extension Methods. I felt for a long time that C# is borrowing more and more features from JavaScripts. Things, like 5.ToString() (.Net object types behind all simple types), anonymous class initialization, array initialization {1, 2, 5} - all these exist in JavaScript for a long time. Then - type inference (with "var"). Finally: an article mentioned above shows that extension methods implement something similar to JavaScript prototype functionality. (I know about functional programming, Ruby and Scala.)
What do you think?

Use public Collection<T> instead of public List<T>

The Visual Studio Code Analysis Team Blog: Why does DoNotExposeGenericLists recommend that I expose Collection<T> instead of List<T>? (See comments under part 2 of Imar's article) as well.

Microsoft: Glasnost and Perestroika

Isn't it funny? And this comparison of Microsoft and Hillary Clinton too...

How to fill drop-down box with Enum values

A code snipet from http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=420
(see this code as well)

Suppose you have enum PersonType and want to populate lstPersonType drop-down box with its values.
It is very easy with Reflection:

public enum PersonType {
  Friend = 0,
  Family = 1,
  Colleague = 2,
  NotSet = -1
}

private void BindTypeDropDown()
{
  FieldInfo[] myEnumFields = typeof(PersonType).GetFields();
  foreach (FieldInfo myField in myEnumFields)
  {
    if (!myField.IsSpecialName && myField.Name.ToLower() != "notset")
    {
      int myValue = (int)myField.GetValue(0);
      lstPersonType.Items.Add(new ListItem(myField.Name, myValue.ToString()));
    }
  }
}

Why I don't want to use LINQ to SQL

Here's a reason not to use LINQ to SQL for data mapping. I'm pretty sure the best way to start development is to use manual data mapping approach shown in Imar's article. You always can switch to some O/RM tool later, but if started with LINQ you'll be stuck with it. To me, using LINQ to SQL is pretty much the same as using DataSets: you lose an ability to design your own business layer object in such a way that it resembles business logic, rather than being dictated by database structure. LINQ to objects is a quite different story. It could be a very convenient tool to query your in-memory collections.

Dan Miser - Things I Don't Like About LINQ to SQL

More about Building Layered Web Applications using Imar Spaanjaars' approach

"Building Layered Web Applications with Microsoft ASP.NET 2.0" article published by Imar Spaanjaars differs from other articles on this topic.

• It follows a good Domain-Driven Design design instead of relying DataSets (see my previous notes)


• It presents a clean and ready-to-use code

Imar's article is intended to teach people by a good example. Obviously, he could not put everything necessary for a big application into one article. There are very interesting conversations located beneath each three parts of his article. I commented there as well and put too much stuff there. My fault. So, let me tell you here what I think about Imar's article and what I'm going to add in my implementation of his approach.

1. How to handle transactions in Business Layer (Bll) and allow DAL methods to share database connections without coupling Bll to features of database and OS. (See part 2 of Imar's article)

1) I downloaded Imar’s code and run it on Windows Server 2008 / SQL Server 2008 machine. It works fine. I could not find suggested settings for Microsoft Distributed Transaction Coordinator on Win Server 2008 machine, though. Does anyone know how to detect if it is running? Does the fact that Imar’s application, which uses TransactionScope object, runs without errors mean that MSDTC is running? Am I supposed to get errors if MSDTC is not running, or it would be silent?

2) As Imar mentioned in his answer to Math Random 's comment, in case MSDTC is not available, we would need to use SqlTransaction and therefore all DAL methods involved in saving related data would need to share the same Connection object.

We could pass connection object between DAL method calls inside Bll’s ContactPersonManager.Save() method. What I don't like here is that we're making an internal structure of Bll method ContactPersonManager.Save() dependant on external circumstances (whether MSDTC is available and whether we need to pass SQL Server connection around). Ideally, Bll method shouldn’t care about such things not related to business logic; it only should care about integrity of its objects and for its objects being able to save/retrieve themselves consistently. It would be nice to de-couple Bll method from such external things.

I guess, we need to add a level of indirection here. I would add one more [static ?] DAL class called ConnectionManager. This class would be responsible for providing connections to individual DAL objects (their methods) and for managing transactions. If necessary, Bll methods would call ConnectionManager.BeginTransaction() method to start a transaction. Internally, ConnectionManager would either use MSDTC -> TransactionScope if it is available, or open a SQL Server connection and start SqlTransaction on it otherwise. (Bll methods wouldn't care about those details.)

Then, Bll methods would call individual DAL methods the same way as ContactPersonManager.Save() calls AddressDB.Save(), EmailAddressDB.Save(), and PhoneNumberDB.Save() right now. There would be no need to pass around connection object: each DAL method would obtain a connection from ConnectionManager using ConnectionManager.GetConnection() method. It could be the same shared connection, or it could be a new connection every time. For example, in case we're using SqlTransaction, ConnectionManager would provide DAL methods with the same connection which was opened during ConnectionManager.BeginTransaction() method call.

DAL methods would not call myConnection.Close() methods directly, as they do in Imar 's code. Instead, they will call ContactPersonManager.CloseConnection(myConnection). ContactPersonManager object would then either close a connection or keep it open, depending on if it is still needed (for a pending SqlTransaction).
We also can wrap SqlConnection into our CustomConnection class overriding Close() and Dispose() methods in such a way, that they will ask ContactPersonManager object if connection should be really closed. That would allow us to work with "using" blocks.

It looks like such a design would allow us to decouple Bll layer from the specifics of a particular server (like availability of MSDTC transactions or using SQL Server or Oracle transactions instead.
What do you think? Am I re-creating a wheel here? I have a feeling that such solution already exists, but I don’t know it because of my ignorance.

Preventing Duplicate Record Insertion on Page Refresh

A colleague of mine just pointed me to this article. Personally, I never POSTed to the same page in my classic ASP and PHP applications. Never! I always POSTed to a different page with no HTML but only ASP/DB processing code and then redirected back or to an appropriate ASP/HTML page. To my believe, Web application should clearly distinguish client side processing and server side processing, I never liked Microsoft's attempts to mimic distributed computing by desktop-like ASP.NET page with Web Controls which pretend to be both server and client side. Obviously, it is not only my believe: even Anders Hejlsberg said something similar with his conversation with Bruce Eckel. Thus, .NET team started to look at MVC model too.

However, there are thousands of loyal ASP.NET developers who use classic ASP.NET approach for years. So, I bet, there should be a solution which is common and approved by Microsoft as a standard one. You simply cannot develop in ASP.NET without resolving this issue. So, could some of you, experienced ASP.NET developers, point me (a newbie in .NET world) to a standard solution?

Foundations of Programming and The Code Wiki Book by Karl Seguin

Karl Seguin allowed me to copy and display on my site PDF copies of his articles. I found it more convenient than to download original ZIP versions (zipped PDF of Foundations of Programming was created by Tim Barcz).
According to Karl, you are allowed to copy, distribute and display articles, provided that you always attribute articles to him, do not use them for commercial purposes and do not alter in any way.
Unzipped PDF documents allow for reading online. These articles are very interesting. Look at Karl's The Code Wiki site and read his blog.

For better understanding of what N-Tier and Domain-Driven Design are, I would probably recommend to read 2 chapters of The Code Wiki Book first, then to read Foundations of Programming article and finally, to read Building Layered Web Applications with Microsoft ASP.NET 2.0 article by Imar Spaanjaars as a good and simple practical example of N-Tier DDD approach.

Identity starts from 0 instead of 1. SQL Server bug?

We experience a strange problem. Identity field of a table is set to [1,1] by generating script. Every time, when we drop the whole database, and then re-create it by running script in SQL Server 2008 Management Studio, and then insert a record into that table programmatically (C# code), SQL Server set identity field value of that first record to 0 instead of 1!
Then we also programmatically delete all records from that table and then execute
DBCC CHECKIDENT('" + targetTableName + "', RESEED, 0)

All next runs of the same C# function inserts records correctly, with identity field starting from 1 as expected.

So, again, identity field does not want to behave correctly if database was just created by script. After inserting a record and deleting it, everything works fine.

Is it SQL Server 2008 bug? Any idea on possible workaround?

Scala and F#

I have heard what Bruce Eckel, Ian Cooper, and David Pollak said about Scala and decided to give it a try. I downloaded Scala and bought a pre-print PDF edition of Programming in Scala.
It is cool! It is extremely interesting. Martin Odersky's book is aimed to beginners and is outstanding. The only thing which bothers me is lack of Scala IDE.

Since Scala is a functional language related to ML languages and running on Java VM, and F# is a functional language related to OCaml and running on .NET platform, I decided to try learning both languages simultaneously. I installed F# addition to Visual Studio.NET and bought Don Syme's book. As everything born in Microsoft, F# already has Visual Studio.NET IDE support. On the other hand, "Expert F#" is harder to read, and F#'s syntax looks more strange for OOP programmer than Scala's one.
But it's interesting too! What i simportant, C# also got some functional features, so that's all inter-related.

Named constants and Enums: why and how to reconcile them against database tables?

Enums are very convenient, because they make code developer-friendly. If a method takes enum parameter, IntelliSense would help developer to choose a correct parameter value (and compiler would catch passing incorrect integer). On the other side, it is often a good idea to put put corresponding values into database lookup tables. It would allow to use primary/foreign keys to ensure data integrity (suppose, that your application is not the only way to read or modify data, so you want to be sure data is correct). Question is, how to synchronize enums with database?

Because VBScript does not have named enums, I used named string constants instead. One technique I employed in my Roles Rights Management (RRM) system was to auto-generate VBScript constants by reading lookup table and using eval() function during Application startup. It's much more developer-friendly to allow calls like

RRManager.HasRight (cnstCanSeePage, ControlNumber)

than to force a developer to use numbers like

RRManager.HasRight (1, ControlNumber)

Again, in C# we would use enums instead of string constants. The question is:
* is it better to auto-generate those VBScript string constants (or to auto-generate C# enums; I hope it's possible) from database values or is it better to reconcile hard-coded string constants / enums against database values on Application startup? What are ramifications of using each approach? *
If I remember correctly, Imar Spaanjaars also touched upon this issue in a discussion beneath his article.

Auto-generation of enums might be dangerous. Suppose that someone deleted a row from a database and as a result enumValueOne is not generated any more. Then, if another programmer calls AnObject.DoSomething(enumValueOne) application would probably crash.
On the other side, if you don't auto-generate, but rather reconcile enums against database on Application startup, and someone deleted a database record, your Application will immediately tell you about that problem and just won't start. It's safer. But it's less convenient to use this approach if available enum values changes frequently: now you keep essentially the same data in two places, so you have to modify code every time you add or delete a record from database. Not good!

I think the answer is:
- If a list of possibilities is fixed and is not going to be changed frequently, do not auto-generate Enumerations. Instead, reconcile them against database values on Application startup.
- If a list of possible enum values changes frequently, use auto-generation from a database.

What do you think?

Imar Spaanjaars

I mentioned earlier today that there still are a couple of articles on implementing N-Tier approach in ASP.NET which could be good (as opposed to junk published on 15seconds.com.) So, at least a first part of Imar Spaanjaars' article is very good. He is using very readable style. His approach features well structured business objects, and custom DAL objects. It certainly could be useful!

N-Tier with ASP.NET

1. Abstract:
For a last couple of weeks I've been looking for a good example of implementing N-tier approach for ASP.NET application. Our company is switching its Intranet from plain old ASP to a modern ASP.NET (3.5) application. It's been proposed to follow a modular N-tier approach.

2. Big Ball of Mud:
I was googling for N-Tier ASP.NET application. All I found was unbelievable bad. It looks like that all those authors from http://www.15seconds.com just heard buzzwords like N-Tier, O/RM, loose coupling, but never understood them. Instead they went ahead and implemented their little poor-designed, overcomplicated examples where all the layers are tied together in a Big Ball of Mud (BBM) where each layer directly uses ADO.NET and heavily depends on datasets with millions of useless methods;   - and they immediately published their articles. BBM examples:
Designing N-Tiered Data Access Layer Using Datasets
N-Tier Web Applications using ASP.NET 2.0 and SQL Server 2005

3. I still hope:
I'm still looking for a good example of N-Tier approach with ASP.NET and ADO.NET. I still hope that one of the following articles might be good:
Building Layered Web Applications with Microsoft ASP.NET 2.0
Implementing a Generic Data Access Layer in ADO.NET
Architecting LINQ To SQL Applications

4. Foundations of Programming (N-Tier, DDD, DI, NHibernate):
I was happy to find Foundations of Programming series of articles written by Karl Seguin at the very beginning of my exercises. Despite its theoretical name, this series shows fairly clear and practical approach of designing modular C# [Web] application with separated business layer (Problem Domain), Data access layer (DAL), and presentation layer.

There are simple C# examples. You implement business (domain) objects like a Car, a Model, an Upgrade; type-safe collections of those objects are implemented as generic lists - List<Car>, List<Model>, List<Upgrade>.

Domain objects are almost persistent-agnostic: they do not know in details how to save themselves to a database or retrieve themselves from a database. All they really should know is how to create an instance of class which implements IDataAccess interface and then to call one of its GetCar(), GetAllModels(), SaveCar(), etc. overloaded methods.

Domain objects are completely presentation-agnostic. That's a task of presentation layer to databind to business objects and collections. As Karl said, "You'll also be happy to know that ASP.NET and WinForms deal with domain-centric code just as well as with data-centric classes. You can databind to any .NET collection, use sessions and caches like you normally do, and anything else you're used to doing."

Then we can create or own DAL methods or we can use one of O/RM tools like NHibernate.
Everybody agrees that NHibernate (Hibernate for .NET) is very good. It's one of the most known and widely used O/RM tools. It's free and well-documented. For me - it's a way to go.

We also can use Dependency Injection tools like StructureMap to test pieces of code.


5. Our possible design outlines:

So, a picture is pretty clear. We need

1) To implement set of classes which represent our business logic. There would be

    public class Employee {}
    public class Project {}
    public class WorkActivityReport {}
    public class Contract : WorkActivityReport {}
    public class CorporateTask : WorkActivityReport {}

etc.

2) To implement collection classes which also represent business logic. I would encapsulate collections into static container/factory classes. There would be

public static class ContractList
{
      // private static List<Contract> m_contracts;
      public static List<Contract> getContractList(int projectId, int sortOrder) {}
}

It makes perfect sense to delegate part of business classes' functionality to "Factory" classes. So, it could be a

public static class ContractManger
{
      public bool saveContract(Contract thisContract) {}
      public List<Contract> getContractList(ContractType thisContractType) {}
}

With Factory classes we might not need custom collection classes, but just use generic collections instead. All calls to DAL layer methods will be performed by Factory classes' methods. It makes sense. I even used this approach in my little poor VBScript classes (ASP). Imar Spaanjaars recommends it too.

3) To implement DAL. (I vote for O/RM or, at least, custom DAL classes.)

4) To implement ASP.NET presentation layer using standard technique - master pages, code-behind, sessions, cache, etc.

I've been through implementing business logic before. In our current classic ASP application I implemented Roles Rights Management System (RRM) using VBScript classes. While VBScript is a bad tool, it still was a real pleasure to program with classes, to make a set of methods representing Roles/Rights business logic and to bind system to a normalized MS Access database. It's surprisingly easy to do, if you understand that OOP business layer comes first. It should not be database-driven design. It should be Domain Driven Design. You just create classes, properties, and methods for every entity your business needs. Everything else comes after it.