ASHX HTTP Handler to read online website in ASP.NET



SQLHub.com basically focuses on SQL Server and related technology even sometime when I feel something in .NET very special, I would like to share with my blog readers.
Generally in ASP.NET we used to work with .ASPX file most. ASPX file is heart of ASP.NET technology. Basically ASPX file represent HTML kind of representation which used to bind with ASPX.CS file which is largely known as code-behind file. When you run ASPX file, it compiles code-behind and display the results but have you ever think that you could bypass code-behind file?
ASXH (HTTP Handler) is really very powerful but underutilized utility. Even if you will search GOOGLE, you won’t find much example. I tried to GOOGLE this topic and found mostly one example of retrieving images with HTTP Handler.  HTTP Handler is not limited to retrieve images only but it is really VERY powerful tool to use.
I thought to present different example of HTTP Handler so I prepared one small demo of it which read the URL given in that.
NOTE: this is just a very BASIC demo; you can customize the code and can use it for your own purpose. It has lots of scope to improvement but the intention of this article is to show you different usage of HTTP Handler only.
Please follow the below given steps to make one sample example.
1.)    Create new project in VS 2008, steps are: File->New->Project->Visual C#->Asp.Net web application
2.)    Right click on website name in solution explorer and click one Add->New Item->Generic Handler
3.)    Give the name “Handler.ashx” to the file and click on “Add” button.
4.)    Copy and paste the below given code in your Handler.ashx.cs file. (You should already have Default.aspx file in your website but we don’t want to add anything in that, rather we will execute that page only when we will finish code)
using System;
using System.Web;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;
public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        const string ROOT = http://www.SQLHub.com”;
        string url = context.Request.QueryString.ToString();
        CookieContainer CC = new CookieContainer();
        HttpWebRequest Req = (HttpWebRequest)WebRequest.Create(ROOT + HttpUtility.UrlDecode(url));
        Req.CookieContainer = CC;
        try
        {
            WebResponse webResponse = Req.GetResponse();
            string sTxt = new System.IO.StreamReader(webResponse.GetResponseStream(),
                System.Text.Encoding.UTF8).ReadToEnd();
            webResponse.Close();
            context.Response.ContentType = webResponse.ContentType;
            context.Response.ContentEncoding = System.Text.Encoding.UTF8;
            context.Response.Write(sTxt);
        }
        catch
        {
            context.Response.Redirect(url);
        }
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}
5.)    Go to your web.config file, add following one line under Configuration->System.Web->httpHandler

<add verb=* path=Default.aspx type=Handler/>
6.)    That’s it; you are now ready to run your application without even writing single line of code in your default.aspx. Open your Default.aspx page by double clicking on it from solution explorer and run your website by pressing F5 key.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

ASHX HTTP Handler to read online website in ASP.NET



SQLHub.com basically focuses on SQL Server and related technology even sometime when I feel something in .NET very special, I would like to share with my blog readers.
Generally in ASP.NET we used to work with .ASPX file most. ASPX file is heart of ASP.NET technology. Basically ASPX file represent HTML kind of representation which used to bind with ASPX.CS file which is largely known as code-behind file. When you run ASPX file, it compiles code-behind and display the results but have you ever think that you could bypass code-behind file?
ASXH (HTTP Handler) is really very powerful but underutilized utility. Even if you will search GOOGLE, you won’t find much example. I tried to GOOGLE this topic and found mostly one example of retrieving images with HTTP Handler.  HTTP Handler is not limited to retrieve images only but it is really VERY powerful tool to use.
I thought to present different example of HTTP Handler so I prepared one small demo of it which read the URL given in that.
NOTE: this is just a very BASIC demo; you can customize the code and can use it for your own purpose. It has lots of scope to improvement but the intention of this article is to show you different usage of HTTP Handler only.
Please follow the below given steps to make one sample example.
1.)    Create new project in VS 2008, steps are: File->New->Project->Visual C#->Asp.Net web application
2.)    Right click on website name in solution explorer and click one Add->New Item->Generic Handler
3.)    Give the name “Handler.ashx” to the file and click on “Add” button.
4.)    Copy and paste the below given code in your Handler.ashx.cs file. (You should already have Default.aspx file in your website but we don’t want to add anything in that, rather we will execute that page only when we will finish code)
using System;
using System.Web;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;
public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        const string ROOT = http://www.SQLHub.com&#8221;;
        string url = context.Request.QueryString.ToString();
        CookieContainer CC = new CookieContainer();
        HttpWebRequest Req = (HttpWebRequest)WebRequest.Create(ROOT + HttpUtility.UrlDecode(url));
        Req.CookieContainer = CC;
        try
        {
            WebResponse webResponse = Req.GetResponse();
            string sTxt = new System.IO.StreamReader(webResponse.GetResponseStream(),
                System.Text.Encoding.UTF8).ReadToEnd();
            webResponse.Close();
            context.Response.ContentType = webResponse.ContentType;
            context.Response.ContentEncoding = System.Text.Encoding.UTF8;
            context.Response.Write(sTxt);
        }
        catch
        {
            context.Response.Redirect(url);
        }
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}
5.)    Go to your web.config file, add following one line under Configuration->System.Web->httpHandler

