Showing posts from March, 2011

Elmah: to find more, query AllXml as Xml!

select convert(xml,AllXml)
.value('(//queryString/item[@name="id"]/value/@string)[1]','nvarchar(255)') as product_id
, errorid
from elmah_error
where convert(xml,AllXml)
.exist('//item[@name="PATH_INFO"]/value[@string="/products/product.aspx"]') = 1

Since Elmah stores in the AllXml column many informations for the logged errors, those can be queried in the XPath/XQuery fashion. The AllXml values needs to be converted to the Xml type and the great thing is MS-SQL Server supports XML queries.

This sample outputs all values used for the "id" query string field, at the "/products/products.aspx" requests. In this way will get a good idea what to fix on that page.

Totals on Reports using LinqDataSource

One common request for reporting is to show aggregations as well, like sums, averages, counts, etc among with the report itself. So for example if an expenses/charges/profits report shows the list of all products sold in some date/time range, then the person who analyzes the report would like to see and some totals (sum of the costs, sum of the charges, sum of profits and the total number of products), somewhere in the report's bottom.

If the number of reports is slightly lower and the features are limited, like the reports need to support only the HTML version, LINQ with LinqDataSource can be the right thing to use, for RAD reports. The control is built so the developers can write less and do more, so basically they set the ContextTypeName, TableName, Select statement if any, WhereParameters, OrderParameters, etc, but this control it doesn't yield the aggregates.

I extended the LinqDataSource in the spirit of "write less do more" principle and I changed its default be…