Monday, 23 March 2009

Event Handlers with Dynamic Controls

Have you ever had to create a page dynamically? You can use datagrids and such-like and bind directly to datasets of some description but what if you are doing it manually and still want your dynamic controls to call event handlers? You might have found that when you press your button or whatever, the event handler is not called.
What you need to know is that the server will map a control id to the event handler after the page is loaded. If you are generating dynamic controls, you need to re-create them with the same IDs in the Page_Load before the event handler is called. This way, although you have effectively garbage-collected the old controls with their event handlers, because of the mapping of name to event, it will behave correctly using the link from the new control that you need to set up.
This means that even if you need to change the layout of dynamic controls after the event handler is called, you will firstly need to create them the same as before in Page_Load and then after the event handler is called, you could then modify them accordingly.

Compiler error

Ever got this one?:
"An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately"
"The compiler failed with error code 1"

Beautiful and helpful all at the same time. Anyway, in my case this was caused by the inherits in the top of the aspx file pointing to the correct name of class but not the correct namespace. For some reason it is not a build error you just get this wonderful error instead. The detail of the error says, "....._ascx.FrameworkInitialize()': no suitable method found to override" which is equally unhelpful.

Tuesday, 17 March 2009

Dodgy Joins

When do you use the ON statement in a join and when do you use the WHERE clause?
Many newbies fall over on this and scratch their heads. I have fallen foul of it many times but have now learnt what I've done wrong.
Take the following tables:
itemprices
Price: decimal (19,5)
QuotationReference: varchar(128) (foreign key references quotes)
ItemName: varchar(128) (foreign key references items)

items
ItemName: varchar(128)
DefaultPrice: decimal(19,5)

It has any calculated prices that have been applied for particular quotations. The first table has 0 to count(*) from items rows per quotation. In other words, for quotation X, the first table might have between 0 and lets say 100 rows (if there are 100 rows in the items table).
Now, I want to get the price for each of the 100 items in the second table for a particular quote but I want to use the Price from the first table (if it exists) in preference over the DefaultPrice in the second.
The SQL might be:
SELECT items.ItemName, coalesce(Price,DefaultPrice) as Price
FROM items LEFT OUTER JOIN itemprices ON items.ItemName = itemprices.ItemName
WHERE QuotationReference = @QuoteRef

It looks correct doesn't it? You use LEFT OUTER JOIN since there might not be an entry for each item in the prices table and then you filter out all the rows that do not belong to your quotation. You will find that it does not work. Can you see what might happen?
The join is incomplete. Simply joining on the itemname would be fine if your quotation had an entry in itemprices but if it didn't then it would join rows from any other quotation. You would then apply the WHERE clause and lose all of the rows. Interestingly if you had a single entry for your quotation OR multiple entries including one for your own quotation, the join would work it is ONLY in the case where your quotation has no entry and at least one other quotation does. In other words you might not notice straight away.
The reason the join is incorrect is that logically you do not want to relate rows that simply have the same name, they also need to have the same quotation reference. A join should only link rows that are related so that if you had no WHERE clause, the data would still be logically correct and consistent. In our case, the join should be:
SELECT items.ItemName, coalesce(Price,DefaultPrice) as Price
FROM items LEFT OUTER JOIN itemprices ON items.ItemName = itemprices.ItemName
AND itemprices.QuotationReference = @QuoteRef
WHERE QuotationReference = @QuoteRef

If you were joining two tables that both had quotation references, you might want the ON statement to use the column name rather than the parameter but either way works. Now what happens if you have no entry for your quotation in itemprices but you DO have entries for other quotations, the join will fail. You will get NULL for the itemprices columns and the select will return the DefaultPrice instead. You would have to tailor it slightly if you were writing a view since you would have no parameters to join on. In this case you would have to start from the quotes table so that you can return info for all quotes:
SELECT quotes.Reference,items.ItemName,coalesce(Price,DefaultPrice) as Price 
FROM quotes
CROSS JOIN items ---Ensure you have all items for all quotes
LEFT OUTER JOIN itemprices ON items.ItemName = itemprices.ItemName
AND itemprices.QuotationReference = quotes.Reference

The Triangle Problem

