December 3, 2013

C# - Owner Drawn ComboBox Control

1. Introduction to Owner Drawn Controls

When we place a control on the form, the control’s appearance is usually decided by the control itself. Let us say for example that you placed a List Box control on the form, the appearance of ListBox control is drawn by the dot net frame code written for that control itself. That means, the ListBox class or its parent class can take the responsibility of drawing the control. This is the normal behaviour for almost all the control.

In the same case, let us say that the Form performs the drawing of each ListBox item with a double lined red border around each item. In this case, the Form is called as Parent or Owner of the ListBox and ListBox control is seen as "Owner Drawn Control". In easy words, the Form (Owner of the ListBox) draws the ListBox Items. In this article, we will see how de we create an Owner Drawn ComboBox.

2. About the Example

Below is the Screenshot of the application that we are going to create:

In the above screenshot, the default appearance of the ComboBox is changed to display the color boxes. This ComboBox is partly drawn by the Form. That is, the ComboBox with a Down Arrow is drawn by the control itself. The Form draws the content of the ComboBox when it is dropped down.

The Owner of the ComboBox in the example will perform the Following drawing:

  1. Draws the Color Rectangle
  2. Draws Text String next to the colour rectangle
  3. Draws the Red Border around the selected Item

Once the colour is selected, the text typed in the textbox will appear in the selected colour.

3) About Owner Draw

The control Listbox, Combobox, TabControl and TreeView Control supports Owner Draw. All these controls have the property called "DrawMode", which specifies what kind of owner drawn will be used. The ComboBox as well as ListBox supports three kinds of DrawMode and these modes are:

  1. Normal
  2. OwnerDrawFixed
  3. OwnerDrawVariable

3.1 Normal Mode

This is the default drawing mode set to a ListBox and ComboBox control. In this Mode, windows take the responsibility of drawing the control and each item contained in it.

3.2 OwnerDrawFixed

When this mode is set to the DrawMode property, the owner will draw the control by handling the "DrawItem Event". Each item will have fixed height.

3.3 OwnerDrawVarible

In this mode, the owner will draw as well as measure the individual item. So we can say that each item can have different height. When this Mode is set, the owner of the control should provide "DrawItem" and "MeasureItem" event handlers. In the MeasureItem handler, each item’s height will be measured and in the DrawItem each item will be drawn. The MeasureItem handler will be called only during the control initialization. But, the DrawItem will be called for each item when the drop arrow is clicked.

For this example, we need to set the OwnerDrawFixed to the DrawMode property as shown below:

4. Coding – Arranging the Form

1) In the Form Load handler function, populate the combo box with the strings that represents the colour. Below is the code:

//Snippet 02: Populate the Owner Drawn Combo box
private void frmComboSample_Load(object sender, EventArgs e)

2) Next, write a function that returns the "System.Drawing.Colour" for a given colour name. Below is the function:

//Snippet 03: Get the Colour selected in the Combo box
public Color GetColor(String ColorName)
    Color col = Color.Black;
    if (ColorName == "Red")
        col = Color.Red;
    else if(ColorName == "Blue")
        col = Color.Blue;
    else if(ColorName == "Green")
        col = Color.Green;
    else if(ColorName == "Chocolate")
        col = Color.Chocolate;
    else if(ColorName == "Gold")
        col = Color.Gold;
    else if (ColorName == "Purple")
        col = Color.Purple;
        col = Color.LightSeaGreen;

    return col;

3) In the ComboBox Selection change, the selected colour is retrieved to set the "ForeColor Property" of the textbox. To retrieve the colour the previously written function "GetColor() method" is used. Below is the code for that:

//Snippet 03: Set the Textbox colour based on the Combo Selection
private void OwnerDCombo_SelectedIndexChanged(object sender, EventArgs e)
    if (OwnerDCombo.SelectedIndex == -1)

    String strColor = (String)OwnerDCombo.Items[OwnerDCombo.SelectedIndex];
    TxtNotePad.ForeColor = GetColor(strColor);

5. Coding – DrawItem Handler

1) As already specified, <controlname>_DrawItem will perform the drawing for each item present in the combo box. This handler will be called for each item present in the combo box (When DrawMode is not normal). In this handler, first, we retrieved the Color Object and colour name by making use the index reported by the "DrawItemEventArgs".

private void OwnerDCombo_DrawItem(object sender, DrawItemEventArgs e)

