Design Pattern 4 - Command Pattern

A Command pattern is an object behavioral pattern that allows us to achieve complete decoupling between the sender and the receiver. (A sender is an object that invokes an operation, and a receiver is an object that receives the request to execute a certain operation. With decoupling, the sender has no knowledge of the Receiver's interface.) The term request here refers to the command that is to be executed. The Command pattern also allows us to vary when and how a request is fulfilled. Therefore, a Command pattern provides us flexibility as well as extensibility.

To be continued...

Design Pattern 3 - Factory Method Pattern

The Factory Method pattern allows for the instantiation of objects at runtime. We can think of Factory Pattern as a factory which is responsible for "manufacturing" an object. A Parameterized Factory receives the name of the class to instantiate as argument.

Obviously, a factory is not needed to make an object. A simple call to new will do it for you. However, the use of factories gives the programmer the opportunity to abstract the specific attributes of an Object into specific subclasses which create them. In addition, one principle in OOP programming is to reduce coupling. Avoid using new key work means we also avoid the dependency between classes which make it much easier for unit testing.



There is one very good article in Wikipedia about Factory Method pattern, where using an example of Pizza factory produce different type of Pizzas. The code is in Java, with a little effort I have re-written it in C# as the code shown below:

using System;

namespace FactoryMethodPattern
{
    abstract class Pizza
    {
        public abstract double getPrice();
    }

    class HamAndMushroomPizza : Pizza
    {
        public override double getPrice()
        {
            return 8.5;
        }
    }

    class DeluxePizza : Pizza
    {
        public override double getPrice()
        {
            return 10.5;
        }
    }

    class HawaiianPizza : Pizza
    {
        public override double getPrice()
        {
            return 11.5;
        }
    }

    class PizzaFactory
    {
        public enum PizzaType
        {
            HamMushroom,
            Deluxe,
            Hawaiian
        }

        public static Pizza createPizza(PizzaType pizzaType)
        {
            switch (pizzaType)
            {
                case PizzaType.HamMushroom:
                    return new HamAndMushroomPizza();
                case PizzaType.Deluxe:
                    return new DeluxePizza();
                case PizzaType.Hawaiian:
                    return new HawaiianPizza();
                default:
                    return null;
            }            
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // Loop with foreach through a Enum
            foreach (PizzaFactory.PizzaType pizzaType in 
                Enum.GetValues(typeof(PizzaFactory.PizzaType)))             
            {
                Console.WriteLine("Price of " + pizzaType + " is £" 
                    + PizzaFactory.createPizza(pizzaType).getPrice());
            }
        }
    }
}

Reference: Factory method pattern in Wikipedia.

Design Pattern 2 - Decorator Pattern

I finally can’t resist the temptation and joined the Sky Sports a few weeks ago, as I am a big fun of English premiere league. While I was placing the order on the internet, somehow the Decorator pattern came into my mind.

By definition, the Decorator Pattern is used for adding additional functionality to a particular object as opposed to a class of objects. It is easy to add functionality to an entire class of objects by subclassing an object, but it is impossible to extend a single object this way. With the Decorator Pattern, you can add functionality to a single object and leave others like it unmodified.



To implement Decorator Pattern for sky.com, let us create an interface first, we named it as ITVBundle.

public interface ITVBundle
{
 string Description();
 double Cost();
}

To subscribe sky sports or movie channels, you have to subscribe a basic pack even you don’t want to watch them. So the first class I need to create is BasicPack which implement ITVBundle interface.

public class BasicPack : ITVBundle
{
 public double Cost()
 {
  return 18.50;
 }

 public String Description()
 {
  return "Basic Packs";
 }
}

An decorator abstract class which also implement ITVBundle interface:

public abstract class AddOnDecorator : ITVBundle
{
 public abstract string Description();
 public abstract double Cost();
}

The next bit is my favourite, which is to add Sports packs or Movies Pack to the Basic Pack. This works by adding a new decorator class that wraps the original class. Let’s look at the example below:

public class AddSportsPack : AddOnDecorator
{
 ITVBundle _itv;
 public AddSportsPack(ITVBundle tvBundle)
 {
  this._itv = tvBundle;
 }

 public override string Description()
 {
  return _itv.Description() + ", with Sports Pack";
 }

 public override double Cost()
 {
  return _itv.Cost() + 9.99;
 }
}

From the above you can see the wrapping is typically achieved by passing the original object as a parameter to the constructor of the decorator when it is created. The decorator implements the new functionality, but for functionality that is not new, the original (wrapped) class is used. The decorating class must have the same interface as the original class.

Here is the example on using Decorator Pattern:

BasicPack bp = new BasicPack();
AddSportsPack plan1 = new AddSportsPack(bp);

Please see the full working code:

using System;

namespace DecoratorPattern
{
    public interface ITVBundle
    {
        string Description();
        double Cost();
    }

    public class BasicPack : ITVBundle
    {
        public double Cost()
        {
            return 18.50;
        }

        public String Description()
        {
            return "Basic Packs";
        }
    }

    public abstract class AddOnDecorator : ITVBundle
    {
        public abstract string Description();
        public abstract double Cost();
    }

    public class AddMoviePack : AddOnDecorator
    {
        ITVBundle _itv;
        public AddMoviePack(ITVBundle tvBundle)
        {
            this._itv = tvBundle;
        }

        public override string Description()
        {
            return _itv.Description() + ", with Movie Pack";
        }

        public override double Cost()
        {
            return _itv.Cost() + 9.99;
        }
    }

    public class AddSportsPack : AddOnDecorator
    {
        ITVBundle _itv;
        public AddSportsPack(ITVBundle tvBundle)
        {
            this._itv = tvBundle;
        }

        public override string Description()
        {
            return _itv.Description() + ", with Sports Pack";
        }

        public override double Cost()
        {
            return _itv.Cost() + 9.99;
        }
    }    

    class Program
    {
        static void Main(string[] args)
        {
            BasicPack bp = new BasicPack();
            AddMoviePack plan1 = new AddMoviePack(bp);
            AddSportsPack plan2 = new AddSportsPack(plan1);

            Console.WriteLine("You have choosen plan 2: {0} \nThe cost is: ￡{1}", 
                plan2.Description().ToString(), plan2.Cost().ToString());
        }
    }
}

The output will be looking like:



Use the Decorator pattern when:

- You have an existing component class that may be unavailable for subclassing.
- You want to attach additional state or behaviour to an object dynamically.
- You want to make changes to some objects in a class without affecting others.
- Avoid subclassing because too many classes could result.

Design Pattern 1 – Strategy Pattern

In the past 6 months, I have been reading and studying the well-known book Design Patterns, Elements of Reusable Object-Oriented Software by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides (GOF). While studying the book, I do feel it has taken me to the next level of understanding OOP concepts and has given me more confidence to write elegant and reusable code. I think any Object-Oriented software developer should read the book. In my next few articles I will be covering the most frequently used patterns in .net applications.

The first design pattern I’d like to talk about is Strategy Pattern. The main purpose of the Strategy pattern is to decouple concrete code into separate classes, which promotes reusability. As your project grows in size, having reusable modules is a necessity. This pattern also allows you to easily add new modules into a C# ASP .NET software design with limited impact on the rest of the system.



Let’s assume one situation that you are trying to print out a greeting message on screen in different languages. Here is the non-pattern way how your code may look like:

class HelloWorldClientNoPattern
{
 public void SayHello(string strName, string strLanguage)
 {                        
  switch (strLanguage)
  {
   case "english":
    Console.WriteLine("Hello World " + strName);
    break;
   case "spanish":
    Console.WriteLine("Hola mundo " + strName);
    break;
   case "german":
    Console.WriteLine("Hallo Welt " + strName);
    break;                
  }
 }
}

class Program
{
 static void Main(string[] args)
 {
  
  HelloWorldClientNoPattern client = new HelloWorldClientNoPattern();
  client.SayHello("Can Lu", "english");
  client.SayHello("Can Lu", "spanish");
  client.SayHello("Can Lu", "german");

 }                
}

What is wrong with this approach? Well it is nothing wrong it works but there are at least couple of problems.
- The code above is not reusable. If we want to use the SayHello method somewhere else, I have to write the same code again.
- The code is not decoupled. If you want to add a new greeting language. For example if you want to print SayHello in Chinese, you have to modify the client class.

The strategy pattern is to solve this kind of problem. The most important part of the Strategy pattern is the main interface. This is what allows the polymorphism and allows us to pass the client any type of algorithm, as long as it uses the same interface. The interface will look like:

public interface IHelloWorldStrategy
{
 void SayHello(string strName);
}

Then it’s time to create different classes to implement this interface. For example we could have:

class HelloWorldEnglish: IHelloWorldStrategy
{
 public void SayHello(string strName)
 {
  Console.WriteLine("Hello world " + strName);
 }
}

class HelloWorldChinese: IHelloWorldStrategy
{
 public void SayHello(string strName)
 {
  Console.WriteLine("Ni Hao " + strName);
 }
}

Now we can rewrite our client class. Instead of using Switch Case/If then else, we're using an Interface as the implementation of the strategy. This allows us a generic pointer to whichever algorithm we end up actually using.

class HelloWorldClient
{
 IHelloWorldStrategy strategy;
 public HelloWorldClient(IHelloWorldStrategy s)
 {
  this.strategy = s;
 }

 public void SayHello(string strName)
 {
  strategy.SayHello(strName);
 }
}

Notice the constructor takes a variable of the interface type. This is powerful because, if we derive each algorithm class from the same interface, then we can pass the client algorithm and it will be able to handle any kind algorithms that we create; as long as they implement the same interface, can be utilized in this client.

Please find the full working code below:

using System;

namespace StrategyPatternDemo
{
    public interface IHelloWorldStrategy
    {
        void SayHello(string strName);
    }

    class HelloWorldEnglish: IHelloWorldStrategy
    {
        public void SayHello(string strName)
        {
            Console.WriteLine("Hello world " + strName);
        }
    }

    class HelloWorldSpanish : IHelloWorldStrategy
    {
        public void SayHello(string strName)
        {
            Console.WriteLine("Hola mundo " + strName);
        }
    }

    class HelloWorldGerman : IHelloWorldStrategy
    {
        public void SayHello(string strName)
        {
            Console.WriteLine("Hallo Welt " + strName);
        }
    }

    class HelloWorldClient
    {
        IHelloWorldStrategy strategy;
        public HelloWorldClient(IHelloWorldStrategy s)
        {
            this.strategy = s;
        }

        public void SayHello(string strName)
        {
            strategy.SayHello(strName);
        }
    }    

    class Program
    {
        static void Main(string[] args)
        {
            HelloWorldClient myHelloWorld = new HelloWorldClient(new HelloWorldEnglish());
            myHelloWorld.SayHello("Can Lu");

            myHelloWorld = new HelloWorldClient(new HelloWorldSpanish());
            myHelloWorld.SayHello("Can Lu");

            myHelloWorld = new HelloWorldClient(new HelloWorldGerman());
            myHelloWorld.SayHello("Can Lu");            
        }                
    }
}

The output will be like:



There are a number of advantages to structuring a family of algorithms in this manner, but the most important is that doing so decouples the client from the implementation details of any particular algorithm. This promotes extensibility in that additional algorithms can be developed and plugged in seamlessly, as long as they follow the base interface specification, thereby allowing algorithms to vary dynamically. Moreover, the Strategy pattern eliminates conditional statements that would otherwise litter client code. Whenever we have switch case statement in code, we probably need to think twice, should we use Strategy Pattern to replace it.

Two ways of deleting duplicated rows in Oracle

The most effective way to detect duplicate rows in Oracle is to join the table against itself as shown below. Assume table_name has two columns col1 and col2

SELECT *
  FROM table_name A
 WHERE A.rowid > (SELECT min(B.rowid)
          FROM table_name B
         WHERE A.col1 = B.col1
           AND A.col2 = B.col2)

To delete the duplicate rows:

DELETE FROM table_name A
 WHERE A.rowid > (SELECT min(B.rowid)
          FROM table_name B
         WHERE A.col1 = B.col1
           AND A.col2 = B.col2)

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from table_name
 where rowid in (select rowid
                   from (select rowid,
                                row_number() over(partition by col1, col2 order by col1, col2) dup
                           from table_name)
                  where dup > 1);

Get URL Querystring Parameters using Javascript

As we know in ASP.net we can use Request.QueryString["variableName"] to retrieve the values of the variables in the HTTP query string. However if we need to get the parameter value in the client script, for instance we want to have an alert window to display a parameter value is not as straight forward as we do in server-side programming. Use the gup() helper function in JavaScript below can help us with this

function gup(name) {
 name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
 var regexS = "[\\?&]" + name + "=([^&#]*)";
 var regex = new RegExp(regexS);
 var results = regex.exec(window.location.href);
 if (results == null)
  return "";
 else
  return results[1];
}

The way that the function is used is fairly simple. Let's say you have the following URL:

http://localhost/script/directory/NAMES.ASP?name=Fred&id=123

You want to get the value from the frank parameter so you call the javascript function as follows:

var name_param = gup( 'name' );

Exporting data to Excel from a GridView in ASP.net

Hate or like it, regardless how pretty your GridView looks like, how easy it’s to print, customers just want to export everything to excel to do their own manipulation. So willy-nilly we must find a good solution to handle this. In asp.net, it’s actually very easy to do, but there are a few things we need to be aware of. Let’s have a look at the function first.

private void Export2Excel(GridView gv, string strFileName)
{
 Response.Clear();
 Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
 Response.Charset = "";

 // If you want the option to open the Excel file without saving then
 // comment out the line below
 // Response.Cache.SetCacheability(HttpCacheability.NoCache);

 Response.ContentType = "application/vnd.xls";
 System.IO.StringWriter stringWrite = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
 gv.RenderControl(htmlWrite);

 Response.Write(stringWrite.ToString());

 Response.End();
}

By calling above method in button click event, we can export any GridView to excel.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
    Export2Excel(GridView1, "demo.xls");
}

