mShed

Unique Column That Allows Multiple Nulls

2009-04-06T16:01:00.000+01:00

Sometimes it may be necessary for a column to hold unique values, but still allow multiple nulls. When this scenario occures then a Unique Index will not suffice as all values within the column MUST be unique, meaning it will only allow a single null value.

One solution is to include an INSERT, UPDATE trigger on the table that checks the value being inserted/updated to ensure it is unique, but will ignore null values. The following trigger does the trick:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_TRIGGER_NAME] ON [dbo].[TABLE_NAME]
  FOR INSERT, UPDATE
AS
  BEGIN
    SET NOCOUNT ON ;
    IF EXISTS ( select  1
                from    inserted AS i
                        inner join dbo.TABLE_NAME AS t on i.[COLUMN_NAME] = t.[COLUMN_NAME]
                group by t.[COLUMN_NAME]
                having  count(t.[COLUMN_NAME]) > 1 ) 
      BEGIN
        ROLLBACK
        RAISERROR ('[COLUMN_NAME] must be unique', 16, 1)
      END
  END

Replace the values [TG_TRIGGER_NAME], [TABLE_NAME], [COLUMN_NAME] to match the names used within your database.

Converting XML to CSV using C#

2009-04-05T10:14:00.000+01:00

While messing around with C# to converting XML to another format using XSLT (XSL Transformation), I decided it would be fun to try and convert an XML file to CSV as shown (figure 1). The C# code below is what I used to apply the XSLT:


//Create a new XML document and load the XML 
fileXmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("cdcatalog.xml");

//Create an XSLT object and load the XSLT file            
XslCompiledTransform xslTran = new XslCompiledTransform();
xslTran.Load("csv.xsl");

// This is for generating the 
outputXmlTextWriter writer = new XmlTextWriter("cdcatalog.csv", System.Text.Encoding.UTF8);

//Apply the transformation and write disk
xslTran.Transform(xmlDoc, null, writer);

//Close the writer
writer.Close();

I thought the C# code was going to be more difficult, but it was a nice surprise to find that I only needed to write 7 lines of code. Next, I had to produce the XSLT. This was more difficult because I wanted to include the headings at the top of the CSV file. After some research on Google, I generated the following transformation. It is split into two parts. The first generates the headings and the second outputs a record on each line which has the fields comma delimeted with strings surrounded by speach marks:

The first record in the XML file is used to determine the titles at the top of the CSV file, this can be changed by amending the XPath used in the first . Below is an example of some XML which the XSLT can transform into a CSV file:




        
                Empire Burlesque
                Bob Dylan
                USA
                Columbia
                10.90
                1985
        
        
                Hide your heart
                Bonnie Tyler
                UK
                CBS Records
                9.90
                1988
        
        
                Greatest Hits
                Dolly Parton
                USA
                RCA
                9.90
                1982

For the XSLT to work the XML needs to be in a rigid format like above. If you would like to use it for any of your projects then it may be nesessary to change the XPath conditions so that it matches your XML files.

NOTE: the "title" title will actually be "cdtitle"

Thats all folks!

Cork Screw At Alton Towers

2009-04-05T09:49:00.002+01:00

Not sure why I had this picture on my hard drive but thought somebody may be interested in it :-) so here it is:

This was one of the first roller coasters I ever went on and thought it was fab, it's such a shame that it is no longer.

An Orb In My Lounge!

2009-04-05T08:58:00.004+01:00

While looking at a few photographs I have taken, I've noticed that I have captured an orb in my lounge.

What are these orbs, are they really supernatural? Some people would say it is from the flash reflection within the TV. I'm not so sure because of the angles, but I will leave the conclusion upto you!