Monday, February 28, 2011

SQL 2005 - Examples of Various Select Queries


1. Introduction


In this Article, we will see how we can query the data from the database using the "Select statement" in SQL. In this article, I will show some basics of it. Before you start working on this, download the Script file attached and run it on the SQL Server to create the database Northwnd. If you already have it, skip the download.

2. Row and column filtering

2.1 Selecting all the columns


Consider the below example:

Example 1:
Select * from Employees

This will select all the columns from the table Employees. Before you execute it, make sure the NorthWnd database is selected as shown in the below Picture.



In the above Query, * represents all the columns and Employees specifies the table name.

2.2 Filtering the Specific Column


Example 2:
Select EmployeeID, FirstName, LastName, City from Employees

The above query does not select all the columns from the table employees because we replaced the * with specific column names. So, the * in the previous query states all the columns. Here, in the above query we ask for four specific columns. Usually, people will not go for all the columns by using the * as it generates network traffic. Keep in mind that it is always good to specify the column you want in place going for all the columns. Execute above query and see the results.

2.3 Concatenating the Columns


Example 3:
Select EmployeeID,
TitleOfCourtesy + ' ' + FirstName + ', ' + LastName As [Full Name] 
from Employees;

In the above example, first, note that the query is wrapped into three lines. SQL Server understands where the query starts and where it ends. The second columns are the concatenated column formed by joining the columns TitleOfCourtesy, FirstName and LastName. To have a valid column name we used "As [Full Name]". The Full Name is given in the Square brackets because it has the Space between FullName. You no need to use Square brackets when the column name does not have space. Also, note that the concatenation is done by the operator plus by joining the table columns with a constant string like space and comma wrapped in single quotes. Execute the above query to see the results. Here, the column Full Name is called the "Column Alias".

Alternate Query for the Column Alias:

Example 4:

Select EmployeeID,
[Full Name] = TitleOfCourtesy + ' ' + FirstName + ', ' + LastName 
from Employees;

2.4 Usage of distinct in SQL2005


Select city from Employees

The above example will select City from the table employees. When you look at the output, the city names Seattle and London is repeated more than once. How do we see all the different city names in the column City? Below is the query for that:

Example 5:

Select distinct city from Employees


The "distinct" keyword in front of the column name tells the SQL server that does not repeat the column value once it is already displayed. The output of the above query will show only the different city names.

2.5 Where clause in SQL2005


So far we had seen filtering the column names from the table. How about filtering only the required row that we need? We should use the "where clause" for this purpose. Where clause will operate based on the Boolean expression formed on the column values. Consider below specified example:

Example 6: 

Select * from Employees where employeeid = 5;

In the above query, we are selecting all the columns from the table employees. But, we are selecting the row which has employeeid = 5. How does SQL look the above SQL statement:

  1. I need to Select all the columns because * is specified in place of column names. 
  2. For each row, I need to compare the value in the employeeid with constant 5. When the expression returns true, select that row.

So, the expression you specify in the where clause or where condition will be evaluated for each row. And, the row will be displayed only when the condition returns true.

Example 7:

Select * from Employees where employeeid > 5;


3. Operators in SQL Server


In the previous section we saw the basics of select statements, how do we filter the columns and how do we filter rows. We finished at the usage of the where clause usage. In this section, we will continue exploring the where clause with some useful operators.


3.1 Usage of Like operator



You can use the Boolean operator = to compare the string also. But, it will try to match the exact strings. How about matching the string with some patterns? The answer is; use the "Like operator". Consider the below simple example:

Example 8

Select * from employees where Title like 'Sales%';

In the above query, we are matching the title column with the string that follows a specific pattern Sales%. The Sales% denotes any string that starts with Sales. Execute the query to see the results. There symbols other than "%" that can be used with Like operator. Those operators are shown below:




Play with the above patterns with the like operator with your own queries. 

3.2 Between Operator


This operator is used to check whether the value falls in between an upper and lower range. Please consider the below example:

Example 9

Select * from employees where employeeid between 4 and 7

Here, we specified that we need all columns from the employee table and their Ids should be in the range of 4 to 7. Also not that you can use this "between operator" for String and Date ranges also. Execute the above query in the NortWnd database to see the results.

3.3 IS NULL operator