However there are a few things you need be aware of:

1. If you receive a HttpException regarding to type 'GridView' must be placed inside a form tag with runat=server. You need to overide VerifyRenderingInServerForm Method as below:

public override void VerifyRenderingInServerForm(Control control) { }

2. In Ajax enabled environment. You will very likely to receive an exception error as: Exception is Sys.WebForms.PageRequestParserErrorException .The message received from the server could not be parsed. This is because modified Response object cannot work in Ajax Framework. The workaround is to use asynchronous post back through the trigger in asp:UpdatePanel. Here are the code:

3. We must turn off AllowPaging to false before we are doing the export.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
 GridView1.AllowPaging = false;  
 Export2Excel(GridView1, "Overview.xls");
 GridView1.AllowPaging = true;
}

4. If you see this exception Exception RegisterForEventValidation can only be called during Render().You need to notify ASP.Net that not to validate the event by setting the EnableEventValidation flag to FALSE.

Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="demo.aspx.cs" Inherits="demo" EnableEventValidation = "false"

That has covered all errors I have seen in the past. Please let me know if there are any problems or questions. Another very good post I found on aspsnippet, is definitely worth a read.

Create a master and detail relationship report in ASP.net

A very common requirement I received from customers is to display a summary report in the main page, while user click on each row of the grid view, a new window will popup and show the detail information of the selected row. One scenario is that we have a list of customer orders in the main page, when user click on each order inside the gridview, a window will popup to show the order details.

