Watin 2.0 and Gallio 3.1 in x64 environment: fixing System.Reflection.ReflectionTypeLoadException and System.Runtime.InteropServices.COMException "The interface is unknown" issues

We use Gallio/MbUnit and Watin at my work for unit testing of an ASP.NET website, as it provides excellent reports including the raw Watin events (click, type, select) and screenshots for failed cases. Today I spent a few hours trying to get the project working on my new Windows 7 x64 development machine, and things did not start out very well. When I first tried to load the project in the Gallio Icarus runner none of the unit tests showed up and it indicated that an exception was thrown while exploring tests. I tried using both the x64 and x86 versions of Gallio/MbUnit, and even tried using the TestDriven.NET loader from within Visual Studio, but couldn’t get anything to work. Here is the full error message for Google’s sake:

[error] An exception was thrown while exploring tests.
Location: D:\SVN_Development\OQA Unit Testing\OQA Testing Framework\OQA Testing Framework\bin\Debug\OQA Testing Framework.EXE
Reference: OQA Testing Framework, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
Details: System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. 
   at System.Reflection.Module._GetTypesInternal(StackCrawlMark& stackMark) 
   at System.Reflection.Assembly.GetTypes() 
   at Gallio.Common.Reflection.Impl.NativeAssemblyWrapper.GetTypes() in c:\RelEng\Projects\MbUnit v3.1\Work\src\Gallio\Gallio\Common\Reflection\Impl\NativeAssemblyWrapper.cs:line 72 
   at Gallio.Framework.Pattern.TestAssemblyPatternAttribute.PopulateChildrenImmediately(IPatternScope assemblyScope, IAssemblyInfo assembly) in c:\RelEng\Projects\MbUnit v3.1\Work\src\Gallio\Gallio\Framework\Pattern\TestAssemblyPatternAttribute.cs:line 147 
   at Gallio.Framework.Pattern.TestAssemblyPatternAttribute.Consume(IPatternScope containingScope, ICodeElementInfo codeElement, Boolean skipChildren) in c:\RelEng\Projects\MbUnit v3.1\Work\src\Gallio\Gallio\Framework\Pattern\TestAssemblyPatternAttribute.cs:line 75 
   at Gallio.Framework.Pattern.DefaultPatternEvaluator.Consume(IPatternScope containingScope, ICodeElementInfo codeElement, Boolean skipChildren, IPattern defaultPrimaryPattern) in c:\RelEng\Projects\MbUnit v3.1\Work\src\Gallio\Gallio\Framework\Pattern\DefaultPatternEvaluator.cs:line 153

The error message indicates that it is due to an issue with missing assemblies, and suggested that you make sure the CopyLocal option is set to true for all 3rd party references, but that didn’t help either. Finally I created a test project and started ripping the original project to peaces to find out why it was failing. Turns out it worked fine if I remove a reference to an interface stored in the Watin.Core.Dll assembly, so I setup a workaround and was finally able to load the tests. Only problem was that it would fail whenever the test tried to load Watin. At this point I figured it had to be an issue with the Watin dll, and it turns out that it was simply trying to load an x64 version of the library that doesn’t actually exist (the library is x86 only). This link suggested setting Visual Studio to use x86 as the build platform type instead of “Any CPU”, and once I did that the original project started working. Yay!

It didn’t take long though for something else to fail, as it threw an exception as soon as one of the tests tried to access the Document object. This time the exception was System.Runtime.InteropServices.COMException with Message="The interface is unknown. (Exception from HRESULT: 0x800706B5)" coming from mshtml.HTMLDocumentClass.IHTMLDocument2_get_url(). Another quick search lead to another StackOverflow answer suggesting to run Visual Studio as an Administrator. That seem to do the trick!

So if you are using an x64 machine to do unit testing with Gallio, MbUnit, and Watin, make sure to set the platform to x86 and make sure to run Visual Studio and Icarus as an Administrator! I should probably get back to working on those unit tests now :-P

Enjoy!

Channel 9 Full Screen Video Player: My first Bookmarklet

I really enjoy watching videos from Channel 9, Microsoft’s community site that targets users and developers. It is a great way to keep updated on the latest technologies and learn more about future products before they come out. The only thing that I don’t like is that the website is not very user friendly for people that have multiple monitors. This surprises me, as most developers that I know use at least two monitors, sometimes three or four. Even after their recent layout change, which increased the size of the video player significantly, the embedded video player is still too small to be able to read code from screencasts. There is of course a full screen button, but this doesn’t really work when multitasking on multiple monitors as it will close as soon as you click on another browser or window on another screen. Both Silverlight and Flash have this issue, which they consider to be a security feature, but it renders the full screen mode worthless useless you stop using your second monitor while watching the video.

Currently there are only two workarounds to the Silverlight/Flash full screen issue, both of which YouTube has already implemented using buttons at the top of the video player. The first is enlarging the video so that it takes up more of the page, and the second is opening it in a new page that can be resized to as large or small as you want. Alternatively you can use a different video player such as the embedded Windows Media Player, but while the WMV links on Channel 9 display full screen in the browser they sometimes have buffering or quality issues and cannot take advantage of the Smooth Streaming that the Silverlight player can offer. All I really wanted was a way to enlarge the video to fit the full width of the browser window, so I figured why not just write a script to do it?

Bookmarklets are essentially small snippets of javascript code that live in a browser’s favorites and do simple things like opening a Wikipedia box for searching, Subscribe/Share/Note the current webpage in Google Reader, using Google SideWiki, or adding any item to an Amazon Wishlist. Due to cross-site scripting restrictions implemented in most browsers they are relatively safe, so they shouldn’t be able to drain your bank account or spam all your friends on Facebook. Plus unlike Greasemonkey scripts they will only run when you click on the link in your favorites, so they won’t slow down or change your normal browsing habits. This seems like a perfect fit for resizing the Silverlight video player on Channel 9.

So after dissecting the way that the Google bookmarklets worked I started coding my own. If you want to try it out you can simply drag this link to your favorites (or right click and select "Add to Favorites"): C9FullScreen and then click on the new link that it created after browsing to your favorite Channel 9 video. To see how it works, here is the code that gets embedded into the C9FullScreen link:

javascript:b=document.body;if(b&&!document.xmlVersion){void(z=document.createElement('script'));void(z.src='http://www.theg2.net/bookmarklets/C9SLFullScreen.js');void(b.appendChild(z));}else{alert('Bookmarklet-Failed');}

And here is the script that gets injected into the page to resize the video:

//C9FullScreen: Increases the size of the Silverlight player to the full width of the window //Works with Internet Explorer, Safari, Firefox, and Chrome //Author: Greg Bray //Information: http://codeblog.theg2.net/2009/11/channel-9-full-screen-video-player-my.html //Blog: http://blog.theg2.net //License: Released to Public Domain //Version 1.1 released 5/22/2010 to use new zap file and fix issue with http://channel9.msdn.com/learn/courses/SharePoint2010Developer/SharePoint2010DeveloperRoadmap/SharePoint2010IntegrationwithPowerShell/ //alert('Start JS Code'); //Reverse a string. Used to get PID from URL //Code From: http://www.bytemycode.com/snippets/snippet/400/ String.prototype.reverse = function(){ splitext = this.split(""); revertext = splitext.reverse(); reversed = revertext.join(""); return reversed; } //Method to enlarge the Channel 9 Silverlight Player. function C9SLFullScreen(){ var b=document.body; var d=document.getElementById('ctl00_MainPlaceHolder_Starter_MediaPlayer'); if(d){ //Good test video is http://channel9.msdn.com/posts/Charles/Introducing-Click-to-Run-in-Office-2010/ //alert("Player DIV found. Now check if Silverlight Player is already loaded."); o = d.getElementsByTagName('object'); if(o.length==1){ //Video is already playing //alert('Player already loaded. Clear player and grab URL/PID'); if(navigator.userAgent.toLowerCase().indexOf('chrome/') > -1){ alert('Player has already loaded. Resizing the video in Chrome will cause the player to load a partial version of the clip from the cache. Please refresh the page and try going C9FullScreen before starting the video.'); } else { myregexURL = /m=(.+),autostart=/ //Regex to get video URL from object myregex = /\/ch9((?:\/\d)+)\// //Regex to get pid from video url mURL=myregexURL.exec(d.getElementsByTagName('object')[0].getElementsByTagName('param')[1].value)[1]; mpID= myregex.exec(mURL); //Match regex pID = mpID[1].replace(/\//g,""); pID = pID.reverse(); //alert("pID:"+pID+"\r\n"+mURL); d.removeChild(o[0]);//Remove active Silverlight object. Use o[0] to reference the player object } } else { //Video player not loaded. Grab movie url and pid. myregex = /\/ch9((?:\/\d)+)\// mURL=d.getElementsByTagName('a')[0].href; mpID= myregex.exec(mURL); //Match regex if(mpID){ pID = mpID[1].replace(/\//g,""); pID = pID.reverse(); } else { //Learn/Courses videos do not include the id in the url, so grab it from the rdf tag. UPDATE 7/23/2010: Now standard C9 shows don't include it in the URL either! myregex2 = /\/(\d+)\/Trackback.aspx/ dSummary = document.getElementById("contents"); if(dSummary){ strHtml = dSummary.innerHTML; pID = myregex2.exec(strHtml)[1]; } else { alert("Error: Cannot find trackback element containing video ID."); return; } } //alert("pID:"+pID+"\r\n"+mURL); } //Hide bar at top document.getElementById("cb_main").style.display = "none"; document.body.style.padding = "0 0 0 0"; //Get window size (Leave a little extra as some browsers will act strange with the scroll bar. var h = window.innerHeight || document.documentElement.clientHeight || document.getElementsByTagName('body')[0].clientHeight; var w = window.innerWidth || document.documentElement.clientWidth || document.getElementsByTagName('body')[0].clientWidth; z=document.createElement('div'); //Set Style (see http://www.comptechdoc.org/independent/web/cgi/javamanual/javastyle.html) //z.style.position = "absolute"; //z.style.left = "20px"; //z.style.top = "200px"; intW = (w-35); intH = (h-20); z.style.width = intW+"px"; z.style.height = intH+"px"; z.style.marginLeft = "5px"; z.style.marginTop = "5px"; z.style.marginBottom = "10px"; z.style.backgroundColor = "LightGrey"; z.style.border = "medium solid black"; //z.innerHTML = s;//'<center>'+s+'</center>'; bStart ="true"; //true=Starts onload, false=waits for click. Download will start immediatly, so true is recommended. NOTE: the AutoHide feature does not work (must be set to true otherwise it is always hidden). z.innerHTML = '<object data="data:application/x-silverlight-2," type="application/x-silverlight-2" width="'+intW+'" height="'+intH+'"><param name="source" value="http://channel9.msdn.com/App_Themes/default/VideoPlayer10_01_18.xap" /><param name="initParams" value="m='+mURL+',autostart='+bStart+',autohide=true,showembed=true,postid='+pID+'" /><param name="background" value="#00FFFFFF" /><a href="http://go.microsoft.com/fwlink/?LinkID=124807" style="text-decoration: none;"><img src="http://go.microsoft.com/fwlink/?LinkId=108181" alt="Get Microsoft Silverlight" style="border-style: none"/></a></object>' b.insertBefore(z, b.firstChild); }else{ alert('ctl00_MainPlaceHolder_Starter_MediaPlayer not found. Make sure Channel 9 video page is loaded. Note: this does not work on the main homepage'); } } C9SLFullScreen(); //alert('End JS Code');

