JCooney.NET

This Christmas re-live the passion all over again with the famous* “developers developers developers” shirt, the only garment to accurately encapsulate the energy, enthusiasm and profuse sweating of Microsoft CEO Steve Ballmer’s famous speech to developers everywhere. As a special gift to YOU I’ve unleashed this zenith of word-art creativity and masterpiece of screen-grabbing for you to buy on CafePress for about $25 US (meaning that I’m making a whole $6 on EACH SHIRT!!!1). This is a little experiment of mine to see just how much more productive designing t-shirts is vs. developing advertising-sponsored software. If I sell a single shirt then this will be by FAR and away the most profitable. The t-shirt is available in 3 colors (pictured below) - “Sweaty Billionaire Blue” (my fave), “#6f6f6f” and “SteveJobsInvented Black”. So what are you waiting for? BUY BUY BUY!

Or if you’re of more the DIY persuasion you can download the original image and print it out yourself and iron-on-transfer it on to a t-shirt.

Comments

It all started with me writing this kind of T-SQL query over and over:
Select * from sysobjects where xtype = ‘U’ and name like ‘%foo%’
For the longest time I’ve wanted to write a SQL Server Management Studio (SSMS) add-in that would allow me to search for schema entities like tables and stored procedures by name non-modally to help me find things in “big” schemas. SSMS already has a schema entity search/filter feature built-in. You launch it by clicking the button with the funnel-shaped icon as shown in the screen-shot below. I wasn’t too enamoured with this since it launches a modal dialog, it only lets you search across one database, and only for one kind of thing at a time (table, stored procedure, view etc). Yes, I am that confused/lazy that I may not know which database I want to look at when I start.

Instead I imagined something where I could press a key to launch the “search” functionality, start typing and see anything that loosely matched what I had typed. Then when I found what I wanted I could click on it in the list of results and go to that item in the object explorer.

Downloads

Download the MSI here if you just want to use the add-in   

Download the code here if you want to modify the add-in or create your own

So Why Does This Qualify as a Black Art?
Writing add-ins for SSMS is something of a black art. It’s not supported, the APIs aren’t documented or designed for extensibility, and it’s really only possible by virtue of the fact that SSMS is built on top of the Visual Studio codebase. The only decent coverage I was able to find on the web was this article on ASP Alliance which doesn’t provide any code.  I’m hoping by writing this post and providing a working add-in I can start a revolution in SQL Server Management Studio add-in writing.

Why You Should Write a Cool Add-In
So I can use it. So other developers can use it. Writing stuff against supported, published, documented APIs is….well…quite frankly it’s passé. With intellisense and google and codeproject and the MSDN and blogs even infrastructure people can write code these days. So where’s the fun? It’s here my friends – all the pain of the Visual Studio object model combined with the added bonus of being unsupported and undocumented!

Starting a new Add-In Project
Creating a new SSMS add-in is very much like creating a new Visual Studio 2005 add-in. Expand the “Other Project Types” node on the left-hand side of the “New Project” dialog and select the “Extensibility” node. Then choose a Visual Studio Add-in.

Step through the rest of the Visual Studio Add-in wizard, and examine the output. You should have a project that looks something like the following:

You can delete the .AddIn files – Visual Studio 2005 switched to a nicer, registry-free way of add-in registration using XML files, however SSMS does not seem to use these. Instead for SSMS to use your add-in you’ll need to add some information about it to the windows registry.

Add-In Registration
As a .NET developer you probably hardly ever have to look at the registry. For those of you young enough to have never done any Microsoft development before .NET you’ve probably NEVER had to look at the registry. That’s all about to change, because registering your SSMS add-in is ALL about the registry.
To register your add-in you’ll have to add the following keys and values to the registry:

All of this information should be contained in a registry key with the namespace of your add-in, and the type inside that namespace that implements IDTExtensibility2. If you created a new add-in via the wizard in Visual Studio this will be .Connect.
There are a few different places in the registry this registry key can live. One is Computer\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\Addins\ (or Computer\HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools\Shell\Addins\ for x64 based versions of windows).
Another is the equivalent registry hive for the current user: Computer\HKCU\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\Addins\ - I think this is a better place to register your add-in because it isn’t x86/x64 specific, and can be written to without admin privileges. The downside is that it will only register the add-in for that user. Adding this registration information as part of the add-in deployment is handled by the Registry target in the setup project.
If some aspect of your add-in registration is incorrect or your add-in throws an exception on load you will see the following error message:

Clicking “Yes” here will delete the registration information for the add-in in the registry, so don’t do that unless you like re-typing all that stuff. Once you get the registry stuff right you should probably export the key as a back-up in case you accidentally DO click yes one time, or if you want to develop on a different machine.
SSMS is based on the Visual Studio shell, which still uses COM extensively. For this reason the type in your add-in that implements IDTEExtensivility2 and handles the “plumbing” inside of SSMS needs to be COM-visible, and registered (hence the GUID attribute on the Connect class if you ran the Visual Studio add-in wizard). Visual Studio handles the registration when you build the add-in on your machine, but you’ll need to take care of that when you deploy your add-in to other users to get past the classic “but it works on my machine” problem.
You do this in your installer project by changing the Register property on the assembly from vsdraDoNotRegister to vsdraCOM as shown here

Add-In Debugging
To debug the add-in I set the “Debug” option to “Start external program” and provide a path to the SQL Server Management Studio executable as shown in the following screen shot.

The SSMS Object Model
SQL Server Management Studio must have once been known as SqlWorkbench, based on the assembly and executable names. The foundation for understanding the managed code in SSMS is an assembly called SqlWorkbench.Interfaces.dll (typically located in the following location: C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWorkbench.Interfaces.dll – add an ‘ (x86)’ in after “Program Files” for an x64 machine)
This assembly contains 5 namespaces which in turn contain many classes and interfaces for interacting with SSMS. Since the purpose of this assembly seems to be a place to define common interfaces it is unsurprising that the majority of types in this assembly are interfaces. The namespaces are:

• Microsoft.SqlServer.Management
• Microsoft.SqlServer.Management.QueryExecution
• Microsoft.SqlServer.Management.SqlMgmt
• Microsoft.SqlServer.Management.UI.VSIntegration
• Microsoft.SqlServer.Management.UI.VsIntegration.ObjectExplorer

The concrete classes that implement these interfaces (which you will be dealing with at run-time) seem to be located in separate assemblies of their own. For example the Object Explorer concrete classes are in the ObjectExplorer.dll assembly, which should be in the same directory as theSqlWorkbench.Interfaces.dll. The reflector class browser was invaluable (as always) when trying to find my way around the object model. I’d advise you to add every single managed dll in the same directory as SqlWorkbench.Interfaces.dll into reflector and use the search functionality (search is the new UI) in that tool to find your way around.

SSMS uses another API called Sql Management Objects (or SMO for short) for lots of things. Unlike the SSMS APIs SMO is a fully documented, supported piece of code developed by the SQL team for administering SQL Server. SMO is the logical successor to DMO, a COM API for administering SQL Server 2000.  SMO is fully managed and one of the best-designed APIs I’ve come across in recent times. If you want to script out SQL objects, enumerate through tables/views/columns or programmatically manipulate your SQL database this is the way to do it. In developing add-ins I would try to leverage SMO as much as possible since it integrates well with SSMS, and is much better documented and supported.

Since SSMS is built on Visual Studio knowledge of the Visual Studio object model is also quite helpful. Visual Studio makes extensive use of the “service provider“ design pattern, and the IServiceProvider interface. Many, many objects in the VS object model (and SSMS also) implement this interface, or inherit from other objects that implement this. This API is means you should always be able to “get“ the type you need, and presumably makes the object model smaller and less confusing. Unfortunately I find it hard to use, since there feels like less guarantees about if GetService will return you anything, and under what circumstances this might happen. Also the very high granularity of objects in the VS object model usually means it is hard to know which type to ask for, as there are often several likely-sounding candidates.

Adding Fuzzy Search
My original vision for searching for tables and stored procedures in the schema using T-SQL always involved a “%LIKE%” type search, and I wanted my add-in to continue in this tradition as much as possible. Fortunately I was able to find a commercial component called highlight express developed by a small ISV ShuffleText. They were keen for feedback, and the code changes required to use their stuff were so modest I decided to give it a go. I’ve been really happy – their stuff is all pure .NET code and runs quite fast. Now I keep thinking of other places a fuzzy text search would be useful – my messenger buddy list, Intellisense, active directory lookups, there are lots of places for non-exact-match searching could be used.