<add verb=* path=Default.aspx type=Handler/>
6.)    That’s it; you are now ready to run your application without even writing single line of code in your default.aspx. Open your Default.aspx page by double clicking on it from solution explorer and run your website by pressing F5 key.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Delete Duplicate records or Select Duplicate records in SQL Server 2008/2005/2000

People may face problem in selecting or deleting duplicate records from database. This is not a new problem; every SQL Developer may face this kind of situation at least once in his/her careers. Now a days, especially after SQL Server 2005, it is becoming really easy to find duplicate records and delete it with the help of CTE (Common Table Expression), basically CTE is introduced in SQL Server 2005 version so long way back in SQL Server 2000, there was no CTE and people were using logical trick to overcome this issues.

I have already written articles on that topic which I want to share with you today.

Click here to look at the trick which works in SQL Server 2000/2005/2008.
Click Here to look at the new method with CTE which should work in SQL Server 2005+ version only.

I see many of the .NET developer still now aware with these kinds of techniques and facing problem due to unawareness. This is the main reason I wrote follow up post for my past two articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

QUOTENAME function and dynamic T-SQL in Microsoft SQL Server 2008

In one of my recent project I was working with dynamic SQL building and I had to use QUOTENAME function. QUOTENAME function is really very useful and sometime it is mandatory to use in SQL Server 2008/2005/2000.

According to MSDN QUOTENAME Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier.

Let us see first how it works and then I will let you know how much it will be useful in dynamic SQL.

select QUOTENAME(‘ritesh shah’)
–result would be
–[ritesh shah]
GO
select QUOTENAME(‘ritesh shah’,‘”‘)
–result would be
–“ritesh shah”
GO
After looking at above T-SQL and its results, question might pops up in your mind that it nothing just padding Square Bracket to the string by default and if you give any special character for covering your text, it is covering your text with that special character than where question comes about usefulness in dynamic SQL.
Well let me have one example script here first.

–CREATE one sample database to use
Create Database QuoteNameDemoDB
GO
USE QuoteNameDemoDB
GO
–create one sample table
CREATE TABLE SQL HUB
(
      ID INT
)
GO
–while generating above table, you will get an error.
–Msg 102, Level 15, State 1, Line 1
–Incorrect syntax near ‘HUB’.
–the main reason for error is, blank space is not allowed
–in table name so I will do something like this script now.
CREATE TABLE [SQL HUB]
(
      ID INT
)
–table got created successfully
GO
–now let us try to access that table
SELECT * FROM SQL HUB
–AGAIN error in this statement as we can’t use space.
–Msg 208, Level 16, State 1, Line 1
–Invalid object name ‘SQL’.
–so now, you have to use either of the below option.
–1.)
SELECT * FROM “SQL HUB”
–2.)
SELECT * FROM [SQL HUB]
–Now suppose you want to create dynamic SQL Script which execute
–SELECT statement with every table of database, eg:
select ‘SELECT * FROM ‘ + name from sys.objects where type_desc=‘USER_TABLE’
–when you generate dynamic SQL from above script, copy it and try to execute it.
–you will face error because still there is a space between SQL and Hub.
–you can modify above query with QUOTENAME and you will be ok.
select ‘SELECT * FROM ‘ + QUOTENAME(name) from sys.objects where type_desc=‘USER_TABLE’
–use master
–DROP DATABASE QUOTENAMEDEMODB
–GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

