Sql Server DateTime Conversion

Suppose that your model requires selecting over a date range. It is frequently more convenient to store DateTime.MinValue than it is to store a NULL as this allows for easy selecting without doing a bunch of field < value or null or field > value or not null monkey business. The only problem is that DateTime.MinValue doesn’t fit into SQL server, and DateTime.MaxValue won’t store correctly either. You can even end up where you get a situation where you are storing DateTime.MaxValue then checking a freshly loaded entity to see if it equals DateTime.MaxValue and it doesn’t!

One solution is to create an extension method to trim down the DateTime to only include valid values in a deterministic way as follows:

    public static class DateTimeExtensions
    {
        private static readonly DateTime minSqlDateTime = DateTime.Parse("1/1/1753 12:00:00 AM");
 
        public static DateTime ToSqlDateTime(this DateTime dt)
        {
            if (dt < minSqlDateTime)
                dt = minSqlDateTime;
            return new DateTime(dt.Ticks - (dt.Ticks % 10000000));            
        }
    }

Note that this is designed for SQL Server. Other database types may need different levels of precision.

Instead you could implement a custom IUserType for NHibernate which tells NHibernate how to translate a DateTime that is out of bounds into one that is in bounds such as:

    public class SqlDateTimeUserType : IEnhancedUserType
    {
        private static readonly DateTime minSqlDateTime = DateTime.Parse("1/1/1753 12:00:00 AM");
 
        public new bool Equals(object x, object y)
        {
            return x == null ? y == null : x.Equals(y);
        }
 
        public Type ReturnedType
        {
            get { return typeof(DateTime); }
        }
 
        public SqlType[] SqlTypes
        {
            get { return new[] { NHibernateUtil.DateTime.SqlType }; }
        }
 
        public int GetHashCode(object x)
        {
            return x.GetHashCode();
        }
 
        public object NullSafeGet(IDataReader dr, string[] names, object owner)
        {
            //no need to alter values coming out of the database
            DateTime? obj = NHibernateUtil.DateTime.NullSafeGet(dr, names[0]) as DateTime?;
            return obj == null ? ToSqlDateTime(DateTime.MinValue) : obj.Value;
        }
 
        public void NullSafeSet(IDbCommand cmd, object obj, int index)
        {
            //set it to a safe value going into the database
            ((IDataParameter)cmd.Parameters[index]).Value = ToSqlDateTime((DateTime)obj);
        }
 
        public object DeepCopy(object value)
        {
            return value;
        }
 
        public object Replace(object original, object target, object owner)
        {
            return original;
        }
 
        public object Assemble(object cached, object owner)
        {
            return cached;
        }
 
        public object Disassemble(object value)
        {
            return value;
        }
 
        public bool IsMutable
        {
            get { return false; }
        }
 
        public object FromXMLString(string xml)
        {
            return DateTime.Parse(xml);
        }
 
        public string ObjectToSQLString(object value)
        {
            return value as string;
        }
 
        public string ToXMLString(object value)
        {
            return value as string;
        }
 
        private static DateTime ToSqlDateTime(DateTime dt)
        {
            if (dt < minSqlDateTime)
                dt = minSqlDateTime;
            return new DateTime(dt.Ticks - (dt.Ticks % 10000000));
        }
    }

These user types are mapped as follows:

    <property name="ImportDate" type="Framework.SqlDateTimeUserType, Framework"/>

The only problem with this is that a simple test such as the following will fail:

        [Test]
        public void CanSaveBadValuesToImportDate()
        {
            With.AutoRollbackTransaction(() =>
            {
                Product p = new Product("Apples");
                p.ImportDate = DateTime.MinValue;
                Session.Save(p);
                Session.Flush();
                Session.Clear();
                Product p2 = Session.Load<Product>(p.Id);
                Assert.AreEqual(p.Name,p2.Name);
                Assert.AreEqual(p.ImportDate, p2.ImportDate); //fails here
            });
        }

We could try and create a bunch of edge conditions on our converter such as:

        public object NullSafeGet(IDataReader dr, string[] names, object owner)
        {
            DateTime? obj = NHibernateUtil.DateTime.NullSafeGet(dr, names[0]) as DateTime?;
            if (obj != null && obj == minSqlDateTime)
                return DateTime.MinValue;
            return obj == null ? ToSqlDateTime(DateTime.MinValue) : obj.Value;
        }

But that could get out of hand in a hurry. It also would cause errors if you stored DateTime.MinValue.AddSeconds(1). If we wanted to go really over the top, we could create our own class that implements operator DateTime (explicit and implicit) which would do the conversion to a SQL range when we first assign a value. This would solve both problems, but you’d end up with your domain needing a special DateTime class for all of your entities instead of using the built in. Your call…

Posted in c#, NHibernate | 1 Comment

JQuery In Action

JQuery In Action:
347 Pages, not a single mention of the queue() method.

To be fair, they do mention “mainaining state”. (sic)

Posted in Uncategorized | Leave a comment

Is this the .future you were expecting?

ICANN RFC for anything.* domain names. Might want to take a look. Most of you readers play in this here internets thing, this could effect you or the company you work for. :)

Posted in Uncategorized | Leave a comment

ActionFilter / ParameterBinder sequence

For future reference for myself and others:

