ASP.NET Cookies

A cookie is a small bit of text that accompanies requests and pages as they go between the Web server and browser. The cookie contains information the Web application can read whenever the user visits the site.

A Visual Studio project with source code is available to accompany this topic: Download.

Scenarios
Cookies provide a means in Web applications to store user-specific information. For example, when a user visits your site, you can use cookies to store user preferences or other information. When the user visits your Web site another time, the application can retrieve the information it stored earlier.

Cookie Limitations
Most browsers support cookies of up to 4096 bytes. Because of this small limit, cookies are best used to store small amounts of data, or better yet, an identifier such as a user ID. The user ID can then be used to identify the user and read user information from a database or other data store. (See the section "Cookies and Security" below for information about security implications of storing user information.)

How to: Write a Cookie
Cookies provide a means in Web applications to store user-specific information, such as history or user preferences. A cookie is a small bit of text that accompanies requests and responses as they go between the Web server and client. The cookie contains information that the Web application can read whenever the user visits the site.

The browser manages the cookies on client computers. Cookies are sent to the client using the HttpResponse object, which exposes a property called Cookies. Any cookies that you want your Web application to send to the browser must be added to this collection. When you write a new cookie, you must specify the Name and Value. Each cookie must have a unique name so that your Web application can identify it when the browser sends it with future requests.

There are two ways to write a cookie to a user's computer. You can either directly set cookie properties on the Cookiescollection or you can create an instance of the HttpCookie object and add it to the Cookies collection. You must create cookies before the ASP.NET page is rendered to the client. For example, you can write a cookie in a Page_Load event handler but not in a Page_Unload event handler. 

To write a cookie by setting cookie properties on the Cookies collection
In the ASP.NET page you want to write a cookie, assign properties to a cookie in the Cookies collection.


The following code example shows a cookie named UserSettings with the values of the subkeys Font and Color set. It also sets the expiration time to be tomorrow.

Response.Cookies["UserSettings"]["Font"] = "Arial";
Response.Cookies["UserSettings"]["Color"] = "Blue";
Response.Cookies["UserSettings"].Expires = DateTime.Now.AddDays(1d);

To write a cookie by creating an instance of the HttpCookie object

• Create an object of type HttpCookie and assign it a name.
• Assign values to cookie's subkeys and set any cookie properties.
• Add the cookie to the Cookies collection.

The following code example shows an instance of the HttpCookie object named myCookie, which represents a cookie named UserSettings.

HttpCookie myCookie = new HttpCookie("UserSettings");

myCookie["Font"] = "Arial";

myCookie["Color"] = "Blue";

myCookie.Expires = DateTime.Now.AddDays(1d);

Response.Cookies.Add(myCookie);

How to: Read a Cookie
Cookies provide a means in Web applications to store user-specific information, such as
history or user preferences. A cookie is a small bit of text that accompanies requests and
responses as they go between the Web server and client. The cookie contains information
that the Web application can read whenever the user visits the site.

The browser is responsible for managing cookies on a user system. Cookies are sent to the
server with a page request and are accessible as part of the HttpRequest object, which
exposes a Cookies collection. You can read only cookies that have been created by pages
in the current domain or path.Procedure

To read a cookie
Read a string from the Cookies collection using the cookie's name as the key.
The following example reads a cookie named UserSettings and then reads the value of the
subkey named Font.

if (Request.Cookies["UserSettings"] != null)
{
    string userSettings;
    if (Request.Cookies["UserSettings"]["Font"] != null)
    { userSettings = Request.Cookies["UserSettings"]["Font"]; }
}

How to: Delete a Cookie
You cannot directly delete a cookie on a user's computer. However, you can direct the
user's browser to delete the cookie by setting the cookie's expiration date to a past date.
The next time a user makes a request to a page within the domain or path that set the
cookie, the browser will determine that the cookie has expired and remove it.

To assign a past expiration date on a cookie

