HttpHandlers and HttpModules in ASP.NET

In order to understand the concept of HttpHandlers and HttpModules in ASP.NET, we must need to understand first, how our web server and ASP.NET collectively reacts when a user requests for a particular resource? Our web server i.e. IIS does the following:

• It looks for the extension of the requested resource file e.g. (.aspx, .ascx etc.).
• It looks for ISAPI extension mapped against this file extension.
• If found a mapping, then pass request to that particular ISAPI extension.

So, For example, in case of a resource Page1.aspx file, web server will forward that request to ASP.NET Engine through ISAPI extension (aspnet_isapi.dll). At high level, ASP.NET Engine is a kind of a HTTP Pipeline because HTTP request moves through a number of HttpModules and finally reaches a particular HTTP Handler as shown in following figure.

Now, by default, request for an ASP.NET page is handled by a HttpHandler called "ASP.NET Page Handler". Also, this request passes through a number of build-in HttpModules like OutputCache, Session, WindowsAuthentication, FormsAuthentication, RoleManager etc.

Now, we can define both of these concepts as:

HTTPHandler:

ASP.NET Engine uses HttpHandlers to handle specific requests on the basis of their extensions. ASP.NET Page Handler handles all requests coming for (.aspx) pages. We can define our own custom HttpHandler to handle a specific request with a specific extension, say .jpeg, .gif, or .imran. But there will always be only one handler for a specific request.

HTTPModules:

ASP.NET Engine uses HttpModules to inject some specific functionality alongwith ASP.NET default functionality for all incoming requests regardless of its extensions. There are a number of built-in modules already available in ASP.NET HTTP Pipeline as we discussed earlier. But we can write our own custom HTTP module to perform some additional functionality (for example, URL rewriting or implementing some security mechanism) for all incoming requests.

Advantages of hosting WCF Services in IIS as compared to self-hosting?

What are the advantages of hosting WCF Services in IIS as
compared to self-hosting?
There are two main advantages of using IIS over self-hosting:-
Automatic activation
IIS provides automatic activation that means the service is not necessary to be running in
advance. When any message is received by the service it then launches and fulfills the request.
But in case of self hosting the service should always be running.
Process recycling
If IIS finds that a service is not healthy that means if it has memory leaks etc, IIS recycles the
process. For every browser instance, a
worker process is spawned and the request is serviced. When the browser disconnects the worker,
process stops and you lose all information. IIS also restarts the worker process. By default, the
worker process is recycled at around 120 minutes. So why does IIS recycle. By restarting the
worker process it ensures any bad code or memory leak do not cause issue to the whole system.
In case of self-hosting both the above features, you will need to code yourself. Lot of work
right!!.

Cursor in SQL

A Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.
You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.
 

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.

Life Cycle of Cursor

1. Declare Cursor

   A cursor is declared by defining the SQL statement that returns a result set.
   

2. Open

   A Cursor is opened and populated by executing the SQL statement defined by the cursor.
   

3. Fetch

   When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
   

4. Close

   After data manipulation, we should close the cursor explicitly.

5. Deallocate

   Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

 Simple Exp .

declare @BrhCode varchar(5)
declare @BrhName varchar(50)
DECLARE TBName CURSOR
For
select BrhCode,BrhName from branch_master
Open TBName

FETCH NEXT FROM TBName into @BrhCode,@BrhName
WHILE @@FETCH_STATUS = 0
    BEGIN
        --print @BrhCode
        Insert into user_module_Detail values('Renewal Receipt', @BrhCode,'Transaction','A')
        FETCH NEXT FROM TBName into @BrhCode,@BrhName
    END

CLOSE TBName
DEALLOCATE TBName

Types of Cursors

1. Static Cursors
   A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.
   You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.
   

2. Dynamic Cursors

   A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
   

3. Forward Only Cursors

   A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.
   There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.
   
   A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
   A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.
   

4. Keyset Driven Cursors

   A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.
   

SQL SERVER – Examples of Cursors

 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 

Static Cursor - Example

 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Dynamic Cursor - Example

 --Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Mohan'
 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Deepak'
 DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee 

Forward Only Cursor - Example

 --Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Sonu'
 DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
 Go
Select * from Employee 

Keyset Driven Cursor - Example

 -- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empupdate CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Pavan'
 Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 END
END
CLOSE Keyset_cur_empupdate
DEALLOCATE Keyset_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Keyse Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empdelete CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
 Go Select * from Employee 

How to optimize performance of a stored procedure?

Well, There are many cases where our query are working fine there are few records. But the same query is performing in very bad manner when there are huge records. for example : lacs of records or more..

In such case, we can scan our query/SP for following points to increase the performance of our Query/SP.

1) Always specify column name while selecting the records from table and never write query
like
  select * from Table_Name
instead it should be
 select column_name1, column_name2, column_name3... from Table_Name

2) while checking existence of records, never use query like below...

 if exists(select * from Table_Name where column_name = @Value)
 begin
 end
Instead it should be

if exists(select 1 from Table_Name where column_name = @Value)
begin
end

3) While selecting records, never forget to write SET NOCOUNT ON.

4) Remove all unnecessary joins.

5) Never use aggregate functions while selecting the records, Instead select it once, store it somewhere like temporary table and then do all your operations. It will definitely improve performance of the query.