Writing a recursive query using Common Table Expressions

In our following example, 2 tables are linked to each other.

The product table is linked to the category table. In the category table, categories can have several levels of parent-child relations.

For examaple:
The category table can contain:

      Food -> Beverages -> Beer

           OR

      Food -> Beverages -> Sodas -> Cola

The Product table however can contain:

      Duvel -> Beer

How to get the products and their corresponding category fast, by defining the root category as a parameter?

You can do this with a recursive query using CTE (common table expresssions)

Our category table and product table:

 

 The common table expression which will return all items for category 'Food'

1: WITH  RecursiveQuery  AS 

2: ( 

3:  SELECT   c1.ID,  c1.Category  ,  CAST(  AS  NVARCHAR( 50))  AS  Parent  ,  0 AS  Level  ,  c1.ParentCategory 

4:  FROM     Category  c1 

5:  WHERE    c1.ParentCategory  IS  NULL 

6:   AND  c1.Category  =   

7:  UNION  ALL 

8:  SELECT   c2.ID,  c2.Category  ,  RecursiveQuery.Category  AS  Parent  ,  Level  +  1 AS  Level,  c2.ParentCategory 

9:  FROM     Category  c2 

10:  JOIN  RecursiveQuery  ON  RecursiveQuery.ID  =  c2.ParentCategory 

11: )

12: 

The select statement returning the products with their respective category based upon the main category 'Food'

1: SELECT   Category,  

2:   ISNULL(Products.Product  ,  )  AS  Product,  

3:   ISNULL(Products.UnitPrice,  0)  AS  UnitPrice 

4: FROM     RecursiveQuery 

5: INNER  JOIN  Products  ON  Products.CategoryID  =  RecursiveQuery.ID 

6: ORDER  BY  Level  ,Parent,  Category

7: 

See the result:

Have Fun!

SQL Server Day 2010

Thursday, December 2nd, 2010.
San Marco Village in Schelle (Antwerp)
 
Book those calendars now!

Export Excel sheet to PDF document using Aspose

Last month I stumbled upon a business issue, which would allow an application to extract a single worksheet out of an excel document and save it as a PDF document.

Using Aspose, this can be done easily!

public class XlsToPdf
    {
        public void ExportSheet()
        {

            ///lets say we have an xls document containing 6 sheets
            ///each strongly named like sheet1 => sheet6
            ///suppose that you need to save sheet5 as a pdf document.

            string sourceWorkBook = "source.xls";
            string destinationDocument = "Destination.pdf";

            Aspose.Cells.Workbook sourceWb = new Aspose.Cells.Workbook();
            sourceWb.Open(sourceWorkBook);

            foreach (Aspose.Cells.Worksheet workSheet in sourceWb.Worksheets)
            {
                if (!workSheet.Name.ToUpper().Contains("sheet5"))
                    workSheet.IsVisible = false;
            }


            ///First thing to do is to save the xls in xml format, 
            ///but the formattype used already refers to PDF
            string destinationDocumentXml = "destination.xml";
            sourceWb.Save(destinationDocumentXml, 
                Aspose.Cells.FileFormatType.AsposePdf);


            Aspose.Pdf.Pdf pdfDocument = new Aspose.Pdf.Pdf();
            //bind the created xml to the PDFDoc you intialized.
            pdfDocument.BindXML(destinationDocumentXml, null);
            //Save...
            pdfDocument.Save(destinationDocument);

            ///If necessary, you can cleanup and remove 
            ///the intermediate files created
            ///using DirectoryInfo and files delet techniques from the System.IO
            ///...code goes here
        }
    }

You can find the Aspose help here: Cells and PDF

Have fun

Parser error : “The async attribute on the page directive is not allowed in this page”

Last week I bumped upon this error while I was trying to do an asynchrounous WCF service call from my "MS Project 2007 Web Access"-custom user control.

Turns out that you cannot set the Async="true" atttibute in the page directive af an aspx page in the Project Web Access.

The reason for this? Very simple ... the that SharePoint’s SPPageParserFilter class blocks some page directive attributes. When you use other attrubutes than the following list, you will get the error "The 'xxx' attribute on the page directive is not allowed in this page."

The list:

• autoeventwireup
• buffer
• classname
• codebehind
• codepage
• compileroptions
• contenttype
• culture
• debug
• description
• enabletheming
• enableviewstate
• enableviewstatemac
• errorpage
• explicit
• inherits
• language
• lcid
• lineparagmas
• maintainscrollpositiononpostback
• masterpagefile
• responsencoding
• smartnavigation
• strict
• stylesheettheme
• targetschema
• theme
• title

To take care of this issue we chose to have a webservice call which will handle the Ascynhrounous WCF call, and from our web user control we called this webservice method sycnhrounous.... Maybe this is not the best solution, but we really had an issue, so a quick fix was necessary here...