The "IS NULL" operator is useful to check the empty columns. Note, here empty stands for the column that never received any values through the SQL statement. If a column does not have any value at all then what do we compare to bring that column? That is where the IS NULL operator comes into the picture.

Example 10

Select * from employees where Region IS NULL

The above query will bring all the records which have no region specified.

3.4 And, OR, NOT in SQL2005


These are all "Boolean Operators" and used in the same way we programmers uses them in the conditional statements. You can combine two or three conditions together in the where clause using these operators.

Example 11: 

Select * from employees where title = 'Sales Representative' AND City = 'London'

In the above query "AND Operator" is used to get the combination of Two conditions. Above SQL will retrieve all the employees who has the job title as Sales Representative as well as he belongs to city London.

Example 12

Select * from employees where title = 'Sales Representative' OR City = 'London'

The Condition is joint through the "OR Operator" which states the employee should be a Sales Rep Or else he should be from the London city.

Example 13

Select * from employees where title = 'Sales Representative' AND NOT City = 'London'

The above query uses both And operator and "NOT operator". As per the Operator precedence, the Not operator is applied first to skip all the records which have City as London. So the above query will return all the Sales Rep employees who do not belong to the City London.

3.5 In Operator in SQL2005


The In operator allows you filter a particular column by comparing it with a given list of values. You can use "IN operator" with sub-Queries also. First, we will look at the Simple query that uses this operator. Then we look how it is used in the sub-queries. Look at the simple example below:

Example 14: Select * from employees where FirstName IN ('Janet', 'Margaret', 'Steven', 'Michael', 'Robert')

The above query will select all employees who have the first name provided in the comma separated list. Now look at the example below:

Example 15: Select * from employees where FirstName IN (Select FirstName from employees where TitleOfCourtesy = 'Ms.')

The above query will first execute the sub-query to get all the first name with title Ms. Then the main query performs the filter based on the Subquery returned, "First Name". Also, note that subquery need not be on the same table. It can be a query for a different table also. The list for the IN operator is formed using the Sub-Query in the above example.

Note: The above query examples are created using the NorthWnd database. If you don’t have the DB download the attached script and execute in your SQL server. 

NorthWnd Database : Download 

[ Dot Net Remoting ] - Singleton Server Activated


1. Introduction


In the previous two articles, we saw the server activated, single call remote objects. Also remember, each call to the server will create a new remote object in the "Single call" technique. In this article, we will see how the Singleton remote objects work. Then we will walk through an example.

The example here does not require a big explanation, as you are already familiar with the remoting by the previous article on the remoting topic.


2. Server activated, Singleton remote object


In this technique also, the object is created by the server and will be kept in the server’s remote object pool. Unlike the single call technique, here only one object is created and maintained in the memory pool. All client applications access the same remote objects. That is why we call this remote object as singleton remote object.

To better understand the usage of Singleton remote object, a simple application is created.

3. The application


As I already told, there is no much explanation is required on the coding side. You just need to say that you want to register the object as singleton remote object. That is all. Everything else is taken care by the server application. Below is the change that you should know:

RemotingConfiguration.RegisterWellKnownServiceType(typeof(SingletonServer.RegVotes), "RegVotes", WellKnownObjectMode.Singleton);

Deploy the Server on one machine. Run the client application from the different machine to see how it works. 

Below is the Screenshot of a server and two clients: 

Fig 1. Example - Singleton Server Activated


In this example, all the clients access the remote object RegVotes. This object is registered as singleton object so the server maintains the single copy of the object. All the clients share that single object. You can select one the voting option and click the Vote now button. The server returns the average rating after client’s vote is registered.

4. Search Tags


Once you open the solution (Solution file you can find inside the SingletonServer folder) use the following tags to search the projects:

//Client
//Server

You will get the entire step, which is exactly equal to the previous applications. And differs only in the place when registering the objects using RegisterWellKnownServiceType.

5. Previous Articles


This article may be confusing for you if you are reading it without reading the previous parts. The links below get you to the previous two parts:

Look at the "remoting" label for other articles on remoting.

Note: The application created in VS2005 IDE. If you have the latest version say yes to the conversion dialog/Wizard.

Source Code : Download

