The 20 second commute

The story so far - we can filter and sort a database table and select columns to export to Excel.

Ok , that's not bad .... but its not that great either ...  what if you want to use a general query that combines fields from various tables?

Funny enough , this didn't take much to implement either ...   just a slight extension to the schema .....

then change the CmdString generation to use the sqlQuery attribute value - if a value is entered

not forgetting to change the Column2Display List<Of String> ...

which we use to generate the table rows ....

The net result is if we use the following xml request .... contained in say <OrderDetails.xml>

[Notice how we use C0 -> C12 in our query - which we can then use as the required column names  in any order and only the ones we want]

Then by entering the following at a command prompt [or better still - create a shortcut ...]

C:\SQL2Excel OrderDetails.xml

we get ....

Download the latest version and have even more fun !!

Jonathan Aneja  a PM on the VB Team [these lads are really pulling out all the stops] has written a great article entitled XML Programming in Visual Basic 9.0 in the February 2008 edition of MSDN Magazine. Covers the subject very well in just 3 pages

Slan [traditional Irish salutation - here's wishing you well]

There I was sitting back all smug and happy with my routine until someone pointed out

• what about if you try and export a large file - wouldn't you need to filter it ?
• and sorting wouldn't be bad either
• and while you're at it are you sure it can handle null values ? [Well spotted Avner]

Oh dear , back to the drawing board .....

It turned out that sorting and filtering was easy to include by including 2 extra attributes in the Tabledetails schema - a 'Where' attribute and an 'OrderBy' attribute where the user can either enter appropriate values or leave blank in which case the Where attribute defaults = '1=1' and the 'OrderBy' defaults to the Name value in the first Column i.e. CustomerID in this case.

then the additions of 4 lines of code sets up the default values

and extended the cmdString to query the database

and I get

To sort out the last issue - took several cups of coffee - Internet chat with a fellow nerd - it's true what they say - a problem shared is a problem halved.  - the solution ended up as being a simple cast of the appropriate attribute to string - if the attribute is null a blank value is placed in that cell.

Ok - so what have we got now ?

Ability to export columns from a filtered table taken from a database on your server into an excel spreadsheet

[that should keep that bean-counter happy - just place the SQL2Excel.exe in his windows folder - set up an xml file containing the particular TableDetails amended to suit his query in his documents folder - create a shortcut with a command line of 'SQL2Excel  <YourLayout.xml>'  and as they say over here in Connemara - YOU'RE AWAY FOR SLATES [Cryptic translation : the house you're building is weather sealed and the only job left is to place the slates on the felt]

Download the latest version and have some fun !!

[I reserve the right to extend this app to allow the inclusion of a SQL Query to allow cross table querying - watch this space]

Beth Massi  a PM on the VB Team has been posting some great content on her blog. One in particular caught my eye - Quickly Import and Export Excel Data with LINQ to XML - as I was involved in a project where I needed to do just that.

So , Beth's routine works great , but the lazy Irish Man in me took hold and I said to myself  - as we're in the

AGE of DECLARATIVE PROGRAMMING

Wouldn't it be grand if you could use this XElement ....     ... to produce this Excel Sheet

by typing  ->  SQL2Excel CustomerLayout.xml

... Well - here's one solution.....  download and unpack the zip file , read the SetupReadme.txt  and you're on your way

[This assumes you have  the Northwind database loaded on a local SqlExpress server]

So , how does this program work ?  It works by carrying out the following Steps

• Step 1 : Access the file detailing the required layout
• Step 2 : Uses the new XElement type to store the xml fragment contained in that file
• Step 3 : Gets the Data using the 'Select .. for XML' functionality  that SQL Server has had since SQL version 2000
• Step 4 : Converts the XML returned in Step 3 to a format that Excel understands

In Step 4 we use Linq to XML and Expression holes to create an XElement - like so

this produces the following

The only crib [Irishmen always complain - must be the weather] I have is that this must be saved to a file because the process.start expects to be passed a file - I wonder is there someway to pass the in memory object direct to excel .........

So , what do you have to do to produce a different report ?

• Create a new text file named say 'myLayout.xml' in your MyDocuments folder
• Copy the contents of  'LayoutTemplate.xml'  into this file which should now contain

• Enter  appropriate values for your particular Database and Table ,  changing the value of DataSource should you wish to use a remote server
• Enter a value for Name , Heading  and Width for each required field
• Save the file and either
  1. at the the command prompt type 'SQL2Excel  myLayout.xml'  if you want to use the executable 'SQL2Excel.exe' or
  2. Set the Command Line Argument in the Debug tab of the Project properties to myLayout.xml

My thanks to the VB team  for continuing to make programming a pure joy and in particular I'd like to  give a call out to Avner Aharomi who has helped to make VB Literals a reality. However , every now and then you come across a GEM of a book that really gets it where a new topic is concerned - that book in this case is LINQ in Action. It's available right now as an eBook for a measly $27.50 and compliments any good Tablet PC - you could almost say (and I will) that this is what the Tablet PC was made for.

[Please read Module1.txt for an explanation of how this solution works and read Jim's Book for the complete story]

Taken from The 20 second commute: The Blue Zone

Meet Jay Schmelzer - he'll be delivering 2 sessions at TechEd Developers  in Barcelona 5-9 Nov 2007  and I'm not going to miss either one. Jay is one of those rare breeds - a speaker who can keep you totally captivated throughout a session , so much so  that the session is over in what appears to be a mere matter of minutes. Here you see him , in his natural habitat , in that great TechEd innovation - The TLC  ZONE - ready to answer any and every question that a VB nerd could lay on him. In the background on the left you have his able sidekick Chris Smith sharing the goodness that is VB's unique features when it comes to handling XML with LINQ I can personally vouch for the fact that when any of these Microsoft guys or gals give you their card and say 'contact me if you have any further questions' they deliver BIG TIME. Without their help (among others) I would not have been able to offer my take on producing dynamic report using Open XML  and VB9

Wouldn't it be grand if you could use this XML snippet ....     ... to produce this Word Document ....

... Well - here's one solution.....  download and unpack the zip file , read the SetupReadme.txt  and you're on your way.

 Being a very lazy West of Ireland guy , I never like having to do the same thing over and over again - after all, that's what programming is all about - code once, use many times. The snippet above contains 3 types of Sections (document sections if you will) - one produces the Report Title , then there's a line break section and finally there's the the table section - if you need any more types you just add to the case statement in the code (Line 81 in Module1.vb) - again extend once, use many times. With a little thinking (preferable on a hilltop over looking a stretch of water with a gentle westerly breeze in your face - and your mind full of the great ideas disseminated by a podcast from Dot Net Rocks that you've just listened to on your MP3 player) you'd be saying to yourself - All I have to do is change the Title , the SQL Query and the Column Captions and Widths and indicate if I want subtotals  and I'd have a new report - but then you' ld say - Couldn't I just add a root element say <Printout> and then a 2nd element like the one above with the new details - save it off to a file and then call the file from my program [next installment] - then to produce further reports all I'd have to do is edit the xml file that is external to the program with Notepad.

Don't you just love it when a plan comes together ...

this is all made possible by the fact that the Office team produced their Open XML solution and the VB/C# teams collaborated on LINQ to XML - of course the VB guys just had to make it easier by extending the VB implementation with their XML literals and 'Expression holes'. A simple example should show this

In VB

In C#

[One of the most important issues in programming - is to be able to return to your code after say 6 months - and understand instantly it's intent - I would suggest that when it comes to Open XML  VB is the winner  - I know - I'm totally biased - but 10 years of using this technique with VB6 and VBA has converted me to the easy life - I do most of my coding in XML - isn't declarative  programming great]

This blog will mainly concern itself with the VB LINQ implementation and maybe a few words about Open XML but fear not an infinitely better source of Open XML knowledge is available in Wouter van Vugt's excellent free book  Open XML The Markup Explained.

By this stage I'm hoping that you've downloaded the zip file and have the program opened showing the Module1.vb code and the line numbers turned on [Tools - Options - Text Editor - All Languages - General - Display - Line Numbers]

This module has 3 parts to it [isn't that like something out of Julius Caesar's Gallic Wars - Gaul is divided into 3 parts - boy did I not GROK Latin]

1. Sub Main - test harness - calls the other 2 functions and Displays (hopefully) the resultant word document [you do have Word 2007 or Word 2000/XP/2003 with OpenXml extensions raring to go - don't you?]
2. CreateDocument function - black box that takes in the required filename and the Main xml document and produces the document - takes care of all the plumbing and uses the Microsoft SDK for Open XML - takes care of all the relationship issues etc - and produces the zip packaged docx file.
3. GetDocumentXML  function - which I'll now attempt to elaborate on

On line 46 we define an XElement variable [Printout] which contains the XML Snippet for our report - notice that there are no quotes around the XML and that in VS2008 the variable type is INFERRED from the data to the right of the equal sign. [This variable could equally be populated from an external file - Next Installment]

Next on line 66 we set up our main XElement [docXML] which we'll attach directly to the root of our XDocument - like HTML Open XML uses a body element - the namespace used to interpret this is defined as w  [see imports statement line 6]

On Line 69 we set up an XElement [Xel] as a temporary container to hold the XML elements we will generate for each section type

Now we're ready to start - this LINQ to XML really replaces XSLT - see Bill Burrows - Linq to XML videos and XLinq Portal for more info

With XLINQ we can easily traverse thru' the various sections of the XML snippet using the FOR EACH statement on line 72 - note how we address the various section elements - see traversing XML in XLinq overview

Let's take each section in turn

1. Type is Text - we place the element value in the w:t element using an expression hole with the variable section.value.ToString  [notice the familiarity this brings to old ASP programmers]  and we set up the style to use within the w:pPr element again using an expression hole with the variable section.@Style as this time it's an attribute within the section element.
2. Type is LineBreak - we just use a blank paragraph entry w:p to place us on a new line
3. Type is Table -  Line 85 - the main theme for this blog
   • 1st off we've set up a temporary XElement [GridData] to hold the dynamic table data - this will be generated using an SQL Query later [Next Installment] - notice the attributes run form C1 to C6 - this was used to conserve memory - but it also turned out to be very useful as you'll see anon.
   • next we determine the number of rows in our table - you've got to admit that the syntax is cool - full intellisense is available detailing all the options and that's even cooler
   • we assign a value to MinimumNoRows and if the number of Rows [3] is less than this value  [10] we set up a list of integers [4,5,6,7,8,9,10] so that we can iterate thru' the list later on to programmatically produce a blank row for each entry
   • of course to produce a blank row we need to produce a blank cell [<w:tc><w:p/></w:tc>] for each column - which we do in line 103
   • the table look [line 107] depends on whether we have a header row and/or a Total row and is assigned accordingly
   • If the attribute TotalRow on the table is set = "Y"
     • we iterate thru' each column and check whether the SubTotal attribute is set = "Y" .
       • if it is
         • we use the AGGREGATE function to sum up the values in the columns - summing an in memory object - awesome. Notice that the item.attribute needs a "Column Name" parameter which we supply by concatenating "C" with the indexed position of the column [sometimes you just strike lucky]
       •   otherwise
         • we add a blank cell [<w:tc><w:p/></w:tc>].
       • On completion we add a blank paragraph
     • otherwise
       • we set the total row to a blank paragraph
   • On Line 131 with the preliminaries out the way we start to put our table XElement together. Notice that the root of the table element is w:tbl
     • We set the style and look for the table using the w:tblPr element using expression holes to apply the style attribute [line 46] and the tableLook variable we calculated [Line 107]
     • we set up the Column Widths using the w:tblGrid element using an expression hole to apply the column width attribute within a LINQ FROM ... SELECT statement - itself within an expression hole.
     • similarly we set up the Column headers or captions using the column caption attribute.
     • then the piece de resistance - the grid data - a double  FROM ... SELECT  loop - notice the IIf function - if the attribute / column cell value is blank use a blank paragraph otherwise use the value within a w:t element which is inside a w:r element which is within a w:p element [refer back to the example at the start - how many lines of C# code would this take]
     • we set a row for each blank row by enclosing the Cells  list within a w:tr  element - if there are no entries in the BlankRows list we get none
     • lastly we apply the TotalRow element which is either a row containing totals plus a blank paragraph or a blank paragraph

at the end of each iteration the Xel  is added as an element to docXML.

After the last section is traversed the resultant docXML is used to create the documentxml  which is returned to the main function. The document is created and displayed.

The End result - we've converted / transformed an XML Snippet into a WordprocessingML document without using a single line of incomprehensible XSLT.

 Fin.