• Determine whether the cookie exists, and if so, create a new cookie with the same name.
• Set the cookie's expiration date to a time in the past.
• Add the cookie to the Cookies collection object.

The following code example shows how to set a past expiration date on a cookie.

if (Request.Cookies["UserSettings"] != null)
{
    HttpCookie myCookie = new HttpCookie("UserSettings");
    myCookie.Expires = DateTime.Now.AddDays(-1d);
    Response.Cookies.Add(myCookie);
}

Continue Reading →

Static Class

What is a static class?

When we use the static keyword before a class name, we specify that the class will only have static member variables and methods. Such classes cannot be instantiated as they don’t need to: they cannot have instance variables. Also, an important point to note is that such static classes are sealed by default, which means they cannot be inherited further.

This is because static classes have no behavior at all. There is no need to derive another class from a static class (we can create another static class).

Why do we need static classes?

As already written above, we need static classes when we know that our class will not have any behavior as such. Suppose we have a set of helper or utility methods which we would like to wrap together in a class. Since these methods are generic in nature, we can define them all inside a static class. Remember that helper or utility methods need to be called many times, and since they are generic in nature, there is no need to create instances. E.g., suppose that you need a method that parses an int to a string. This method would come in the category of a utility or helper method.

So using the static keyword will make your code a bit faster since no object creation is involved.
The main features of a static class are:

1. They only contain static members.
2. They cannot be instantiated.
3. They are sealed.
4. They cannot contain Instance Constructors (C# Programming Guide).

Continue Reading →

SQL- PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

The following code example produces a three-column table that has seven rows.

SELECT TOP 1000 [MarksID]

      ,[CustID]

      ,[Marks]

  FROM [DBSK].[dbo].[Marks]

Here is the result set.

MarksID               CustID         Marks

1                              1              234      

2                              1              3223     

3                              2              32       

4                              2              43       

5                              3              434      

6                              4              4343     

7                              4              321           

The following code displays the same result, pivoted so that the CustID values become the column headings.Now I have to get Max Msrks for Every CustID.

  select [1],[2],[3],[4] from (select CustID,Marks from Marks) as temptable

  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks

Here is the result set.

1                      2              3              4

3223               43           434         4343  

for the same result you can use the Different Query using temptable

  select CustID,Marks

  into #temptable

  from Marks

  select [1],[2],[3],[4] from #temptable

  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks

  drop table #temptable

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. 

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

Here is the result set of Table.

VendorID            Emp1     Emp2     Emp3     Emp4     Emp5

1                              4              3              5              4              4

2                              4              1              5              5              5

3                              4              3              5              4              4

4                              4              2              5              5              4

5                              5              1              5              5              5 

Now suppose, you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, andEmp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM pvt) p

