Edit, share, execute and debug code within browser - make a fiddle

Many times we want to execute some ideas with JavaScript/Jquery and CSS to see whether we are on the right track?  We want to share HTML, CSS and JavaScript/Jquery code with others and allow them to run it, specially on the forums. If so, make a fiddle. It not only solves problem of sharing but also #9 mentioned in Mike’s post here.

JSFiddle, an online editor for web snippets.

Rather than posting entirety of all the files into forum post, just do the following :

1. Go to JSFiddle (better to bookmark it if you can not remember it)
2. Add your code to appropriate section : HTML, CSS, JavaScript
3. Choose JS Framework if you’re using any.
4. Press run and try to reproduce the problem you’re facing.
5. Save to get a public URL.
6. Share the URL on respective forums.

As Mike said in his post, it should have *relevant code* (as little code as possible, which reproduce the issue). Most of the time doing this, you’ll solve the problem yourself. ;) And If not, it will help a lot to people who’re going to help you. :)

It’s absolutely not necessary to use JSFiddle, you can use JS Bin, CSSDesk etc whatever you’re comfortable with.

For SQL related issue, you can use SQLFiddle (Courtesy : Phil_Factor’s tweet), a tool for database developers to test out their queries.If you do not know SQL or basic database concepts, this site is not going to be very useful to you. In case you’re wondering in what cases it can help you out and how to use it, then you should read this. Check out the sample fiddle here.

Thanks to developers of JSFiddle and SQLFiddle, it’s definitely a solution of sharing a code on public forums and able to run it.

Tip: Schema qualify database objects

When working in a team, it may happen that few of the team mates follow best practices and others don’t give damn about it. They just completes the tasks on the name of ‘getting things done’. It may create nightmares if all the team members don’t follow the best practices. Either do it or don’t.

Let’s see what can happen with below example.

In one of the project, an SP was created with prefix .dbo and it was working fine until changes came. Now one of the team member made changes and whole team had the feeling “all is well” until that SP was not returning the expected output. So you start debugging your app and consider the following could be the issue.

• Being a big Ajax app, you’re getting some Ajax error
• You’re not passing appropriate parameter and you check it through your code and SQL profiler whether parameters are passed in appropriate manner and with correct values.
• Check your respective tables for the values.
• You’re executing SP from SSMS it self to see whether it is working by passing the required parameter values.

You delete the SP and see whether it is returning any value and yes, it is. Later on you found out that SP has no prefix and it is executed to default schema name other than .dbo. So there’re two SPs with different schema name. Great!

Let’s say you’re qualifying schema name for your SP but nothing for query inside that SP, it will default to the schema name of the stored procedure. What books online says about it :

Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not schema-qualified default to the schema of the stored procedure. If a user who creates a stored procedure does not qualify the name of the tables or views referenced in SELECT, INSERT, UPDATE, or DELETE statements within the stored procedure, access to those tables through the stored procedure is restricted by default to the creator of the procedure.

Make sure you always qualify your database objects with specific schema name or you’re life is doomed.

Specify size for character data type

Recently I was referring books online to confirm one strange behavior in SQL Server. It was like in one of the legacy application, string was passed to stored procedure as parameter and it was storing only 30 characters. To my surprise when I saw the code, everything seem to be ok. Later on found out the problem and fault was while converting parameter to varchar there was no size defined.
As per books online,

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Check out the below script which will return only first character of parameter being passed instead of parameter value :

CREATE PROCEDURE GetEmployeeName
    @Name varchar
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Name
END
GO

Now if you define the parameter length, but using cast or convert function, it will return first 30 characters.

ALTER PROCEDURE GetEmployeeName
    @Name varchar(40)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Convert(varchar,@Name) as Name
END
GO
--The longest one-word name of Hawaiian boy
exec GetEmployeeName Kananinoheaokuuhomeopuukaimanaalohilo

Make sure length is defined for all stored procedure parameters and variables to avoid unexpected behavior.
Source : Books Online

How to display message box in Asp.net?

Every new beginner from windows form development background uses MsgBox.Show method but unfortunately it executes server side and will display the message box on server. Then they ask it on different forums seeking help, how to display message box in Asp.net then? (one of the most frequent and common question on asp.net forum)
There are various ways to do it.

