So lately, I've been tasked with working with several databases where the number of tables and views within the databases number into the low thousands.  As you can imagine when working with any kind of long list, the amount of time spent scrolling and searching for the objects you are interested in proves to be a pain point after a while.  In SQL Server Management Studio (SSMS), you do have the ability to set filters, but they aren't persistent and they are limited in value.  For my purposes, the filters helped, but not nearly as much as I would have liked.  So, my first thought was that I wished there was a way that I could create a persistent "folder" structure.  After a quick search of the web, I wasn't able to find anything out there that was what I was looking for, but I did find code samples on creating SSMS add-ins.  I was pretty much resigned to trying to write an add-in myself to do what I wanted to do in SSMS.

After discussing my thoughts about this with a co-worker, he made me aware of a free add-in (SQL Treeo) created by Jakob Dvorak that does a lot of what I was looking for. It allows me to create persistent "virtual" folders as a hierarchy to organize the database objects into a far, far more manageable interface.  As noted in his forum, the add-in is an alpha version and still a work in progress.  I've used it now for a month and give it positive feedback.  There are some bugs and there are a couple modifications that would be nice to have, but all up, it is a solid piece of development and well worth a try (Note: currently it is only available for SQL Server 2008 R2 client).  If you're working with hundreds of tables within a database, it may just make you more productive (as it has for me) and reduce your frustration with constantly having to scroll up and down to find a table or view.

ESTABLISHING A STYLE

Here at Exsilio's Creative Department, we pride ourselves on our ability to approach any project with an original and unique vision. This is especially true when it came time to brand our own printed materials (including various case studies, offering flyers, and our all-up company brochure). Gathering references from both the Swiss-style as well as post-modernism, our team of designers looked towards the avant-garde aesthetic for both creative inspiration and guidance. It was here, that we developed a simple but elegant approach to our newly acquired collateral. By using basic geometric shapes and a limited color palette, we employed the most fundamental rule of graphic design... "Less is more".Through such a minimalist ideology, we were then faced with the challenge of how do we differentiate between the flyers; and more importantly, how do we speak to the content of each individual offering? This meant introducing some form of "personalization" in order to make each piece unique. A simple resolve was adding a collage of background elements to form an individual look & feel for each of the handouts.

RETHINKING THE APPROACH

When it came time to develop our own department's flyer, we saw it as an opportunity to display our creative talents. By doing something a little different and adopting the overall look & feel of our previous materials we demonstrated one of Exsilio's finest traditions - redefining what you thought possible! What better way to communicate such imagination and ingenuity than to implement some form of traditional art practice - in this case, a series of hand drawn sketches. If a graphic artist is to make an impactful design than it must not only be aesthically pleasing but it must also contain substance. From a theoretical standpoint these two applications (of both formal and contemporary design) posed an interesting challenge to our creative team due to the fact that they represent two competing forms of thought. Naturally, print design is based out of the contemporary approach to graphic art, which is the theory of "replication". Ultimately, this debate has divided the art community for centuries. The ability to duplicate a piece of art allows for reproduction on a mass scale - such has been the foundation of graphic design within the business model. Whereas, fine art including painting, sketching, and other traditional practices, prides itself on its inability to be replicable. Thus, we can clearly begin to see how Exsilio's innovative approach defied the norm and combined both forms of design into one successful piece of art. Below is an example of the Creative Services flyer as well as some of the sketches which did not make the cut.

Since Microsoft used Open XML as their document format for the office products since 2007. You can rename the file to a .zip file and then explore the content of the office. Programming against office files is now more of an XML manipulation. Unless you want to purchase additional controls such as Apose which is currently support to up to 2007, Open XML SDK productivity tool is definitely your best friend.

I was working on cloning slides for a complex slide deck this past a couple of weeks which I need to clone the content of the slide as well as charts and images. So I was looking through resources online and I found a couple of good resources such as http://www.prowareness.com/blog/?p=392 and also http://msdn.microsoft.com/en-us/library/dd469465(v=office.12).aspx. However the Chartspace cloning part did not work for me in Anand’s blog, and I couldn’t find any other good resources to clone the charts. In Anand’s blog, he uses the ExternalData in the chartspace which does not resolve in my code, and I also tried to get the node and set the ID as a work around, however it does not work either. The PowerPoint slide would run an error when you try to open the file and requests a repair, and after repair, the content is no longer valid. So as a work around, I used the Open XML SDK Productivity Tool.