Add-Ins and Management Studio Express
Microsoft offer an express version of SSMS for administering SQL Server Express instances (or other SQL Server instances if necessary). Unfortuately I was unable to get my add-in to load and run inside the Express version. The SSMS tools pack (another free but not open-source) SSMS add-in say that they support SQL Server Management Studio Express. Other posts such as this one make it seem even less supported. SqlAssist (a pretty cool commercial product which to my knowledge is written as an add-in in the same manner I have described) states that the express version of SSMS does not allow add-ins.  

TODO Tasks and Excuses for my Lame Add-in Code
Q: Does it work in SQL Server 2008 (Katmai)?
A: The add-in doesn’t work in the latest (NOV 2007 CTP) of SQL Server 2008 (Katmai). It loads and doesn’t crash, but doesn’t work either. Naturally I’d prefer if it did work.

Q: Why do you have a static “Current” property on the SearcherController? Don’t you know Singletons are evil?
A: I wanted to pass an instance of a SearcherController to the add-in UI ala dependency injection. I even created an interface IObjectSearcherUI which exists solely to allow the SearcherController to be injected. The CreateAddinWindow method of the SearcherController actually creates the VS toolwindow that hosts the add-in UI, however I was unable to find a way to get hold of an instance of the SearcherToolWindow in order to inject the dependency. I’ve left the interface in there in the hopes that I (or one of my clever readers) can show me how to get hold of the window instance, and get rid of the singleton forever!

Q: Why is the UI part factored out from the rest of the add-in? You’ve hard-coded which control you’re going to instanciate so what’s the point?
A: I thought about writing two UIs – one in WPF and one in WinForms. If WPF was present I was going to load that, and otherwise fall back to the legacy winforms, however I didn’t get around to implementing both.

Q: You only search on databases, tables, views and stored procedures – could you search on columns or user defined functions too?
A: Certainly. To do that you should enhance the BuildDBObjectDictionary method in the DatabaseObjectSearcher, the searchText_TextChanged event handler in the SearcherToolWindow and probably the ObjectExplorerManager to handle the correct “drill down” to the right level.The code inside the ObjectExplorerManager is probably the ugliest code in the whole solution.

Q: Why the ugly reflection hacks in the ObjectExplorerManager?
A: Those methods weren’t included in the interface that the ObjectExplorer exposed (IExplorerManager) but were necessary for me to “drill down” into the tree-view and select an item. I wish there had been another way.

Q: I tried to connect to a remote SQL Instance with a bazillion databases over a dial-up connection and your add-in didn’t seem to do anything. What’s going on?
A: When the add-in loads it builds a dictionary of all the things you might want to search on. If lots of data has to be retrieved from remote servers over slow networks then that is going to be slow. We lock the dictionary while we’re building it (to prevent people querying it while it is still being built), and also when we’re querying it (since WinForms applications are STA threaded there shouldn’t ever be more than 1 UI thread accessing the dictionary at a time anyway). In order to prevent the add-in locking up SSMS if the dictionary is still being built we wait for 1 second to see if we can acquire a lock on the dictionary. If we can’t we just return nothing from the search results. You’re welcome to look at the internals of the DatabaseObjectSearcher and the BuildObjectDictionary method to try and make this faster if you wish.

Please post any suggestions, bugs, anything I’ve overlooked, registration hacks, other tutorials I didn’t find etc. as comments here 

Comments

The software automata known as “Jeff Atwood” is feared dead/stuck in an infinite loop/gaining a social life after not posting to the popular weblog codinghorror.com for almost 5 days. Our thoughts go out to the development team behind the “Atwood” project and hope they iron out whatever problems their facing soon. There had been criticism of the dev team recently, after some apparent microsoft bias in the latest generations of  the“Jeff” evolutionary neural network caused many Linux and Mac fanboys to enraged at the falseness of the “Atwood”’s claims. The dev team have responded by saying they will try to change the weighting of the network to focus the topic selection process on less controversial topics such as the middle-east, comparative religion, evolution vs. “intelligent” design and which color looks the best. One project insider who did not wish to be named said the AI network had been increasingly distant and withdrawn, shunning its regular content generation tasks. “it didn’t even want to play tower defence!” the source said. This comes a few months after “Atwood” commandeered one evening of CPU cycles on one of its nodes to render this ray-traced image of its actualized self, which it then posted on the codinghorror site. 