//Snippet 04.1: Get the color currently selected in the Combobox
String strColor = (String) OwnerDCombo.Items[e.Index];
Color Selected_Color = GetColor(strColor);
String color_name = Selected_Color.Name;

2) As the function takes the responsibility of drawing the content of each combo box item, should perform the background drawing. First graphics object called “grp” is retrieved and then "DrawBackGround() method" is called on the DrawItemEventArgs. This will enable current item highlight when you hover the mouse on combo box items. Below is the code:

//Snippet 04.2: Get the Graphics from the Argument
Graphics grp = e.Graphics;

3) The DrawItemEventArgs supplies bounding box of the current combo box that needs to be drawn through its member called Bounds. A one-pixel indent is applied on top, left and bottom and the Width to be drawn as set as 20 pixels. Based on this, a Rectangle “rct” is formed. Through the already retrieved Graphics object, the rectangle is drawn. Below is the code:

//Snippet 04.3: Get the bounding rectangle to draw the item
int Rect_left = e.Bounds.X + 1;
int Rect_top = e.Bounds.Y + 1;
Rectangle rct = new Rectangle(Rect_left, Rect_top, 20, e.Bounds.Height - 2);
grp.DrawRectangle(Pens.Black, rct);

When we run the application at this stage, it looks like as shown below:

4) Once the bounding rectangle is formed, the rectangle is filled with the colour reported by the event argument. To perform the fill operation on the rectangle, brush object is formed from the colour object associated to the current item that needs to be drawn. Below is code for filling the rectangle using GDI+ brush:

//Snipper 04.4: Create a Solid Brush and Fill the Item Rectangles
SolidBrush brush = new SolidBrush(Selected_Color);
grp.FillRectangle(brush, rct);

Running the application looks like below now:

5) OK. Now we have colour rectangles in the combo box. Next, we should add the text that belongs to the colour rectangle. To draw the text, we should have offset from the left of the combo box as the rectangle is already occupying the left portion. To apply offset for drawing the text, we should perform the summation of, left offset already done to draw the rectangle (Shown as 1), the rectangle width and the new offset from the right of the colour rectangle (Hardcoded as 2). The summation will give from where (On the left side) the drawing of the text should take place. Below picture explains this:

Now in the code, we made a call to "DrawString() method" on the retrieved grp object. While calling the function, the left location of the string to be drawn is calculated based on the color rectangle’s dimensions, which already explained. Below is the code that draws the text next to the color Rectangle:

//Snippet 04.5: Create a Brush for drawing the texts
SolidBrush textb = new SolidBrush(Color.Black);
grp.DrawString(color_name, e.Font, textb, Rect_left + rct.Width + 2, Rect_top);

Running the application at this  stage look like below.

6) At last, we have to draw a bounding rectangle for the selected combo box item. By performing Bitwise AND (&) with “e.state” we can tell that the current item we are drawing is in selected stated or Not. When an item is in Highlighted state, a bounding rectangle around it is drawn in the Red Rectangle colour. To erase previously drawn red bounding rectangle, the drawing in the else portion is performed. Below is the code:

//Snippet 04.6: Draw a Border around the Selected Item
if ((DrawItemState.Selected & e.State) == DrawItemState.Selected)
    grp.DrawRectangle(Pens.Red, e.Bounds);
    grp.DrawRectangle(Pens.White, e.Bounds);

The below given video shows the completed application:

Source Code: Download

October 20, 2013

ASP.Net - Displaying and Manipulating Data Using FormView Control

1. Introduction

In this example, we will see how do we make use of FormView control to retrieve the data from the database. We are going to use SQLDataSource Control to supply the data for the FormView control. We will also have look at modifying the FormView templates.

The FormView control allows you to place the data bound controls wherever you want. It is just like designing the form for calculating the Electricity Bill based on unit consumption. Now think of getting the table columns from the database and placing that freely wherever we want on the web page. This is what achieved by the FormView control. Have a look at the below picture:

The three column data can be placed wherever we want. The FormView control allows editing as well as inserting the data as well. The underplaying Data Source should provide the support for it. A FormView control has templates associated with it. These templates are:

  1. Header Template
  2. Footer Template
  3. Edit Item Template
  4. Insert Item Template
  5. Item Template
You can modify each template and arrange the controls the way you want. Header and Footer templates are always displayed. Item Template is used to display the data. Edit Template is used when we want to modify the Data and Insert Template is used when we want to do the data insertion. In our example, we are going use the FormView Control to display the data. Okay, let us start with the example.

