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.

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.