Comments

I LOVE everything about the shop4beatz site, from the business model right down to the not-anti-aliased word art (and believe me, I know word art). Kudos to Office Live for allowing someone with clearly not much in the technical skillz realm (I’ll leave the call re: musical sk1llz up to you, dear reader) to throw this together. Found via my only source for “web 2.0 news” uncov. Sadly if he sells a single track he will have made more money than my mind mapper.

Comments

All these are product name acronyms/names for currently shipping microsoft products. The first three differ by 1 letter, the third and fourth also differ by 1 letter. Anyone else think that could potentially be confusing? »

Yesterday I updated my CAPTCHA code, however according to the websecurity.com.ua guys my “captcha still vulnerable” and I need a more reliable one. I say no I don’t, because the previous, less secure one had been quite effective up until then at preventing comment spam. Also the “proof” that is offered by the websecurity.com.ua guys is 4 test comments left in this post here. I’m somewhat suspicious of the level of automation they’re achieving because of the times that these comments were posted - they’re all between 10 and 20 seconds apart. You wouldn’t even need to be a daskeyboard wielding touch typing ninja to achieve that frequency of posting, unless my server is really, really, really slow. I will totally concede that my previous CAPTCHA implementation was “breakable” (or maybe even broken by design) - for all I know this one could also be, but I think it’s put-up-or-shut-up time for the websecurity.com.ua guys. All I’ve seen from them so far is a link to a form on my site with some pre-populated values (it looks like from the state of the form that it has just attempted an HTTP POST which has failed….funnily enough because of an invalid CAPTCHA), something I could cook up in about 30 seconds with WatiN/R, greasemonkey or whatever. I’d like to see from them one of the following:

1. a textual description of how either the CAPTCHA can be bypassed altogether, or how the CAPTCHA value can be programmatically determined from the page/cookies/http traffic/phase of the moon/whatever - like “we take this value from the cookie your site sets, do an MD5 hash of it, salt the hash and then smoke it….”
2. a script that will post comments to my site with no human intervention
3. 50+ comments on a single page inside of 10 seconds, or some number that would be infeasible for a human to do, originating from a single IP address.

Unreasonable?

Comments

LearnWPF.com my WPF community site was recently featured in the “month of bugs in captures” on websecurity.com.ua. Essentially they’re picking existing CAPTCHA implementations and picking holes in them. They wouldn’t have had to pick too hard or far - LearnWPF’s CAPTCHA implementation is only marginally more difficult to break that Jeff Atwood or Leon Bambrick, who both use hard-coded words. For LearnWPF the CAPTCHA word is embedded in the HTML, and passed to the handler that creates the image via the URI (or at least it USED to work that way). This is an intentionally naive implementation - CAPTCHAs don’t have to be convoluted to be effective, and until the websecurity.com.au folks were kind enough to post a link about how bad it was on their site it had done a pretty good job keeping out spam. The CAPTCHA code was build into my friend Darren Neimke’s Single User Blog (which LearnWPF runs on) and I’d never bothered to change it….until tonight! I hope I’m not misrepresenting Darren here, but I see him as a great minimalist - he writes simple code that mostly works (which is why his CAPTCHA is so basic). Thus it was trivial to change the CAPTCHA to work in a different way that would NOT expose the “are you actually human” word or not. For good measure I changed the background colours of the generated image to fit in more with my site theme, and also randomized the font, for a total of about 15 minutes of dev effort (plus or minus a few hours of effort to deploy the updated app to the interwebs). There is one pretty large “environmental assumption” baked in to the implementation - that the browser accepts cookies (a free hint I’m giving out to those 133t security guys on how this crazy code might be implemented). I look forward to seeing how the websecurity folk break* it now (or alternatively I’ll be cursing my own hubris in the morning when I hear about how the site is totally b0rk3d from the update).