• Use JavaScript alert to display messagebox, confirm and prompt
• Download alert control from codeplex and use it.
• Use Modal Popup or Confirm Button from Ajax Toolkit
• Use Jquery UI Dialog

You can use any Jquery based dialog box. I’ve been using Jalert for quite a long time. It is easy to use and you can quickly customize as per the need. One of the best part of Jalert is that it allows you to perform certain activity by using Callback function. Before using Jalert, make sure you’ve referenced Jquery in your project.

Note: Jalert is no longer actively maintained and they recommend to use Jquery UI Dialog.

Now, how do you display it from server side?

You can use following code in your class file under App_Code folder or You can include it in Base Class, being one of the useful feature as suggested by Scott Mitchell here

public enum EnmMsgType
{
      ALERT,
      PROMPT,
      SUCCESS,
      ERROR,
      WARNING
}

/// <summary>
/// Displays Message box 
/// </summary>
/// <param name="strMsg">Message to be displayed</param>
/// <param name="strTitle">Title of the message</param>
/// <param name="strMsgType">Type of the message to be displayed</param>
/// <param name="strCallBack">Function to be executed after message has been displayed</param>
public static void MsgBoxAlert(string strMsg, string strTitle, EnmMsgType strMsgType, string strCallBack)
{
    Page mypage = default(Page);
    string strScript = string.Empty;
   
    try
    {
        string cleanMessage = strMsg.Replace("'", "\\'");

        strScript = GetMessageBoxScript(strTitle, strCallBack, strMsgType, cleanMessage);

        //Gets currently executing page
        mypage = HttpContext.Current.CurrentHandler as Page;
        // Checks if the handler is a Page and that 
        //if the startup script is already registered.
        if (mypage != null && !mypage.ClientScript.IsStartupScriptRegistered("alert"))
        {
            mypage.ClientScript.RegisterStartupScript(mypage.GetType(), "alert", strScript);
        }
    }
    catch (Exception ex)
    {
      //Your exception handling code here
    }
}

You can name function whatever you want. As shown in code strCallBack parameter accepts name of the function which will be called in case you want to perform certain action after Messagebox or Confirmation dialog has been displayed to user and user presses Ok button. If you don’t want to call any function, just pass an empty string to function in above code.

private static string GetMessageBoxScript(string strTitle, string strCallBack, EnmMsgType MsgType, string cleanMessage)
{
    string strClass =  GetMessageboxClass(MsgType);
    StringBuilder sbMessageScript = new StringBuilder();
    sbMessageScript.Append("<script type='text/javascript'>");
    sbMessageScript.Append("$(document).ready(function(){");
    sbMessageScript.Append("jAlert('");
    sbMessageScript.Append(cleanMessage);
    sbMessageScript.Append("','");
    sbMessageScript.Append(strClass);
    sbMessageScript.Append("','");
    sbMessageScript.Append(strTitle);
    sbMessageScript.Append("'");
    if (strCallBack != string.Empty)
    {
        sbMessageScript.Append(",");
        sbMessageScript.Append(strCallBack);
    }
    sbMessageScript.Append(");});");
    sbMessageScript.Append("</script>");

    return sbMessageScript.ToString();
}

Below function will return appropriate class based on Message Type being passed. You have to have those classes added in your CSS file. As per the class respective image will be displayed in message box, just like windows message box.

/// <summary>
/// Gets the messagebox class.
/// </summary>
/// <param name="strMsgType">Type of the Message</param>
/// <returns></returns>
private static string GetMessageboxClass(EnmMsgType strMsgType)
{
    string strClass = String.Empty;

    if (strMsgType == EnmMsgType.ALERT)
    {
        strClass = "alert";
    }
    else if (strMsgType == EnmMsgType.SUCCESS)
    {
        strClass = "success";
    }
    else if (strMsgType == EnmMsgType.ERROR)
    {
        strClass = "error";
    }
    else if (strMsgType == EnmMsgType.WARNING)
    {
        strClass = "warning";
    }
    return strClass;
}

Check out the below example :



I hope it will be helpful to every beginner from Windows Form background. If you have any query/suggestion, please comment. :)

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. :)

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!

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 
ORDER BY T.Title 

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!! :)

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!

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!

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.