And here is the result: A full screen video... Enjoy!

Short .Net Reflector Training Video from Pluralsight

Today’s Simple-Talk newsletter had a video from http://www.pluralsight.com that talked about the basic features of Red Gate’s .NET Reflector. If you are not familiar with the .NET Reflector, I highly recommend you give it a try, as it is by far one of the best .NET tools available. This short 14 minute video walks you through most of the features of the program and shows you why it can be so useful. I’ve used .NET Reflector for system testing or when a library’s documentation is incomplete or out of date. The video doesn’t cover the Analyze feature for finding all class references and dependencies, and it doesn’t talk about all the cool plug-ins you can add, but it does provide a great introduction to a very powerful tool! 

Texas Holdem Hand Equity Calculation in IronPython

This year I have been working in my free time to create easy to use Texas Holdem poker spreadsheets based on IronPython using Resolver One. These spreadsheets can be used to calculate Win/Tie/Loss odds, but some people like to use hand equity instead since it represents a player’s overall stake in the pot. Equity of 1.0 or 100% means that they will win the entire pot, where as equity of 0.5 (50%) or 0.25 (25%) means that they will split the pot with other players. You can again use Monte Carlo analysis to run a number of trials before all of the board cards are dealt to estimate a player’s current equity in the hand. This means that if during 4 trials I would win 2 of them and split the pot with one other player on the other 2, then my equity would be (1.0+1.0+0.5+0.5)/4 =  3/4 or 75%.

Calculating Win/Tie/Loss statistics on an independent player vs player basis is very simple, as the player’s pocket cards are evaluated against each opponent one at a time to show which opponent hands are stronger or weaker than your own. This does not take into account how the pocket cards of other opponents might change the odds, since in reality you usually play verse a table of opponents not just one opponent at a time. Changing the spreadsheet to work against the whole table would require changing the Monte Carlo simulation from a “row by row” analysis to a “table by table” analysis, where by you setup a table of opponents using a random hand for each player given the information that you have about their hand (AK, KK or QQ, Group1, etc..) and then generate a random 5 card hand based on the currently dealt cards.

There are a lot of issues that you have to watch out for in this type of Multiway Isometric Ranged Equity Calculation, but the basics are simple so long as you have a method of comparing multiple player’s hands for a given board. The C# poker library that I use is mainly focused on heads-up hands, but it only took me about an hour to whip up a method that would take a board setup and a list of player pocket hands and return the scored value and equity for each hand as well as the highest ranked hand and a list of the winners. Here’s the code:

#This function can be used to calculate equity for multiple hands and a given board def HandEquity(ulBoard, lstulHands, bPrintResults=False): """Used to calculate hand equity for a given 5 card poker hand and a list of players hands. If a hand wins then it's equity is 1, a loss is 0, and a tie is 1/n where n is the number of hands that tie. ulBoard: A 5 card hand for the board represented as a 52 bit mask in an unsigned long. Use HoldemHand.Hand.ParseHand method to parse "Ac Kd Th 3c 2d" type strings into bitmasks. lstulHands: a list of 2 card pocket hand masks as unsigned longs. Invalid hands that duplicate board cards are accepted but will get None for their score and equity. Use Use HoldemHand.Hand.ParseHand("Ac Kc") or HoldemHand.PocketHands.Query("AK").ToArray() to get hand masks. bPrintResults: True will print the results to the console. Use False for fastest results. NOTE: change 'pass #print' or '#print' lines to 'print' to enable more detailed debug messages Returns a tuple of (lstintScores, lstfEquity, intHighScore, lstHighScorers): lstintScores = integer representing the scored value for each hand. If hand is invalid given the board cards, value will be None. lstfEquity = float representing the hand's equity. If hand is invalid given the board cards, value will be None. Sum of all valid hands will equal 1. intHighScore = integer representing the highest scored value (ie what the winner(s) had). Use HoldemHand.Hand.DescriptionFromHandValueInternal(intHighScore) to get a string description lstHighScorers = list of the index to each of the winners. use len(lstHighScorers) to find out how many winners there were. Example: testBoard = ((288<< (13 * 3)) + (7<< (13 * 2))) #0b100100000000000000011100000000000000000000000000 is Ts 7s 4h 3h 2h testHands = [3, 3 << 13, 3 << (13*2), int('1100',2)] #3 is 0b11 for 3c 2c, "3 << 13" is 3d 2d, "3 << (13*2)" is 3h 2h, and int('1100',2) is 4c 5c (lstintScores, lstfEquity, intHighScore, lstHighScorers) = HandEquity(testBoard, testHands)""" intTotalHands = len(lstulHands) if intTotalHands < 1: raise Exception("Error: lstulHands must have at least one hand.") if HoldemHand.Hand.BitCount(ulBoard) != 5: raise Exception("Error: ulBoard must be a 5 card hand.") if(bPrintResults): print "Start HandEquity with %d hands and board cards: %s" % (intTotalHands, HoldemHand.Hand.MaskToString(ulBoard)) lstintScores = [None]*intTotalHands #List that holds scores for all the handss lstfEquity = [None]*intTotalHands #List that holds equity values for all the hands intHighScore = -1 #The highest score currently found lstHighScorers = [] #list of the index for hands that currently have the highscore intScore = None #temp variable to hold the current score #NOTE: Hand equity will not be available until after this loop finishes! for intIndex,aHand in enumerate(lstulHands): if HoldemHand.Hand.BitCount(aHand) != 2: raise Exception("Error: Pocket hands must contain 2 cards. lstulHands index %03d HandMask=0x%0.13X contains %d cards." % (intIndex, aHand, HoldemHand.Hand.BitCount(aHand))) if(aHand & ulBoard == 0): #Make sure there are no duplicate cards on the board and hand first lstintScores[intIndex] = intScore = HoldemHand.Hand.Evaluate(aHand | ulBoard) #evaluate the hand and board cards to get the score if intScore > intHighScore: intHighScore = intScore #Set new high score lstfEquity[intIndex] = float(1) for aScoreIndex in lstHighScorers: #Reset old high scores equity to zero lstfEquity[aScoreIndex] = 0 lstHighScorers = [intIndex] #Reset high score list elif intScore == intHighScore: #There is a tied pot, so calculate partial equity lstHighScorers.append(intIndex) #Add this index to the current high score for aScoreIndex in lstHighScorers: #Reset old high scores equity to partial equity lstfEquity[aScoreIndex] = 1 / float(len(lstHighScorers)) else: lstfEquity[intIndex] = 0 #losing hand gets no equity #print "%03d: Hand %s score is %s (%d)" % (intIndex, HoldemHand.Hand.MaskToString(aHand), HoldemHand.Hand.DescriptionFromHandValueInternal(intScore),intScore) else: pass #print "%03d: Hand %s has duplicate cards. Score and Equity are None." % (intIndex, HoldemHand.Hand.MaskToString(aHand)) #Hand equity is now finished. if(bPrintResults): #Print results for each hand print "Best hand is '%s' with %d winner(s) getting %0.2f equity in the hand." % (HoldemHand.Hand.DescriptionFromHandValueInternal(intHighScore), len(lstHighScorers), 1 / float(len(lstHighScorers))) for intIndex,aHand in enumerate(lstulHands): if(lstintScores[intIndex] == None): print "%03d: Hand %s HandScore=None Equity=None." % (intIndex, HoldemHand.Hand.MaskToString(aHand)) else: print "%03d: Hand %s HandScore=%09d Equity=%0.2f." % (intIndex, HoldemHand.Hand.MaskToString(aHand), lstintScores[intIndex], lstfEquity[intIndex]) return (lstintScores, lstfEquity, intHighScore, lstHighScorers) #Return Results