Here is what you can do to generate the charts on the fly. In cloning the slides, you need to clone all aspects of the slide. If you have two charts in the slide, you would need to clone both of them for the slide to work, chartspace as well as the external embedded excel worksheet for each of the chart. When you open the slide in the Productivity Tool, you need to explore to the chart xml that you want to clone. Normally if there is only one slide, then you need to open the node under ppt/presentation.xml -> ppt/slides/slide1.xml -> /ppt/charts/chart1.xml. When you select /ppt/charts/chart1.xml, and then click Reflect Code. In the reflect code pane, you will be able to find the code that you would need to create the chart on the fly. It would include CreateChartPart(ChartPart part) function that would call something similar to these five lines of code. And of course you would need to include the rest of the functions in the reflect code pane that the CreateChartPart function calls. If you have multiple charts in the slide that you need to clone, then you would need to get the CreateChartPart function for the second chart as well. This only works if you are cloning the charts onto a separate slide. Because in openxml , charts are indexed within the slide, so you can use the same code in the previous slide including the indexing.

public void CreateChartPart(ChartPart part) {
ChartDrawingPart chartDrawingPart1 = part.AddNewPart("rId2");
GenerateChartDrawingPart1Content(chartDrawingPart1);
EmbeddedPackagePart embeddedPackagePart1 = part.AddNewPart("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "rId1");
GenerateEmbeddedPackagePart1Content(embeddedPackagePart1);
GeneratePartContent(part); }

Here is the function to clone the charts, images and slides:

public static SlidePart CloneSlidePartWithImagesAndCharts(PresentationPart presentationPart, SlidePart slideTemplate) {
int i = presentationPart.SlideParts.Count(); //Create a new slide part in the presentation.
SlidePart newSlidePart = presentationPart.AddNewPart(”newSlide” + i);
i++; //Add the source slide content into the new slide.
newSlidePart.FeedData(slideTemplate.GetStream(FileMode.Open)); //Make sure the new slide references the proper slide layout.
newSlidePart.AddPart(slideTemplate.SlideLayoutPart, slideTemplate.GetIdOfPart(slideTemplate.SlideLayoutPart)); // copy the image parts
foreach (ImagePart ipart in slideTemplate.ImageParts) {
ImagePart newipart = newSlidePart.AddImagePart(ipart.ContentType, slideTemplate.GetIdOfPart(ipart));
newipart.FeedData(ipart.GetStream()); } // copy the chart parts
ChartPart cpart1 = slideTemplate.ChartParts.FirstOrDefault();
ChartPart newcpart1 = newSlidePart.AddNewPart(slideTemplate.GetIdOfPart(cpart1));
CreateChartPart(newcpart1);
newcpart1.ChartSpace.Save(); //Get the list of slide ids.
SlideIdList slideIdList = presentationPart.Presentation.SlideIdList; //Deternmine where to add the next slide (find max number of slides).
uint maxSlideId = 1;
SlideId prevSlideId = null;
foreach (SlideId slideId in slideIdList.ChildElements) {
if (slideId.Id > maxSlideId) {
maxSlideId = slideId.Id;
prevSlideId = slideId; } }
maxSlideId++; //Add the new slide at the end of the deck.
SlideId newSlideId = slideIdList.InsertAfter(new SlideId(), prevSlideId); //Make sure the id and relid are set appropriately.
newSlideId.Id = maxSlideId; newSlideId.RelationshipId = presentationPart.GetIdOfPart(newSlidePart);
newSlidePart.Slide.Save();
return newSlidePart; }

After you clone the slide, you probably would need to edit the chart data, one way to edit each of the chart series is to remove the series and then create the series on the fly. If you have a line chart, you can get the code for creating the linechart by browsing to the ChartSpace, then c:chart (Chart) -> c:linechart (LineChart) -> LineChartSeries, and click Reflect code. If there are multiple LineChartSeries, then you would need to get the code for all series. Most of the code for generating the LineChartSeries are very similar. You could consolidate the functions into one function, however, you would need to make sure that the index for each LineChartSeries is correct, if there are not correct, the slide would not work. You would need to modify the function to include the new data for the new charts on the fly at the GenerateLineChartSeries function. So here is how you can remove the series and then add a new series in it’s place. However, one draw-back for updating the chart series this way is that the backend xslx for each chart is not updated.

ChartPart ChartPart1 = Slide1.ChartParts.FirstOrDefault();
Drawing.Charts.Chart Chart1 = ChartPart1.ChartSpace.Descendants().First();
Drawing.Charts.LineChart LineChart1 = Chart1.Descendants().First();
Drawing.Charts.LineChartSeries ChartSeries1 = Chart1.Descendants().First();
LineChart1.RemoveChild(ChartSeries1);
Drawing.Charts.LineChartSeries NewChartSeries1 = GenerateLineChartSeries(); //You would need to modify the function to either take in parameters in the function call and then add in the data according.
LineChart1.AppendChild(NewChartSeries1);
ChartPart1.ChartSpace.Save();