SQL: Convert a Hash to a Varchar

For some reason this task always stumps me, so here is the solution:

CONVERT([varchar](512), hashbytes('sha', col), 2)

Thanks to Basit

JQuery Validate: Change Option Value

In my current project I have a default set of options that every form gets validated with. In fact, .validate(options) is called for every form.

Today I needed to change one of the options that my form validator was initialized with. Sounds pretty simple, but for the life of me I couldn't find anything like setOption() or .validate("option", {}) like other plugins have.

Turns out that Validate actually makes it too simple. Where other plugins give you non-standardized functions to update the options, Validate gives you the options object directly... but they call it settings.

I can disable the auto onsubmit validation by doing the following:

$("#myForm").validate().settings.onsubmit = false;

This works because .validate() returns the current validator for the form, and the validator neatly exposes the settings collection.

Extending QueryOver With "Or"

QueryOver (introduced in NHibernate 3.0) offers type safe, Linq-esque, syntax for writing Nhibernate queries in your DAO. However, writing a multiple column disjunction with anything other than simple operators can easily become ugly and unwieldy.

T-SQL: Aggregate a column to a comma delimited list

I commonly encounter situations where it is useful to select a comma delimited list as an aggregate in a grouped query. This is a well known problem and yet each time I run across it I have to look something up to solve it.

The following is a detailed breakdown of the solution from the msdn archive.

The scenario I'm covering here is the need to aggregate all the IDs in a joined table into a single column on the master table. We'll use a table structure like this:

CREATE TABLE Parent (
  Id int NOT NULL identity,
  Name varchar(50) NOT NULL,
  Children varchar(256) NULL,
)
CREATE TABLE Child (
  Id int NOT NULL identity,
  ParentId int NOT NULL,
  Name varchar(50) NOT NULL
)

Create some sample data:

INSERT INTO Parent (Name)
VALUES ('Parent 1'), ('Parent 2'), ('Parent 3')
INSERT INTO Child (ParentId, Name)
VALUES (1, 'Child 1'), (1, 'Child 2'), (1, 'Child 3'), (2, 'Child 4'), (2, 'Child 5'), (3, 'Child 6'), (3, 'Child 7'), (3, 'Child 8'), (3, 'Child 9')

Now we can update the Children column on Parent with this:

;
WITH
t AS (SELECT p1.Id, Children = (
    SELECT (',' + convert(varchar, c2.Name ))
    FROM Parent p2
      JOIN Child c2 ON p2.Id = c2.ParentId
    WHERE p2.Id = p1.Id
    ORDER BY c2.Id
    FOR XML PATH( '' )
  ) + ','
  FROM Parent p1
    JOIN Child c1 ON p1.Id = c1.ParentId
  GROUP BY p1.Id)
UPDATE p
SET Children = t.Children
FROM Parent p
  JOIN t ON t.Id = p.Id

And the results look like:

Id | Name | Children
1 | Parent 1 | ,Child 1,Child 2,Child 3,
2 | Parent 2 | ,Child 4,Child 5,
3 | Parent 3 | ,Child 6,Child 7,Child 8,Child 9,

ActiveReports: Grouping

We've used Grape City's (formerly Data Dynamics) ActiveReports off and on for many years. I'm not truly a fan since personally I've never found it that intuitive, but for flexibility they're pretty good.

Recently I ran into a quirk I've encountered before but had forgotten. I won't call it a bug since we're using it in a manner that is undocumented and most likely not supported.

Since AR uses (or appears to use) DataBinder.Eval to evaluate the DataField on each object in the DataSource of a report, we have always used collections of domain objects for our report sources. This works well for textboxes, however, when you try to do a standard report grouping (with header and footer) you'll get stuck wondering why the report only seems to recognize one group. Specifically this occurs when you use a property path of depth greater than one.

The following works:

myTextbox.DataField = "Property1.Property2";
myGroupheader.DataField = "Property1";

This does not:

myTextbox.DataField = "Property1.Property2";
myGroupheader.DataField = "Property1.Property2";

The textbox will populate correctly but the report will only discover one group of records.