Thursday, 31 December 2009

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

Wednesday, 14 October 2009

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.

Saturday, 3 October 2009

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.

Tuesday, 29 September 2009

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);

Wednesday, 23 September 2009

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' );

Thursday, 13 August 2009

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:

Friday, 24 July 2009

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:

Tuesday, 21 July 2009

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

Friday, 5 June 2009

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.

Monday, 1 June 2009

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:

Saturday, 16 May 2009

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.

Thursday, 7 May 2009

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

Wednesday, 29 April 2009

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.

Friday, 17 April 2009

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.

Friday, 3 April 2009

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.

Friday, 6 March 2009

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");
}
}
}
}

Tuesday, 10 February 2009

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.

Friday, 6 February 2009

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.

Wednesday, 4 February 2009

Oracle Performance Analysis Tool – Runstats

A few years ago I learnt this package from Tom Kyte’s famous book, Expert one to one. The package has become as my first option when I need to benchmark a number of different solutions since then. Basically RUNSTATS is a simple test tool that allows comparison of two executions of code and displays the costs of each in terms of the elapsed time, session-level statistics (such as parse calls), and latching differences. The latter of these, latching, is the key piece of information that this tool provides.

In this entry I have included two quick demos on how to install and test RUNSTATS package in Oracle. My working environment is Oracle 10g release 2 on IBM AIX, my oracle client is SQL*Plus: Release 10.2.0.1.0 on Windows XP Proffessional Console.

Before we start to install the package, we need to make sure the user have access to V$STATNAME, V$MYSTAT, and V$LATCH. You must be granted direct SELECT privileges (not via a role) on SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH.

drop user demo cascade;
create user demo identified by demo;
grant connect, resource to demo;
grant create view to demo;
grant select on v_$statname to demo;
grant select on v_$mystat to demo;
grant select on v_$latch to demo;
grant select on v_$timer to demo;
conn demo/demo@orcl

What the following script do are:
- Create a view to gather the statistics
- Create a temporary table to store the statistics.
- Runstats package.

create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer;

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/

create or replace package body runstats_pkg
as

g_start number;
g_run1 number;
g_run2 number;

procedure rs_start
is
begin
delete from run_stats;

insert into run_stats
select 'before', stats.* from stats;

g_start := dbms_utility.get_time;
end;

procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);

insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;

end;

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);

dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
dbms_output.put_line( chr(9) );

insert into run_stats
select 'after 2', stats.* from stats;

dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '999,999,999' ) ||
to_char( c.value-b.value, '999,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
-- and (c.value-a.value) > 0
and abs( (c.value-b.value) - (b.value-a.value) )
> p_difference_threshold
order by abs( (c.value-b.value)-(b.value-a.value))
) loop
dbms_output.put_line( x.data );
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line
( 'Run1 latches total versus runs -- difference and pct' );
dbms_output.put_line
( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

for x in
( select to_char( run1, '999,999,999' ) ||
to_char( run2, '999,999,999' ) ||
to_char( diff, '999,999,999' ) ||
to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
)
) loop
dbms_output.put_line( x.data );
end loop;
end;

end;
/

Compare the performance between the HEAP table and IOT table:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as demo

SQL>
SQL> create table heap as select * from dual;

Table created
SQL> create table iot(dummy primary key)
2 organization index
3 as select * from DUAL;

Table created
SQL> analyze table HEAP compute statistics;

Table analyzed
SQL> analyze table IOT compute statistics;

Table analyzed
SQL> set serveroutput on size 1000000;
SQL> exec RUNSTATS_PKG.rs_start;

