OK, I should be using an ORM, I know. So I’m using the opportunity to illustrate problems you WILL encounter when hand writing SQL.
Spot the error here:
1: INSERT INTO ValueReading
2: IndexName, SketchId, Value, Color, MeterId, ModeId, MaterialId)
3: VALUES (@id,@index,@sketch,@color,@value,@meterId,@modeId,@materialId)
Hint: look at the Value and Color columns. And note: Color is an integer, Value is a string.
Now tell me how I would duplicate that problem with a good ORM?
With any good ORM this error COULD still happen, but it would break at COMPILE TIME. As is, this will only break at RUN TIME. Why, because with an ORM you get type checking — which the compiler knows about.
BTW: don’t say that a stored proc would fix this, not every database has stored procs, and the one I’m currently using does not, so the argument is not valid in this case.
Actually, you could screw up your mapping file and get the columns the wrong way around 🙂 The ORM might catch this at “warm up” time as it’s interpreting the mapping file. I might be being dim here (it’s been a long day), but which ORM do you know which captures this at compile time?
I wouldn’t be too hard on yourself for not using an ORM though, I love em only 95% of the time 🙂
@Tobin: I am 100% with you on the “95% of the time” comment.
To be fair, you almost have to break up ORMs into two categories: those that create the mappings for you (SubSonic, LINQ to SQL, etc), and those that require you to create the mappings (NHibernate, …).
I was mainly referring to the first type. Although, because of the syntax with the mapping files, it is still much harder to make that mistake.
If you look at an NHibernate property mapping (using the hbm files), both the column and the property will be mapped together on the same entry. Plus the order of the properties in the mapping file doesn’t matter. Nor does the order in which you set the properties on the object you are about to save.
Where as with an INSERT statement, the order of the column and the data being passed in must be exact, plus the two items are not right next to each other (in SQL, I put them on separate lines so I can sort of visually match them up).
This is just one, of many, ways that I believe that SQL stinks as a language.