We can easily achieve this in .net with a little bit JavaScript, the technique we discussed in my previous post Calling a JavaScript function from server side code. In today’s demo I am going to use Orders and Order Details tables from Northwind database. Let’s start.

1. Create our main page orders.asp. In the design view, drag a data source and select all from table orders.





2. In the source view, add 2 JavaScript functions after the body tag. The first function is to highlight the row on mouse hover. The second function is to open a popup window and pass the order no parameter OrderID to the second page orderdtl.asp which I am going to create in step 4.

3. Create a RowDataBound event handler as below.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
 e.Row.Attributes["onmouseover"] = "highlight(this, true);this.style.cursor = 'pointer'";
 e.Row.Attributes["onmouseout"] = "highlight(this, false);";

 e.Row.Attributes["onclick"] = "PopUpWin(this, 'OrderDtl.aspx', 'name',150,200,410,315)";
}

4. Create a new webpage called orderdtl.asp.
5. In the page design view, drag a GridView on to the page and configure the data source selecting from Order Details



In where clause, set the parameter value from Request.QueryString(“OrdrID”).



6. Save all and run the page. If everything works well, you should see the result as:

How to highlight a GridView's row on mouse hover in ASP.net?

With small amount of JavaScript we can correctly highlight the GridView row when user hovers over each row. This can be very helpful when we have a very wide report. To achieve this, we can use the technique I discussed in the previous article: Calling a JavaScript function from server side code.

The following JavaScript can do the trick, please note this also works well if you have set the AlternatingRowStyle of the GridView.

function highlight(tableRow, active) {
    if (active) {
        tableRow.originalstyle = tableRow.style.backgroundColor;
        tableRow.style.backgroundColor = '#cfc';
    }
    else {
        tableRow.style.backgroundColor = tableRow.originalstyle;
    }
}

Now we can drag a GridView to the page and selected a valid data source. In this demo, I use the Order table in Northwind database. In order to highlight the row on mouse hover, we need to add some custom JavaScript code to onmouseover and onmouseout event handlers of the rows of our GridView. In codehind we have:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{            
    e.Row.Attributes["onmouseover"] = "highlight(this, true);this.style.cursor = 'pointer'";
    e.Row.Attributes["onmouseout"] = "highlight(this, false);";
}

Run the application, you will see the result as the screenshot below:

How to call a Server Side Function from Client Side Code using JavaScript in ASP.net Ajax

You cannot call server-side code ‘directly’ from client-side code. That is because by design, the server side code executes at server side and client side code at the client. However there are some workarounds. To call serverside code from javascript, you will need to use AJAX. ASP.Net AJAX introduced a technique called PageMethods using which we can call server side methods from JavaScript. Try to imagine a scenario, in a user registration form, when user type a user name in a textbox, a server side process is triggered to find whether this is an exiting user or not in our user table in the database. In this example I am using the SQL Server 2005 as backend.

1.Create a demo table on Northwind db and insert 3 users into the table.

CREATE TABLE demo(user_id varchar(10) PRIMARY KEY);
insert into demo values ('user1');
insert into demo values ('user2');
insert into demo values ('user3');

2.Every server-side method that is called from the client-side, must be declared as “static”, and also has to be decorated with the [System.Web.Services.WebMethod] tag. Let’s create a simple function as below:

[System.Web.Services.WebMethod]
public static bool IsUserExist(string strUserID)
{
    bool blUserExist = false;
    string connectionString = "Data Source=localhost;Initial Catalog=Northwind; " + 
        "Integrated Security=True";
    SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();            
    string strSqlStatement = "select * from demo where user_id = '" + strUserID + "'";
    SqlCommand cmd = new SqlCommand(strSqlStatement, conn);
    SqlDataReader reader = cmd.ExecuteReader();
   
    if (reader.HasRows)
        blUserExist = true;            

    reader.Close();
    conn.Close();

    return blUserExist;
}

3. Go to the Markup page and drag a ScriptManager to the page. The “EnablePageMethods” attribute has to be added on the ScriptManager tag and set it to “true”.

4. Adding a simple HTML TextBox as:

5. Adding the JavaScript code as below:

The “OnSuccess” is the name of the JavaScript function that will be called if the request is successful. Whereas the “OnFailure” will be called if an exception is thrown.

6. Running the application. Will see the screenshot as below:

Ways to call JavaScript function from C# in ASP.net

As we all know in ASP.net framework, C# code executes on the server whereas JavaScript code executes in the client's browser. We can't actually "access" JavaScript from the server-side. All that we can do is write the JavaScript function call into the markup and have it execute when all server-side processing is completed and the page renders to the browser.

There are number of workarounds we can achieve this. To demonstrate let us declare a JavaScript Function in the markup page first.

Now we need to decide how we are going to execute this function in our code page.

1. We want to call the function on a page_load event, then we can put the code below to the page_load method:

protected void Page_Load(object sender, EventArgs e)
{
    if (!ClientScript.IsStartupScriptRegistered("alert"))
    {
        Page.ClientScript.RegisterStartupScript
            (this.GetType(), "alert", "alertMe();", true);
    }
}

2. We want to call the function on a buttion click event, we can write the code as:

protected void Button1_Click(object sender, EventArgs e)
{
    Button1.Attributes.Add("onclick", "alertMe()");    
}

Or

protected void Button1_Click(object sender, EventArgs e)
{
    Button1.Attributes["onclick"] = "alertMe()";
}

3. If we have a GridView and we want to call the method when the GridView Row get clicked by the user. This can be very useful in master-detail relationship data. For example, we have a summary GridView report shows customer order headers, when user click on the row, a small window will popup to show the details of the selected order. To do this, we simply add the following line to the GridView RowDataBound event:

GridView1.Attributes["onclick"] = "alertMe()";

OOP Basics: Inheritance

This little article is a basic introduction of using inheritance in C#. Inheritance is one of the primary concepts of object-oriented programming. It allows you to reuse existing code. Through effective employment of reuse, you can save time in your programming. Let's take a look at the program below:

using System;

namespace InheritanceSample
{
    public class Parent
    {