Here is a sample print-out when bPrintResults is True:

testBoard = ((288<< (13 * 3)) + (7<< (13 * 2))) #0b100100000000000000011100000000000000000000000000 is Ts 7s 4h 3h 2h
testHands = [3, 3 << 13, 3 << (13*2), int('1100',2)] #Hands: 3c 2c, 3d 2d, 3h 2h, and 4c 5c
(lstintScores, lstfEquity, intHighScore, lstHighScorers) = HandEquity(testBoard, testHands, True)

Start HandEquity with 4 hands and board cards: Ts 7s 4h 3h 2h
Best hand is 'Two pair, Three's and Two's with a Ten for a kicker' with 2 winner(s) getting 0.50 equity in the hand.
000: Hand 3c 2c HandScore=033622016 Equity=0.50
001: Hand 3d 2d HandScore=033622016 Equity=0.50
002: Hand 3h 2h HandScore=None        Equity=None
003: Hand 5c 4c HandScore=016942384 Equity=0.00

This is just a first step towards a full table Monte Carlo Simulator, but it gives you all of the details that are needed to find the winner(s) for a hand and calculate hand equity. The code used to generate a random board would be the same as in the other poker spreadsheets, but the player hand selection logic would need to use some sort of Round Robin algorithm to make sure that each player gets equal access to all of their possible pocket cards. Then you would use the HandEquity method to calculate the player’s equity for each hand and aggregate the results. My next goal is to change the spreadsheet code to use multiple threads, so I probably won’t tackle multi-way analysis anytime soon.

Windows 7 Issues with JMicron JMB36X Controller in AHCI mode

I had a lot of issues when setting up a RAID controller when installing Windows 7 a few months ago, and today I had more problems trying to get an IDE drive to work in the same machine. The ASUS P6T motherboard has two hard drive controllers: one Intel ICH10R SATA controller for the 6 red SATA ports, and one JMicron controller for the two orange SATA ports and the single IDE connection. When I installed an old IDE hard drive to the JMicron controller, it was recognized in Windows but I was never able to format the drive. It kept saying that the window needed to be refreshed or that I needed to reboot the machine.

After trying a few times with no progress, I decided to check and see if there was any updated drivers on the ASUS website. Turns out there was a recent BIOS update for the JMB322 firmware, so I download it to a jump drive, rebooted the computer and used the EZ Flash 2 utility built into the BIOS to run the update (I love this feature!) After changing the ICH10R settings back to RAID and setting the volume to be the primary hard drive and default boot device I was able to load back into Windows, but now there was another issue: There were two JMicron JMB36X devices listed in the device manager and only one of them was working.

I tried removing both of them, thinking that one was a duplicate, but they both were detected again and one of them always listed “This device cannot start. (Code 10)” as the error message. The BIOS settings had the JMicron controller setup as AHCI mode, but no matter what I tried I could not get it to work. I even tried the latest drivers right from the JMicron website, but still nothing.

Finally I tried changing the JMicron Controller settings from AHCI to IDE, and it started working again. This time I could detect the drive and the formatting worked just fine. Seems like the P6T motherboard still has a few bugs to work out specifically with the SATA and IDE controllers when using Windows 7 64bit edition. If I had to do it again I would probably ditch the hardware RAID array and instead buy an SSD drive for much better performance and a lot less headaches trying to set things up.

Powershell V2 training videos on Channel 9

If you are a Windows system administrator and are not familiar with Powershell yet, you should be! Even programmers should find a .NET based command shell useful in many situations where they want to test an assembly or use it in a simple script. I just found out that Channel 9 has a PSV2 tag for all of their Powershell V2 videos, which cover different aspects of Powershell in a short video format. These videos don't appear to have a lot of views or comments, but they are definitely good learning material and could use a bit more exposure. Check out this one on Variables, Types, and Operators:

 

Also if you are using Powershell, I highly recommend using the now free Powershell Analyzer for creating and editing your scripts. I think that it still runs V1 of Powershell, but it is the best IDE that I have worked with so far. The Powershell Plus that replaced it just doesn't fit my needs quite as well.

Learn WPF on a Sunday Morning!

I have been working on a few projects that involve WPF and Silverlight these last few weeks, and I have to say that the learning curve is a bit steeper than I expected. I have worked with Windows Forms and ASP.NET Web Forms for a while and know most of the tricks regarding layout and data binding, but WPF brings a lot of new technology and features into the mix. I had tried to jump straight into Silverlight design using Expressions Blend, but I got lost and overwhelmed in the process. This weekend I took a step back and started with learning WPF, which is the way that applications are designed in Silverlight. There are a lot of great resources, but all I needed to get moving were a few videos from the www.WindowsClient.net website. Their series on WPF showed the basics for layout and design options in a simple WPF application. Here are some good videos to get you started:

1. Creating a simple WPF application (10:19)
2. Using XAML in WFP (10:34)
3. Overview of the different layout panels in WPF (14:05)
4. Simple WPF Data Grid Editing using ListView and GridView (31:03)
5. WPF Effects (11:41)
6. Host a WPF Control in a Win32 Application (19:59)

There is plenty more to learn about WPF (Animations, Styles, Video, etc), but this is a great way to get a start using WPF.

Enjoy!

Simple Talk article about TSQL Select For XML clause

I had to do some work today creating a C# web service that would query a MS SQL database and return an XmlDocument with the results. After writing the query I tried loading the data into a DataTable and then using the WriteXML method to turn it into XML, but the layout wasn't exactly what I wanted and the use of a TableAdapter, StringWriter, XMLTextWriter, and XMLDocument seemed a bit excessive. I then remembered that MS SQL 2000, 2005, and 2008 support the For XML clause on Select statements. I had read an article from Simple Talk a few months ago about the different options and by adding "FOR XML RAW ('LogEntry'), ROOT ('LogEntries')" to the end of the query I was able to get the SQL server to format the data for me and then use the ExecuteXmlReader() method to create an XmlReader and load the data into an XmlDocument. The article is a great read for anyone that needs to work with XML data from an MS SQL server.

http://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

.NET Dynamic Languages Deep Dive: Bring your scuba gear!

So I’ve been playing around with IronPython for a little while now and have been having a lot of fun learning to combine dynamic and static programming techniques. I haven’t tried to do anything too fancy yet, but using an IronPython powered spreadsheet to create a Texas Holdem Monte Carlo Simulator did show that you can get reasonably good performance just hacking away at a problem, even when there are a few extra levels of indirection, binding, and code generation added into the mix.

Lately I’ve been keeping my Google Reader RSS feed full of cartoons, news, and blog posts, and I just barely got around to digesting Bruno Terkaly’s break down of C# 4.0 and the Dynamic Language Runtime. He uses his blog to do presentations, so there usually are a lot of diagrams and a few key paragraphs of text, but if you have been keeping up with the developments then the pictures really help to pull things into perspective.

This post in particular was expanding on Jim Hugunin’s talk about the use of Dynamic Languages in Microsoft .NET from PDC 2008 almost a year ago. I had seen Anders Hejlsberg’s presentation about The Future of C#, but I hadn’t had a chance to watch the video about the DLR until today. If anyone is wondering why there is so much hype about the DLR, the new Dynamic type in C#/VB.NET, or the emergence of IronPython and IronRuby, I suggest taking a look at both of these videos, as they show what in my mind is the future of programming. These technologies have been picking up steam over the last year or two, but when .NET 4.0 and VS 2010 ship in the near future, I expect to see a lot more applications taking advantage of this new powerful platform.

The DLR in particular is quite fascinating, in that it bridges the gap between Dynamic and Static languages, as well as creating a method for connecting .NET code to COM, Javascript, Python, Ruby, and whatever else you want to tie into it with a custom binder. Also, with the DLR being supported in Silverlight, there are a lot of new opportunities for running managed dynamic code directly from a browser. Gestalt for instance is a newly released project hosted at the Microsoft mix online community labs, which has the goal of letting web developers replace or supplement their JavaScript code with Ruby, Python & XAML directly embedded into HTML pages using the standard <Script> tags that get parsed by Javascript and Silverlight. Very interesting stuff!