2. About the example

The Example application, which uses the FormView control, is shown below:

Using the Numbered links user can navigate through the records. The header and Footer will be displayed statically and it will not get changed when we navigate between the records. Data Manipulation can be done using the Edit, Delete and New Links. The Items marked in green boxes are bound to the database and when the user makes record navigation, the content in the Green Boxes get changed.

3. Configure Data Source

A DataSource control should be configured to supply the data for the FormView control. In our example, SQLDataSource control is configured to supply the data to the FormView control.

The FormView control enables data editing as well and to allow data editing the underlying data source should also support the data manipulation. In our example, SQLDataSource control is configured to retrieve the data from the Titles table of the Pubs database. Also, the SQLDataSource control is configured in such a way that it can perform the data manipulation as well. The below-given video shows how the SQLDataSource is configured.

Video 1: Configuring the Data Source

4. Configuring the FormView Control – Header, Footer

Once the FormView control is placed on the Default.aspx page, using the Quick Task Options, the SQLDataSource created in the previous section is linked to it, using the Choose Data Source option. Have a look at the FormView control shown below:

In the above picture:

1: Shows the FormView Control.
2: Text added using the Header Template. This text will be constantly displayed when a user moves from one page to other using numbered links at the lower left of the form.
3: Text added using the Footer Template. This behaves same as Header template.
4: This shows that user can go to different records. This is done using the paging option in Quick Task List.
5: These options will be displayed when you selected data source is configured for editing the data.

The below video explains how do you Configure header and footer template.

Video 2: Configuring the FormView control

5. Item Template, EditItem Template and InsertItem template

When a user moves between the records by clicking the numbered link at the end of the control, the FormView will be in ReadOnly Mode. When the FormView control is displayed in the ReadOnly mode the Item Template is used. When user clicks the link “Edit” the EditItem template get displayed. The same way InsertItem template gets displayed when the “Insert” link is clicked. Each template can be edited as a HTML page.

In the Template editing mode, you can place a completely different control and bind that to the control. The below-given video shows editing the InsertItem and EditItem templates.

Video 3: Completing the FormView Control

Source Code : Download

August 19, 2013

C# - Understanding GDI+ with Pen and Brushes of Solid, Hatch, Gradient types

1. Introduction

In this article we will have a look at how do we perform the drawing operation using the GDI plus functions available with DotNet. GDI stands for "Graphical Device Interface" and using that you can create rich drawing applications, show useful information on the form as a drawing say for example showing a pie chart of sales on past 5 years.

In this article I will show how do you perform the drawing using Pen and Brushes.

2. What is Pen and Brush in C#?

A "Pen" is a graphics object, which can be used to perform line drawing. A pen has the properties like the color of the Pen and thickness of the pen. A brush is also a graphics object, which can be used to paint the region. Suppose if you want to fill the area you can use the Brush. Think about Painting a door, a wooden plate etc.

In this article I will show how to use the "Plain Solid Brush", "Gradient Brush" and "Hatch brush".

3. About the Finished Example

Below is the Screen shot of the finished application:

The Black screen is the Panel control and it is used in this example as a drawing canvas. When the Fill Brush is not enabled whatever you draw is drawn using only the Pen. That means you see the drawing outline that is Rectangle outline in our example. You can switch between Solid Brush and Advanced brush using the Advanced Brush checkbox. When the checkbox is enabled, you see the advanced brush options that can be toggles between Hatch and Gradient using the Toggle switch shown as seven in the screen shot above.

4. Drawing the Rectangle Object

To draw a rectangle in GDI+ we need its size and position. So in the first part of the development, we placed the controls to collect the Rectangle’s position and the size (Section 1 and 2 in the screenshot above). Then the rectangle is drawn in the Panel named as DrawingCanvas.

1) First the required namespace to perform the 2d drawing is placed in the using directive as shown below. Note that this required when we want to use the rich functionalities of the GDI+

//Sample 00: Required Name Spaces
using System.Drawing.Drawing2D;

2) Then a Rectangle object is created and its size and position are filled by the user-supplied values.
//Sample 01: Create the rect object.
Rectangle rect = new Rectangle();
rect.X = Int32.Parse(txtX.Text);
rect.Y = Int32.Parse(txtY.Text);
rect.Width = Int32.Parse(txtWidth.Text);
rect.Height = Int32.Parse(txtHeight.Text);