* most of the websecurity links to scripts that “break” other captchas don’t seem to work, but I try hard not to be too critical of the work of others.

Comments

I’m a big fan of Mozilla FireFox, and use it every day. I first heard about Mozilla Prism (formerly WebRunner) via Scott Barnes, and was immediately struck by how similar it sounded to a feature in IE since circa 1996 - HTML Applications or HTA’s. Clearly I’m not the only one who’s seen the simmilarity. HTA’s give you full access to local resources like disk, allow you to customize the icon and disable the browser chrome. One of the touted features of Prism is that it runs as a separate process, so if your browser crashes it won’t take your applications with it. »

Designing frameworks is hard (by all accounts). Once you ship something it’s like pouring cement - it becomes really, really hard to change if you later decide something isn’t the way it should have been. The only non-breaking way to expand is out, adding more classes or another abstraction layer over the thing you just built. Once approach favoured by Microsoft is to release beta versions of the framework, allowing potential consumers of an API the chance to use it before the cement sets. On the surface this seems like a great way for API designers and framework teams to get feedback, except when it doesn’t work. Let’s say your framework is designed to help people doing “industrial” development - building the highrises, skyscrapers and superdomes of the software development world. The very beta-ish nature of your framework is going to make it practically impossible for them to test it for you. The best you could probably hope for is some people in the field taking a look at your stuff and saying “yeah, that looks kind of like the sort of thing we could use (or which we had to already build for ourselves)”. Now you can understand the issues facing the Acropolis team at Microsoft. 

People doing non-trivial projects (as opposed to single-person side projects and proof-of-concepts) are less likely to use your framework because it’s beta. They know it is likely to be unstable, there isn’t a wealth of knowledge on how to work around the issues, and they know the API they’re building on could change on them. Paradoxically these are exactly the type of people that will come up against the real-world issues with your API and could give the great feedback. Sadly this also happened with WPF - Longhorn/Vista and WinFx/.NET3.0 seemed to be incubating for so long there was a sense that they wouldn’t be released “for ages” and there was still lots of time to fix any issues. People held back using WPF in anger until around beta 2 and by then it was too late to make any changes. WPF is a great framework, but some parts - most notably the validation - suffered because problems with the API were spotted to late in the development cycle. I’m as guilty of this as the next person who downloads something new and shiny from Microsoft - plays around with it, comes across a problem (be it a design bug or implementation bug) and then shrugs and says optimistically “well, it will hopefully be fixed in the next version“ (either that or I screen-cast the bug and tear the team a new a$$hole).

A friend of mine is suffering similar problems on a smaller scale - he has developed a very cool library which he intends to sell as a component. The problem is that people are put off by the “beta” status of the library and thus aren’t using it, meaning that he can’t get much feedback and it will take him longer to eventually get out of beta. What are the answers? Well, having an internal customer that is either forced or madly gung-ho enough to bet the farm on your library, regardless of the risk must be a godsend for API designers. Expression Blend was likely that “customer” for the WPF team (as well as the Max project to a lesser extent). ASP.NET and the BCL team were/are for the CLR team. Sadly not everyone has this luxury, but clearly there is a lot to be gained from “eating your own dog food” API-wise.

Comments

Patrick from Nova-Mind emailed me recently asking if I could put the word out: They’re looking for a C#/WPF developer, team leader and Mac developer. Nova-Mind are a small ISV situated on the south side of Brisbane, Australia. Their main product is a mind mapping application (so I guess that kind of makes them my competition, although when you look at their feature list and mine you’ll see it’s not much of a contest). They have their roots in the Mac world, which means if you go to work for them you’ll stand a good chance of getting a quad-core mac pro and a 30” cinema display as your workstation, based on what I saw when I went out to their offices to visit a while back. They’re also a nice bunch of people, so if you’re sick of cranking out yet another ASP.NET intranet application and have got the skills don’t be shy. 

(unfortunately I can’t find a way to easily link to the jobs on seek - here are the the job IDs. enter “C# WPF“ as your keywords and choose Brisbane as your location and you’ll find the WPF one) 

http://it.seek.com.au  JobID 10767018

http://it.seek.com.au JobID 10767587

http://it.seek.com.au JobID 10766635

Comments