.NET 2.0 transaction model

How to handle transactions using ASP.NET 2.0 and SqlClient. Nice article here.

Serialization of IDictionary objects

By design, objects that implement IDictionary (Hashtable, SortedList, ListDictionary, or HybridDictionary) cannot be serialized. This Q&A describes a way of making these objects serializable by using (a "hidden hook"), and implementing IXmlSerializable: http://msdn2.microsoft.com/en-us/magazine/cc164135.aspx

Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.

Using SQL Server 2005 XML And Unpivot

I have a table with contracts stored as XML. Using the XML query possibilities in SQL Server 2005 together with the UNPIVOT keyword I can get statistics on different types of contracts.

My table has these columns:
ContractNumber int (Primary Key)
contractXML xml

My XML looks something like like this, and is stored in a column of type XML:

<Contract>
  <Customer>
  ...
  </Customer>
  <Order>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product3</Name>
      <Price>13.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product2</Name>
      <Price>23.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
  </Order>
</Contract>

To get a table with a max of 5 OrderItems as columns, I can use this query:

    SELECT
      ContractNumber,
      contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)') AS Product1,
      contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)') AS Product2,
      contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)') AS Product3,
      contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)') AS Product4,
      contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)') AS Product5
    FROM Contract

 

This is nice, but what if I want to get the number of each product sold? The answer is that I can use the UNPIVOT operator!
Something like this will do it:

SELECT Name, COUNT(*) AS [Count] FROM
(
    SELECT
        ContractNumber, col, Name
    FROM
    (SELECT
    ContractNumber,
    [col1] = contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)'),
    [col2] = contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)'),
    [col3] = contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)'),
    [col4] = contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)'),
    [col5] = contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)')
    FROM Contract) col
    UNPIVOT(
        Name
        FOR col
        IN ([col1],[col2],[col3],[col4],[col5])
    ) AS unpvt
) AS T
GROUP BY PackageName

The UNPIVOT operator gives the values in the 5 columns as 1 column.

If I had only the 1 row in my Contract table from the example above, the result would be:

Name	Count
Product1	3
Product2	1
Product3	1

This is a simple example, and it has a max number of ordered products per contract of 5. Could maybe be extended.

If you want a table with the counts for the different products as columns, then something like this would do the job:

SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract

This would give this result:

Product1	Product2	Product3
3	1	1

And we could of course UNPIVOT this result too:

SELECT
   ProcuctCount
FROM
(SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract) cols
UNPIVOT(
   ProductCount
   FOR cols IN (Product1, Product2, Product3)
) AS unpvt

This should give a table like this:

ProductCount

3

1

1

Apache Leap Year Bug

Seems Apache (Web Server) has some rather embarrassing problems with leap years: http://blogs.lodgon.com/johan/Leap_year_issues_in_apache_commonsnet
https://issues.apache.org/jira/browse/NET-188

Gøran's blog

I see that this guy, who was a presenter at MSDN Live in Oslo yesterday, has some good links and stuff relating to WPF and hopefully soon something on MVC (Model-View-Controller): http://blog.goeran.no/CategoryView,category,Presentation.aspx

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

This blog entry explains how to read the "login failed" error message for SQL Server 2005. The messages can be very cryptic, like for instance "Error: 18456, Severity: 14, State: 8. It is the "State" part that tells you the reason the login failed.

Windows Workflow Foundation Links

This article by Don Box and Dharma Shukla should be good: Simplify Development With The Declarative Model Of Windows Workflow Foundation

The most trivial and useless WF application, to get newcomers started: http://www.codeproject.com/KB/WF/HelloWF.aspx

Jump start WF: http://www.codeproject.com/KB/WF/JumpStartWF.aspx

Windows Live Writer

Blogging just became easier. Using the Windows Live Writer it's pretty easy to make blog entries.

Developing Enhanced Web Experiences with Microsoft® ASP.NET AJAX Extensions

https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=117972

Intro to Continuous Integration with VS2008

This should be interesting:
http://blogs.msdn.com/buckh/archive/2007/08/14/tfs-2008-a-basic-guide-to-team-build-2008.aspx

Comparison of different versions of SQL Server 2005 features

Needed to know if SQL Server Express Edition supports indexed views, and found an article on the Microsoft Web Site. Uh-oh... computer says NO!

CustomValidator dependent on multiple controls

This article describes how to validate 2 or more controls to see if at least 1 of them has content.

Microsoft Office XP Resource Kit downloads

Useful stuff for MS Office Developers.

The underlying connection was closed: Unable to connect to the remote server.

This error sometimes occurs when consuming webservices through a proxy.

Load Balancing / EPiServer

Just putting in a few links so I won't loose them:

How to configure a Windows Server 2003 Load Balancing cluster:
Web Farming with the Network Load Balancing Service in Windows Server 2003

EPiServer:
Configuring the Cache in Multi-Server Scenarios
Configuring EPiServer Enterprise Edition

Script error on ASP.NET 1.1 pages

I got an error message on my aspx web page after one of the latest Windows updates:

Unable to find script library '/aspnet_client/system_web/1_1_4322/WebUIValidation.js'. Try placing this file manually, or reinstall by running 'aspnet_regiis -c'.

After trying to reinstall the script library, and reinstalling aspnet on my web application, I found a solution by simply adding the following code to my aspx file:


<script language="javascript" type="text/javascript" src="/aspnet_client/system_web/1_1_4322/WebUIValidation.js"></script>

Update:
After I put in the script-tag above in my "master page", I got an other error on pages not having any validation controls, so I had to put in a hidden dummy validator.

Changing Locale of the ASPNET account

I have a server that has been installed using default language/locale "en-US".

This becomes a problem with the date format, which is "MM/dd/yyyy" in en-US, while in Norway where I live we use (nb-NO) "dd.MM.yyyy".

There is also a problem with which character to use as a decimal point and which to use as a thousand marker/separator:
Norwegian (nb-NO): decimal point is comma (",") , thousand marker is space. Ex: 2 345,67
US English (en-US): decimal point is dot ("."), thousand marker is comma. Ex: 2,345.67

So I need a way to set the locale that the ASPNET account is using.

There are several ways. You can set this in the web.config file:

<system.web>
    <globalization
        culture="nb-NO"
        uiCulture="nb-NO" />
</system.web>

You can also set it in the users session, by using the Session_Start event handler:

protected void Session_Start(object sender, EventArgs e)
{
    this.Session.LCID = 1044;
}

If you want to change the default settings of the ASPNET account (and you have the guts), you could go in and change the settings in the registry.

NB! It may be risky to change settings in the registry. The author of this blog is not responsible for any damage that may be caused by doing so.

Anyway here it is, change the settings under the following key:

HKEY_USERS\S-1-5-20\Control Panel\International

Here you can see settings for number formats, date formats languages etc for the user. I have as of now NOT TESTED THIS, but the key for the ASPNET user should according to a news group be S-1-5-20.

Good luck!

ADODB serialization

Found a simple but cool way to serialize an ADODB Recordset without converting to DataSet on CodeProject.

Security Developer Center: Security Tips: Defend Your Code with Top Ten Security Tips Every Developer Must Know

Not sure if I have bookmarked this before. Well, better safe than sorry...