Dionoid

FileSharedPersonalizationProvider

Looking at the provider model of ASP.NET 2.0, I thought it would be nice to write my own PersonalizationProvider. Just to see how it all works.

The WebPart framework in ASP.NET 2.0 uses a PersonalizationProvider to read and store it's WebPart data (i.e. information about the webparts on a page). WebParts can be added for SharedScope (for all users) and UserScope (per user). Because I'm only interested in the SharedScope WebParts, I thought it would be a good idea to store this SharedScope information somewhere on the filesystem, and not in a database (like the default PersonalizationProviders do).

Even better: why not store the SharedScope pagesettings inside the .aspx file itself, as a Base64 encoded string in the 'Description' attribute of the <%@ page %> directive! An .aspx page containing SharedScope PageSettings would then look something like this:

By reading the SharedScope PageSettings from the .aspx page itself, no database interaction is needed! Also, the PageSettings can be put in Cache, with a dependancy to the .aspx file itself. So by writing new PageSettings to the .aspx page, the Cached PageSettings are invalidated automatically.

To use the FileSharedPersonalizationProvider, configure it in the web.config like this:

And here's the source code for the FileSharedPersonalizationProvider. Note that my FileSharedPersonalizationProvider:

• doesn't load or save UserScope pagesettings,
• throws errors when the impersonated user has no rights to write the .aspx page
• doesn't use a lock for reading and writing files

Posted Thursday, March 10, 2005 10:17 AM by Dr.NETjes | 2 comment(s)

XML querying in SQL2000

Do you want to query on unstructured xml data inside SQL2000 text fields? Keep on reading!

I've created a table 'Person' with a text field containing flat xml data (see below):

Now I'd like to query the xml-data in the 'ContentXml' field, for example:

“Select all rows where 'birthdate' is at least '1/1/1970', and 'gender' equals 'male'. “

Using SQLServer2005, querying on data inside xml fields is as easy as:

SELECT *
FROM   Person
WHERE  ContentXml.value('(//birthdate)[1]', 'DateTime') >= CAST('1/1/1970' AS DateTime)
       AND ContentXml.value('(//gender)[1]', 'VarChar') = 'male' 

But what if you're using SQLServer2000 and want a solution right now? SQLServer2000 doesn't “know” xml.....
Well, take a look at the query below, running on SQLServer2000 and returning the same results:

SELECT *
FROM   Person
WHERE  dbo.fn_XmlElementValue(ContentXML, 'birthdate') >= CAST('1/1/1970' AS DateTime)
       AND dbo.fn_XmlElementValue(ContentXML, 'gender') = 'male'

You want to see the fn_XmlElementValue function? Here it is:

CREATE FUNCTION dbo.fn_XmlElementValue(@xml text, @tagname varchar(100)) 
RETURNS varchar(2000) 
AS 
BEGIN 
 DECLARE @startpos int, @endpos int 
 IF (NOT @tagname IS NULL) 
 BEGIN 
  SET @startpos = CHARINDEX('<' + @tagname + '>', @xml) + LEN('<' + @tagname + '>') 
  IF (@startpos > 0) 
  BEGIN 
   SET @endpos = CHARINDEX('</' + @tagname + '>', @xml, @startpos) 
   IF (@endpos > @startpos) 
   BEGIN 
    -- Return the requested value 
    RETURN (SUBSTRING(@xml, @startpos, @endpos - @startpos)) 
   END 
  END 
 END 
 --  Tag empty or not found 
 RETURN NULL 
END

Is this slow? No, certainly not! I just inserted 10.000 Persons in my table, containing xml data with random birthdates between 1/1/1960 and 1/1/1980. The select query is still very quick, and seems even quicker than the corresponding SQLServer2005 query...

Note: the given solution is only suitable for flat xml data, where the elements have unique tag-names and don't have attributes but only values.
But then again, it's a pretty powerful solution if you want to add fields to a table without having to change the table's design.

Posted Wednesday, March 02, 2005 11:02 PM by Dr.NETjes | 3 comment(s)