1. CustomModelBinderAttribute -> GetBinder()
2. IModelBinder -> BindModel()
3. ActionFilterAttribute -> OnActionExecuting()
4. ActionFilterAttribute -> OnActionExecuted()
5. ActionFilterAttribute -> OnResultExecuting()
6. ActionFilterAttribute -> OnResultExecuted()

(As of Beta 1)

Posted in Uncategorized | Leave a comment

Stack Overflow! MVC Model Binders Gone Awry

The recently released MVC Beta 1 has some pretty cool features. One of the features that I’ve been using for a while is Model Binders. These are now turned on by default which can cause some problems if you’re not expecting this behavior. For example, say you have an action as follows:

        public ActionResult Binder(Dog dog)
        {
            return View("Index");
        }

Here’s the model:

    public class Dog
    {
        public string Name { get; set; }
        public Dog cat { get; set; }
    }

Calling this action will cause a stack overflow error, as the default model binder will attempt to recreate every dog in the chain.

I would not have noticed this as I use my own implementation of the model binders to provide functionality for pulling an entity from NHibernate that is passed by ID via Ajax. The problem is that I recently switched over to the [Transaction]/ATM method of managing transactions with NHibernate. The problem with this method is that it creates a dynamic proxy of your controller so it can intercept the transaction. In doing so it looses the method argument attributes and then – boom – stack overflow. So now I’m off to hack YET ANOTHER open source project to see if I can get it to not remove the method parameter attributes. :(

Posted in Uncategorized | 3 Comments

Dead Wrong UI Design

I am a Microsoft Fan Boy. Today I’m going to show how good a friend I am and point out the bit of cilantro stuck between the teeth of Microsoft SQL Server Management Studio Express.

For this example I’m designing a new table in the management tool, using a bit field to indicate if a pet is dead or not. (Nevermind the obvious flaw that there are several types of dead.) Obviously I want a default value of NOT-DEAD or as we like to call it around here: 0.

Dead Bit
Ok, no problems so far. Lets put in some sample data:

Dead Wrong

Wait a minute now. You mean that when I specify the default value I must put a zero, but when I enter data into the table I have to type out ‘false/true’ for every entry? Ohh, and just in case you’re curious no, you can’t type ‘false/true’ in for the default value.
I’m sure none my readers would ever do anything like this. :)

(All 5 of you.)

Posted in SQL Server | Leave a comment

Create an ISession specific cache

Sometimes it is useful to create a cache specific to a particular session. I use this for pre-NH cache in my repositories that I can query with Linq before hitting NH sometimes. You could easily modify this to not be templated and instead work for any object…

If you think there is something wrong with this approach please let me know. :)

public class SessionCache<T>
    {
        private ISession sn;
        private IList<T> entities;
 
        private void ValidateSession(ISession session)
        {
            if(sn == session)
                return;
            sn = session;
            entities = new List<T>();
        }
 
        public void Add(ISession session, params T [] entity)
        {
            ValidateSession(session);
            foreach (T t in entity)
            {
                if(!Equals(t, default(T)) && !entities.Contains(t))
                    entities.Add(t);
            }
        }
 
        public void Remove(T entity, ISession session)
        {
            ValidateSession(session);
            if(entities.Contains(entity))
                entities.Remove(entity);
        }
 
        public T[] GetEntities(ISession session)
        {
            ValidateSession(session);
            return entities.ToArray();
        }
 
        public void Clear()
        {
            if(entities != null)
                entities.Clear();
        }
    }
Posted in Uncategorized | 1 Comment

ModelStateDictionary and ParameterBinding

I laid this out in more detail on the forums here:
http://forums.asp.net/p/1317154/2610342.aspx#2610342

I thought I’d mention it to this group so that people were aware of this difference between the interaction the old ParameterBinders and Rescues in version 4 and the new ModelBinders and HandleErrorAttributes of version 5.

The new way of doing parameter binding expects all errors to be placed into the ModelStateDictionary specified in the GetValue method.
public override object GetValue(ControllerContext controllerContext, string modelName, Type modelType, ModelStateDictionary modelState) {

The ModelStateDictionary can then be checked in the controller for errors via ViewData.ModelState.IsValid

I think the idea is that multiple binding errors can then be reported to the user at one time:
foreach (var value in ViewData.ModelState.Values)
{
value.Errors…..
}

Unfortunatly our current NameValueDeserializer that is used by the DeserializeAttribute throws exceptions when it encounters errors. These exceptions are NOT caught by Rescues / HandleErrorAttributes. The ControllerActionInvoker (in preview 5) calls

IDictionary parameters = GetParameterValues(methodInfo);

before the try/catch block that passes off errors to the HandleErrorAttributes. So if you’re using any kind of attribute deserialization and expecting your exceptions to be displayed all pretty like in your rescues… then move line #177 to line #180 of your ControllerActionInvoker.cs :)

-Will

Posted in ASP.Net, c# | Leave a comment

Mvc Preview 5 Authorization Filters

Looks like the bits were changed some for preview 5 around the action filters. The ‘Cancel’ property was removed from the ActionExecutingContext. It looks like for my purposes I can use IAuthorizationFilter and FilterAttribute to get at an AuthorizationContext which has the cancel attribute.

Posted in Uncategorized | Leave a comment

Multi Monitor Wallpapers at FrozenTear.com

I’ve put up some of my multi monitor wallpapers at frozentear.com. If you develop on a 3200×1200 screen you might want to check this out. No promises, I’m a coder not an artist. :)

Posted in Other | Leave a comment