Monthly Archives: February 2012

BackgroundWorker Intro Part 1 – Work with ProgressBar

Winform application usually need asynchronous process. Progress status of this process commonly showed with progressbar control.
As a default Thread class accomplish the asynchronous process but .Net has built in BackgroundWorker component to do async easily.

BackgroundWorker component/class has events which are DoWork, ProgressChanged, RunWorkerCompleted.
DoWork handles main async job. ProgressChanged handles displaying async progress info to user. RunWorkerCompleted handles event when async process is completed.

This BackgroundWorker has Cancel ability, so that user can cancel asnyc in the middle of its process. Continue reading

Introduction to Asp .Net Ajax – 2 (UpdateProgress)

Ajax makes rich web application. Users don’t have to reload page in order to get response from server. Asynchronous proccess is a key to Ajax.
With Asp .Net Ajax building ajax enabled web app become easier. Built in controls like ScriptManager, UpdatePanel, ContentTemplate, Triggers, and UpdateProgress
In earlier post (Introduction to Asp .Net-1) I have given an example on how to use Built in Asp .Net Controls. In this post I will give example of UpdateProgress Control.

UpdateProgress Control
When we run on long query process or file upload/download operation or some complex calculation, we need to inform users about progress status since the page is not reloading.
Asp .Net Built in UpdateProgress Control informs progress to users asynchronously. It has ProgressTemplate Tag inside Control’s Tag. We can write progress information inside ProgressTemplate Tag. Continue reading

Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function

Introduction
Retrieve parts of recordset on web request is a very common task. Trivial method using SqlDataSource as Data Connector and GridView or List View with Data Pager Control to view recordset and its page.

Web request resulting million records will degrade performance even though developer break down onto pages. According to some experiments there is a better way than usual method to do web paging.
Please check these links http://www.4guysfromrolla.com/articles/031506-1.aspx, http://www.4guysfromrolla.com/webtech/042606-1.shtml

The best performance using record ranking method and fortunately start from SQL Server 2005 it has record ranking function Row_Number().
In earlier post about SQL Server Row_Number() function, I have explained how to code and the function’s result on sql.

To do custom paging on web, we need ObjectDataSource because it has attributes like EnablePaging, SelectMethod, SelectCountMethod, MaximumRowsParameterName, etc which are enabled us to write a code / class that handles web paging behaviour.
We can write any database operation on methods inside ObjectDataSource so that makes ObjectDataSource flexible.
Other common component on ASP .Net like SqlDataSource is only work for SQL Server and does not has default custom paging capabilities.
Continue reading

Limit Record Number on SQL Server 2005 using Row_number() function

SQL Server has capability to show sequence record number on ‘select’ statement. This also referred as record ranking. The function is Row_number() over (order by <columnname>).
The order of sequence relate to specific column name using Order by columnname part. The order can be ascending or descending.

I use AdventureWorks on SQL Server 2005 for demonstration purpose. The sql code will show Store Type Customer info like Store Name, Territory and Sales Value.
Here’s the sql code:

select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name order by Sales.Store.Name

It declare Rowno column as a sequence record number and its order is ascending Store Name.

Try with descending order of Store Name:

select row_number() over(order by Sales.Store.Name desc) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
order by StoreName

We will see the sequence order is descending.

So how to limit record with row_number() function? We can use above sql code and declare it as a table and use that table to filter using rowno column.
For example we want to show first record until 50th use below code:

select * from (
select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
) as tbl where rowno between 1 and 50
order by StoreName

As you can see that above query is declared as tbl table and it use a where and between clause to filter rows.
Try with different parameters i.e between 51 and 100.

This sql code is usefull to break one large recordset into several recordsets. Retrieve large recordset consumes a lot of resources and time on web request. We can use this sql method to devide one large web page into pages.

Cheers,
Agung Gugiaji

Introduction To Asp .Net Ajax (ScriptManager, UpdatePanel, ContentTemplate, Triggers)

Quoting a simple definition of Ajax from w3school site “AJAX = Asynchronous JavaScript and XML.
AJAX is not a new programming language, but a new way to use existing standards.
AJAX is the art of exchanging data with a server, and update parts of a web page – without reloading the whole page.”
Source: http://www.w3schools.com/ajax/default.asp.

Ajax is a web client script so it can work with all server side scripting. Asp .Net with Visual Studio 2010 provides easy way to create Ajax application.
Some of main Component of Ajax are ScriptManager, UpdatePanel, ContentTemplate, Triggers. Continue reading

SQL Server Backup and Restore strategy – Part 2

Continuing from my earlier post about backup strategy I will explain on how to restore database from full, differential and log backups.

Assuming we have four backup files on disk which are testdb_full.bak, testdb_differential.bak, testdb_log_1.bak, testdb_log_2.bak. These files were created during backup processes with its sequence are full, differential, first log and second log.
We name the database with ‘TestDB’.

First of all open SQL Server Management Studio and restore the full backup with norecovery option with below syntax:
Continue reading

PHP Example Retrieve DB2 Records Per Page

Retrieving large recordset contains so many rows on web page will consume a lot of resources both server and client. Responsiveness of web page decreasing.

We need to retrieve that recordset part by part and display it using web paging method. In my previous post about Limiting DB2 Record Number I had show sql example to limit large recordset on SAMPLE Database.

This php example show employee’s related project:
Continue reading