3) The black screen that you see in the Sample application screenshot is a Panel Control. The "CreateGraphics() method" will return the graphics object. You can use this method on any window objects like Panel, Form or even controls like textbox, listbox etc. In our case, we asked the Panel Control to retrieve the Graphics object from it. And this object is stored in grp of type Graphics. Once the Graphics object is ready, a Pen is created with a color of Goldenrod, which is a preset color. You can see all the present color by Typing Color dot. Below is the code:

//Sample 02: Get the Graphics from the Canvas(Panel) and Create the pen
Graphics grp = DrawingCanvas.CreateGraphics();
Pen pen = new Pen(Color.Goldenrod);

4) Before performing the Drawing (Note we write all these code in the Draw Button click handler), we clear any previously drawn by calling the Clear method of the Graphics object. Then the Rectangle is drawn with the user supplied Rectangle demotions.

//Sample 03: Clear the Graphics and Draw Rectangle
grp.DrawRectangle(pen, rect);

Test Run 1: The Video Explains how the sample looks when user feeds different values for the Rectangle. You can learn how Position and Size of Rectangle used in drawing a rectangle in the below video.

Video 1: Explains how Rectangle is Drawn

5. The GDI+ Pen

Using the pen object we can defines the drawing line color and its thickness. In our example application, controls marked by the number 3 are used to create a pen. In the previous section we created a pen just by specifying the color. This line of code is expanded to use the user selection on the form. Let us have look at the code for creating and using the Pen object:

1) First two variables are declared. The variable pencolor is used specify the color of the pen and this variable will be filled with the user selected color. The variable thickness is used to specify the pen thickness when we create the pen object.

//Sample 03: Create the Pen
Color pencolor;
int thickness;

2) Based on the user selected Pen color using the Radio buttons, the pencolor variable is filled by taking predefines color values like Color.Pink. The code is below:

//3.1: Decide Pen Color
if (radGolden.Checked == true)
    pencolor = Color.PaleGoldenrod;
    pencolor = Color.Pink;

3) Similarly, the thickness value also filled by the user selected line thickness combo box option. The pen thickness in our example has three standard thicknesses, but you can specify any float value to create a pen thickness. Below is code that stored the pen thickness:

//3.2: Decide Pen Thickness
if (rad1.Checked == true)
    thickness = 1;
else if (rad5.Checked == false)
    thickness = 3;
    thickness = 5;

4) Finally a pen object is created making use of the pencolor and thickness variables populated previously. This pen object can be supplied to Graphics functions so that the function uses this new pen whenever it performs a line drawing.

Pen pen = new Pen(pencolor, thickness);

Below is some of the Pen and look at the Lines that forms the Rectangle:

Video 2: Creating a Pen

6. The GDI+ Brushes

In this example I am going to show you different types of Brushes. Imagine a brush that you use to paints the walls of your house. The GDI+ brushes can also be used similar to this. We draw something using the pens that defines outer lines and then paints (Using brush) the region inside the region formed by the pens. But you are not restricted like brush can be used only with pens.

The types of brush that you are going to see here are:

  1. Simple Solid Brush
  2. Gradient Brush
  3. Patterned Brush

The Simple Solid brush fills the color in plain solid color. The Gradient Brush fills color between two colors applying the linear transformation from one color to another color. The hatch brushes fills the region with a given pattern. All these types of brushes in effect are shown in the below picture:

OK. Let us see, how do we achieve it. 

1) Below is the CheckedChanged event handler for the checkbox marked as four in the first picture of the article. In this handler we decide to enable or disable the entire GroupBox that belongs brushes.

//Sample 05: Enable the Brush Group Box
private void chkBrushEnable_CheckedChanged(object sender, EventArgs e)
    if (chkBrushEnable.Checked  == true)
        grpBrush.Visible = true;
        grpBrush.Visible = false;

2) During the Form load, by default only the Solid brush option will be displayed. The user should check the Advanced Brush check box to enable the Gradient/Hatch Brush options. This code is below:

//Sample 06: Disable Advanced Brush Option
private void frmRectDraw_Load(object sender, EventArgs e)
    grpBrushAdv.Visible = false;

3) When the Advanced brush is displayed, solid brush is hidden. The same way, when the solid brush is displayed the advanced brush is hidden. This is done through CheckedChanged event handler of the Advanced Brush check box. The code for that is shown below:

//Sample 07: To show or Hide Advanced brush option
private void chkAdvBursh_CheckedChanged(object sender, EventArgs e)
    if (chkAdvBursh.Checked == true)
        grpBrushAdv.Visible = true;
        grpBrushSolid.Visible = false;
        grpBrushAdv.Visible = false;
        grpBrushSolid.Visible = true;

4) In the Example application screenshot, the control item marked as 7 is a Label Control. This label control is used as the toggle button which alternate between and Gradient and Hatch. When the control label shows as Gradient then the user will see controls relevant to Gradient Brush. And, the same hold true for the Hatch brush also. We specify different captions for the color based radio buttons based on the current brush mode (Gradient or Hatch). The controls specific to the hatch brush will be shown/hidden based on the current brush mode. This kind of work is done in the click event handler for the Label control:

//Sample 08: Show the controls relevent for Hatch brush or
// Gradient Brush
private void lblHatchGr_Click(object sender, EventArgs e)
    if (lblHatchGr.Text == "Gradient")
        lblHatchGr.Text = "Hatch";
        lblHatPat.Visible = true;
        radHatTypeHorizontal.Visible = true;
        radHatTypeVerticle.Visible = true;
        radHatTypeWave.Visible = true;
        radHatTypeHorizontalBrick.Visible = true;
        HatchGB1.Text = "Forecolor";
        HatchGB2.Text = "BackColor";
        lblHatchGr.Text = "Gradient";
        lblHatPat.Visible = false;
        radHatTypeHorizontal.Visible = false;
        radHatTypeVerticle.Visible = false;
        radHatTypeWave.Visible = false;
        radHatTypeHorizontalBrick.Visible = false;
        HatchGB1.Text = "From";
        HatchGB2.Text = "To";

5) Below is the function GetSolidBrushColor_FromUI that gets the color for the solid brush. This function reads the user selected radio button for the solid brush color and assign that to the out parameter passed in as color. Note that the out parameter guarantees the caller that the function will definitely assign a value in the color parameter and the caller no need to initialize the value. The function is below:

//Sample 09: Get Solid Brush color from UI
private void GetSolidBrushColor_FromUI(out Color color)
    if (radSolBrRed.Checked == true)
        color = Color.Red;
    else if (radSolBrGreen.Checked == true)
        color = Color.Green;
        color = Color.Blue;

6) Look at the sample application screenshot labeled at 6, the GroupBox names “From” and “To” will be changed as ForeColor and BackColor when the Brush mode changes from Gradient to Hatch. The function shown below will return the color values from the same Radio buttons that comes under these two set of radio groups which changes their name based on the brush mode:

//Sample 10: Get Color1 and Color2 from UI
private void Get_Col1_Col2(ref Color color1, ref Color color2)
    if (radhatCol1Blue.Checked == true)
        color1 = Color.Blue;
        color1 = Color.Yellow;

    if (radHatCol2Green.Checked == true)
        color2 = Color.Green;
        color2 = Color.Yellow;

7) The Inflate_Rect function written below will diminish the rectangular dimensions based on the current pen thickness. Note that the function takes the parameter as the reference type so the caller will expect the changes in the passed-in rectangular dimensions. So for we had looked at the some the helper functions written for the sample. Let us go the Hatch brush and filling techniques.

//Sample 11: Reduce rectangular dimension
private void Inflate_Rect(ref Rectangle rect)
    int inflate_to;
    int thickness;

    if (rad1.Checked == true)
        thickness = 1;
    else if (rad5.Checked == false)
        thickness = 3;
        thickness = 5;

    inflate_to = thickness;

    rect.X = rect.X + inflate_to;
    rect.Y = rect.Y + inflate_to;
    rect.Width = rect.Width - inflate_to*2;
    rect.Height = rect.Height - inflate_to*2;

8) In the OnPaint handler after drawing the rectangle with the required Pen attributes, we fill the rectangle with the user selection of brush. Note that before making the Fill operation using the brush, the rectangle drawn using the Pen is inflated. You can create a solid brush by specifying the color. The graphics object supports lot of drawing functions with a Fill, say for example something like FillRectangle, FillEllipse etc. In the below code we make sure the Advanced brush option is not selected by the user and then create a SolidBrush by specifying the solid fill color. Once the brush is created it can be used with the Graphics object. In our case we used the solid brush with the FillRectangle function. Below is the code that creates and uses the Solid Brush:

Inflate_Rect(ref rect);
if (chkAdvBursh.Checked == false)
    //Sample 12.1: Create Solid Brush and Perform the Fill
    //A. Declaration
    Color brush_color ;
    Brush Solid_brush = null;
    //B. Create Solid Brush
    GetSolidBrushColor_FromUI(out brush_color);
    Solid_brush = new SolidBrush(brush_color);
    //C. Fill the Rectangle
    grp.FillRectangle(Solid_brush, rect);

9) Based on the Toggle Label, we create the Gradient Brush. To create gradient brush we need two colors as the gradient interpolates two colors in a linear way. Look at the previous picture for the gradient brush and in that the color interpolation is applied between blue and green in a horizontal way. In our example, we get both the colors required for the gradient effect by making a call to the function Get_Col1_Col2. Then we pass the from_color and to_color to the LinearGradientBrush contractor to create the Gradient_brush object. Once we have Gradient brush in hand we make a call to the function FillRectangle to get the Gradient effect. The code for this is shown below:

if (lblHatchGr.Text == "Gradient")
    //Sample 12.2: Create Gradient Brush and Perform the Fill
    //A. Declaration
    Color from_color= Color.White;
    Color to_color = Color.White;
    Brush Gradient_brush = null;
    //B. Create Gradient Brush
    Get_Col1_Col2(ref from_color, ref to_color);
    Gradient_brush = new LinearGradientBrush(rect, from_color, to_color,
    //C. Fill the Rectangle
    grp.FillRectangle(Gradient_brush, rect);

In the above code, we asked the gradient to be applied horizontally by specifying the LinearGradientMode.Horizontal. The Gradient Modes are shown below:

10) In the else part of the Gradient Brush section we create the pattern brush to fill the rectangle. To see a pattern we should specify the Back Color and Fore Color when creating the pattern brush. After the having these colors (Note we used same function call Get_Col1_Col2), the user selected hatch pattern is stored in the style object. Refer msdn to know other hatch pattern as there are much more patterns available. All these information are passed to HatchBrush constructor to create the object Hatch_brush. Then as usual, this hatch brush is passed to the FillRectangle function of the Graphics object. The code is below which constructs the Pattern brush and uses that to fill the rectangle:

    //Sample 12.3: Create Hatch brush and perform the Fill
    //A. Declaration
    Color fore_color = Color.White;
    Color back_color = Color.White;
    Brush Hatch_brush = null;
    HatchStyle style;

    //B. Get Fore Color, Back Color. Also decide hatch style
    Get_Col1_Col2(ref fore_color, ref back_color);

    if (radHatTypeHorizontal.Checked == true)
        style = HatchStyle.Horizontal;
    else if (radHatTypeVerticle.Checked == true)
        style = HatchStyle.Vertical;
    else if (radHatTypeWave.Checked == true)
        style = HatchStyle.Wave;
        style = HatchStyle.HorizontalBrick;

    //C. Create Pattern Brush
    Hatch_brush = new HatchBrush(style, fore_color, back_color);

    //D. Perform Drawing
    grp.FillRectangle(Hatch_brush, rect);

You can see the how the Brush works in the sample from the below shown video.

Video 3: Creating and using Brushes

Source Code: Download

June 10, 2013

C# - Retrieve List of Stopped Service using WMI Query

 1. Introduction

What is WMI? WMI stands for "Windows Management Instrumentation". WMI is set of classes living in a separate world of dot net framework in form of wrapper classes around set of native classes that retrieves hardware information. Say for Example, if I want to retrieve the information like remaining battery in my laptop when it is off the power source. The dotnet framework may not support getting this kind of information but the WMI will. To know list of WMI classes, do a google search for “WMI Classes”. When open the link coming from MSDN (Article written in 2013) the left side pane consists of all WMI classes in groups like Win32 Classes, WMI System classes, etc The MSDN link is shown below:

Figure 1

In this Article we will create a simple example that will Query the all the System Services which is in the stopped state.

2. Starting the Project

We are going to display the List of stopped service in a console window. So, create a Visual C# Console Application. Once the IDE gives you generated code, the first thing we should do is, bring WMI environment to our project.

To access the WMI classes we need to use the "System.Management.dll". Right click you project and using the Add Reference and add the Reference to System.Management as shown in the below given picture. This reference will appear under the reference node of the project.

Figure 2

1) Select the .Net Tab
2) Pick the Assembly
3) Click OK