UNPIVOT

   (Orders FOR Employee IN

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt;

GO

Here is the result set.

VendorID            Employee            Orders

1                              Emp1                     4

1                              Emp2                     3

1                              Emp3                     5

1                              Emp4                     4

1                              Emp5                     4

2                              Emp1                     4

2                              Emp2                     1

2                              Emp3                     5

2                              Emp4                     5

2                              Emp5                     5

3                              Emp1                     4

……….

.......

Continue Reading →

SQL - User Defined Functions

This article covers all the basics of User Defined Functions. It discusses how (and why) to create them and when to use them. It talks about scalar, inline table-valued and multi-statement table-valued functions. 

With SQL Server 2008, Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

What Kind of User-Defined Functions can I Create?

There are three types of UDF in Microsoft SQL Server 2008:

• Scalar functions.
• Inline table-valued functions.
• Multistatementtable-valued functions.

How do I create and use a Scalar User-Defined Function?

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp,image and user-defined data types.You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

CREATE FUNCTION whichContinent

(@Country nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @Country

when 'Argentina' then 'South America'

when 'Belgium' then 'Europe'

else 'Unknown'

end

return @return

end

Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name. 

print dbo.WhichContinent('USA')

create table test (Country varchar(15), Continent as (dbo.WhichContinent(Country)))

insert into test (country) values ('USA')

select * from test

Country                Continent

USA                      Unknown

Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.

How do I create and use an Inline Table-Value User-Defined Function?

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables. The following code shows the fnGetEmployeesByCity UDF, which accepts a city and returns a table containing all employees' first name, last name, and address:

CREATE FUNCTION fnGetEmployeesByCity (@sCity VARCHAR(30))

    RETURNS TABLE

AS

RETURN

    (

        SELECT    FirstName, LastName, Address

        FROM    Employees

        WHERE    City = @sCity

    )

GO

This inline table value-returning UDF can be selected from or even joined to because it returns a rowset via the table datatype, as shown here:

SELECT * FROM dbo.fnGetEmployeesByCity('seattle')

A more complicated use of a UDF that returns multistatement tables could involve retrieving all employees by city, but if no customers match the given city then a dummy row is returned where the Address field is filled with "No matching employees found in the specified city," as shown below :

CREATE FUNCTION fnGetEmployeesByCity3 (@sCity VARCHAR(30))

    RETURNS @tblMyEmployees TABLE

    (

        FirstName VARCHAR(20),

        LastName VARCHAR(40),

        Address VARCHAR(120)

    )

AS

BEGIN

    INSERT    @tblMyEmployees

    SELECT   FirstName, LastName, Address

    FROM     Employees

    WHERE    City = @sCity

    ORDER BY LastName

    IF NOT EXISTS (SELECT * FROM @tblMyEmployees)

        INSERT @tblMyEmployees (Address)

            VALUES ('No matching employees found in the specified city')

       

    RETURN

END

GO

Continue Reading →

SQL: Joins

An SQL join is used to combine rows from multiple tables. An SQL join is performed whenever two or more tables are joined in an SQL statement.

The different kinds of SQL joins are:

• INNER JOIN (or sometimes called simple join)
• LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• FULL OUTER JOIN (or sometimes called FULL JOIN)

Let's take a look at each of them.

INNER JOIN (simple join)

Chances are, you've already written an SQL statement that uses an INNER JOIN. It is the most common type of SQL join. SQL INNER JOINS return all rows from multiple tables where the join condition is met.

       The syntax for the INNER JOIN is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Illustration of an INNER JOIN

An INNER JOIN returns the shaded area:

  Here is an example of an INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This SQL INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_ name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

       If we run the SQL statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

      

       Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

LEFT OUTER JOIN

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

       The syntax for the LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Illustration of a LEFT OUTER JOIN

A LEFT OUTER JOIN returns the shaded area:

 Here is an example of a LEFT OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

       If we run the SQL statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

       

       Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a <null> value.

Old LEFT OUTER JOIN Syntax

As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the LEFT OUTER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT OUTER JOIN

Another type of join is called a RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

       The syntax for the RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

        In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Illustration of a RIGHT OUTER JOIN

A RIGHT OUTER JOIN returns the shaded area:

   

Here is an example of a RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and name). It contains the following data:

supplier_id	supplier_name
10000	Apple
10001	Google

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10002	2013/08/14

        If we run the SQL statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

       Our result set would look like this:

order_id	order_date	supplier_name
500125	2013/08/12	Apple
500126	2013/08/13	Google
500127	2013/08/14	<null>

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.

FULL OUTER JOIN

Another type of join is called a FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met. Click here

       

       The syntax for the FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Illustration of a FULL OUTER JOIN

A FULL OUTER JOIN returns the shaded area:

 Here is an example of a FULL OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, <nulls> would be extended to those fields in the result set.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set. If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let's look at some data to explain how FULL OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10004	2013/08/14

        If we run the SQL statement (that contains a FULL OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

       Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2013/08/12
10001	Hewlett Packard	2013/08/13
10002	Microsoft	<null>
10003	NVIDIA	<null>
<null>	<null>	2013/08/14

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a <null> value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a <null> value.

CROSS JOIN

Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

Continue Reading →