[C#] - Debugging C# Applications - 1


1. Introduction to Debugging


Debugging is the process of finding the logical errors in the program by examining the statements during the execution. The execution of a program pauses when the breakpoint is hit. Here, I will walk you through the debugging the sample application supplied with this article.

The screen shot of the application is given below:



The first textbox will accept a number below 36000. The Get Prime Sum button will calculate sum of all the prime numbers between 1 and number given in the input. The prime numbers are displayed on the multi-line enabled text box. And, the summation of the prime number is displayed in the label control below the multi-line text box.

I am not going to explain the code that runs behind this form. However, you can understand it. What I do is, explain you some debugging techniques that you can use in your program to get away from the logical troubles.

2. Inserting a Break point


As already told, breakpoint temporarily pauses the program to examine some piece of code. Let us first put a breakpoint on the event handler for the Get Prime Sum button. Follow the Steps:

1) Open the attached solution
2) Locate the source code for the Get Prime Sum Handler
3) In the margin left click as shown below:



4) Notice the color of the statement is changed and there is a red ball in the margin area. We call this as BreakPoint. When we run the program and when the execution reaches the breakpoint, the program pauses on the statement.



5) Run the application. Type 16 in the small text box; click the Get Prime Sum button. Notice how the program pauses the execution at break point. You will get a pointing arrow showing the current statement that is under examination. Below Picture shows this:



6) Press F5 and close the running application.


3. Toolbar buttons: Step into, Step Over, Step Out, Continue, and Stop Debugging


Stepping through the statements one by one allows us to examine the state of each variable and effect of the statement execution. We will see how these debugging commands work in this section.
First, have a look at the screenshot below:



Don’t have this toolbar?

Attempt 1:
Right click next to any of the toolbar buttons already displayed. From the displayed menu select debug:



Still, do not have the all the above-specified toolbar buttons? Follow the Attempt 2.


Attempt 2:
1) Click on the "tools|Customize…" menu option
2) From the displayed dialog under the "Command-Tab", select the debug item in the category listbox.
3) Drag&Drop the commands as shown in the red box below to the debugging toolbar or even to any popup menu item



4. Step Over Debug Option


"Step Over" tells go ahead and execute the next immediate statement. Each time you do step over the next statement will get executed. Now run the application again and type 16 in the input text box and I hope you don’t remove the breakpoint still. The breakpoint will hit at button’s click event handler. Now click on the "Step Over Button". The statement txtOutput.text = “” is executed now. Click the step over button until you reach the last statement as shown below:



Place the cursor over txtInput.Text exactly over the ".Text" portion of it. The displayed pop-up shows the current value in the text box txtInput. The below screen shows this:



Like this, you can examine each variable, before executing a statement and after executing a statement. As we asked for Step Over, the execution moves statement by statement. Press F5 and Close the running application.

5. Step Into and Step Out


I once again expect that the breakpoint not yet removed. If  you already removed it, place it back on the same statement and run the sample application with 12 in the input box. Once execution resumes at second statement inside the button click event handler, click the "Step Over Button". Now we are at the “if” conditional statement.

The return value of the function IsNumberValid is part of the condition. In the previous section when we do step over the execution gone to the next statement. It means the function is executed before moving to the next statement.  When the "Step Into" command is used the execution passed inside the function. Now click the Step Into button. The execution now reaches the first statement in the function now as shown below:



Now step over until you reach the conditional statement. OK. The step into will enters inside the function that is part of the current statement. The step out is a reverse of it. Say for example, in our case once you reached the conditional statement you decided it is not worth to explore further inside this function. So you can go for "Step Out" option, which will execute the remaining statements in the function and the execution pauses on the statement, which called this function. Now click the step out function to go back to the statement that called the IsNumberValid function.

6. Breakpoint window


With a breakpoint window, you can easily manage multiple break points. A sample screen shot of the breakpoint window is shown below:



1) When you un-check the item in the window, the breakpoint will be disabled. You can see a disabled breakpoint in the above screen shot’s code area. You can right click the breakpoint bubble in the code area margin and disable it using the context menu as an alternate method.
2) The breakpoints icon shown in the toolbar in the above picture is used to bring the breakpoint window. If you cannot able to see the toolbar icon bring it from the customize option as explained in section 3 of this article.

Play around this window yourself. It is a kind of easy go.

7. Conditional Breakpoint


