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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)



This is sixth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past five articles in this series. Below are the links of the same.



Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.


CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

–this T-SQL will return only key value and rank
SELECT
[KEY],
[RANK]
FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
);
GO

–this is sample search of CONTAINSTABLE
–with FORMSOF clause which we used in CONTAINS also in previous article
Select t2.* FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
) as t1 join FTSTest t2 on t1.[key]=t2.Id
GO

–there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE
–You can give weight of the search word between 0.0 to 1.0
–search word with greater weight comes up with higher rank so that you can sort it easily.
SELECT
ct.[RANK],
ct.[KEY],
pm.[TestName],
pm.[TestDescription]
FROM CONTAINSTABLE
(
ftsTest,
TestDescription,
N’ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))’
) ct
INNER JOIN FTSTest pm
ON ct.[KEY] = pm.ID
ORDER BY ct.[RANK] DESC;
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5)


This is fifth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past four articles in this series. Below are the links of the same.

We have already looked at FREETEXT and FREETEXTTABLE predicate in iFTS using Microsoft SQL Server 2008. Now, this is time to look at one more interesting predicate “CONTAINS” in iFTS.
CONTAINS is a more advanced version of FREETEXT predicate. It can accept column(s), search condition like FREETEXT and search simple words, other than that, it can search close words to the word specified into search criteria, thesaurus, synonyms, inflectional words. CONTAINS predicate don’t automatically search inflectional words but you have to use FORMSOF clause with it.

Let us see some practical example to make concept clearer.

Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article.

–you won’t get any data as inflectional words are not searched by default
–NOTE: we have Volatiles as a plural and in our data, it is singular.
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,‘Volatiles’)

–you will get results as you are making exact search
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,‘Volatile’)

–enabling inflectional word search so you can get data with singular forms of words
–even if you put plural in search criterial and vice versa
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N’FORMSOF(INFLECTIONAL,Volatiles)’)

–now we knew that we have three row as a results when we search Volatile word
–now I want to be more specific, I just want those row which has “Volatile” near “Again”
–you will get only one row as a results
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N’Volatile NEAR Again’)

 
CONTAINS is really lot more powerful than FREETEXT search. Isn’t it?

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4)

This is fourth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past three article in this series. Below are the links of the same.

BTW, I am going to use the same table I have created in my previous article so for practical exercise get the table and script from that article if you are directly landing to this page.
If you have read above articles than you must be clear with FREETEXT predicate. Now, we will continue over the topic of this article.
If you are SQL Server developer then you might know TVF. FREETEXTTABLE is working something like it. You have to use FREETEXTTABLE in FROM clause only, just like a regular table or TVF. It will accept three things as below.
1.)    Table Name
2.)    Column Name(s)
3.)    Search Text
It will not return any row with real value rather it will return two columns.
1.)    [KEY]
2.)    [RANK]
First column [KEY] will return the key value of the Index column you have in your original table so that you can join this [KEY] column to your original table and get real full row whenever it is needed.
Second column [RANK] will return relevance ranking of the search text in the column(s) you have specified in FREETEXTTABLE.
So now this is really enough theory, isn’t it? Let’s have some real practical task.
–as per explaination given above,
–below query will return just key and rank. you can confirm
–whether you get right key or not based on your search criteria
select [Key],[Rank] from FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’)
GO

–just commpare [KEY] with ID column of our FTSTest
Select * from FTSTest
GO

–now we can get real value by making join of FREETEXTTABLE and FTSTest
SELECT T2.* FROM FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’) as t1 JOIN FTSTest as T2
ON T1.[KEY]=T2.Id
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Read typed XML in SQL Server 2008

So far, I have written few articles on XML topic, before we move further, I would like to summarize those here with the respected links so if anybody interested, can have look at it.

Apart from above four XML article, today I would like to introduce, how you can read typed XML in Microsoft SQL Server 2008.

I have one very small example to share with you. Have a look.

SET ANSI_NULLS ON
DECLARE @MyXML Xml
SET @MyXml =  
     
             
                     
                        TCLP VOA   
                        TCLP-SVOA   
                        Metals Group1  
                 
                     
                        Cynide   
                        Mercury   
                        TO-15  
                   
           
      ‘

;WITH XMLNAMESPACES (‘http://sqlhub.com/client/’ as L,
                              ‘TestGroup’ as TG)
SELECT      T.c.value(‘(TG:Test1)[1]’, ‘varchar(20)’) as Test1,
            T.c.value(‘(TG:Test2)[1]’, ‘varchar(20)’) as Test2,
            T.c.value(‘(TG:Test3)[1]’, ‘varchar(20)’) as Test3
FROM      @MyXml.nodes(‘(L:Clients/L:Body/TG:TestGroup)’) T(c)
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah