csharp-source.net

http://csharp-source.net/

SQL Server 2005 Recursion and WITH Clause

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

Recursive queries have been added to T-SQL in SQL Server 2005 in the form of the WITH clause and CTE.  The books online documentation is pretty straight forward but a few people have asked me to work up a few useful samples to help get them going.  Simply copy/paste these samples into a query window and execute.

Table of Contents Hierarchy

set nocount on declare @Sample1 table ( RecordID int Primary key NOT NULL , ParentRecordID int, SortOrder int, Description nvarchar(100), Salary money ) /* Start loading of test data */ insert into @Sample1 values(1,null,null,'CEO',10) insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9) insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8) insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7) insert into @Sample1 values(5,2,2,'Marketing Director - TV',6) insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5) insert into @Sample1 values(7,4,1,'Human Resources Director',4) insert into @Sample1 values(8,4,2,'Some other item',3) insert into @Sample1 values(9,6,1,'Research Analyst',2) set nocount off; with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC) as ( select RecordID,ParentRecordID,SortOrder,Salary, convert(varchar(100),'') TOC from @Sample1 where ParentRecordID is null union all select R1.RecordID, R1.ParentRecordID, R1.SortOrder, R1.Salary, case when DataLength(R2.TOC) > 0 then convert(varchar(100),R2.TOC + '.' + cast(R1.SortOrder as varchar(10))) else convert(varchar(100), cast(R1.SortOrder as varchar(10))) end as TOC from @Sample1 as R1 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID ) select * from RecursionCTE order by ParentRecordID,SortOrder asc Results RecordID ParentRecordID SortOrder Salary TOC ----------- -------------- ----------- ----------------- 1 NULL NULL 10.00 2 1 1 9.00 1 3 1 2 8.00 2 6 1 3 5.00 3 4 2 1 7.00 1.1 5 2 2 6.00 1.2 7 4 1 4.00 1.1.1 8 4 2 3.00 1.1.2 9 6 1 2.00 3.1

Sum Up Subordinate Salaries of All Employees

set nocount on declare @Sample1 table ( RecordID int Primary key NOT NULL , ParentRecordID int, SortOrder int, Description nvarchar(100), Salary money ) /* Start loading of test data */ insert into @Sample1 values(1,null,null,'CEO',10) insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9) insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8) insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7) insert into @Sample1 values(5,2,2,'Marketing Director - TV',6) insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5) insert into @Sample1 values(7,4,1,'Human Resources Director',4) insert into @Sample1 values(8,4,2,'Some other item',3) insert into @Sample1 values(9,6,1,'Research Analyst',2) set nocount off; with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary) as ( select RecordID,ParentRecordID,SortOrder,Salary from @Sample1 where ParentRecordID is null union all select R1.RecordID, R1.ParentRecordID, R1.SortOrder, R1.Salary from @Sample1 as R1 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID ) select sum(R1.salary) as Salary from @Sample1 as R1 JOIN RecursionCTE as R2 on R1.RecordID = R2.RecordID Results Salary --------------------- 54.00 (1 row(s) affected)

Sum Up Subordinate Salaries of a Specific Employee

set nocount on declare @Sample1 table ( RecordID int Primary key NOT NULL , ParentRecordID int, SortOrder int, Description nvarchar(100), Salary money ) /* Start loading of test data */ insert into @Sample1 values(1,null,null,'CEO',10) insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9) insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8) insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7) insert into @Sample1 values(5,2,2,'Marketing Director - TV',6) insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5) insert into @Sample1 values(7,4,1,'Human Resources Director',4) insert into @Sample1 values(8,4,2,'Some other item',3) insert into @Sample1 values(9,6,1,'Research Analyst',2) set nocount off; with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary) as ( select RecordID,ParentRecordID,SortOrder,Salary from @Sample1 where ParentRecordID =2 -- specific employee id union all select R1.RecordID, R1.ParentRecordID, R1.SortOrder, R1.Salary from @Sample1 as R1 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID ) select sum(R1.salary) as Salary from @Sample1 as R1 JOIN RecursionCTE as R2 on R1.RecordID = R2.RecordID Results: Salary --------------------- 20.00

Manager to Subordinate Salary Differential

set nocount on declare @Sample1 table ( RecordID int Primary key NOT NULL , ParentRecordID int, SortOrder int, Description nvarchar(100), Salary money ) /* Start loading of test data */ insert into @Sample1 values(1,null,null,'CEO',10) insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9) insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8) insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7) insert into @Sample1 values(5,2,2,'Marketing Director - TV',6) insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5) insert into @Sample1 values(7,4,1,'Human Resources Director',4) insert into @Sample1 values(8,4,2,'Some other item',3) insert into @Sample1 values(9,6,1,'Research Analyst',2) set nocount off; with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential) as ( select RecordID,ParentRecordID,SortOrder, convert(money,null) as ParentSalary, Salary, convert(money,null) as Differential from @Sample1 where ParentRecordID is null union all select R1.RecordID, R1.ParentRecordID, R1.SortOrder, convert(money,R2.Salary) as ParentSalary, R1.Salary, convert(money,R2.Salary - R1.Salary) as Differential from @Sample1 as R1 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID ) select * from RecursionCTE order by ParentRecordID,SortOrder asc RecordID ParentRecordID SortOrder ParentSalary Salary Differential ----------- -------------- ----------- --------------------- --------------------- --------------------- 1 NULL NULL NULL 10.00 NULL 2 1 1 10.00 9.00 1.00 3 1 2 10.00 8.00 2.00 6 1 3 10.00 5.00 5.00 4 2 1 9.00 7.00 2.00 5 2 2 9.00 6.00 3.00 7 4 1 7.00 4.00 3.00 8 4 2 7.00 3.00 4.00 9 6 1 5.00 2.00 3.00

Downloading Files with the WebRequest and WebResponse Classes

http://www.developer.com/net/net/article.php/3340151

A few years ago I was being tasked with writing a console application that would download and save from the Web a file name that was passed to it via the command line. The application took me about 4 hours to write and involved a lot of Sockets programming and data conversion. With .NET, the WebRequest and WebResponse classes now reduce this task to about 5 minutes!

Here’s a simple function that you can easily plug into your C# application that will download a specified server file and save it to a local file. Instead of describing the function and then presenting it, the function contains comments that document exactly what is going on.

// Remember to add the following using statements to your code
// using System.Net;
// using System.IO;

public static int DownloadFile(String remoteFilename,
                               String localFilename)
{
  // Function will return the number of bytes processed
  // to the caller. Initialize to 0 here.
  int bytesProcessed = 0;

  // Assign values to these objects here so that they can
  // be referenced in the finally block
  Stream remoteStream  = null;
  Stream localStream   = null;
  WebResponse response = null;

  // Use a try/catch/finally block as both the WebRequest and Stream
  // classes throw exceptions upon error
  try
  {
    // Create a request for the specified remote file name
    WebRequest request = WebRequest.Create(remoteFilename);
    if (request != null)
    {
      // Send the request to the server and retrieve the
      // WebResponse object 
      response = request.GetResponse();
      if (response != null)
      {
        // Once the WebResponse object has been retrieved,
        // get the stream object associated with the response's data
        remoteStream = response.GetResponseStream();

        // Create the local file
        localStream = File.Create(localFilename);

        // Allocate a 1k buffer
        byte[] buffer = new byte[1024];
        int bytesRead;

        // Simple do/while loop to read from stream until
        // no bytes are returned
        do
        {
          // Read data (up to 1k) from the stream
          bytesRead = remoteStream.Read (buffer, 0, buffer.Length);

          // Write the data to the local file
          localStream.Write (buffer, 0, bytesRead);

          // Increment total bytes processed
          bytesProcessed += bytesRead;
        } while (bytesRead > 0);
      }
    }
  }
  catch(Exception e)
  {
    Console.WriteLine(e.Message);
  }
  finally
  {
    // Close the response and streams objects here
    // to make sure they're closed even if an exception
    // is thrown at some point
    if (response     != null) response.Close();
    if (remoteStream != null) remoteStream.Close();
    if (localStream  != null) localStream.Close();
  }

  // Return total bytes processed to caller.
  return bytesProcessed;
}

Finally, here’s an example of using the DownloadFile function.

int read = DownloadFile("http://www.mysite.com/problem1.jpg",
                        "d:\\test.jpg");
Console.WriteLine("{0} bytes written", read);

Office Open XML Document Inspection

http://www.microsoft.com/uk/msdn/screencasts/screencast/253/Office-Open-XML-Document-Inspection.aspx

Office Open XML Document Profiling

http://www.microsoft.com/uk/msdn/screencasts/screencast/252/Office-Open-XML-Document-Profiling.aspx

Office Open XML Content Controls and Repeating Data

http://www.microsoft.com/uk/msdn/screencasts/screencast/261/Office-Open-XML-Content-Controls-and-Repeating-Data-Part-1-of-3.aspx

http://www.microsoft.com/uk/msdn/screencasts/screencast/259/Office-Open-XML-Content-Controls-and-Repeating-Data-Part-2-of-3.aspx

http://www.microsoft.com/uk/msdn/screencasts/screencast/260/Office-Open-XML-Content-Controls-and-Repeating-Data-Part-3-of-3.aspx

Open XML Developer Workshop

http://msdn.microsoft.com/en-us/office/bb738430.aspx

Word Developer Portal

http://msdn.microsoft.com/en-us/office/aa905482.aspx

Creating an Open XML Document in .NET

http://openxmldeveloper.org/articles/OpenXMLDocFromDotNet.aspx

Article author: Sanjay Kumar Madhva, Sonata Software Limited

Introduction

The idea of this article is to show how easy it is to create an OpenXML WordprocessingML using .NET and using System.IO.Packaging provided by WinFx.

What we need, is to create a windows application that lets user enter multi line of text. The user is provided a button that on click creates a WordprocessingML. When the user clicks on the button all we need to code for, breaking the user entered text into paragraphs and creating an document.xml as shown below. Package it into an OpenXML document.

Content of document.xml

Here are twelve easy steps to create a word processing document like the one above and write it out as a valid Open XML document …

Step 1. Create a new C# project.

Create a new project “WordDocCreator” in a new solution.

Step 2. Add reference to WindowsBase provided WinFx.

Right click on the reference and form the pop up menu select “Add Reference”

Add reference windows pops up. Select “Windows Base” from the .NET tab as shown in the fig below.

Step 3. Add a Text box to the form

Drag and drop TextBox from the ToolBox on to the form.

Step 4. Change the textbox to accept MultiLine.

Change the textbox property to accept multiline.

Step 5. Resize the textbox

Resize the textbox to fit to the screen.

Step 6. Add two Buttons.

Step 7. Rename the text box, and the two buttons.

• Change the forms title text to Document Creator
• Rename the Text box to mleTextForDocument
• Command button 1 to Exit and change the text to “E&xit”
• Command button 2 to GenerateDocument and change the text to “&Generate Document”

Step 8. Code for close event of Exit button

Double click on the exit button to generate the click event for the button add close() in the button clicked event as shown below.

private void Exit_Click(object sender, EventArgs e)
{
close();
}

Step 9. Add using directive

using System.Xml;
using System.IO;
using System.IO.Packaging;

Step 10. Create a method to get the save file name and path.

We may have to create a file dialog that will accept a filename from the user. We can call the SaveFileDialog provided by the .net framework to get the directory and the name of the file in which the user wants to save the document content.

The code is as follows.

//Get the file path where the user wants to save the document.
private string GetSavePath()
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.AddExtension = true;
//Get only Docx file
sfd.Filter = “docx|”;
sfd.CheckPathExists = true;
sfd.DefaultExt = “.docx”;
sfd.ShowDialog();
return sfd.FileName; // return the filename and the path
}

Step 11. Code for close event of GenerateDocument button

The document creation can be achieved by following 5 easy steps:
1. Take the text entered by the user in the multilane edit has to be split into paragraph and creating an “document.xml” as shown under “Content of document.xml”.
2. Creating an instance of Package class
3. Create the main document part (document.xml) using the package class.
4. Create the relationship file.
5. Close the document.

