Dec 5, 2011 at 10:43 PM
Edited Dec 6, 2011 at 7:04 AM
FormattedMessage is a free form text column formatted according to the formatter. If you want to query on the FormattedMessage column then you will have to use LIKE or parse the column data to locate what you are interested in. E.g. you could
store XML in the FormattedMessage and use SQL XQuery or retrieve into .NET and query using LINQ To XML.
If you have data in the FormattedMessage column in a format you know then you could query it. Assume that you store pageName in FormattedMessage as [[PageName]]=myPage.aspx; then you could query it:
select * from [log] where Timestamp > '11-21-2011'and FormattedMessage LIKE '%[[PageName]]=' + 'myPage.aspx;%'
If you wanted to extract the PageName then you would have to parse the FormattedMessage text.
Of course, this is not particularly efficient or elegant.
If you create a custom DatabaseTraceListener with a custom column then you can query on that column as well as having easy access to the column data in your query result.
If you go with a normalized ExtendedProperty table (as per the example I sent you) then you could just query data like this:
SELECT ep.Value as PageName
FROM [log] AS l
LEFT OUTER JOIN ExtendedProperties AS ep ON ep.LogID = l.LogID WHERE l.TIMESTAMP > '11-21-2011'
AND ep.[Key] = 'PageName'
Or create some views so you don't have to specify the joins and the view will return properties you are interested in.
Enterprise Library support engineer