Awesome Collection Of ASP.NET Hacks, Tips and Tricks

We added a bunch of ASP.NET Hacks, Tips and Tricks , below is the full list

Applications

* ASP.NET: Environment Details
* ASP.NET: Encrypt your applications settings
* ASP.NET: Launch an external application
* ASP.NET: Display Version Information

Caching

* ASP.NET: Clear all Cached objects
* ASP.NET: Data Caching

Controls

* ASP.NET: Access Master Page controls from the Content Page
* ASP.NET: Maintain an entered password in a TextBox
* ASP.NET: Apply a Please Wait message to a Button
* ASP.NET: Clear all TextBox values
* ASP.NET: Creating an accessible label
* ASP.NET: Add dynamic controls with events
* ASP.NET: Get a list of all selected items in a CheckBoxList
* ASP.NET: Find which control caused a postback
* ASP.NET: Limit selections for the Calendar
* ASP.NET: Add a confirmation popup to a Button
* ASP.NET: Insert a ListItem into a DropDownList
* ASP.NET: Add paging to a repeater
* ASP.NET: Append client side functions with Attributes.Add
* ASP.NET: Add a total row to a GridView
* ASP.NET: How to export a GridView to Excel
* ASP.NET: Render a control to a string

Database

* ASP.NET: Convert a DataSet to a DataView
* ASP.NET: Extract data from a SQLDataSource to a DataTable
* ASP.NET: Insert data into SQL Server
* ASP.NET: Loop through data in a DataTable
* ASP.NET: Loop through data using a DataReader
* ASP.NET: Storing connection strings in the web.config file

Dates

* ASP.NET: Calculate a person's age from their date of birth
* ASP.NET: Calculate the difference between two dates
* ASP.NET: Simple date and time methods

Debugging

* ASP.NET: Enabling page tracing
* ASP.NET: Simple debugging tips

Email

* ASP.NET: Send an email

Encryption

* ASP.NET: Encrypt a string using MD5

Files

* ASP.NET: Files and Path information
* ASP.NET: Rename a directory
* ASP.NET: Reference files with relative paths
* ASP.NET: Read and display a text file

Images

* ASP.NET: How to crop an Image
* ASP.NET: How to save a remote image
* ASP.NET: Drawing images and bar charts with System.Drawing

Javascript

* ASP.NET: Register a javascript function

Objects and Classes

* ASP.NET: Accessing the Response object in a Class
* ASP.NET: An introduction to classes and properties
* ASP.NET: Looping using an Enumerator
* ASP.NET: Use Response.Filter to intercept your HTML
* ASP.NET: Use C Sharp and VB.NET in the same project
* ASP.NET: Using Generics to create a property list

Pages

* ASP.NET: Change the current Master Page
* ASP.NET: Nested Master Pages

Sessions

* ASP.NET: Redirect the page when the session ends

Strings

* ASP.NET: Filter words
* ASP.NET: Remove the last character from a string
* ASP.NET: Reverse a string
* ASP.NET: String Concatenation
* ASP.NET: Strip HTML tags from a string
* ASP.NET: Truncate a string to a set number of whole words

Validation

* ASP.NET: Using Page.IsValid

Visual Studio

* ASP.NET: Changing the default browser
* ASP.NET: Keyboard Shortcuts
* ASP.NET: Using a task list

Web

* ASP.NET: Access the web via a proxy server
* ASP.NET: Convert HTML tables to a DataSet
* ASP.NET: Retrieve data from a web page
* ASP.NET: Create an RSS Feed
* ASP.NET: Custom Error Pages
* ASP.NET: Find out where a visitor came from
* ASP.NET: Using an AppOffline.htm file for updates
* ASP.NET: XHTML Strict Validation

The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks

Bookmark that URL because we will be adding more hacks, tips and tricks

Integer Math In SQL Server

What do you think the following query will return in SQL Server?

SELECT 3/2

If you said 1.5 then you are wrong! The correct answer is 1, this is because when doing division with 2 integers the result will also be an integer.
There are two things you can do
1 multiply one of the integers by 1.0
2 convert one of the integers to a decimal


Integer math is integer result
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

SELECT @Val1/@Val2

Result 1

Convert explicit or implicit to get the correct answer
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

--Implicit
SELECT @Val1/(@Val2*1.0)
--Explicit
SELECT CONVERT(DECIMAL(18,4),@Val1)/@Val2

Result 1.50000000000000

Three Ways To Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Sort Values Ascending But NULLS Last

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

The 2 approaches with a datetime data type

DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

The 2 approaches with an integer data type

DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

SQL Gotcha: Do you know what data type is used when running ad-hoc queries?

This is for SQL Server 2000 only, SQL Server 2005 is a lot smarter which is another reason to upgrade.
When running the following query you probably already know that 2 is converted to an int datatype


SELECT *
FROM Table
WHERE ID =2

What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.

CREATE TABLE TestAdHoc (id bigint primary key)

INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6


Now let's run these 2 queries which return the same data

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666



SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

Now run the following SET statement and run the 2 queries again

SET SHOWPLAN_TEXT ON

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666


SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

And what do we see?

First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)

Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)


The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.

So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.

SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'

So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.


SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))


So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.