IronPython SpeechSynthesizer Mad Lib!

I just submitted a Mad lib Spreadsheet to the Resolver One Exchange that shows how to use the System.Speech.Synthesis.SpeechSynthesizer class to make a talking Mad Lib. In case anyone else is looking to do Speech Synthesis from IronPython, I will post the code here as well. """A Self Reading MadLib using the System.Speech.Synthesis.SpeechSynthesizer Object from .NET 3.5. Usage: Import the script, change the words or call AutoFill() and then call Speak()""" import re import clr clr.AddReference("System.Speech") #Requires .Net 3.5 for Speech Recognition and Synthesis from System.Speech.Synthesis import SpeechSynthesizer #See http://msdn.microsoft.com/en-us/library/system.speech.synthesis.speechsynthesizer_members.aspx synth = SpeechSynthesizer() #synth.SetOutputToWaveFile("c:\MadLib.wav") #Setup SSML to speak. See http://www.w3.org/TR/speech-synthesis/ #NOTE: use %(index)s for each word, which will get Interpolated in the Speak-Click method #Text taken from http://www.redkid.net/cgi-bin/madlibs/littleredriding.pl xmlSSML = """<speak version="1.0" xmlns="http://www.w3.org/2001/10/synthesis" xml:lang="en-US"><voice name="%(voice)s"> <s>One day, Little <emphasis level="strong">%(2)s</emphasis> Riding Hood was going through the forest carrying a basket of <emphasis level="strong">%(5)s</emphasis> for her grandmother.</s> <s>Suddenly, she met a big <emphasis level="strong">%(1)s</emphasis>wolf.</s> <s><emphasis level="strong">%(3)s</emphasis> said the wolf.</s> <s>Where are you going, little <emphasis level="strong">%(9)s?</emphasis></s> <s>"I'm going to my grandmother's house," she said.</s> <s>Then the wolf <emphasis level="strong">%(4)s</emphasis> away.</s> <s>When Miss Riding Hood got to her grandmother's house, the wolf was in bed dressed like her grandmother.</s> <s>"<emphasis level="strong">My</emphasis>, Grandmother," she said, "What big <emphasis level="strong">%(6)s</emphasis> you have."</s> <s>"The better to <emphasis level="strong">%(10)s</emphasis> you with," said the wolf.</s> <s>"And <emphasis level="strong">Grandmother</emphasis>," she said, "What big <emphasis level="strong">%(7)s</emphasis> you have."</s> <s>The wolf said, "The better to <emphasis level="strong">%(11)s</emphasis> you with."</s> <s>And then she said, "What big <emphasis level="strong">%(8)s</emphasis> you have, Grandmother."</s> <s>But the wolf <emphasis level="strong">said</emphasis> nothing.</s> <s>He had just died of <emphasis level="strong">indigestion</emphasis>, from <emphasis level="strong">eating</emphasis>, Grandmother.</s> <s>Ha Ha Ha</s> </voice></speak>""" #setup list of voices lstVoices = [] for aVoice in synth.GetInstalledVoices(): if aVoice.Enabled: print aVoice.VoiceInfo.Name lstVoices.append(aVoice.VoiceInfo.Name) aVoice = lstVoices[1] #Choose voice lstWords = range(0,12) lstWords[1] = "" #Ajective lstWords[2] = "" #Color lstWords[3] = "" #Exclamation lstWords[4] = "" #Past Tense Verb lstWords[5] = "" #Plural noun #1 lstWords[6] = "" #Plural noun #2 lstWords[7] = "" #Plural noun #3 lstWords[8] = "" #Plural noun #4 lstWords[9] = "" #Silly Word lstWords[10] = "" #Verb #1 lstWords[11] = "" #Verb #2 #If you are too lazy to fill in the words yourself def AutoFill(): lstWords[1] = "stinky" lstWords[2] = "blue" lstWords[3] = "yippie!" lstWords[4] = "el bowed" lstWords[5] = "coolers" lstWords[6] = "hammers" lstWords[7] = "Q-Tips" lstWords[8] = "Sail Boats" lstWords[9] = "scallywag" lstWords[10] = "toast" lstWords[11] = "love" def MadLib(): if(lstWords[1] != ""): xmlText = xmlSSML % {"voice":aVoice, "1":lstWords[1], "2":lstWords[2], "3":lstWords[3], "4":lstWords[4], "5":lstWords[5], "6":lstWords[6], "7":lstWords[7], "8":lstWords[8], "9":lstWords[9], "10":lstWords[10], "11":lstWords[11]} print "Finished Mad Lib." print re.sub(r'<[^>]*?>', '', xmlText) else: print "You have to fill in the words or call AutoFill() first!" def Speak(): if(lstWords[1] != ""): print "Call Stop() to Stop voice" MadLib() ssml = xmlSSML % {"voice":aVoice, "1":lstWords[1], "2":lstWords[2], "3":lstWords[3], "4":lstWords[4], "5":lstWords[5], "6":lstWords[6], "7":lstWords[7], "8":lstWords[8], "9":lstWords[9], "10":lstWords[10], "11":lstWords[11]} synth.SpeakSsmlAsync(ssml) else: print "You have to fill in the words or call AutoFill() first!" def Stop(): synth.SpeakAsyncCancelAll() Enjoy!

Box Selection in Visual Studio: You learn something new every day!

I was browsing on the Coding4Fun site and came across this cool video about Box Selection in Visual Studio 2010:

<a href="http://video.msn.com/?mkt=en-US&amp;playlist=videoByUuids:uuids:3e57917d-1b92-4188-b898-25a1d64a408e&amp;showPlaylist=true&amp;from=shared" target="_new" title="Box Selection and Multi-Line Editing Demo">Video: Box Selection and Multi-Line Editing Demo</a>

Looks like deleting a box selection has been around for a little while in both Visual Studio and Office (simply hold the alt key while you select the text), I just never knew about it. Heck, only recently learned about using the clipboard ring a few months ago. VS 2010 adds the ability to also do inserts and replaces when doing a box selection, which really would come in handy while coding.

Export Vonage Call Log using IronPython and WatiN