Once started using the breakpoints for some period of time on your big projects, you will really come to a situation “Hey, I need to break the execution of my loop when the loop is running for 160th time” or “I need to break my execution when a particular variable’s value changed”.  Of course, it is not a big deal as we can go ahead and place some extra piece of code to have a break point. But, this requires a re-compilation. And, in some corporate project, this re-compilation and re-launch consume considerable amount of time. The "Conditional Breakpoints" help you do this without the need for re-compilation as it does not involve any source code change. Let us explore the conditional breakpoints with the downloaded application.

First let us put a conditional breakpoint, which evaluates a Boolean expression and breaks when the expression returns true. Follow the steps below to do that:
1) Locate the function SumIfPrime.
2) Once you located the function put a breakpoint on a statement shown in the below picture



3) Right click the breakpoint and select Condition… from the context menu
4) From the displayed dialog type the condition as shown in the below picture, make sure the "Is true Radio Button" is in checked state. I will talk about "Has Changed Radio Button" later. Click OK.



Now, the breakpoint will be hit, only when the expression is evaluated to true. The expression will be true when the value of the variable i is between 20 to 30. Also, note the placement of the break point that pauses the execution based on the condition supplied. When the execution reaches the breakpoint, the runtime engines evaluates the expression supplied and pauses the execution only when the expression returns true. In our case, the conditional breakpoint stops the execution for all the prime numbers that comes in between 20 and 30. Below is the screenshot that shows how the break point looks:



To test this conditional breakpoint, launch the application. Then, type 137 in the input box. Click the Get Prime Sum button. How many time the breakpoint hit happened? This is a simple example, but you can find more suitable and useful one when you are debugging big solutions for the corporate company you are working for.

The “has changed” radio option tells the runtime that when the variable typed or return value of the expression changes, hit the break point. Need an Example? OK.

1) Right the click the conditional breakpoint
2) In the condition text box type PrimeSum
3) Check the radio button has changed

Now test the application. Whenever the value in the PrimeSum changed, the breakpoint hit happens. Hope you like this first part of debugging. Rest in Next. I mean part 2 of this article. Bye.

Note: The attached download sample is in VS2005. If you have the latest version, say yes to the conversion UI

Source Code : Download

[ASP.Net] - Understanding and Using ImageMap Control


1. Introduction


ImageMapButton is a server-side control, which allows you to display an image take an action when the particular part of the image is clicked. Think about that you are working on a Car sales website for a reputed car manufacturing company. And, your client asks to display a nice picture of their car model on one page and when the user clicks the parts of it (the car), the details about the part should be displayed on the next page. ImageButtonControl will fulfil this need.

In this article, I will show you the usage of the ImageButtonControl with the use Map of India. You can try the car model stuff yourself, as I believe this walk-through article will help you do that.

Let me first tell you, what we are going to do?

In the First Page which is the default page, we will display the Map of India. When you click on the AndhraPradesh state, we will navigate to a separate page that talks about Andhra. Similarly, when we click on the TamilNadu state, we will navigate to a separate page that talks about TamilNadu. Each of these pages has a normal image that highlights the state you clicked. Also, each page has the Go Back link to navigate to the Main Map page.

OK. Let us start our example.

2. Start a website



  1. First, start a File System based website with Visual C# as the Language. [Actually, we are not going to write any code here. But you can extend your learning if you need]
  2. Right click your website in the solution explorer, select Add New Item.
  3. From the Displayed dialog select WebForm and Name the form Andhra.aspx
  4. Add one more Web Form and Name it TamilNadu.aspx
  5. Go to Windows explorer and Browse to the Web Site Folder and create a directory called Images.
  6. Open the Attached solution, copy the content (Images! What else? ) Of the Images folder to your newly created Images folder.

The copied Images are Shown Below:



The Solution Explorer looks like below now [Refresh the Explorer, if Images folder is not displayed]:



(Forget about web.config if it is not there)

3. Image Map Button on Default page

In the default page, we are going to place the ImageMap control. So, when the website is accessed the initial page called default.aspx will display this Imagemap control.  The outline of the India will be loaded into this Imagemap control. Let us see how we can do it.

1) First, open the Default.aspx page and go to the design view.
2) Drag and drop the ImageMap control on the Page.



3) Once it is done, set the Image URL Property. Click on the small button at the end of the property value. From the displayed dialog, select Image Wmap.Jpg as shown in the below picture:



4) Click OK.

Now the ImageMap control displays the Outline of the India Map.

4. Set the Images for Other Pages


