Tuesday, December 27, 2011

dotPeek, an alternative to .NET Reflector

Those who have been using .NET Reflector, must be knowing that it’s not free anymore. Those who don’t know about Reflector, don’t have to bother much now. You could download dotPeek, an awesome decompiler, which is in the Early Access Program stage. It’s been developed by JetBrains, the maker of Resharper and more developer productivity tools. 

You can check out what its latest build offer here and can download it from here.

In this year, there are more posts related to add-ins and tools, but will have more programming problems and their solutions related posts in coming new year. :)

Wednesday, December 14, 2011

SQL Server Add-In : SSMS Tools Pack

SSMS Tools Pack, I downloaded this free Add-in 3 weeks ago, and I am glad that I did that. Below are the few features that I’ve been using so far :

  • SQL Snippets
  • Format SQL
  • New Query Template
  • CRUD Generation
  • Regions and Debug sections

You can check out more useful features here and you can download it here.

This add-in makes life easier with its number of features that are missing from SSMS.

Hope you like it and have fun!

Sunday, July 31, 2011

Outer joins and search predicates – II

In my last post we saw that if we place search predicate in WHERE clause instead of placing it along with ON clause, we may get expected output. Now let's understand what happens behind the scene.
Note : we'll use pubs database for the example. 

Suppose we want to list down all the book titles, then we might write following query.
SELECT T.title_id as ID, T.Title 
FROM titles T 

Now we are asked to list down sales quantity for any given store for all the titles. If the given title is not sold, the quantity should be zero. For that we might write following query.

SELECT T.title_id, T.title, IsNull(S.qty,0) Qty 
FROM titles T 
LEFT JOIN sales S on T.title_id = S.title_id 
WHERE S.stor_id = '6380' 
ORDER BY T.title 

If you run above query, you'll get only two rows which were sold instead of remaining titles with zero quantity. Now if you see the execution plan, you could see that LEFT JOIN is converted to INNER JOIN by the execution engine. Check out below snap.

Now if you need to get all the rows with quantity zero if title is not sold, just place your search predicate along with ON clause in LEFT JOIN and you'll get the expected output. So our expected query should be like below :

SELECT T.title_id, T.title, IsNull(S.qty,0) Qty 
FROM titles T 
LEFT JOIN sales S on T.title_id = S.title_id 
and S.stor_id = '6380' 
ORDER BY T.title 

Always be careful when using search conditions with OUTER JOIN and construct your queries

as per the output expected. Enjoy!! :)

Sunday, July 10, 2011

Outer joins and search predicates

We often use outer joins to construct queries and get expected output, but what if you get unexpected out and the query seem ok. Few days back I experienced the same thing and found the problem with the placement of predicate (search condition).

Let’s take an example :

We’ll use Orders and Customers table from Northwind database.

Let’s check out INNER JOIN and LEFT JOIN result without any condition first.


As you can see in the snap above both are returning same no of rows ( I’ve taken top(5) for the snap, as both the queries have more than 100 rows)

Now let’s check out INNER JOIN with predicate (search condition) in WHERE and ON clause.


Both the query returned the same result.

Now let’s check out LEFT JOIN with WHERE clause.


It has also returned the same no of rows, but if we use predicate (search condition) along with ON clause rather than WHERE clause, it will return more no of rows (unexpected output).


Explanation could be found out on SQL Server Books Online which says,

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

An outer join query can produce completely different results depending on how you write it, and where predicates are placed in that query. Take Care!

Thursday, June 2, 2011

Microsoft Community Contributor Award 2011

I am very happy to announce that I am one of many to receive this award. I consider this an honour to be recognized by Microsoft for my contribution to Asp.net Forums and look forward to continuing the same. Thanks Microsoft!

Thursday, May 5, 2011

VS 2010 and ASPX Edit Helper

Some of my friend reported that they can’t use ASPX Edit Helper with VS 2010 and from the original post it is evident that it works great with VS 2005 and VS 2008.

But one of the guy added support for VS 2010 using original souce code. You can download the latest source and binaries (with latest bug fixes) here and if you’d like to change something, of course you can as source code is available.

Sunday, April 10, 2011

Useful Visual Studio Add-Ins

Recently one of the guy asked on Asp.net forum that is there any shortcut key to insert comments above method or function. There’s no exact shortcut but yes, you can insert comment above your method and function by typing 3 slash (///) in c# and 3 single quote in VB and still you’ve to type summary for your method or function to define what it does.

But there exists add-in which help you to generate comments with summary on your defined shortcut. Just download GhostDoc, install it and define your short cut. Now whenever you’re required to generate comments, fire your defined shortcut once you’re done with that method or function and it will insert comments with summary.

Many of us generally type the ASPX markup ourselves instead of using VS designer. To make it easy here’s the add-in ASPX Edit Helper Though it was designed for VS 2005 but it works with VS 2008 without any problem. You just need to place binaries in AddIns folder. If folder is not there then create it. Next step would be to select it from

Tools –> Add-In Manager –> ASPX Edit Helper

and you’re ready to go. There are more useful Add-Ins which can make your life more easy and can save you some time. Please check out below link :

Visual Studio Add-Ins Every Developer Should Download Now