PL/SQL procedure successfully completed
SQL> declare
2 x varchar(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x from heap;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed
SQL> exec RUNSTATS_PKG.rs_middle;

PL/SQL procedure successfully completed
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x from iot;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed
SQL> exec RUNSTATS_PKG.rs_stop;

Run1 ran in 151 hsecs
Run2 ran in 110 hsecs
run 1 ran in 137.27% of the time

Name Run1 Run2 Diff
LATCH.session timer 1 0 -1
LATCH.session allocation 3 2 -1
LATCH.session idle bit 38 37 -1
LATCH.library cache lock alloc 1 2 1
LATCH.simulator hash latch 2 3 1
LATCH.active checkpoint queue 1 0 -1
STAT...free buffer requested 5 6 1
STAT...calls to kcmgcs 4 5 1
STAT...calls to kcmgas 0 1 1
STAT...redo entries 9 10 1
STAT...redo ordering marks 0 1 1
LATCH.KWQP Prop Status 1 0 -1
LATCH.KMG MMAN ready and start 1 0 -1
LATCH.job_queue_processes para 1 0 -1
LATCH.OS process allocation 0 1 1
LATCH.channel operations paren 6 8 2
STAT...bytes received via SQL* 1,021 1,019 -2
STAT...session cursor cache hi 5 7 2
LATCH.bufq statistics 2 0 -2
LATCH.Memory Queue 2 0 -2
LATCH.kks stats 6 4 -2
LATCH.In memory undo latch 2 0 -2
LATCH.undo global data 0 2 2
LATCH.redo allocation 2 0 -2
LATCH.redo writing 1 3 2
LATCH.object queue header oper 9 7 -2
LATCH.simulator lru latch 1 3 2
STAT...db block changes 47 43 -4
STAT...enqueue releases 7 3 -4
STAT...enqueue requests 8 3 -5
STAT...consistent changes 37 32 -5
LATCH.kwqbsn:qsga 6 0 -6
LATCH.qmn task queue latch 6 0 -6
LATCH.active service list 7 0 -7
LATCH.library cache pin 20,116 20,109 -7
LATCH.Shared B-Tree 8 0 -8
LATCH.shared pool 10,081 10,073 -8
LATCH.messages 14 6 -8
STAT...db block gets from cach 39 30 -9
STAT...db block gets 39 30 -9
STAT...workarea memory allocat -14 -4 10
STAT...recursive cpu usage 58 46 -12
LATCH.row cache objects 21 33 12
LATCH.library cache lock 96 81 -15
LATCH.library cache 20,227 20,212 -15
STAT...CPU used when call star 64 48 -16
STAT...CPU used by this sessio 64 48 -16
STAT...undo change vector size 2,140 2,160 20
LATCH.checkpoint queue latch 9 32 23
LATCH.JS queue state obj latch 30 0 -30
STAT...DB time 156 116 -40
STAT...Elapsed Time 162 120 -42
STAT...redo size 2,892 2,940 48
LATCH.SQL memory manager worka 6 73 67
LATCH.enqueues 80 9 -71
LATCH.enqueue hash chains 79 8 -71
STAT...index scans kdiixs1 0 10,000 10,000
STAT...table scan rows gotten 10,000 0 -10,000
STAT...table scans (short tabl 10,000 0 -10,000
STAT...no work - consistent re 10,001 1 -10,000
STAT...shared hash latch upgra 0 10,000 10,000
STAT...table scan blocks gotte 10,000 0 -10,000
STAT...consistent gets from ca 30,009 10,010 -19,999
STAT...consistent gets 30,009 10,010 -19,999
STAT...calls to get snapshot s 30,005 10,004 -20,001
STAT...session logical reads 30,048 10,040 -20,008
LATCH.cache buffers chains 60,183 20,137 -40,046
STAT...session uga memory max 130,816 65,408 -65,408
STAT...session pga memory max 131,072 0 -131,072
STAT...session pga memory -196,608 0 196,608

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
111,061 70,857 -40,204 156.74%

PL/SQL procedure successfully completed

SQL>

From the test result we can easily find out the IOT performs much less latching and runs a lot faster than the heap.

RUNSTATS is a very handy tool when we need to compare a number of different solutions. I recommend every Oracle DBA or developer have it installed in your environment.