A lot of people who do not work with software wonder why software has defects in it. To the end-user, something like Microsoft Word looks simple enough, why is it that in a certain scenario it locks up or crashes?
It would be easy to say that people who write software understand why and are able to avoid these situations with the correct processes but sadly this is not usually true at all. Even very able and competent engineers make mistakes and, of course, we expect the less competent engineers to make mistakes.
I came across the following example in a computer magazine once and apparently it is a classic testing question:
q) You need to write a function that takes 3 inputs and determines wether they form an equilateral, an isoceles or a scalene triangle or displays an error if the inputs cannot form a triangle.
Spend a few moments and see how many tests you could think of to determine the type of triangle.
Ideally, the functionality for this would be specified in a very robust way by somebody who is both a good spec writer and has done the necessary research to ensure all bases are covered! Back in the real world, you would be very lucky to get a spec for this level of functionality and even if you did, you would still have to interpret it.
When I first heard the problem I immediately thought that all values would form a triangle thinking of the phrase "any three points make a triangle" (which of course they don't if they are on a straight line).
After some hints from a colleague I started thinking about inputs that are negative or zero and but still forgot about what happens if 1 side is much longer than the others and therefore the triangle can't close. I didn't think about what happens if the 3 inputs are not specified correctly, such as 23E04 or what happens if the input is mistyped like 23..56 or if the inputs are specified to too much precision or whether the inputs are guaranteed to be within the limits of the specification. I think I managed about 6 tests out of a potential of 15 and I thought I was pretty clued up!
|Being the engineer, I then got really concerned that this was the real-life experience of many software engineers. When you write code you are assumed to be able to guess what error checking etc is required in your code and implement it. Of course, most of the error combinations would probably never surface but what if they do in a way that is hiddden from the end-user. Something gets corrupted and crashes?
The real question is, what can I do about it? Well you can have a committee to discuss what tests you need to perform but this is unlikely to reveal much more than you would by yourself (big quiz teams don't always win first prize!) so what else can you do?
You can write Unit Tests as you go along. You can test functions using test cases that give you very good overall coverage of the code. You can spend more time on functions that use logic rather than functions that simply call a string of other functions sequentially. You can keep the test cases as a reminder for when you are testing newer code. You can share tests with other developers so that over time, you gradually increase the types of error conditions that you test for. It is an underused mechanism for reliable code.
You can develop shared controls. For instance, you could design a web control that only allows valid numbers to be typed in so that your functions are never passed malformed data and don't fall over. You can even create simple classes like strings etc that can perform earlier testing on data before it has a chance to upset the belly of your code.
At the end of the day, the issues need to be analyzed and something needs to be put in place to keep the closed-loop quality control doing its job properly. You need to learn if your counter-measures are working and either modify them or drop them if they are not. You need to listen to other people and what they use. You need to take your craft seriously.

Thursday, 12 March 2009

INNER JOINS, OUTER JOINS and CROSS JOINS

Sql Joins, they seem easy to begin with but then as soon as you think you understand, something happens leaving you scratching your head. This is a brief but hopefully very useful introduction to the 4 main joins.
We will begin by assuming we have a database with two tables that is used to track people borrowing books from the library. We will ignore all the irrelevant columns that we might actually have and keep it simple:


Table 1 (Members)
Name: varchar(250)
ID: int (identity, autoinc, primary key)


Table 2 (Bookings)
Book: int (foreign key to books table)
Member: int (foreign key to members table)
DateOut: DateTime


So when a user takes a book out, its ID number, the member's ID number and the date are put into the Bookings table. We now want to use the data in the tables. Suppose we want to know who has what books checked out, we need information from more than one table (we will forget the book name for now and assume we only want the date, book id number and the person's name). We need to join them together.
What are we actually doing? We are relating information from the rows of one table to the rows of another. In some cases there will be 1 row in each that matches but in most cases, as here, there can be many. For instance a single person can borrow e.g. 5 books so 1 row in members might match 5 rows in Bookings. It is worth noting for later that a row in one table might match zero rows in the other table (somebody with no books on loan) which we will look at later.

INNER JOIN


An inner join simply says that the information we want to return from our select statement will have at least one entry in both tables. For each row we can join using the criteria (the columns that we will match), a row will be returned in the results. If we cannot match a row from one to the other, the row will not be returned.
select Name, Book, DateOut from Members INNER JOIN Bookings on Members.ID = Bookings.Member

If John has a book taken out (and therefore e.g. an entry for book 11 on 20th Jan), the previous select would include: John, 11, 20th Jan. If however there was a member called Luke who had no books on loan, his name would NOT show in the results because his row in members would not JOIN any rows in the bookings table.

It is very common that actually we want to know everybody in the system and if they have any books on loan or not. We simply use...

LEFT OUTER JOIN


A left outer join says that we will return items from the left-hand table even if there are no entries in the right hand table. If we run exactly the same select as before but with a LEFT OUTER JOIN,
select Name, Book, DateOut from Members LEFT OUTER JOIN Bookings on Members.ID = Bookings.Member

We would get "Luke, NULL, NULL" as well as "John, 11, 20th Jan" in the results. The NULLS mean there is nothing there, not zero, not blank, nothing i.e. NULL. We can use the coalesce function if we would rather return something useful instead of NULL:
select Name, coalesce(Book, ''), coalesce(DateOut, '') from Members etc..

RIGHT OUTER JOIN


Don't bother. It is the same as the LEFT flavour but the other way round and to be honest, it is usually better to rewrite your select so it effectively reads from left to right and only uses left outer joins.

FULL OUTER JOIN


A full outer join returns all items from both sides whether or not there are matching rows but if they match, they will be returned together. Not generally considered good practice since are the tables even related if something can exist on either side and not the other?

CROSS JOIN


Sometimes, it is not possible to join a table based on any relationship. For instance, suppose we want to work out the prices to replace certain types of books for a particular person. There is a table of 10 book categories with base prices but this has to be multiplied by a certain value that exists in the Members table (perhaps how rich they are!!). You have nothing to join on.
Table 1 (Members)
Name: varchar(250)
ID: int (identity, autoinc, primary key)
PriceFudgeFactor: decimal(9,5)


Table 2 (BookReplacements)
BookType: int (foreign key to book categories table)
BasePrice: Decimal(19,5)


What do you do? Well a CROSS JOIN matches each item in the left hand table to each item on the right with no linked columns. What would result in our case would be a list where the number of rows equals the number of users times 10 (the number of categories) What you would probably then do, using a sub-query is do the calculation using the base price from one side and the factor from the other:

SELECT PriceFudgeFactor * BasePrice as TotalPrice FROM
(
SELECT Name, PriceFudgeFactor, BasePrice FROM Members CROSS JOIN BookReplacements
)

Note there is no "ON" clause after the join since it will match all rows with all other rows. I have used INNER and LEFT OUTER joins frequently, CROSS JOINS occassionally and never RIGHT or FULL OUTER joins so that should give you some help knowing which to use.