private void GenerateDocument_Click(object sender, EventArgs e)
{
string _nameSpaceURI = “http://schemas.microsoft.com/office/word/2005/10/wordml”;
string docFileName = GetSavePath();

//– Step 1 – Creating the document xml
XmlDocument doc = new XmlDocument();
XmlElement _wWordDoc = doc.CreateElement(“w:wordDocument”, _nameSpaceURI);
doc.AppendChild (_wWordDoc);
XmlElement _wbody = doc.CreateElement(“w:body”,_nameSpaceURI);
_wWordDoc.AppendChild(_wbody);
// Check if the string contains a line feed
string[] _SplitStr = mleTextForDocument.Text.Split(‘\n’);
// if it contains line feed then each entry with a line feed goes to a new paragraph.
for (int row = 0; row < _SplitStr.Length; row++)
{
XmlElement _wp1 = doc.CreateElement(“w:p”,_nameSpaceURI);
_wbody.AppendChild(_wp1);
XmlElement _wr1 = doc.CreateElement(“w:r”, _nameSpaceURI);
_wp1.AppendChild(_wr1);
XmlElement _wt11 = doc.CreateElement(“w:t”, _nameSpaceURI);
_wr1.AppendChild(_wt11);
XmlNode _wt1 = doc.CreateNode(XmlNodeType.Text, “w:t”,_nameSpaceURI);
_wt1.Value = _SplitStr[row];
_wt11.AppendChild(_wt1);
}

//– Step 2 – Creating the Package
Package package = null;
package = Package.Open(docFileName, FileMode.Create, FileAccess.ReadWrite);

//– Step 3 – Create the main document part (document.xml)
Uri uri = new Uri(“/word/document.xml”, UriKind.Relative);
PackagePart part = package.CreatePart(uri, “application/vnd.ms-word.main+xml”);
StreamWriter partWrt = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
doc.Save(partWrt);
partWrt.Close();
package.Flush();

//– Step 4 – Create the relationship file
uri = new Uri(“/word/document.xml”, UriKind.Relative);
PackageRelationship rel = package.CreateRelationship(uri, TargetMode.Internal, “http://schemas.microsoft.com/office/2006/relationships/officeDocument”, “rId1″);
package.Flush();

//– Step 5- Close the document.
package.Close();
}

Step 12. Build and Run.

From the Build menu select “Build WordDocument”

To run with out debug, select “Start Without Debugging” under “Debug” menu or press Ctrl+F5.

Application window appears.

Key in some text and click on “Generate Document” button, a file dialog appears. Provide the file name and click save.

Note: remember the save in directory.

Open file explorer and go the above-specified directory. Double click on the document you just created.

See the content you typed being displayed as a document …

Published Wednesday, April 12, 2006 4:39 PM by dmahugh
Filed Under: Open Packaging Convention, WordProcessingML, .NET (C#, VB, J#, C++/CLI)
Attachment(s): WordDocCreator.zip

Learning Resources for Open XML SDK

https://connect.microsoft.com/content/content.aspx?SiteID=589&ContentID=9924

1          The Open XML Standard

Standard ECMA-376, and Ecma TC45

ISO/IEC DIS 29500

2          SDK Team Resource

MSDN library for SDK version 1.0 and 2.0

Download page for SDK version 1.0 and 2.0

Microsoft Connect: Open XML SDK

3          Microsoft-wide Resource

XML in Office Developer Portal: This portal contains technical articles, code samples, developer documentation, and multimedia presentations on working with XML in Office.

Open XML Formats Resource Center: This contains developer resources to help you get started generating and manipulating documents, workbooks, and presentations using the Open XML Formats.

Microsoft Visual Studio Tools for the Office System Power Tools: A set of developer tools that provide additional functionality useful in Office-based development.

4          Forum

MSDN forum

5          Blog

Erik White, Doug Mahugh, Brian Jones, Gray Knowlton, Oliver Bell, Jason Matusow

6          Tech Site

Openxmldeveloper

Openxmlcommunity

7          3rd Party Software

Powertools: PowerTools for Open XML are sample source code and guidance for developers showing how to build PowerShell cmdlets to create and modify Office Open XML documents, and scripts to demonstrate the use of the cmdlets.

ExcelPackage: ExcelPackage provides server-side generation of Excel 2007 spreadsheets.

Open XML Package Explorer: This tool allows full editing of Open XML packages, creating new packages and various other features.

OpenXmlDiff by Pranav Wagh: a simple and straightforward tool that generates a report of all the differences between two Open XML documents.

XMLSpy: XMLSpy provides powerful support for accessing, editing, transforming, and querying XML data saved in Microsoft® Office 2007 documents and other zipped files.

DiffDog: This tool supports to diff/merge Office Open XML files and ZIP archives.

Stylevision: Stylevision allows to graphically design and implement conformant DOCX output from XML and/or database data.

Mapforce: It auto-generates C# code that creates an Open XML spreadsheet.

openxml4j: OpenXML4J is a Java library dedicated to the creation and manipulation of Office Open XML (ECMA-376) and OPC based documents (for example Office 2007 Word, Excel and PowerPoint documents).

PHPExcel: This project provides a set of classes for the PHP programming language, which allow you to write to Excel 2007 files and read from Excel 2007 files. This project is built around Microsoft’s OpenXML standard and PHP.

<oxygen/> XML Editor: This editor allows you to extract, validate, edit and process the XML data stored in Office 2007 files and any other ZIP-based archive. Validation is done using the latest ECMA XML Schemas.

docx4all: currently you can create, open, edit, and print simple docx on Windows, Linux and OSX (with latest Java). You can collaborate with people using the plutext Word 2007 add-in. Table support is on its way.

8          Related Techs

.Net Framework 3.5, which includes LINQ and XML technology.

9          Other resources

Open XML SDK Demo On PDC2008: Open XML SDK V2 demo by Zeyad Rajabi on PDC2008 (size:178M).

Open XML SDK V1 Technical Chat video: Open XML SDK V1 demo and road map by Eric White and Zeyad Rajabi.

OpenXML in wikipedia