SQL Assistant Part 1

I just recently found out about another SQL intellisense product called SQL Asisstant by SoftTree Technologies. Well calling it a SQL intellisense product is a bit of an understatement as it also offers SQL reformatting, spell checking, and code structure tree.

There really is so much ground to cover that I can't possibly do it in one post or I'd be spending all day on it (I do have to actually work, you know :)

This post I'll focus on the basic intellisense engine, which is probably considered the core functionallity. I used it with both Query Analyzer and SQL Server Management Studio, as I'm a SQL Server developer. I couldn't find any difference in functionality between the two. I tested against a SQL server on my local network.

The first thing I noticed is that it's damned fast. It didn't delay the load time of the editor applications at all. And when I expected it to pop up, it did immediately. It also doesn't pop up in unexpected places. This is crucial, as an overly aggressive intellisense hurts productivity and is really anoying. I think they found the right balance.

The popup window itself is small and elegant. It displays as a kind of tree-grid, where you can expand a parent object to see it's children. I've found that it works as a great quick schema browser.
Here's an example. I invoked the SQL Assistant via Ctrl+Space, and typed in a partial name of a table, that allowed me to find and browse it's schema very quickly, especially when comparing how long it takes to find something in the object browser.


It also has a great JOIN assistant, which detects FKs and automatically brings these to the top of the list. Furthermore if you choose the actual column (by expanding the hierarchy) it will also fill in the ON condition for you.



However it does have a flaw, once you reach the ON condition this happens:

Oops! What if I don't want to JOIN on that? It's perfectly valid to have alternative JOIN conditions. (Note: there is a workaround for this which I'll cover at a later time)

Other supported items are local variables. Temp tables aren't supported, and table variables are semi supported, in that you can see them in candidate lists like scalar variables, but you can't get the column information.

Well that's it for now. Like I said above this is just the 1st of a multi part series.

Media Player Tray Control for Vista

As someone who programs for a living and works from home it goes without saying that one of my primary applications is none other than Windows Media Player. When listening to music I rarely need to actually bring up the application window. Coupled with the fact that only one instance of the application is ever used it makes a perfect candidate for an alternative to the Windows Taskbar.

The most popular way to do this is to use Media Player Mini Mode, which adds a new toolbar to the taskbar. This provides some advantages over minimizing to the taskbar:

• The Media Player Toolbar is in a fixed location. You always know where it is if you ever need to pause or skip to the next track. If you have a lot of windows open you don't have to hunt for it.
• Every time a track changes a popup window displays the track info like Song Title, Album, and Artist.
• The player controls are always displayed, you don't have to switch to the application to control it.

However I personally don't like the mini mode, here's why:

• It takes up too much screen real estate. Under most people's Windows setup it takes up the same amount of space on the taskbar as traditional taskbar application. However I run my taskbar at 2U, which means that twice the real estate is used, as the media player toolbar is separated from the taskbar applications.
  
• I actually find the song information popup window annoying. Especially because it activates when your mouse wanders over any part of the toolbar. And the popup lingers for a full seven seconds, which seems like an eternity when you want to get to whatever is behind the window. What makes it worse is that it is located directly underneath where I have the Solution Explorer/Property Window Tab Group docked in Visual Studio. My mouse is always wandering that direction, and if I happen to wander a few pixels down too far I get this for seven seconds.
  

What I prefer to use instead is the Media Player Tray Control. It takes up a mere 17x17 area of my tray and allows me to do my most common media tasks with ease as well. Clicking the tray icon itself pauses/unpauses. A context menu lets you skip or go back a track and gives you rudimentry volume control.



Not to say that the tray control doesn't have its share of flaws as well:

• It seems to have a hard time keeping track of the Media Player application, it's supposed to hide it but various things will cuase media player to come back which confuses the tray app.
• If Explorer crashes for any reason you can't get the tray icon back without first killing the mpxptray.exe process.
• Having the volume controls as menu items? Come on...
• Sometimes that Information Popup window is quite handy when you really want it With the tray control you have to right click the tray app, unhide the media player, find the app in the taskbar, then activate it.

However as I said above I still prefer it over the Mini Mode. So when I tried to install the Windows Media Player Bonus Pack for XP (it comes bundled with this, no standalone install). Windows is kind enough to tell me that it won't allow it to install because of compatibility issues.


I clicked the "Check for solutions online" button, but it was unable to find any solutions (big surprise).

However I was pretty confident that there was only one or two applications that were actually incompatible with Vista. Since I noticed that the executable I downloaded was a self extracting archive, I figured I'd poke around the files it extracted. It even gave me the path where it dumped everything.

Low and behold I found mpxptray.exe in the temp directory. I copied it to a new folder in the Program Files and ran it. Apparently it has no file dependencies because it ran just fine. Right click the tray control, open options, check "Run Tray Control at Startup" and your install is done.

Friend of Red Gate

After seeing my SqlPromptSnippetEditor application Bart Reed, the project manager for SQL Prompt nominated me for the Friends of Red Gate program, which I gladly accepted. For those of you unfamiliar with the program (like I was), it is a lot like a Red Gate version of the Microsoft MVP Program. In fact if you are a Microsoft MVP there's a good chance you are eligible for the Friends of Red Gate program, especially if it is of a SQL or .NET flavor.

I'm not quite yet sure how I'll use this new award, if anything my opinions may carry more weight in future versions of SQL Prompt, which I intend to become even more active in.

SqlPromptSnippetEditor

After being fed up with the ability to use copy and paste in the Snippet editor in SQL Prompt I decided to create my own editor.

While I was at it I got a little carried away and created a full blown Snippet Management application. The funny thing is I know that SQL Prompt 3.5 is slated to be Release Candidated (heh) this month and the full release will come shortly after. At the very least the aforementioned bug will be fixed and probably some of the other features I put in will be included as well since people kept asking for them in the support forums.

Get it here

IMPORTANT! Back up your Snippets file before using, as I make no warranties that it won't completely mangle your file.

Some other things to note: (As I don't have any help written)

• I did my best to make the app very keyboard friendly, get to learn the different shortcuts.
• Most of the list views have the ability to perform multi selected operations
• On the import screen if you see a snippet colored DarkGoldenRod it means that you already have the snippet defined and it is exactly the same as the import snippet. If you see a snippet colored Red it means you already have the snippet defined and it is different from the import snippet (conflicted). Double click the colored snippet to view a comparison.

AddWithValue is evil!

I'm of course talking about SqlParameterCollection.AddWithValue. As with any blanket statement about some kind of technology being evil of course it isn't, but it sure did throw me for a loop today.

First let me give you a little background, for the last couple of years I've had the privilege of using a technology called SPEW, which reads the definition of a Stored Procedure from the Database and Generates a wrapper class which provides a strongly typed interface to a stored procedure. It means that I never had to instanciate my own SqlCommand objects and add Parameters and have to worry about if I spelled it correctly or look up types.

However that was proprietary technology to the company I no longer work for :( so until I can come up with an equivalent, it's manual creation for me.

So when my options for creating a SqlParamater are:

cmd.Parameters.Add("@SomeID", SqlDbType.Int, 4).Value = _someID;
or
cmd.Paramaters.AddWithValue("@SomeID", _someID);

of course I chose the latter, as I'm adding 20 or so parameters at a time on many stored procedures. It seemed to work just fine at the time.

Now AddWithValue uses type inference since they aren't explicitly set. string maps to NVARCHAR, decimal to DECIMAL, bool to BIT, etc.

So I developed an ASP.NET application locally using the built in development web server. When I deployed it to the target IIS server and tested it it gave me a SQL error to the effect of trying to use an NVARCHAR value as type MONEY, try using CONVERT. Eh? I then profiled it, the SQL generated by ADO.NET was perfectly valid and ran without errors. After messing around with the server configuration and re-deploying the site twice without fixing the problem I went back to the code and on a whim converted it to explicitly state the Sql Type. What do you know it worked on the IIS server.

It seems really weird that this would happen, as one would think the Web Hosting environment would have nothing to do with the way ADO.NET operates a SqlCommand aside from connection pooling.

So lesson learned, don't be lazy and avoid AddWithValue.