QUOTENAME function and dynamic T-SQL in Microsoft SQL Server 2008

In one of my recent project I was working with dynamic SQL building and I had to use QUOTENAME function. QUOTENAME function is really very useful and sometime it is mandatory to use in SQL Server 2008/2005/2000.

According to MSDN QUOTENAME Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier.

Let us see first how it works and then I will let you know how much it will be useful in dynamic SQL.

select QUOTENAME(‘ritesh shah’)
–result would be
–[ritesh shah]
GO
select QUOTENAME(‘ritesh shah’,‘”‘)
–result would be
–“ritesh shah”
GO
After looking at above T-SQL and its results, question might pops up in your mind that it nothing just padding Square Bracket to the string by default and if you give any special character for covering your text, it is covering your text with that special character than where question comes about usefulness in dynamic SQL.
Well let me have one example script here first.

–CREATE one sample database to use
Create Database QuoteNameDemoDB
GO
USE QuoteNameDemoDB
GO
–create one sample table
CREATE TABLE SQL HUB
(
      ID INT
)
GO
–while generating above table, you will get an error.
–Msg 102, Level 15, State 1, Line 1
–Incorrect syntax near ‘HUB’.
–the main reason for error is, blank space is not allowed
–in table name so I will do something like this script now.
CREATE TABLE [SQL HUB]
(
      ID INT
)
–table got created successfully
GO
–now let us try to access that table
SELECT * FROM SQL HUB
–AGAIN error in this statement as we can’t use space.
–Msg 208, Level 16, State 1, Line 1
–Invalid object name ‘SQL’.
–so now, you have to use either of the below option.
–1.)
SELECT * FROM “SQL HUB”
–2.)
SELECT * FROM [SQL HUB]
–Now suppose you want to create dynamic SQL Script which execute
–SELECT statement with every table of database, eg:
select ‘SELECT * FROM ‘ + name from sys.objects where type_desc=‘USER_TABLE’
–when you generate dynamic SQL from above script, copy it and try to execute it.
–you will face error because still there is a space between SQL and Hub.
–you can modify above query with QUOTENAME and you will be ok.
select ‘SELECT * FROM ‘ + QUOTENAME(name) from sys.objects where type_desc=‘USER_TABLE’
–use master
–DROP DATABASE QUOTENAMEDEMODB
–GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

List of T-SQL Running at the moment with Sys.dm_exec_requests and sys.dm_exec_sql_text in SQL Server 2008

This is something we may need many times as a administrator. Sometime when we don’t have profiler running and don’t want to go for any other route to troubleshoot  server performance, I would execute on simple T-SQL statement with the help of DMV (Sys.Exec_Requests) and DMF (Sys.dm_exec_sql_text) which can list out all the T-SQL Statement running at the moment in our database. There are many different ways to go for but this is something very quick and efficient so I keep this simple query handy all time.

SELECT
req.session_id,
req.command,
txt.text,
req.start_time,
req.status,
req.command
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) txt;

Sometime, when you suddenly started facing low performance on the server, you can run above query as a quick glance and look at the insight of SQL Server whether any heavy query is going on right now or not. This is not the only solution to performance issue, there are lot more tools and way but this could be first and quick step.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Frequently Asked Question SQL Server Zone in Different Forums online

It’s been more than 6 months I am actively working in few different online forums like Experts-Exchange.com and forums.aspx.net etc. It has been observed that few questions are really coming repeatedly with different forms. So I thought to list out the articles I have already written for those questions for Microsoft SQL Server. It would be interesting to list many question answered in one post.

Let us see it one by one in no particular order:

Pivot is also one of the interesting as well as frequently asked question in every SQL Server forums. I have written approx 10 different articles on this topic but would like to list three of them here.

Generic Stored Procedure for PIVOT in SQL Server, you can use this SP with almost all PIVOT need.

CTE (Common Table Expression) is also one of the interesting topic which is being asked in forums very often. I have written few articles on this topic too but would like to list our only one here.

Apart from all these useful links, I would like to share two most important script which I used to use with all my servers and databases.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com