Here is another IronPython script that shows how to use the WatiN testing framework, which is great for testing web pages in Internet Explorer, and it is starting to support Google Chrome and Firefox too. We use WatiN at work for testing the full stack on an ASP.Net website, and it works very well even for AJAX sites. This example will login to Vonage and export all of a user's call data to a CSV format.
"""Sample for getting Watin to work with IronPython Usage: Download the WatiN-2.0.10.928-net-2.0.zip file from http://sourceforge.net/projects/watin/files/ put this script file(WatinExample.py), Interop.SHDocVw.dll, Microsoft.mshtml.dll, and WatiN.Core.dll into a folder called Watin import the script or run execfile( "c:\\PATHTOFILES\\WatinExample.py") from console use the Run() method to run the script, or use Reload() to reload the module after making changes.""" import sys import clr import re from System.Reflection import Assembly #clr.AddReference("d:\My Documents\Resolver\Watin\WatiN.Core.dll") #Only works for IronPython 1.1 sys.path.append("D:\My Documents\Resolver\Watin") #Needed so that WatiN can find the Interop.SHDocVw.dll file assembly = Assembly.LoadFile("d:\\My Documents\\Resolver\\Watin\\WatiN.Core.dll") #Better way to import the file clr.AddReference(assembly) from WatiN.Core import * from System import DateTime,TimeSpan def reload(): """Code used to reload this module. Helpful for when testing from console.""" print "Reloading..." global Vonage Vonage.Close() execfile( "D:\\My Documents\\Resolver\\Watin\\WatinExample.py") def run(): """Code used to run a test script. Helpful for when testing from console.""" global listActivity Vonage.Logon("username","password") listActivity = Vonage.ParseActivity("1-(801)-123-4567", DateTime(2008,2,1),DateTime(2008,5,31)) Vonage.ArrayToCSV("C:\\test.csv",listActivity) class Vonage(object): "Static class for working with Vonage website using WatiN" IE = None #Instance of IE object used by WatiN @staticmethod def Logon(strUsername, strPassword): """Initiates WatiN for use in IronPython to scrape the Vonage website for call activity.""" print "Vonage.Login: Loading IE and browsing to Vonage.com" #TODO: Setup logging with different levels. Attach to WatiN for click events Vonage.IE = IE("http://www.vonage.com") Settings.WaitUntilExistsTimeOut = 25 #For development work set the timeout value to 15 seconds instead of default 30 seconds. print "Vonage.Login: Login to Vonage.com" Vonage.IE.TextField(Find.ByName("username")).TypeText(strUsername) Vonage.IE.TextField(Find.ByName("password")).TypeText(strPassword) Vonage.IE.Form(Find.ById("logonForm")).Submit() Vonage.IE.Link(Find.ByText("Log Out")).WaitUntilExists(15) #Waits up to 15 seconds for IE to login. If fails an exception will be thrown @staticmethod def isAuthenticated(): """Returns True if the session is currently logged in or false otherwise.""" return Vonage.IE.Link(Find.ByText("Log Out")).Exists @staticmethod def ParseActivity(strPhoneNumber, dtStartDate, dtEndDate): """Browses to the Activity section and parses the results. strPhoneNumber: Line Number to parse. Must be in 1-(xxx)-xxx-xxxx format. Must append (Fax) or (Virtual) for special lines. dtStartDate: Start Date (Inclusive). dtEndDate: End Date (Inclusive). Returns a list of call details""" if not Vonage.isAuthenticated(): raise "Vonage.ParseActivity Error: User is not authenticated." Vonage.IE.Link(Find.ByText("Activity")).Click() lstActivityData = [["DateTime", "From", "To", "Duration", "TransactionID","FromCallerID","TransferedTo"]] #Store Activity data as list of row data. First element is header row. #Run report for Incoming calls Vonage.IE.Link(Find.ByText("Advanced Activity Search")).Click() Vonage.IE.SelectList(Find.ByName("didID")).Option(strPhoneNumber).Select() #Select the phone number requested. If phone number does not exist an exception will be thrown. Vonage.SetDate("start",dtStartDate) Vonage.SetDate("end",dtEndDate.AddDays(1)) #NOTE: Have to set end date + one day to be inclusive Vonage.IE.RadioButton(Find.ByValue("Incoming")).Click() #First select incoming and run the report Vonage.IE.Form(Find.ByName("activityForm")).Submit() while True: Vonage.ParsePageData(lstActivityData) if Vonage.IE.Link(Find.ByText("Next>>")).Exists: Vonage.IE.Link(Find.ByText("Next>>")).Click() else: break #Run report again for outgoing calls Vonage.SetDate("start",dtStartDate) Vonage.SetDate("end",dtEndDate) Vonage.IE.RadioButton(Find.ByValue("Outgoing")).Click() #First select incoming and run the report Vonage.IE.Form(Find.ByName("activityForm")).Submit() while True: Vonage.ParsePageData(lstActivityData) if Vonage.IE.Link(Find.ByText("Next>>")).Exists: Vonage.IE.Link(Find.ByText("Next>>")).Click() else: break lstActivityData.sort(Vonage.SortData) #Sort data to merge Incoming and Outgoing calls return lstActivityData @staticmethod def ParsePageData(lstData): """Parses the call data from a page and appends each row to lstData""" print "Vonage.ParsePageData: Parse Page data." #TODO parse total results out of string and display "xx of xx" using table row count re.DOTALL = True #Make the . character match newlines in re.search for aRow in Vonage.IE.Table("received_calls").TableRows: #print aRow.InnerHtml if aRow.Index > 1 and aRow.TableCells.Count == 6: #Skip first and second row (header row) and any other row with less than 6 cells lstRowData = [] #See Row Data Format above lstData.append(lstRowData) #Add row to list lstRowData.append(DateTime.Parse(aRow.TableCells[0].Text + aRow.TableCells[1].Text)) #Create DateTime lstRowData.append(aRow.TableCells[2].Text.Trim()) #add From lstRowData.append(aRow.TableCells[3].Text.Trim()) #add To TODO: Parse Transfer data lstRowData.append(TimeSpan.Parse(aRow.TableCells[4].Text)) #add Duration lstRowData.append(long(aRow.TableCells[5].Text.Trim())) #add TransactionID regEx = re.search("<b>([^/]+)</b>'",aRow.TableCells[2].InnerHtml) #Get caller ID info if regEx and len(regEx.groups()) == 1: lstRowData.append(regEx.group(1)) else: lstRowData.append(None) regEx = re.search("Forwarded to (\d+)'",aRow.TableCells[2].InnerHtml) #Get forward info if regEx and len(regEx.groups()) == 1: lstRowData.append(regEx.group(1)) else: lstRowData.append(None) @staticmethod def SortData(lstA, lstB): "Comparer used to sort array data. Rates string datatypes highest, then compares based on datetime" if type(lstA[0]) == str: return -1 elif type(lstB[0]) == str: return 1 else: return cmp(lstA[0],lstB[0]) @staticmethod def SetDate(strPrefix, dtDate): """Used to set the Start and End Dates strPrefix: start or end dtDate: Datetime (inclusive)""" Vonage.IE.SelectList(Find.ByName(strPrefix+"Month")).Option(dtDate.ToString("MMM")).Select() Vonage.IE.SelectList(Find.ByName(strPrefix+"Day")).Option(dtDate.ToString("dd")).Select() Vonage.IE.SelectList(Find.ByName(strPrefix+"Year")).Option(dtDate.ToString("yyyy")).Select() Vonage.IE.SelectList(Find.ByName(strPrefix+"Time")).Option("Midnight").Select() @staticmethod def Close(): """Closes IE instance and cleans up""" if Vonage.IE <> None: print "Closing IE..." Vonage.IE.Close() Vonage.IE = None @staticmethod def ArrayToCSV(filename, listData): """Code used to convert an array of row data into a CSV file.""" csvFile = open(filename, "w") for aRow in listData: bFirst = True for aValue in aRow: if bFirst: bFirst = False #Reset first flag else: csvFile.write(",") #seperate values with a comma #Write data item with special conversions if type(aValue) in (int, long, float): #Keep integer types the same. Also DateTime is a special case (See ExcelManager.SetCell) csvFile.write(str(aValue)) #write numbers without quotes elif type(aValue) is TimeSpan: csvFile.write(str(aValue.TotalMinutes)) #write value as total minutes elif aValue is None: pass #write null entry as nothing (ie: 1,2,,4) else: #Cast everything else to a string csvFile.write("\""+str(aValue)+"\"") #Convert everything else to a string and enclose in brackets csvFile.write("\n") #New line after each row. Note: \r\n doesnt work correctly on IPY 1.1 csvFile.close()
Enjoy!

IronPython Date Selector: Pick a date... any date...

Another quick IronPython snipet that shows how to use the Windows.Form.MonthCalendar control to create a simple date selector.

"""rslCalendarForm contains the code for creating a Windows Form with a Calendar control to let the user choose a Date value. Usage: execfile(dirname(__file__)+"\\rslCalendarForm.py") #use execfile with the full path to the file name to load the module frmCalendar = CalendarForm("Window Title") #Create and load the form. GUI thread is blocked until the form is closed dtSelected = CalendarForm.LastDateSelected #Retrieve selected value from the static CalendarForm.LastDateSelected variable. """ #NOTE: Form code taken from http://www.voidspace.org.uk/ironpython/presentation.html and was originally written by Michael Foord import sys import clr clr.AddReference('System.Drawing') clr.AddReference('System.Windows.Forms') from System.Windows.Forms import Application, Form from System.Windows.Forms import Button as frmButton from System.Windows.Forms import (ControlStyles, ContextMenuStrip, DockStyle, FormStartPosition, ImageList, Keys, MenuStrip, MessageBox, MessageBoxButtons, MessageBoxIcon, Panel, TabControl, TabAlignment, TabPage, ToolStrip, ToolStripButton, ToolStripMenuItem,ToolStripLabel, ToolStripItemDisplayStyle, WebBrowser, Padding, FormBorderStyle,SizeGripStyle) from System.Windows.Forms import (MonthCalendar, Day) from System.Drawing import Size,Point from System import Uri, Exception, DateTime class CalendarForm(Form): "Windows Form that displays a Calendar control. This will run in the main GUI thread." LastDateSelected = None #Static value used to store the last date selected def __init__(self, Name="Calendar",dtStartDate=None): "Name is the title of the control. Callback is a method that accepts a Datetime as a parameter and will be called when the form is closed." Form.__init__(self) CalendarForm.LastDateSelected = None #Clear last date selection self.Text = Name self.Name = Name self.Width = 210 self.Height = 230 self.FormBorderStyle = FormBorderStyle.FixedDialog self.SizeGripStyle = SizeGripStyle.Hide self.MinimizeBox = False self.MaximizeBox = False self.initMenu() self.initCalendar(dtStartDate) self.CenterToParent() self.ShowDialog() def createToolstripButton(self, name, text, clickHandler=None, keys=None): menuItem = ToolStripButton(Name = name, Text = text) if keys: menuItem.ShortcutKeys = keys if clickHandler: menuItem.Click += clickHandler return menuItem def initMenu(self): menuStrip = MenuStrip( Name = "Main MenuStrip", Dock = DockStyle.Bottom ) mSpacer = ToolStripLabel(" ") mBack = self.createToolstripButton('Save', '&Save', self.onSave) mSpacer2 = ToolStripLabel(" ") mForward = self.createToolstripButton('Cancel', '&Cancel', self.onCancel) menuStrip.Items.Add(mSpacer) menuStrip.Items.Add(mBack) menuStrip.Items.Add(mSpacer2) menuStrip.Items.Add(mForward) self.Controls.Add(menuStrip) def initCalendar(self, StartDate=None): "Setup the Calendar Control." pCalendar = Panel(Dock = DockStyle.Fill) pCalendar.Padding = Padding(25, 10, 25, 10) self.mCalendar = MonthCalendar() self.mCalendar.Location = Point (10, 10) self.mCalendar.FirstDayOfWeek = Day.Sunday self.mCalendar.ShowTodayCircle = False self.mCalendar.ShowToday = False self.mCalendar.ShowWeekNumbers = False self.mCalendar.MaxSelectionCount = 1 self.mCalendar.DoubleClick += self.onSave if StartDate==None: self.mCalendar.SetDate(DateTime.Now) else: self.mCalendar.SetDate(StartDate) pCalendar.Controls.Add(self.mCalendar) self.Controls.Add(pCalendar) def onSave(self, _, __): "Set the CalendarForm.LastDateSelected to the selected date and then close the form." CalendarForm.LastDateSelected = self.mCalendar.SelectionStart self.Close() def onCancel(self, _, __): self.Close()