        string parentString;
        public Parent()
        {
            Console.WriteLine("Parent Constructor.");
        }

        public Parent(string myString)
        {
            parentString = myString;
            Console.WriteLine(parentString);
        }

        public void print()
        {
            Console.WriteLine("I'm a Parent Class.");
        }

    }

    public class Child : Parent
    {

        public Child(): base("From Parent")
        {
            Console.WriteLine("Child Constructor.");
        }

        public new void print()
        {
            base.print();
            Console.WriteLine("I'm a Child Class.");
        }

        public static void Main()
        {
            Child child = new Child();
            child.print();
            ((Parent)child).print();
        }
    }
}

Output:


Base classes are automatically instantiated before derived classes. Notice the output from above: The Parent constructor executed before the Child constructor. When a base class declares a method as virtual, a derived class can override the method with its own implementation. If a base class declares a member as abstract, that method must be overridden in any non-abstract class that directly inherits from that class.

A derived class can hide base class members by declaring members with the same name and signature. The new modifier can be used to explicitly indicate that the member is not intended to be an override of the base member. The use of new is not required, but a compiler warning will be generated if new is not used

OOP Basic: Polymorphism

Polymorphism gives us the ultimate flexibility in extensibility which is a basis of OO programming. Understanding Polymorphism is crucial to any OO language professional. The benefit of polymorphism is that you don’t need to know the object’s class to execute the polymorphic behaviour. We use polymorphism to achieve the late binding. For example, you may have many classes in an application, each with its own save method. When the application is saved, each object knows the class it belongs to and automatically calls the correct save method.

using System;

namespace Polymorphism
{
    class Shape
    {
        public virtual void draw()
        {
            Console.WriteLine("Draw shape....");
        }
    }

    class Circle: Shape
    {
        public override void draw()
        {
            Console.WriteLine("Draw circle....");
        }
    }

    class Triangle : Shape
    {
        public override void draw()
        {
            Console.WriteLine("Draw triangle....");
        }
    }

    class Rectangle : Shape
    {
        public override void draw()
        {
            Console.WriteLine("Draw rectangle...");
        }
    }

    class Create
    {
        public Shape CreateShape(string str)
        {
            Shape s;
            switch (str)
            {
                case "c":
                    s = new Circle();
                    break;
                case "r":
                    s = new Rectangle();
                    break;
                case "t":
                    s = new Triangle();
                    break;
                default:
                    s = new Shape();
                    break;
            }
            return s;
        }
    }

    class Program
    {             
        static void Main(string[] args)
        {            
            Console.WriteLine("Please type a Shape class you want to create: ");
            string strShape = Console.ReadLine();           
            Console.WriteLine("string is: {0}", strShape);            

            Create c = new Create();
            Shape s = c.CreateShape(strShape);
            s.draw();
            Console.ReadLine();
        }
    }
}

In the above example, if we want to add a new class to draw Oval shape, we will have a class like:

class Oval: Shape
{
    public override void draw()
    {
        Console.WriteLine("Draw oval....");
    }
}

We want to be able to ‘plug-in’ this class into our application without having to make any code changes in the original code and call the draw() of Oval class during run time. By using Polymorphism, the only place we need to make the change is our Create method and we leave our client application code untouched.

Understanding polymorphism is key to designing scalable, plug-and-play architecture application.

Copying a DataGridViewRow to another DataGridView Control

Sometimes a simple task can be very tricky. Today I was trying to make a copy of DataGridView to another DataGridView, in the traget DataGridView I need to delete a few rows and possibly add a few new DataGridViewColumn(s), all these changes must have no impact whatsoever on the Original DataGridView object. Because of the nature of C#, object is passed by reference. So I cannot do things like DataGridView dgv_copy = dgv_original if I want to make some changes in the second DataGridView without affecting the original one.

What should I do? Firstly I need to create columns first, populate the DataGridViewColumns from the original to the new one. This is very straight forward, however when it comes to copy the rows. I came across a problem. The DataGridViewRow clone method does not work as expected. The Clone method copies the row and its property values, but does NOT copy the cell values that the row contains. So we have to write some code to copy the cell values. Please find the working example below and feel free to let me know should you have any queries.

private DataGridView CopyDataGridView(DataGridView dgv_org)
{
    DataGridView dgv_copy = new DataGridView();
    try
    {
        if (dgv_copy.Columns.Count == 0)
        {
            foreach (DataGridViewColumn dgvc in dgv_org.Columns)
            {
                dgv_copy.Columns.Add(dgvc.Clone() as DataGridViewColumn);
            }
        }

        DataGridViewRow row = new DataGridViewRow();

        for (int i = 0; i < dgv_org.Rows.Count; i++)
        {
            row = (DataGridViewRow)dgv_org.Rows[i].Clone();
            int intColIndex = 0;
            foreach (DataGridViewCell cell in dgv_org.Rows[i].Cells)
            {
                row.Cells[intColIndex].Value = cell.Value;
                intColIndex++;
            }
            dgv_copy.Rows.Add(row);
        }
        dgv_copy.AllowUserToAddRows = false;
        dgv_copy.Refresh();

    }
    catch (Exception ex)
    {
        cf.ShowExceptionErrorMsg("Copy DataGridViw", ex);
    }
    return dgv_copy;
}

Dynamically display tooltips when mouse hover a DataGridViewCell in WinForm application

In one of my recent projects, a customer asked me to provide a dynamic tool tip depending on the DataGridViewCell that user have his mouse hovered. For a Windows Forms DataGridView, you can handle the dataGridView1.CellToolTipTextNeeded event and dynamically provide the tip text there.

dataGridView1.CellToolTipTextNeeded += new DataGridViewCellToolTipTextNeededEventHandler(data GridView1_CellToolTipTextNeeded);
void dataGridView1_CellToolTipTextNeeded(object sender, DataGridViewCellToolTipTextNeededEventArgs e)
{
    e.ToolTipText = string.Format("tip for row {0}, col {1}", e.RowIndex, e.ColumnIndex);
}

Run the application, you would see a similar screen shot as below:

Populate a Data Bound TreeView in .Net using C#

A nice and clear Treeview navigator can dramatically improve customers’ experience of using our software. Putting the Treeview at left hand side of the screen with two panel at right divided by a horizontal panel splitter, a typical outlook style user interface, has become very popular in business application.

Visual Studio 2008 offers some out-of-the-box features that we can easily create a Treeview control in the form designer without writing much code. However if we need to (Most of time I believe we do) programmatically populate a Treeview menu, particularly if we want to populate a Treeview based on a database table, this does require some codes. In this demo, I am demonstrating how we can populate a Treeview control with database tables. The backend database is Oracle 10g R2, I use the employee table under HR schema. It shouldn’t matter which database you are using, as long as your table or view in hierarchy structure, for instance like:

ID EMP_NAME PARENT_ID
100 Steven
101 Neena 100
102 Lex 100
103 Alexander 102
104 Bruce 103
105 David 103
106 Valli 103
107 Diana 103
108 Nancy 101

- Firstly in the form designer, we drag a Treeview control and make it dock left.
- Drag a Splitter control to the right hand side of the form and make it dock left as shown below.

- Drag a Panel control to the right hand side and make it dock top.
- Drag a Splitter control to the bottom right and make it dock top.
- Drag a third Panel to the bottom right and make it fill the rest of the screen as shown below.

- To populate the TreeViewNode, I am using a recursive function. The tag property is used to store the level of the hierarchy.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace TreeViewDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        DataSet dsTreeView = new DataSet();

        // Populate public DataSet
        private DataSet PopulateDataTable()
        {
            string connectionString = "Data Source=orcl;Persist Security Info=True; "
                + "User ID=hr;Password=hr;Unicode=True";

            string queryString = "select * from employees";

            OracleConnection connection = new OracleConnection(connectionString);
            OracleCommand command = new OracleCommand(queryString, connection);
            OracleDataAdapter da = new OracleDataAdapter(command);

            try
            {
                connection.Open();
                if (!dsTreeView.Tables.Contains("emp"))
                    dsTreeView.Tables.Add("emp");
                da.Fill(dsTreeView, "emp");
            }
            catch (OracleException oex)
            {
                throw new Exception(oex.Message);
            }
            finally
            {
                connection.Close();
            }
            return dsTreeView;
        }
        // The level of tree
        int intLevel = 0;

        // Recursive function to populate Treeview node
        public void FillChildren(TreeNode _Parent, int _ParentID)
        {
            intLevel++;
            DataView Data = new DataView(dsTreeView.Tables["emp"]);
            Data.RowFilter = "manager_id = " + _ParentID;

            // Define the image index in the lowest level
            if (Data.Count == 0)
            {
                _Parent.ImageIndex = 2;
                _Parent.SelectedImageIndex = 2;
            }

            foreach (System.Data.DataRowView foo in Data)
            {
                TreeNode t = BuildNode((string)foo["last_name"]);
                t.Tag = intLevel;
                FillChildren(t, int.Parse(foo["employee_id"].ToString()));
                _Parent.Nodes.Add(t);
                System.Diagnostics.Trace.WriteLine("Added node at level " + intLevel.ToString());
            }
            intLevel--;
        }

        private TreeNode BuildNode(string Text)
        {
            return new TreeNode(Text);
        }

        private void LoadMenuTreeView()
        {
            PopulateDataTable();
            DataView Data = new DataView(dsTreeView.Tables["emp"]);
            Data.RowFilter = "manager_id is null ";
            foreach (System.Data.DataRowView foo in Data)
            {
                TreeNode t = BuildNode((string)foo["last_name"]);
                t.Tag = 0;
                FillChildren(t, int.Parse(foo["employee_id"].ToString()));
                this.treeView1.Nodes.Add(t);
            }

            this.treeView1.ImageList = this.imageList1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadMenuTreeView();
        }

        private void treeView1_AfterExpand(object sender, TreeViewEventArgs e)
        {
            e.Node.ImageIndex = 1;
        }

        private void treeView1_AfterCollapse(object sender, TreeViewEventArgs e)
        {
            e.Node.ImageIndex = 0;
        }       
    }
}

Run the application, we will see a data bound treeview as shown below.

You can easily add some nice features, such as passing a parameter from a treeview to a DataGridView in the right screen or using a status bar at the bottom to display a tree node full path. Hopefully you find it useful. Happy coding.

How to Compare two DataTables in ADO.net

Sometimes it’s very useful to compare two DataTables and return the different records to a third DataTable. I came across this when I needed to handle my own data concurrency control. Basically my logic is:

- Populate a DataTable of a DataSet and Copy the content of the DataTable to another DataTable immediately after this. (For example we populate a DataTable A and then make a copy to DataTable B.)
- When user update the DataTable A in my application, the event handler firstly populate to a DataTable C from the database.
- Comparing the DataTable C with the DataTable B, if they are same means underlying data hasn’t been changed, then proceed the update otherwise user can choose either to overwrite the data or cancel the operation.

Unfortunately you can’t do a straight comparison of two DataTables. For instance if you write something like: DataTable 1 == DataTable2 or DataTable1.GetHashCode() == DataTable2..GetHashCode() or DT1.Equals(DT2), none of this would work. Below is the code I am using to compare to DataTables, pretty self-explanatory. Hopefully you find it helpful.

private void button1_Click(object sender, EventArgs e)   
{   
    // Something to do with the Initialization of the FirstDataTable and SecondDataTable   

     DataTable dt;   
     dt = getDifferentRecords(FirstDataTable, SecondDataTable);   

     if (dt.Rows.Count == 0)   
         MessageBox.Show("Equal");   
     else  
         MessageBox.Show("Not Equal");   
}

#region Compare two DataTables and return a DataTable with DifferentRecords   
 public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)   
 {   
     //Create Empty Table   
     DataTable ResultDataTable = new DataTable("ResultDataTable");   

     //use a Dataset to make use of a DataRelation object   
     using (DataSet ds = new DataSet())   
     {   
         //Add tables   
         ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });   

         //Get Columns for DataRelation   
         DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];   
         for (int i = 0; i < firstColumns.Length; i++)   
         {   
            firstColumns[i] = ds.Tables[0].Columns[i];   
         }   

         DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];   
         for (int i = 0; i < secondColumns.Length; i++)   
         {   
             secondColumns[i] = ds.Tables[1].Columns[i];   
         }   

         //Create DataRelation   
         DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);   
         ds.Relations.Add(r1);   

         DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);   
         ds.Relations.Add(r2);   

         //Create columns for return table   
         for (int i = 0; i < FirstDataTable.Columns.Count; i++)   
         {   
             ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);   
         }   

         //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.   
         ResultDataTable.BeginLoadData();   
         foreach (DataRow parentrow in ds.Tables[0].Rows)   
         {   
             DataRow[] childrows = parentrow.GetChildRows(r1);   
             if (childrows == null || childrows.Length == 0)   
                 ResultDataTable.LoadDataRow(parentrow.ItemArray, true);   
         }   

         //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.   
         foreach (DataRow parentrow in ds.Tables[1].Rows)   
         {   
             DataRow[] childrows = parentrow.GetChildRows(r2);   
             if (childrows == null || childrows.Length == 0)   
                 ResultDataTable.LoadDataRow(parentrow.ItemArray, true);   
         }   
         ResultDataTable.EndLoadData();   
     }   

     return ResultDataTable;   
 }  
#endregion

Delete rows from a DataTable

When I tried to delete DataRows from a DataTable based on some conditions, I wrote the code as below but receive an InvalidOperationException complaining {"Collection was modified; enumeration operation might not execute."}

DataColumn dc = dsData.Tables["tbl"].Columns["order_qty"];                
foreach (DataRow dr in dsData.Tables["tbl"].Rows)
{
     if (dr[dc].ToString() == "0")
         dsData.Tables["tbl"].Rows.Remove(dr);
}

Then I changed my code to:

DataColumn dc = dsData.Tables["tbl"].Columns["order_qty"];                
foreach (DataRow dr in dsData.Tables["tbl"].Rows)
{
     if (dr[dc].ToString() == "0")
         dr.Delete();
}

Everything works fine. Then I went to MSDN and found the explanation of difference between using Delete and Remove method on data table:

There are two methods you can use to delete a DataRow object from a DataTable object: the Remove method of the DataRowCollection object, and the Delete method of the DataRow object. While the Remove method deletes a DataRow from the DataRowCollection, the Delete method only marks the row for deletion. The actual removal occurs when the application calls the AcceptChanges method. By using Delete, you can programmatically check which rows are marked for deletion before actually removing them. When a row is marked for deletion, its RowState property is set to Deleted.

If a row is marked for deletion and you call the AcceptChanges method of the DataTable object, the row is removed from the DataTable. In contrast, if you call RejectChanges, the RowState of the row reverts to what it was before being marked as Deleted.

Obviously my first example won’t work because I used the Remove method which deletes a DataRow from the DataRowCollection within a foreach loop.

How to Hide Rows from DataGridView in WinForm Application?

When you see the title, you may wonder why I am writing this, this is so obvious, we can easily achieve this by writing something like:

foreach (DataGridViewRow dgvr in MyDataGridView.Rows)
{
     if (string.IsNullOrEmpty(dgvr.Cells["sales"].Value.ToString()))
     {
         dgvr.Visible = false;
     }
}

However if you do this, you are more likely to get the error:
InvalidOperationException,
Row associated with the currency manager's position cannot be made invisible.

Why? Because you cannot make modifications to a row at runtime which is bound to a DataSource, unless you suspend the binding on all rows using CurrencyManager object like code shows below:

CurrencyManager cm = (CurrencyManager)BindingContext[dgvBulkSheetDtl.DataSource];
foreach (DataGridViewRow dgvr in MyDataGridView.Rows)
{
     if (string.IsNullOrEmpty(dgvr.Cells["sales"].Value.ToString()))
     {
         cm.SuspendBinding();
         dgvr.Visible = false;
     }
}

Simple isn’t it? One thing you need to be aware is that if your DataGridView allows user to sort the columns, then you have to hide the rows again because the hidden rows will show up again. In this case, what you probably can do is to filter the data out from your data source.

Implement MSChart in a Windows Form Application

In my previous post, I have demonstrated step by step installing MSChart controls, a fantastic tool to generate amazing Charts in both Winform and ASP applications, on Visual Studio 2008. Now it’s time to show some working examples. Using MSChart to display a graph from Database data is very straight forward, the key is the syntax of binding data to a chart control. My demo is tested under Oracle 10 R2 and Visual Studio 2008.

First of all we need create a demo table, let’s say we want to compare the Actual Sales vs Forecast.

create table demo
(
  sku number,
  wcdate date,
  actual number,
  forecast number,
  constraint pk01 primary key (sku, wcdate)
)
/
insert into demo values (24, to_date('08/03/2009', 'dd/mm/yyyy'), 3020, 3200);
insert into demo values (24, to_date('15/03/2009', 'dd/mm/yyyy'), 3131, 3200);
insert into demo values (24, to_date('22/03/2009', 'dd/mm/yyyy'), 2937, 2500);
insert into demo values (24, to_date('29/03/2009', 'dd/mm/yyyy'), 2842, 2500);
insert into demo values (24, to_date('05/04/2009', 'dd/mm/yyyy'), 2988, 2500);
/

Secondly we create a new WinForm application in VS 2008, drag a Char control from tool box and place it at the top section of the form.

Next drag another DataGridView control and place it at the bottom of the form. We will then have something like the screenshot below.

Press F7 to go to the code view, we can then write some code to pull the data from database and bind the data to the chart control:

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace ChartDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        
        DataSet dsdata = new DataSet();
        
        // Populate public DataSet
        private DataSet PopulateDataTable()
        {            
            string connectionString = "Data Source=orcl;Persist Security Info=True; "
                + "User ID=scott;Password=xxxxx;Unicode=True";

            string queryString = "select * from demo";

            OracleConnection connection = new OracleConnection(connectionString);
            OracleCommand command = new OracleCommand(queryString, connection);            
            OracleDataAdapter da = new OracleDataAdapter(command);

            try
            {
                connection.Open();
                if (!dsdata.Tables.Contains("chart"))
                    dsdata.Tables.Add("chart");
                da.Fill(dsdata, "chart");
            }
            catch (OracleException oex)
            {
                throw new Exception(oex.Message);
            }
            finally
            {
                connection.Close();
            }
            return dsdata;
        }

        // Load the data to a DataGridView
        private void LoadDGV()
        {
            dataGridView1.DataSource = PopulateDataTable();
            dataGridView1.DataMember = "chart";
        }

        // Render the chart
        private void LoadFcstVsActualChart()
        {            
            DataView dv = new DataView(dsdata.Tables["chart"]);
            chart1.Series["Actual"].Points.DataBindXY(dv, "wcdate", dv, "actual");
            chart1.Series["Forecast"].Points.DataBindXY(dv, "wcdate", dv, "forecast");
        }

        //Form load event
        private void Form1_Load(object sender, EventArgs e)
        {
            LoadDGV();
            LoadFcstVsActualChart();
        }
    }
}

I believe above code is quite self-explanatory, firstly we created a DataSet and populate the data to the DataSet, then we binded the DataView to the Chart control, at the end we put all methods into Form load event to load data to DataDridView and generate the Chart afterwards. The import thing to remember is the syntax of binding data to a chart.

// Render the chart
private void LoadFcstVsActualChart()
{            
    DataView dv = new DataView(dsdata.Tables["chart"]);
    chart1.Series["Actual"].Points.DataBindXY(dv, "wcdate", dv, "actual");
    chart1.Series["Forecast"].Points.DataBindXY(dv, "wcdate", dv, "forecast");
}

There are loads of different ways we can make our chart prettier to attract more eyeballs. For example you can change the background color or border style in the properties of the chart.

Or you can change the Chart Display type in Series collection. To see the best practice, please refer to Samples Environment for Microsoft Chart Controls from Microsoft

Now you can see how simple is that to use MSChart controls. A few lines of code can do the trick, I have tried MSChart in my last 2 projects, our customers who are so used to traditional dull business applications just love it. I hope you find it useful as well. Let me know if there is any problem or questions.

Step by Step installing MS Chart Controls on Visual Studio 2008

Generating wonderful charts in .Net application sometimes can easily get customers blown away. Microsoft purchased some products from Dundas Company about a year and a half ago and implementing theirs charts technology into the products. And from last September they give us the free charting controls (both Web and WinForms) for use in our .NET 3.5 applications.

Step 1: To get start you need to download and install:
Microsoft Chart Controls for Microsoft .NET Framework 3.5 and Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008.

Step 2: Once you have the installation files downloaded, install MSChart.exe first, as the add-on requires this to be installed. Both are quick installs, and should take no more than a few minutes, so when both have been successfully installed to your machine, you can start up Visual Studio.

Step 3: create a new Web or WinForm Application, and now to get the new control into our toolbox, we can right-click on a blank area of it and select Add Tab. Name it something like MSChart:



Step 4, right-click in the new tab and select Choose Items...



We should then see a dialog box like this:



Step 5: Click the Browse button and navigate to the Program Files\Microsoft Chart Controls\Assemblies folder. Here you want to add both the Windows.Form and the Web DLLs. Once they're added, they will be available to select in the list box:



So once they're added, and checked, they should be available in our toolbox:



After you’ve done the configuration, I strongly recommend you to download the Samples Environment for Microsoft Chart Controls from Microsoft and read the docs: Microsoft Chart Controls for .NET Framework Documentation.

In my next post, I will write a quick demo on how to implement MSChart in a Windows Form Application to display a graph from database data.

Export Data from DataGridView to Excel in C# Windows Forms

A very common requirement as a .Net developer would receive from customer is to export the data from DataGridView to excel. As developers we don’t particularly like Excel because it lacks of centralized control, poor metadata awareness. awkward email/shared network sharing etc. But we have to admit Excel is a great tool and our customers are so used to it, this is not something we should ban customers from using, on the contrary we should help customers to use excel with high quality data. Here is where this requirement coming from. I have been researching on the internet for a stable solution, however I didn’t find one. I found one from here which looks very good but contains a few bugs, I updated the code to make it fully work. It works fine in my environment, feel free to comment if you see a problem.

using System;
using System.Windows.Forms;
using System.Reflection;


namespace Demo
{
    class CommonFunctions
    {
        /// Exports a passed datagridview to an Excel worksheet.
        /// If captions is true, grid headers will appear in row 1.
        /// Data will start in row 2.        
        public static void ExportToExcel(DataGridView datagridview, bool captions)
        {
            object objApp_Late;
            object objBook_Late;
            object objBooks_Late;
            object objSheets_Late;
            object objSheet_Late;
            object objRange_Late;
            object[] Parameters;
            string[] headers = new string[datagridview.ColumnCount];
            string[] columns = new string[datagridview.ColumnCount];

            int i = 0;
            int c = 0;
            int t = 0;
            int j = 0;


            try
            {
                for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                {
                    headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
                    if (c < 26)
                    {
                        t = c;
                        i = t + 65;
                        columns[c] = Convert.ToString((char)i);
                    }
                    else
                    {
                        t = c % 26;
                        j = c / 26 - 1;
                        j = j + 65;
                        i = t + 65;
                        columns[c] = Convert.ToString((char)j) + Convert.ToString((char)i);
                    }
                }
                // Get the class type and instantiate Excel.
                Type objClassType;
                objClassType = Type.GetTypeFromProgID("Excel.Application");
                objApp_Late = Activator.CreateInstance(objClassType);
                //Get the workbooks collection.
                objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks",
                BindingFlags.GetProperty, null, objApp_Late, null);
                //Add a new workbook.
                objBook_Late = objBooks_Late.GetType().InvokeMember("Add",
                BindingFlags.InvokeMethod, null, objBooks_Late, null);
                //Get the worksheets collection.
                objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets",
                BindingFlags.GetProperty, null, objBook_Late, null);
                //Get the first worksheet.
                Parameters = new Object[1];
                Parameters[0] = 1;
                objSheet_Late = objSheets_Late.GetType().InvokeMember("Item",
                BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                if (captions)
                {
                    // Create the headers in the first row of the sheet
                    for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + "1";
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                        BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = headers[c];
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                        null, objRange_Late, Parameters);
                    }
                }

                // Now add the data from the grid to the sheet starting in row 2
                for (i = 0; i < datagridview.RowCount; i++)
                {
                    for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + Convert.ToString(i + 2);
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                        BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                        null, objRange_Late, Parameters);
                    }
                }

                //Return control of Excel to the user.
                Parameters = new Object[1];
                Parameters[0] = true;
                objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
                null, objApp_Late, Parameters);
                objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
                null, objApp_Late, Parameters);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
    }
}

Use of Bind Variables

What is bind variables?
A bind variable is simply a mechanism through which you can create a placeholder for a literal (constant) value within an SQL statement. For example, to retrieve the record for employee id is 198, 199, 200.... we can write SQL like:

SELECT first_name, last_name, email FROM employees WHERE employee_id = 199;
SELECT first_name, last_name, email FROM employees WHERE employee_id = 200;
SELECT first_name, last_name, email FROM employees WHERE employee_id = 201;

To use bind variable, we can use following instead:

SQL> variable emp_id number
SQL> exec :emp_id := 199

PL/SQL procedure successfully completed.

SQL> select first_name, last_name, email from employees where employee_id = :emp_id;

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Douglas              Grant                     DGRANT

SQL> exec :emp_id := 200

PL/SQL procedure successfully completed.

SQL> select first_name, last_name, email from employees where employee_id = :emp_id;

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Jennifer             Whalen                    JWHALEN

SQL>

Why use bind variables?
Every time we submit a brand new sql statement to the database , it must be parsed, qualified, security checked, optimized, and so on. All of these will consume a large amount of CPU. Hence, the fewer new SQL statements that are presented to the system, the better your system will scale and perform. To minimize the number of different SQL statements that are parsed by the database, we must make the use of bind variables. Let’s have a look at the next example. Here I created 2 procedures, proc1 is using bind variables and proc2 is not using bind variable.

drop table demo;
/
create table demo(x int);
/
create or replace procedure proc1 as
begin
  for i in 1 .. 100000 loop
    execute immediate 'insert into demo values (:x)'
      using i;
  end loop;
end;
/
create or replace procedure proc2 as
begin
  for i in 1 .. 100000 loop
    execute immediate 'insert into demo values ( ' || i || ')';
  end loop;
end;
/

Now we use runstats package to compare the cost of above two procedures. For details on setting up runstats, please see my last post.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as appuser

SQL> set serveroutput on size 100000;
SQL> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed

SQL> exec proc1

PL/SQL procedure successfully completed

SQL> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed

SQL> exec proc2

PL/SQL procedure successfully completed

SQL> exec runstats_pkg.rs_stop

Run1 ran in 1637 hsecs
Run2 ran in 3180 hsecs
run 1 ran in 51.48% of the time
 
Name                                  Run1        Run2        Diff
STAT...parse time elapsed                2       1,444       1,442
STAT...parse time cpu                    1       1,481       1,480
STAT...Elapsed Time                  1,638       3,181       1,543
STAT...DB time                         261       1,851       1,590
STAT...recursive cpu usage             218       1,836       1,618
STAT...CPU used when call star         242       1,873       1,631
STAT...CPU used by this sessio         241       1,875       1,634
STAT...bytes received via SQL*       4,428       2,403      -2,025
LATCH.shared pool sim alloc              0       2,109       2,109
LATCH.messages                         535       3,204       2,669
STAT...redo size                23,705,976  23,701,772      -4,204
LATCH.simulator lru latch            7,476     107,418      99,942
STAT...calls to get snapshot s         139     100,130      99,991
STAT...recursive calls             101,212     201,203      99,991
STAT...enqueue requests                 56     100,049      99,993
STAT...enqueue releases                 56     100,049      99,993
STAT...parse count (total)              53     100,048      99,995
STAT...parse count (hard)               10     100,007      99,997
LATCH.simulator hash latch           7,671     107,720     100,049
STAT...consistent gets                 685     100,802     100,117
STAT...consistent gets from ca         685     100,802     100,117
STAT...db block gets               102,757     302,556     199,799
STAT...db block gets from cach     102,757     302,556     199,799
LATCH.enqueues                         376     200,713     200,337
LATCH.enqueue hash chains              424     200,844     200,420
STAT...session logical reads       103,442     403,358     299,916
STAT...physical read bytes          32,768     352,256     319,488
STAT...physical read total byt     360,448     679,936     319,488
LATCH.kks stats                        483     552,311     551,828
STAT...session pga memory          131,072    -458,752    -589,824
LATCH.library cache lock               456     600,524     600,068
LATCH.library cache pin                455     600,540     600,085
LATCH.cache buffers chains         511,275   1,111,634     600,359
LATCH.row cache objects                871   1,201,081   1,200,210
LATCH.shared pool simulator             60   1,792,124   1,792,064
LATCH.library cache                  1,238   2,569,396   2,568,158
LATCH.shared pool                    1,181   3,413,797   3,412,616
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     545,818  12,481,378  11,935,560      4.37%

PL/SQL procedure successfully completed

SQL>

Now, you can see the proc1 is the clear winner, which took significantly less time to run also with much less latches.

When to use bind variables?
Do we need to use bind variables explicitly in our PL/SQL store procedures? The answer may surprise you? Most of time we don’t need to, because every reference to a PL/SQL variable is in fact a bind variable. This is just how PL/SQL works. Want a prove? Look at the next example:

create or replace procedure find_emp(p_empno in number) as
  type rc is ref cursor;
  l_cursor rc;
begin
  open l_cursor for
    select * from emp where empno = p_empno;
end;
/

Execute the procedure and trace the execution plan using TKPROF.

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Feb 8 23:53:32 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set timed_statistics = true;

Session altered.

SQL> alter session set sql_trace = true;

Session altered.

SQL> exec find_emp(7839);

PL/SQL procedure successfully completed.

SQL> SELECT pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||      
  2         '_ora_' || p.spid || '.trc' AS trace_file  
  3  FROM   v$session s,  
  4         v$process p,  
  5         v$parameter pa  
  6  WHERE  pa.name = 'user_dump_dest'  
  7  AND    s.paddr = p.addr  
  8  AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 

TRACE_FILE
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_4328.trc

SQL> alter session set sql_trace = false;

Session altered.

SQL>

Use TKPFRO generate SQL trace report: Please refer here for detail set up.

D:\> tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_4328.trc d:\trcout.txt
Tkprof shows us:

SELECT * 
FROM
 EMP WHERE EMPNO = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.02          0          1          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.02          0          1          0           0

ALL plsql variable references were bound. However it is not always the case, the only time we need to specifically use bind variables is when you're putting together dynamic SQL. For example, when we put DML statement after ‘execute immediate’ in plsql program.

-- Not using Bind Variable. Hard parse required when exec the procedure each time
create or replace procedure no_bv(p_empno number) as
begin
  execute immediate 'update emp set sal = sal*2 where empno = ' || p_empno;
end;
/
-- Bind Variable, no hard parse required
create or replace procedure bv(p_empno number) as
begin
  execute immediate 'update emp set sal = sal*2 where empno = :x '
    using p_empno;
end;
/

When not to use bind variables?
Every rule has its exception. In performance tuning world, nothing is absolute. In a data warehouse environment, it’s normal you would have a bunch of SQL queries which can take seconds or minutes or even hours to run, because most of data warehouse jobs are running over night, so nobody is actually waiting for the result of each query, users are waiting for all queries to finish getting the answer. In this scenario, the overhead of parsing time is a tiny propotion of the overall execution time. In data warehouse system, the goal is to get the best query optimization plan possible to reduce the runtime, not to execute as many queries as possible like we do in OLTP system. In this case, not to use bind variables could allow CBO to see actual literal so that it can come up with a optimal plan for your query.

Oracle Performance Analysis Tool – SQL_Trace and TKPROF

As a DBA/Developer we often hear users complaining that certain part of the web or desktop applications run too slow, it would be very helpful if we are able to trace an Oracle session to pinpoint the root cause. Here is when SQL_Trace and TKPROF utility come to play. Unlike switching on AUTOTRACE in sql plus to test a single query in isolation, TKPROF shows you a different set of data for all sql statments executed by your application as it runs.

What is SQL_Tace and TKPROF? In short SQL_TRACE is the used to write performance information on individual SQL statement down to trace files in the file system of the database server. These trace files are not in a proper readable format, for that reason we need TKPROF utility to transform the trace file into a text-based report for performance analysis.

To be able to run the following simulation test, we need to create a user and grant alter session directly to that user.

drop user demo cascade;
create user demo identified by demo;
grant connect, resource to demo;
grant alter session to demo;
grant all on plan_table to demo;
grant select on v_$session to demo;
grant select on v_$process to demo;
grant select on v_$parameter to demo;

Create a test table and a package to simulate a typical work load.

create table test
(
  col1 varchar2(30)
);
/
CREATE OR REPLACE procedure simulate_workload_with_binds as
  var1 varchar2(30);
begin  
  -- This parameter enables the collection of certain vital statistics 
  -- such as CPU execution time, wait events, and elapsed times. 
  execute immediate 'alter session set timed_statistics = true';
  -- Set the maximum size of trace file as unlimited
  execute immediate 'alter session set max_dump_file_size = unlimited';
  -- Set a suffix of the trace file
  execute immediate 'alter session set tracefile_identifier = ''test1_binds_single_process''';
  -- turn on extended SQL tracing
  execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
  
  -- main body of code  
  for c in 1 .. 500 loop
    insert into test (col1) values (c);
    commit;
  end loop;
  commit;

  for c in 1 .. 50 loop
    update test set col1 = col1;
    commit;
  end loop;

  for c in 1 .. 500 loop
    begin
      select col1 into var1 from test where col1 = c;
    exception
      when others then
        null;
    end;
  end loop;
  
  for c in 1 .. 500 loop
    insert into test (col1) values (c);
    commit;
  end loop;
  commit;
  
  for c in 1 .. 500 loop
    delete from test where col1 = c;
    commit;
  end loop;
  commit;  
  -- turn off extended SQL tracing 
  execute immediate 'alter session set events ''10046 trace name context off''';
end;

The trace files will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value by issuing a following statement:

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Use TKPROF utility to generate report:

D:\>tkprof D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_8288_test1_binds_single_process.trc d:\trcout.txt.

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       28      0.00       0.72          0          1          0           0
Execute   3625      0.48       0.47          0       3880       8123       27000
Fetch      545      0.10       0.07          0       3578          0         533
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4198      0.59       1.27          0       7459       8123       27533

Misses in library cache during parse: 18
Misses in library cache during execute: 16

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  undo segment extension                          1        0.00          0.00
  log file sync                                   1        0.00          0.00

   17  user  SQL statements in session.
   17  internal SQL statements in session.
   34  SQL statements in session.
********************************************************************************
Trace file: D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_8288_test1_binds_single_process.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
      17  user  SQL statements in trace file.
      17  internal SQL statements in trace file.
      34  SQL statements in trace file.
      20  unique SQL statements in trace file.
    5981  lines in trace file.
       1  elapsed seconds in trace file.

A list of key elements to analyse TKPROF report:

1. Compare the number of parses to number of executions. A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement.

2. Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.

3. Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption. These performance benchmarks are defined by the DBA and need to be tailored to each database. What may be considered a high number of disk reads for an OLTP application may not even be minimal for a data warehouse implementation.