Now we will set the Images for other two pages. This time, we will use Normal Image controls. When we click the particular state; that is TamilNadu or Andhra in the ImageMap control on the Default.aspx page, we will move to the corresponding pages.


  1. Open the design view of the Andhra.aspx page.
  2. Drag and Drop the Image control on the design surface
  3. Set the Image URL property to the image WMapAP.JPG as you already did it for the ImageMap control
  4. Add a label control below the Image and set the following Text property: Andhra Pradesh. The language used here is Telugu
  5. Add a Hyperlink control below the Image. Set the Navigate property to Default.aspx. Now, when the link is clicked you will move to default.aspx page.



Open the TamilNadu.aspx page, Place an Image control and set the URL image to WMapTN.JPG. Add a label as set its text property to The State of TamilNadu. The language used is Tamil. Add a Hyperlink control to navigate back to the Default.aspx as you did for the Andhra.aspx


5. Set the HotSpots for ImageMap control


Now it is time to set the HotSpot for the ImageMap control. A HotSpot is a portion of the image, which will produce some action when it is clicked. There are three different ways to define the hotspot for an Image. The hotspot can be defined as Circle or Rectangle or Polygon. In our case even though the polygon is best suited, I am going to define rectangle hot spots for the State of Andhra and Polygon hotspot for the State of TamilNadu.



For the state of Andhra, two rectangles are specified as a hotspot. You can use the paintbrush to do this. Take a copy of the Image and Follow the instructions below:


  1. When you cursor is at the top left of the first rectangle, the X co-ordinate specified LEFT and Y co-ordinate specifies the TOP. Now drag your mouse to get the first rectangle as shown above. When you are at the Bottom right hand side corner, X co-ordinate specifies RIGHT and Y co-ordinate specifies the BOTTOM. Note Down these top, left, bottom, right co-ordinate values from the paintbrush. Do the same for the second rectangle.
  2. Now let us go to the Tamil Nadu state. Place your cursor as shown above and note down each pair of X&Y co-ordinates. These points defines our polygon which more suitable for irregular shapes.

Now you have the set of Points. The first two sets define the Rectangle and the other big pairs of X&Y define the polygon. Let us set the information collected on the paintbrush to the HotSpot property of the ImageMap control.

1) Open the Default.aspx page
2) Click the ImageMap control
3) Set the HotSpot Mode property to PostBack. This will help you to handle the events in the server immediately when a hot spot is clicked. But, in the example, we are not writing any code. You can experiment that.
4) Now click on the button […] next to the HotSpot property.
5) From the Displayed dialog, click on Add down arrow, and select RectangleHotSpot. Do it one more time, as we need to Rectangle hot spot for the State of Andhra.



6) Set the Top, Left, Bottom, Right property for both the Rectangle hot spots. Remember, you already noted down those points for both the rectangle using the paintbrush.
7) Set alternate text property for both the rectangle hotspot to Andhra Pradesh. This text is displayed as a tool tip when the mouse is on the hot spot.
8) Set the Hotspot mode for both the rectangle hot spots as Navigate. And, set Navigate URL property (Again for both rectangle hot spots) to the page Andhra.aspx. Use the dialog, which easily sets the property in the relative path notation.
9) Now add the Polygon Hotspot the same way you added the rectangle hot spots. One Polygon spot is enough.
10) Set Hotspot mode to Navigate, Set Navigate URL to TamilNadu.aspx and set the Alternate text to TamilNadu.
11) Set the Coordinates property for the point you noted down  for the polygon using the paintbrush. Put all the points as X1,Y1,X2,Y2….Xn,Yn

6. Run And Test


1) Press F5 and Run the site
2) Hover the mouse inside the state of Andhra. Once an Icon changed to Hand, stop moving the mouse and You can see the alternate text.
3) Left click when the hand icon is displayed
4) You are moved to Andhra.aspx page
5) Click the Go Back link to move to the Default page
6) Now click inside the TamilNadu, and you will be moved to Tamilnadu.aspx page.

7. How do I use the Attached Sample?


1) Download the attached zip file
2) Extract it to a folder
3) Open visual studio 2005
4) Click the menu item File|Open|Web site
5) Select the Extracted Folder


Note: The Attached solution is created in visual studio 2005

Source Code : Download

Like this site? Tell it to your Firend :)