Pages

Tuesday 3 August 2010

Exporting webforms to Excel files

In this post I will explain a simple way to generate excel files out of webforms using mime types.

It is very easy, just follow this steps:

1. In the "@ page" declaration, add these attributes: EnableViewState="false" CodePage="1252". By disabling viewstate you prevent some errors when opening the file with Excel and the CodePage is for allowing unicode characters to be displayed correctly in the worksheet.

2. In the Page_Load handler, type the following line:
     Response.ContentType = "application/vnd.ms-excel"
    It is important to use this value because others (application/excel and application/ms-excel) does not work always.

3. Do not use CSS files. Since your content will be generated as an Excel file, you cannot link to external files. To allow styles, put all your css in the same page.

That's it, by performing these steps you can now export an aspx page to a Excel file.



Related articles by Zemanta
Enhanced by Zemanta

Sunday 1 August 2010

Readable queries


It is important to every developer, to write semantic query to allow himself or other to easily understand what a query is intended to do. In this post I'm going to describe a simple technique that I use every day to make my database queries easy to read and understand.
Let us suppose that we have the following database:


Casing

Write your queries using Pascal Casing, i.e, capitalize the first character of every word as in:

Select * From Student

Joins
When writing 'joins', put the main entity in the first place. For instance, if I need to know all the students and their courses, notice that here I am talking about the students, so the query would look like this:

Select *
From Student s Join StudentCourse sc
On s.StudentId = sc.StudentId

 

In this query, notice that I used an alias for each table, since my tables are named using Pascal Casing, I can use the first character of every word for compounding the corresponding alias. Also notice that 'On' statement is indented used one tab which commonly is equivalent to four white spaces. The comparison of values for the join is made following the same order of the tables, first for Student and then for StudentCourse.


If you need to write longer joins as in 'students with their corresponding payment and the courses', the query would be written as follows:

      Select *
      From Student s Join Payment p          
         On s.StudentId = p.StudentId
      Join StudentCourse sc
         On s.StudentId = sc.StudentId

 

Here I put the second join in a new line and keep the intent for the on statement. This is very useful when writing very long queries that imply joining data between several tables.

Select
In the select statement put the columns separated by comma in the same line except for those that either has an alias or is inside any function or both like this:

Select Name, LastName, Hobby,
Datepart(Year, Isnull(DoB, getDate())) Year,
Amount, Description

From Student s Join Payment p
   On s.StudentId = p.StudentId

 

Notice that in this query, the Select and the From are separated by a blank line, this is easily separate the sections of the query. This query is written in Ms Sql Server but the idea applies for other engines as well.

Sub-queries
When writing sub-queries, follow the same rules but tab each sub-query according to its level and use the parenthesis as in the following example:

Select *
From Student s Join (
    Select StudentId
    From Payment
    Where Amount > 0
) HavePayments
   On s.StudentId = Havepayments.StudentId

In and not in
Put the in or the not in at the end of the where statement, this avoid confusing when reading the query. Compare these two queries:

Select *
From Student s
Where DoB = getDate()
And Name = 'Bob'
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0
)

 
Select *
From Student s
Where DoB = getDate()
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0
)
And Name = 'Bob'

The first query is easier to understand because after the not in there is no more query to read so you don't have to keep in mind the rest of the predicates in the where statement.

Conclusion
This techniques help to write more semantic queries, easy to read and easy to understand. The examples given above are written in sql server but the main idea can be applied to other database engies.

Every query is different and no rules can be taken so seriously because you cannot force your query requirements fit these recommendations, but certainly you will notice the benefits of applying these techniques to your queries specially then they turn very long and complex.

Enhanced by Zemanta