Works well for selecting singe dates, and could be modified to support selecting a date range if desired. I prefer having a Start Date and End Date, and selecting each separately. Full code can be downloaded here.

Enjoy!

IronPython Excel COM Automation: Pivot this!

I have been playing around with IronPython recently while creating spreadsheets in Resolver One. So far I have been having a lot of fun, but I haven't really been doing anything that would be useful in a work setting. The main reason for this is because most of the spreadsheet work that I do revolves around pivot tables and Auto Filter, which are immensely helpful for visually analyzing data. I thought I might try and use Excel's COM Automation to add Pivot Charts and Auto Filter to any system with IronPython, and here is the code that I have come up with so far:
UPDATE 7/13/2009: After releasing the initial code, I found that there was a problem with the HideToolbars function causing the Excel toolbars to be disabled permanently. Also, the code would not work if the user's Region was set to something other than "en-US" due to this known bug. I updated the code to disable the HideToolbars function and use Reflection and the InvokeMember method to force the Culture to be "en-US". If you want to view the old "en-US" only code, you can download it here.
"""ExcelManager contains code for interacting with an Excel spreadsheet. This code should work with both IronPython 1.1 and IronPython 2.0 NOTE: Microsoft Excel must be installed on the machine for this to work. Code should be compatible with Office 2003 or Office 2007 If you get an error message about "IOError: Could not add reference to assembly Microsoft.Office.Interop.Excel" then you need to install the Primary Interop Assemblies for Microsoft Office, which can be downloaded from: Office 2003 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en Office 2007 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=59DAEBAA-BED4-4282-A28C-B864D8BFA513&displaylang=en Install from Office Setup CD: http://msdn.microsoft.com/en-us/library/aa159923(office.11).aspx#officeprimaryinteropassembliesfaq_after The original code gave an Error: 0x80028018 "Old Format or Invalid Type Library" when trying to set worksheet values (Name, Cell.Value2, etc) This is a known issue when the computer's region is set to something other than en-us: http://support.microsoft.com/kb/320369 The ExcelManager.SetProperty method was added as a workaround to this issues. It uses the Reflection and the InvokeMember method to ensure that the CurrentCulture is set to en-US. This method should be used whenever you set a property value to ensure that the code will work in any culture. More info at http://www.ironpython.info/index.php/Interacting_with_Excel """ import sys import clr clr.AddReference("Office") clr.AddReference("Microsoft.Office.Interop.Excel") from Microsoft.Office.Interop import Excel from System.Reflection import Missing, BindingFlags from System.Threading import Thread from System.Globalization import CultureInfo from System import Array, DateTime, DayOfWeek def reload(): """Helper method to reload this script when testing or debuging""" print "Reloading..." global ExcelManager ExcelManager.CloseExcel(True) #Force Excel to close execfile( "D:\\My Documents\\Resolver\\RslPivot V1.4\\ExcelManager.py") def run(): """Sample method for how to use the Excel Manager""" global SheetData SheetData = [['a','b','c'],[1,2,3],[4,5,6],[7,8,9]] #Data should be packed row by row in a multi-dimentional array. First Row is Header Row. ExcelManager.OpenExcel(True) #Open Excel but do not show the window yet ExcelManager.NewBlankWorkbook("My Autofilter") ExcelManager.LoadSheetData(SheetData) ExcelManager.CreatePivotTable("My Pivot Table") ExcelManager.CreatePivotChart("My Pivot Chart") ExcelManager.HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]) #Disable all the toolbars except the Pivottable, Standard toolbar and FormulaBar ExcelManager.ShowWindow() class ExcelManager(object): """Static class for managing the Excel application. See http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(office.11).aspx for more details.""" ExcelApp = None #Excel Application Interop Object ExcelWorkbook = None #Excel Workbook Interop Object ExcelWorksheet = None #Excel Worksheet Interop Object PivotTable = None PivotChart = None LocalShortDateFormat = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern #used to format from OADate to correct format @staticmethod def SetProperty(target, name, value): """Used to set properties using the en-US culture, which is the safest way to do Excel COM integration. See: http://support.microsoft.com/kb/320369""" try: args = Array[object]([value]) return target.GetType().InvokeMember(name, BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, None, target, args, CultureInfo(1033)) except: print "Error setting Property: " + name print sys.exc_info() raise @staticmethod def OpenExcel(ShowWindow=False): """Opens a new Excel Application. ShowWindow: True means the window will be visible, otherwise the window will be hidden. """ #print "ExcelManager: Opening Excel" ExcelManager.ExcelApp = Excel.ApplicationClass() if ShowWindow: ExcelManager.ShowWindow() else: ExcelManager.HideWindow() @staticmethod def ShowWindow(): ExcelManager.ExcelApp.Visible = True @staticmethod def HideWindow(): ExcelManager.ExcelApp.Visible = False @staticmethod def CloseExcel(ForceClose=False): """Closes the Excel application. If ForceClose=True then the user will not be prompted to save any changes.""" if ExcelManager.ExcelApp <> None and ExcelManager.ExcelWorkbook <> None: print "Quitting Excel..." if ForceClose and ExcelManager.ExcelApp.Workbooks.Count > 0: ExcelManager.SetProperty(ExcelManager.ExcelWorkbook, "Saved", True) ExcelManager.ExcelApp.Quit() @staticmethod def NewBlankWorkbook(SheetName="Worksheet"): """Opens a new blank workbook in Excel with one worksheet with the given Name.""" #print "ExcelManager: NewBlankWorkbook" ExcelManager.ExcelWorkbook = ExcelManager.ExcelApp.Workbooks.Add() #NOTE: use Item.__getitem__(index) to be compatable with both IronPython 1.1 and IronPython 2.0 ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(3).Delete() #workbook starts with 3 blank worksheets. Delete the second and 3rd. ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(2).Delete() #ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1).Name = SheetName #Threw error whenever the computer's region was set to something other than en-us. Use SetProperty method instead. ExcelManager.SetProperty(ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1), "Name", SheetName) ExcelManager.ExcelWorksheet = ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1) @staticmethod def SetCell(RowIndex, ColIndex, Value): #print "Loading Cell(" + str(RowIndex) + str(ColIndex) + ") with " + str(Value) aCell = ExcelManager.ExcelWorksheet.Cells.Item.__getitem__(ColIndex).Item.__getitem__(RowIndex) if type(Value) == DateTime: #aCell.Value2 = Value.ToOADate() #Threw exception when culture was not en-us. Use SetProperty instead ExcelManager.SetProperty(aCell, "Value2", Value.ToOADate()) ExcelManager.SetProperty(aCell, "NumberFormat", ExcelManager.LocalShortDateFormat) else: ExcelManager.SetProperty(aCell, "Value2", Value) @staticmethod def SetRow(RowIndex, ArrayValues): #print "Loading Row " + str(RowIndex) + " with " + str(ArrayValues) intColIndex = 0 for aValue in ArrayValues: intColIndex += 1 ExcelManager.SetCell(RowIndex,intColIndex, aValue) @staticmethod def GetCommandBar(strName): """Helper method used to get a Commandbar. This way the code works with both IronPython 1.1 and IronPython 2.0""" if hasattr(ExcelManager.ExcelApp.CommandBars, "Item"): return ExcelManager.ExcelApp.CommandBars.Item(strName) #IronPython 2.0 else: return ExcelManager.ExcelApp.CommandBars[strName] #IronPython 1.1 @staticmethod def LoadSheetData(SheetData): """Loads data into the Excel WorkSheet. SheetData should contain an object for each row, with each object containing data for that row.""" intRow = 0 for aRow in SheetData: intRow +=1 ExcelManager.SetRow(intRow,aRow) ExcelManager.ExcelWorksheet.UsedRange.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, True) #Enable Autofilter @staticmethod def CreatePivotTable(strName="Pivot Table"): """Creates a Pivot Table. See http://www.eggheadcafe.com/community/aspnet/2/10050513/solved--export-data-from.aspx for more info.""" NewSheet = ExcelManager.ExcelWorkbook.Worksheets.Add() ExcelManager.SetProperty(NewSheet, "Name", "Pivot Table") ExcelManager.ExcelWorkbook.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, ExcelManager.ExcelWorksheet.UsedRange, NewSheet.UsedRange, strName, True, True, True, False, Missing.Value, Missing.Value, False, False, Excel.XlOrder.xlDownThenOver, 5, Missing.Value, Missing.Value) ExcelManager.ExcelWorkbook.ShowPivotTableFieldList = True ExcelManager.GetCommandBar("PivotTable").Visible = False ExcelManager.PivotTable = NewSheet @staticmethod def CreatePivotChart(strName="Pivot Chart"): """Creates a Pivot Chart. See http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart_properties(VS.80).aspx for more info. Example code: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/c7b0f6dd-8890-41c7-a5e0-e18f70686f8c""" ExcelManager.PivotChart = ExcelManager.ExcelWorkbook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) #ExcelManager.PivotChart.ChartWizard(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlChartType.xlColumnClustered, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, True, "Pivot Chart Name", Missing.Value, Missing.Value, Missing.Value) #This method threw an 0x800A03EC error message, so set the values seperately ExcelManager.SetProperty(ExcelManager.PivotChart, "ChartType", Excel.XlChartType.xlColumnClustered) #ExcelManager.PivotChart.SetSourceData(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlRowCol.xlColumns) #Threw error... but seems to work fine without it. Must assume that it is a pivot chart ExcelManager.SetProperty(ExcelManager.PivotChart, "HasLegend" , True) ExcelManager.SetProperty(ExcelManager.PivotChart, "HasTitle", True) #Show chart title and set it's text ExcelManager.SetProperty(ExcelManager.PivotChart.ChartTitle, "Text", strName) ExcelManager.SetProperty(ExcelManager.PivotChart, "Name","Pivot Chart") #Set Tab name @staticmethod def HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]): """Hides all the toolbars and only shows the worksheet and charts. If the toolbar's name is in lstExcept then it will remain visible. NOTE: May cause tool bars to be perminantly disabled if the worksheet is saved! It is advised not to use this method, needs to be reworked.""" #for aBar in ExcelManager.ExcelApp.CommandBars: # if lstExcept.Contains(aBar.Name): # aBar.Enabled = True # else: # aBar.Enabled = False #disable #ExcelManager.ExcelApp.DisplayFormulaBar = lstExcept.Contains("FormulaBar") pass
The code should be pretty straight forward. The Reload() method is helpful for reloading the whole module when testing from a console. The Run() method shows the steps to load a new spreadsheet. The ExcelManager static class is the core of the logic and provides an entry point for interacting with the spreadsheet.
There is not a lot of debuging code, so if something goes wrong it is just going to throw an exception, but this should be enough to help get things started. I did fix some funky problems with importing DateTime, where they kept getting converted into integers. Should work fine now, but anything other than Datetime, numerical types or strings will probably not get imported correctly.
If anyone else needs to do Excel Interop with IronPython, feel free to use the code, which can be downloaded here.
Enjoy!