3. Start Coding – Column Formatted Console Output

1) Add the using directive for easy access of the "WMI Classes" in the program main.

//Sample 01: Required Name Space for the WMI Query
using System.Management;

2) From the Win32_Service class we are going to take three coumns called Name, StartMode, State. "Name" is the service name. "StartMode" will tell the service start type like Manual or Automatic etc. The "State" column tells at what state the service is. How do I know all these information? The Class Win32_Servie Information is taken from the MSDN As shown below:
Figure 3

Right! We are going to take three columns and will display that in the console window. The function written below will keep each of the columns at certain location in the console output window.

//Sample 02: Function that Provides the Padded column Output
public static void PrintColumnData(string Column1, string Column2, string Column3)
    Console.CursorLeft = 0;
    Console.CursorLeft = 40;
    Console.CursorLeft = 55;

We can use the above function to start each column at specific localtion as shown in the below depiction. To set the caret at a specific column location in the console output the CursorLeft property is used. OK, now we will concentrate on retrieving the stopped services.
Figure 4

4. The ManagementObjectCollection

Go back and Look at Figure 3, from that you can know that class name is Win32_Service and class data member that we want to retrieve are Name, StartMode, State.  Also note that we want to retrieve only the stopped services. The state member of the class tells us whether the service is stopped or not. The screen taken from the MSDN for possible values of State is shown in Figure 5:

Figure 5

WMI supports query based retrieval of the information that we are looking for. You can map the information given by MSDN in Figure 3 with a database table and form a Query for information retrieval. The mapping between WMI Class and Database Table is shown in Figure 6 by taking the Win32_Service class as an example.

Figure 6

OK. Now you have the understanding of the mapping. We use "SelectQuery" to form the WMI Query. The SelectQuery object created on the "WMI Win32_Service Class" using the above mapping technique is given in the below code snippet:

//Sample 03: First form the Select Query
SelectQuery Qry = new SelectQuery("Select Name, " +
    "StartMode, State From Win32_Service " +
    "Where State = 'Stopped'");

Using this SelectQuery object Qry, a searcher object of type "ManagementObjectSearcher" is created. The searcher object is useful to retrieve the collection of the "ManagementObject" based on the supplied query. The Get() methods will give the collection. As the Query is against the Win32_Service with the where condition asking for the service in stopped state, the Get() method of the searcher instance will do the search in WMI object pool and picks the required objects then packs that in the "ManagementObjectCollection". Below is the code:

//Sample 04: Create the Object_Search Instance. The Instance
//           Will search and bring the Object based on Supplied
//           Query
ManagementObjectSearcher searcher = new ManagementObjectSearcher(Qry);

//Sample 05: Use the searcher object to get List f Stopped
//           Service from the Win32_Service Class
ManagementObjectCollection services_stopped = searcher.Get();

5. List the Windows Services

The ManagementObjectCollection is collection of ManagementObject. Now have a look at Figure 6, ManagementObjectCollection is records returned or you can consider that as table. ManagementObject can be seen as single record. And we get the columns from this single record. In the below code we are iterating through the Collection service_stopped and display each column of the single WMI instance using the notation WMIInstance[<Field Name>] for example mobj[“StartMode”]. Below is code that retrieves the required columns and displays that using the PrintColumnData explained in section 3 of this article.

//Sample 06: Iterate through the collection and display the stopped
//           Services.
PrintColumnData("Service Name", "Start Mode", "Status");
PrintColumnData("============", "===========", "======");
foreach (ManagementObject mobj in services_stopped)
    PrintColumnData(mobj["Name"].ToString(), mobj["StartMode"].ToString(), mobj["State"].ToString());

The output of the program is shown below:

Figure 7

Source Code : Download

May 15, 2013

SQL 2005 - Sub-Queries, Derived Tables, and Common Table Expression (CTE)

1. Introduction

In this article, I will explain some useful select queries that come handy when you want to retrieve data from different tables. There are lot of way you can form select queries that returns same result. For this article, I used Microsoft's Pubs database. Let us explore the queries one by one.

2. Formatting the Query

Let us say you want to create mailing address of Authors from the Pubs database. The problem is that the data stored in the Authors table is not in the mailing address format. The data is spawn among multiple columns. Now, have a look at the below query:

Authors mailing address in different table column
Fig 1. Authors mailing address in different table column