Simple-Talk article about SQL FOR XML usage

I read a great article from Simple-Talk today about how to use the FOR XML option in Microsoft SQL server. I usually would passively post these to a shared RSS reader feed or star them for future reference, but I found the article through the Red Gate Newsletter and I don't really want to add the entire feed just to share one article. I do however suggest signing up for the newsletter if you are interested in MSSQL, .NET, or Exchange as they often have a lot of good articles about coding in or managing these systems.

Simple-Talk is a service from Red Gate, which creates tools for Exchange, SQL Server and .NET including the .NET Reflector, which is an invaluable tool for anyone using the .NET framework. I use it all the time to help with testing and debugging or when using .NET libraries that have poor user documentation. It even includes add-ins for changing code in .NET assemblies or adding an embedded IronPython console for playing around with objects and methods. Very handy indeed!

IronPython programming can be harder than it looks!

After playing around with IronPython for a few months, I definitely like how easy it is to manipulate objects. Everything is truly open, and if you want to attach one object to another or pass a function around as a variable then "it just works". I have mainly been using IronPython for fun, but right now I would be a bit scared to use such a fluid language for a structured large scale production application: The lack of static typing and compile time checks would make we wary of using IronPython for "real code" instead of just scripting. I stumbled on a video of Giles Thomas on Channel 9 (posted in March 2008 but mystically appeared in my RSS reader this afternoon) and it sounds like Resolver Systems had similar concerns but combined IronPython with Test Driven Development to build a solid foundation for Resolver One. That way they get plenty of up front testing and don't need to second guess the dynamic nature of programming in python. Seems to have worked well so far, as Resolver One is a rock solid product from my experience.
Usually when somebody says that "it just works", the phrase comes with an asterisk and a long line of small text at the bottom of the page. In my experience with IronPython this is still the case, as there are a few things that are much harder to do than they should be. Michael Foord, another Resolver Systems developer, co-wrote the book on IronPython, and today wrote an article about a case when IronPython code ends up being a bit tricky. If you ever need to get the docstrings from an arbitrary IronPython object, or just want to see a detailed explanation of some of the inner workings of python, the Techie Blog is a great read!

Installing Windows 7 on ASUS-P6T with IHC10R RAID Array

I recently purchased a new computer, and I decided to go with the following components:

Motherboard: ASUS P6T LGA 1366 Intel X58 ATX

CPU: INTEL Core i7 920 2.66G quad core with Hyper Threading (4 cores, 8 threads)

RAM: 3x2GB DDR3 1600

Hard Drive: 2x250GB Seagate Barracuda 7200.10 ST3250310AS setup as RAID 1 Mirror

Video Card: MSI N9400GT-MD512H 9400GT RTL with passive cooling design

Case: APEVIA X-ALIEN MX-ALIEN-BK Black Steel ATX Full Tower

The build went fine, but I got stuck trying to install Windows 7 64Bit RC, as I could not get it to load the drivers for either the JMicron JMB363 or Intel ICH10R SATA controllers. The SATA Port 0 and 1 are controlled by the JMicron JMB363 controller, which can be setup as a special Speed or Backup mode using Intel Drive Xpert technology. I couldn't find any information about what these actually were (I assume Raid 0 and Raid 1, but not sure), so I decided to try and setup the drives on the ICH10R controller as a standard RAID 1 array instead. After updating the bios using the built in ASUS EZ Flash 2 feature (very cool!), I setup the ICH10R controller in RAID mode in the BIOS, disabled the quick boot and the Floppy drive (was causing Windows 7 setup to be incredibly slow trying to loading drivers from a floppy drive that didn't exist), and pressed CTRL+i to setup the drives in mirror mode.

Windows 7 setup still couldn't detect any of the drives. The motherboard came with a driver disk, but I couldn't get any of the SATA drivers for Intel or JMicron on it to work. Even after breaking the raid and installing one drive on each controller, and changing from RAID to AHCI, it still couldn't find them. I tried downloading 3 different versions of the ICH10R driver, even the ones that said they were Windows 7 compatible. I final found a driver that worked for the JMicron controller on the motherboard driver disk tucked away in an install directory in an AMD64 directory. This wasn't what I wanted, but at least I was able to get Windows installed. It took a long time and I thought that it stalled on the "Completing Installation" section, but after an hour or so it changed to a message saying that the installation will continue after rebooting (which I force to reboot after 10 minutes of waiting). Looks like they still haven't fixed any of the useless notifications during windows 7 installation.

After Windows loaded up, I downloaded and installed the latest drivers and ran Windows update, which updated the drivers again. A few reboots, and everything looks great, although it still is running off of the JMicron controller and is not in RAID 1 like I wanted. I switched the drives over to the ICH10R controller, but then it wouldn't load into windows. I was about to cry, but then I noticed that there were two drives listed in the boot sequence, and one of them was still a blank drive. I switched the order of the drives so that the one with Windows was on the lowest SATA port, and then after rebooting it loaded into Windows again (crisis adverted!). Now the drives were on the correct controller, which was still setup in RAID mode in the bios. Unfortunately you can't create a RAID array in the boot loader from an existing disk without loosing all of the data, but by downloading and installing the Intel Matrix Storage Console I was able to convert an existing disk to a RAID 1 array. The process failed the first time, but I rebooted, tried again, and after 2 hours (SATA 3.0GB/sec my ass....) I had everything working just how I wanted.

A bit of a pain, but at least I got it to work. It might be that there is a special driver for the ICH10R SATA RAID mode that is hidden somewhere, but I couldn't find anything that worked. Best bet if you are installing Windows 7 64-bit on ASUS-P6T is to connect a drive to Port 0 or 1 and load the JMicron drivers from the install/AMD64 directory. You can then move over to the Intel controller after updating all the drivers in Windows and build whatever type of RAID array you want using the Intel Matrix Storage Console.

Update 10/1/2009: More issues with JMicron controller and IDE drive.

PowerShell Script for Processing TimeManagment CSV Data

If you are a code geek I highly suggest looking at the An App A Day site to see some cool open source projects that were designed and created in less 24 hours. I have been using the TimeManagment program to create my time cards for a few years now, although because of it's lack of analysis tools I haven't been able to do much else with the data. If you are interested in tracking and analyzing time spent on your computer, RescueTime is much more stable and has many, many more features, but TimeManagment is a great way to create an audit log and do customized tracking.

Even something as simple as creating a time card can be a bit of a pain, since all of the data is stored in a single CSV file. You end up having to open the file in Excel and scan through thousands of lines like these to find what you were doing at any given time:

03/04/2009,10:31:46,Idle,,0.334114583333333 03/04/2009,10:32:06,javaw,GmailAssistant,0.166666666666667 03/04/2009,10:32:16,explorer,,0.1671875 03/04/2009,10:32:26,chrome,Gmail - Google Chrome,0.0833333333333333 03/04/2009,10:32:31,chrome,Gmail - Inbox (4) - Google Chrome,0.166666666666667 03/04/2009,10:32:41,chrome,Gmail - New Issues and a Top Priority list - Google Chrome,0.0833333333333333 03/04/2009,10:32:46,chrome,Gmail - Total Protection Service Summary Report - Google Chrome,0.25 03/04/2009,10:33:01,chrome,Gmail - Search results - Google Chrome,0.0833333333333333 03/04/2009,10:33:06,chrome,Gmail - McAfee Registration Details - Google Chrome,0.0833333333333333

I finally got fed up with it and created a PowerShell script to do the job for me (including removing 1 hour each day for lunch). Now I can click a button and out pops a nice little report listing my hours for any given date range:

Start processing file: TimeManagement 2009MAR03.csv Start processing file: TimeManagement.csv Fill empty days from 3/13/2009 to 3/15/2009 Force Change data From: 3/3/2009 11:53:11 AM 3/3/2009 1:18:12 PM 01:25:01 to 10:00 AM 6:00 PM Force Change data From: 3/13/2009 11:56:26 AM 3/13/2009 5:59:43 PM 06:03:17 to 9:30 AM 6:00 PM Total Processing time 6.28 seconds for 16,619 valid csv entries and 623 unknown entries. Start Timecard 03/01/2009 - 03/02/2009 07:38:19 Hours 10:50 AM-7:28 PM 1 HR lunch 03/03/2009 07:00:00 Hours 10:00 AM-6:00 PM 1 HR lunch 03/04/2009 05:31:47 Hours 10:32 AM-5:03 PM 1 HR lunch 03/05/2009 06:29:37 Hours 10:39 AM-6:09 PM 1 HR lunch 03/06/2009 05:16:34 Hours 10:29 AM-4:45 PM 1 HR lunch 03/07/2009 - 03/08/2009 - 03/09/2009 08:20:59 Hours 10:26 AM-7:47 PM 1 HR lunch 03/10/2009 06:25:30 Hours 10:08 AM-5:33 PM 1 HR lunch 03/11/2009 07:14:26 Hours 11:00 AM-7:14 PM 1 HR lunch 03/12/2009 05:45:33 Hours 11:17 AM-6:02 PM 1 HR lunch 03/13/2009 07:30:00 Hours 9:30 AM-6:00 PM 1 HR lunch 03/14/2009 ? 03/15/2009 ? Total Time this period is 67.2 hours for 10 days. Average 6.7 hours per day. Projected: Total Hours 70.6, Total Pay $1,764.33, Take Home $1,369.12 End Timecard

You can see that it is not perfect (It still things that I plan on coming in and working a few hours this weekend. Fat Chance!), but if you are interested here is a link to the PowerShell script file. Should come in handy for anyone else using the TimeManagment program or just interested in processing CSV data files using PowerShell.

Enjoy!

Programs for reading and writing code in different languages

Just a quick post to keep things going. I end up having to read more code than I write, mainly because even when I write code I am usually pulling bits and pieces from old projects. When writing code I always use a full IDE, since I have become dependent on features such as code completion, breakpoints/debugging, and visual designers. For reading code these features are not as important, but code syntax highlighting and region/section collapsing are still very useful. For writing code I use all of the standard IDEs: Visual Studio 2005/2008 for .NET, SQL Management Studio for MS SQL, PowerShell Analyzer or PowerShell Plus for PowerShell, and IronPython Studio for IronPython. For reading code, rather than loading a bulky IDE I have started to use a free program called Notepad++, which supports multiple tabs, region collapsing, and syntax highlighting for a myriad of languages. It works a lot better than reading code in notepad and loads much faster than Visual Studio. Here is a screenshot:

JavaScript and CSS based popup for changing Google Analytics User Segment cookie

I use Google Analytics on all of the sites that I work on for tracking page views and traffic sources. It has great reporting features, but I always have problems with traffic from my office computer or home laptop skewing the results. Most of the computers that I use have dynamic IP addresses, so a static IP filter does not work. Instead you have to setup a cookie based filter. The problem is then how do you setup the cookie and monitor if your traffic is currently being tracked or not? I created a small JavaScript and CSS based popup for setting and clearing the cookie that I can then add somewhere to the site. The first section is the JavaScript code. Place this block somewhere in your HTML or template:

<script language="JavaScript"> <!-- //Code from http://www.netlobo.com/javascript_get_element_id.html function returnObjById( id ) { if (document.getElementById) var returnVar = document.getElementById(id); else if (document.all) var returnVar = document.all[id]; else if (document.layers) var returnVar = document.layers[id]; return returnVar; } //Code from http://www.quirksmode.org/js/cookies.html function createCookie(name,value,days) { if (days) { var date = new Date(); date.setTime(date.getTime()+(days*24*60*60*1000)); var expires = "; expires="+date.toGMTString(); } else var expires = ""; document.cookie = name+"="+value+expires+"; path=/"; } function readCookie(name) { var nameEQ = name + "="; var ca = document.cookie.split(';'); for(var i=0;i < ca.length;i++) { var c = ca[i]; while (c.charAt(0)==' ') c = c.substring(1,c.length); if (c.indexOf(nameEQ) == 0) return c.substring(nameEQ.length,c.length); } return null; } function eraseCookie(name) { document.cookie = name +'=; expires=Thu, 01-Jan-70 00:00:01 GMT;'; } //--> </script>

The second section is the DIV/CSS popup that contains a few links that you can click on. Again just place this anywhere in your HTML or template (it uses fixed positioning and will always appear in the middle of the screen):

<div id="divPopup" style="position:absolute; left:50%; top:40%; width:250px; height:200px; margin-left:-125px; margin-top:-100px;z-index:1;visibility:hidden;background-color: LightGrey;border: medium solid black;padding: 5px;"> <br /> <div style="font-weight:bold;margin: 5px 0px 25px 0px;">Google Analytics Settings:</div><br /> <div style="text-align: center; line-height: 2"><br /> <a style="display:block;" href="#" onclick="alert('Custom Segment cookie __utmv value: ' + readCookie('__utmv'));">Check if Custom Segment is set</a><br /> <a style="display:block;" href="#" onclick="pageTracker._setVar('NoTracking');alert('Custom Segment cookie __utmv set to NoTracking.');">Set Custom Segment to NoTracking</a><br /> <a style="display:block;" href="#" onclick="eraseCookie('__utmv');alert('Custom Segment cookie __utmv cleared.');">Clear Custom Segment</a><br /> </div><br /> <a style="position: absolute; bottom: 5px; right: 10px;" href="#" onclick="returnObjById('divPopup').style.visibility = 'hidden';">Close</a> <br /></div>

The final section is a link that can be placed somewhere on your page. Clicking the link will display the popup:

Put this link wherever you want users to <br /><a onclick="returnObjById('divPopup').style.visibility = 'visible';" href="#">click</a> to open the popup.

I use a period for the link text and hide it somewhere on the page. This way I can click the hidden link from any computer to disable tracking for that browser. This is an example of what the end result looks like:

For some reason Google Chrome does not let your clear the __utmv cookie using JavaScript. It works in IE and it works in Chrome on a localhost page, but it doesn't work on the live blog. A bit strange, but you can always clear the cookie manually in the browser. Oh, and don't forget to setup the filter in Google Analytics to exclude the traffic (see link above)

The next time I work on this I will probably change it so that the tracking status is displayed on the actual link itself using red "__ TRACKING DISABLED __" text if your page views were not being tracked or a normal period to anyone who's traffic was being tracked.

First Post! (aka CodeBlog Mission Statement)

I end up coding in a lot of different languages and keep on forgetting how to do simple things (is it "for x in y", "foreach", etc...). I need some place to store code that I can access from anywhere so that I don't have to keep going to Google each time I forget something. I don't want to post code snippets to my personal blog, so this is the new holding spot for all my coding tricks and treats. And thanks to SyntaxHighlighter 2.0 created by Alex Gorbatchev all those code snippets will be in full color and easy to view. For example, take this prize python gem: ##################################################################################### # Copyright (C) 2009 Greg Bray # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. ##################################################################################### import sys import clr from datetime import datetime print "Imported " + __name__ +" at "+ datetime.now().__str__() #display timestamp class TestClass(object): """This is a test class""" def __init__(self, strGreeting, strPlace=None): "strGreeting is a Salutation such as Hello. strPlace is an optional location." self.Greeting = strGreeting if strPlace == None: self.Place = "World" else: self.Place = strPlace def __str__(self): "Converts object to string" return "%s %s" % (self.Greeting, self.Place) #Run Code print TestClass("Hello") This returns: Imported __main__ at 2009-02-04 16:17:24.950000 Hello World Don't expect any long winded posts about Systems vs. Apps Hungarian Notation (ok... well maybe a few :-) as this is designed to be home to just sweet, sweet code. Enjoy!