The above query uses the operator + which joins two strings. We use this operator to concatenate special chars, two columns, a constant string etc.  Note that the space(2) function adds two blank spaces to the string formed. Char(13) and Char(10) together forms a Line Feed and Carriage Return. The result of executing the above query is show here:

Output of author's mailing address
Fig 2. Output of author's mailing address

This output looks like a mailing address, right?

3. Case...When...End

The "Case..When" is decision making structure in SQL Queries. To know how it can be used, let us consider an example. The Titles table from the Pubs database has the book titles and it also has the price of it. Now, let us say that when we are querying the books from this table, we need to display title and price of the book along with the cost suggestion like Low, Medium and High; based on some price range. Now look at the below query example:

Case...When...End Struct Example of SQL Server
Fig 3. Case...When...End Struct Example of SQL Server
The portion of query marked in blue box shows how the costing level is done. The case when statement structure is making the decision in “When” condition statement. When the condition evaluates to true the “Then” portion will be executed. For Example, when the price lies between 0 and 3, "Low" will be returned. The else potion comes in combination with “When”. It is like:

When (Condition) Then <true> Else <false>

The "End" keyword is used to tell that we are going to terminate the Case...When conditional construct.

4. Sub Queries

A "Sub-Query" is actually referred by the Main query to decide something. Sub-query can use the data returned by the Main query and in the mean time, the main query cannot have access to the columns retrieved by the sub queries. This is shown in the below picture:

Main Query Don't have access to data of Sub-Query
Fig 4. Main Query Don't have access to data of Sub-Query

Now let us look at the Pubs Database. We already know about the Titles table since we used it in the previous examples. This table contains the book title and it price along with other information. The Sales table contains information about the sales of the book titles along with some other information. 

Let us see, how do we use sub-query to return the Book Titles that is not yet sold even single piece. This means that we have a book in the titles table and no entry for that book in the Sales Table. Have a look at the below Query:

Sub-Query and Main Query Relation
Fig 5. Sub-Query and Main Query Relation

In the above example, Main query is on the Titles table and we want to return the titles, which is not making any sales. The sub Query is on the Sales table which participates in the where clause condition of the Main table. Note that the sub-query used the column returned by the main table as T.Title_Id. The sub-query is called by the main query for every row that it returns.

When the sub Query returns no record, the “Not Exists” statement, which is part of the where condition, evaluates to true there by there by signalling main query to return the row.

5. Derived Tables

In the previous example we saw a select statement embedded inside the Where portion of the main select statement. If similar sub query participates on the “From” clause of the Main select query, we call that as "Derived table". See, the table in the expected form does not exist, and hence the result-set derived on the fly. Have a look at the below example which shows how a Table is derived in the "From" clause of the query:

SQL Server Derived Table Example
Fig 6. SQL Server Derived Table Example

The query takes the data from the Titles table of the Pubs database. The internal select statement (marked as blue box) that looks like a sub-query is the derived table here. The derived table provides the Title_id and total sales made on that particular title by making an aggregate query on the Sales table. 

Note that we are joining the Titles table with the data returned from Derived Table based on the Title_id from the Titles table and Title_Id returned by the Derived Table. A query output that is kept in the FROM portion of the SELECT query makes it as Derived Table. Here, we used the derived table technique to display book titles information along with number of titles sold.

The derived Table is an "in-memory table" and SQL server wipes that from the memory when the query execution is completed. In our example the derived table is TS (Title_id, TotalSold).  This two-column table get removed from memory when the Query execution is over.

6. Common Table Expressions (CTE)

We can achieve the same result given by the Derived Table technique by using the "CTE" technique. In "Common Table Expression" technique, we define the table before starting the actual select query. Once the query is defined, it can be referred in the sub-sequent select query multiple times. Now consider the below example:

Common Table Expression Technique (CTE)
Fig 7. Common Table Expression Technique (CTE)

In the above example, first we defined the Common Table Expression as shown in the above depiction (indication mark 1). The common table Name is TotalByTitleId with two fields TitleId, Total in it. In our example, the mapping of CTE columns TitleId, Total is done by querying a table Sales. The returned result is kept as table construct TotalByTitleId.

The next select query uses this CTE construct as table. The select query retrieves the data from the Titles table and joins that with our CTE to get the TotalSales made by a particular title. You can also use multiple CTE in your query and in that case definition of each CTE will be separated by comma like below:

With <CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query )

Like this site? Tell it to your Friend :)