Thursday, January 26, 2006

Dynamic Order By's

Here is cool technique I saw here (http://www.sqlteam.com/item.asp?ItemID=2209)

Basically I knew of no way to do a dynamic order by / group by without creating dynamic sql i.e.

DECLARE @SortOrder varchar(30)
DECLARE @sql varchar(8000)
SET @SortOrder = 'CompanyName'

set @sql = 'SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY ' + @SortOrder

exec(@sql)



This sucks as SQL can not validate the syntax, the engine can not cache the execution plan, and this could open up a SQL injection attack (I am pretty sure that sql will filter / escape the incomming string though).


Anyways here is the better way to do it:



DECLARE @SortOrder tinyint
SET @SortOrder = 2

SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
ELSE ContactTitle
END




Pretty freakin sweet! There is also an interesting item on that site about creating a dynamic where clause by using COALLESCE.

Thursday, January 19, 2006

Why I hate oracle

I unfortunately keep running into places that need to migrate oracle or I have had the unfortunate responsibility of teaching it. I absolutely can not stand this product and I have a list why:

1. Cost. what an expensive POS.
2. useless junk. Oracle ships with tons of utilities that will never need to be used
3. Bloated install. Installes several apache instance / services I don't need
4. GUI = the suck. I have never used such hard management tools
5. no good query tool. The query tools appear to be wrappers around their dos sql utility. Its like microsoft word using dos's edit command for an engine.
6. TNS Names. The only way to connect to a database is to setup a tnsnames.ora file that makes a name map to an ip, port, and service. Pray you dont have a syntax error. Why not just have it so the client can connect to an IP? It makes no sense and is just another step.
7. Roles are sooo confusing. whats a SysOper, SysDba, normal? why not just have a good permissions model
8. Client size. Just to download the client so that I can connect is 550MB. 550MB! Thats MegaBytes! glad I am not on dialup.
9. Support. I tried to email them but they don't have an address on their site. I tried to call them but because I don't have some CID number (or something like that) I can not get through the voice system. I press 0 over and over to get an operator and it just says "Thanks for calling" and hangs up on me.
10. 80's style application. The application takes so long to learn and is so convoluted it reminds me of working on my Apple IIe (may it rest in peace). The GUI looks like a win 3.1 app and the steps required to do anything seem to take forever.

Now I know a lot of this is due to me not knowing a ton about oracle but software should be intuitive. I have learned the basics of a database in an afternoon but I have been playing with oracle (very rarely but still using it here and there) for the past 3 years and I still have no clue why anyone uses it when there are products out there that are comparable for a fraction of the price.

My recommendation to oracle is to keep the engine the same but build some tools that make a developers life easy. Make the install for both client and server as minimal as possible and then allow me to choose what I want. Make it affordable. Modernize the application and interface.

Oh btw Oracle has had a pretty crappy security record. Just now they released 80 patches for Oracle 10 in their quartly patch cycle. YAY! 80 security holes in 3 months!

My other issue was Oracle 9i. It was originally called Oracle 9i Unbreakable as it was sooo secure no one could hack it. Security Researcher David Litchfield found high risk security holes in the application days after it was touted as "Unbreakable". I could not find a count of security holes in the 9i database but I know it sure was replaced with Oracle 10 pretty quickly.

Wednesday, January 18, 2006

Say screw it to parsing filenames

I recently discovered some builtin classes to parse file paths instead of awkward .IndexOf / substring combinations

dim filename as string = "c:\windows\temp\stuff\myfile.txt"

the IO.Path has many shared methods that allow parsing and manipulation

IO.Path.ChangeExtension(filename, "xml") 'c:\windows\temp\stuff\myFile.xml
IO.Path.Combine() 'have not played with this one yet
IO.Path.GetDirectoryName(filename) 'c:\windows\temp\stuff
IO.Path.GetExtension(filename) '.txt
IO.Path.GetFileName(filename) 'myfile.txt
IO.Path.GetFileNameWithoutExtension(filename) 'myfile
IO.Path.GetFullPath(filename) 'c:\windows\temp\stuff\myfile.txt
IO.Path.GetFullPath("blah.txt") 'c:\vss\project\bin\blah.txt"
IO.Path.GetPathRoot(filename) 'c:IO.Path.GetTempFileName() 'c:\temp\tmp260A.tmp (gauranteed unique file)
IO.Path.GetTempPath() 'c:\temp\ (gets windows temp dir)
IO.Path.HasExtension(filename) 'True (false if no file extension)
IO.Path.IsPathRooted(filename) 'True (path is an absolute or relative. absolute in this case)


I recently used this for a project where I wanted to create the directory for a file if the directory did not exist.

IO.Directory.CreateDirectory(io.Path.GetDirectoryName("c:\temp\test1\test1\test1\tmp.txt"))

Note that CreateDirectory will recursively create any missing directories (when I ran this I only had a "c:\temp" directory).


Another handy method is IO.Directory.GetCurrentDirectory() which gets the current working directory of the application

Tuesday, January 17, 2006

Custom EventArgs

I don't know if there is a name for this pattern or not but I have found that it is quite helpfull. When creating custom events it should only have two parameters. One being the object that raised the event and the other should be an object containing data.

I like examples so lets start with these two classes:

public class MyApp

private withEvents eventClass as MyEventRaiser

private sub DoTask()
eventClass.DoTask()
end sub

private sub eventClass_MyEvent(arg1 as string, arg2 as integer, arg3 as bool) handles eventClass.MyEvent

messagebox.show(arg1)
end sub

end class


public class MyEventRaiser

public event MyEvent(arg1 as string, arg2 as integer, arg3 as bool)

public sub DoTask()
RaiseEvent MyEvent("test", 32, true)
end sub

end class


This is fairly cut and dry. When a call to MyEventRaiser.DoTask() is run an event is raised. In this case it is handled by MyClass.eventClass_MyEvent().

The problem with this setup comes to making changes. In every project there is change. We might run into a case where we need to return another parameter when the event gets raised. We could add it on to the event declaration, the RaiseEvent statement, and every place it is handled.

If the event is handled in 50 places we have to make 50 changes. Even though it might be that only one of those handlers actually cares about the new parameter.

To mitigate this issue we create a class that inherits from eventArgs:


public class MyEventArgs
inherits EventArgs

private _arg1 as string
private _arg2 as integer
private _arg3 as boolean

public property Arg1 as string
get
return _arg1
end get
set (value as string)
_arg1 = value
end set
end property

public property Arg2 as integer
get
return _arg2
end get
set (value as integer)
_arg2 = value
end set
end property

public property Arg3 as boolean
get
return _arg3
end get
set (value as boolean)
_arg3 = value
end set
end property

public sub new (arg1 as string, arg2 as integer, arg3 as bool)
_arg1 = arg1
_arg2 = arg2
_arg3 = arg3
end sub
end class

So now we have an object that encapsulates all of the data for our event. We then change our
class that declares the event like so:

public class MyEventRaiser

public event MyEvent(sender as object, e as MyEventArgs)

public sub DoTask()
RaiseEvent MyEvent(me, New MyEventArgs("test", 32, true))
end sub
end class

Starting to look familiar? This is what microsoft did for all of its built in events. The sender is always the object that raised the event. This allows the objects that receive the event access to the object that created it.

Now the only thing left is to change the signature of the methods that handle the method.

public class MyApp

private withEvents eventClass as MyEventRaiser

private sub DoTask()
eventClass.DoTask()
end sub

private sub eventClass_MyEvent(sender as object, e as MyEventArgs) handles eventClass.MyEvent

messagebox.show(e.arg1)
end sub

end class

From now on if we need to add a new parameter we simply add it onto the MyEventArgs class.
By doing this we dont need to change 50 method signatures to accept a new parameter that the method never uses.

Happy Eventing!

Heap vs. Stack

Monday, January 16, 2006

Delegates in vb.net part III

The next item in delegates I want to cover is returning data. In my previous example we called a sub that did a task (writing out a file) but it did not return any data or notify us that we were done. To accomplish this we are going to use a delegate with a callback method.

Here is the code I am going to start with:

Private Sub StartTask()
Dim del As New DoLongTaskDelegate(AddressOf DoLongTask)
del.BeginInvoke("c:\temp.txt", Nothing, Nothing)
End Sub

Private Delegate Function DoLongTaskDelegate(ByVal filename As String) As Integer

Private Function DoLongTask(ByVal filename As String) As Integer
'do long task
Return 537
End Function

Now in order to capture the returned data we will need a method to be executed when the method completes. This method must accept an IAsyncResult as a parameter

Private Sub TaskDone(ByVal ar As IAsyncResult)
End Sub

we then change our BeginInvoke call and set the second parameter to be the AddressOf this new method:

del.BeginInvoke("c:\temp.txt", AddressOf TaskDone, del)

We set the 4th parameter to be the actuall delegate we created. We need to do this so that when the method completes we have access to the delegated method itself (remember that a delegate is basically a pointer to a methd) so that we can access its return value.

In the TaskDone method add these two lines:

Dim del As DoLongTaskDelegate = ar.AsyncState
MessageBox.Show(del.EndInvoke(ar))

The ar.AsyncState now holds the delegate we passed in when calling BeginInvoke. We then call EndInvoke on the delegate to get the result. I know how confusing this is when you start but after you play with it for a while it starts to make more sense.

Delegates in vb.net part II

A delegate also has built in methods that allow background execution of the method it points to which is what we are going to use here. Most times I start a task on a background thread I use Thread.Start(). Unfortunately if you have a method that takes parameters you can not use Thread.Start. Instead we will use a delegate which will allow us to pass parameters to a method running on another thread.

I have a project where I need to write to a file on a background thread. The WriteFile method takes one parameter (the file path).

public sub WriteFile(byVal outputFile as String)
//implementation
end sub

To build a delegate I simply do this:

private Delegate Sub FileWriteDelegate(ByVal outputFile as String)

Note that the delegate has the same method signature as the method I am going to call.

private sub DoTask()
WriteFile("c:\temp.txt")
end sub

To run it on a background thread I change the DoTask method to create an instance of the delegate and then call BeginInvoke.

private sub DoTask()
Dim x As New FileWriteDelegate(AddressOf WriteFile)
x.BeginInvoke(outputFile, Nothing, Nothing)
end sub

You will note that when you call beginInvoke you must specify a string of filename. I have set the other params to nothing. I will cover the other two params of BeginInvoke in my next post. That is pretty much it! I will try to write more on Delegates as I think this is the one feature that most vb.net developers know nothing about or don't know how / when to use them.

Delegates in vb.net

For those of us that are not C# developers we rarely deal with delegates. A delegate is simply a strong typed pointer to a method. C# uses this extensively for wiring of events. VB.NET actually builds the event delegates for us behind the scenes.

Here is a quick example of a simple delegate:

Public Delegate Sub TrafficLightChangedEventHandler(ByVal color As String)

Private Sub TrafficLightChanged(ByVal color As String)
MsgBox(color)
End Sub

Private Sub StartDelegate()
Dim del As TrafficLightChangedEventHandler
del = New TrafficLightChangedEventHandler(AddressOf TrafficLightChanged)
del.Invoke("Red")
End Sub

When startDelegate gets called we create an instance of the Delegate and put in the address of the TrafficLightChanged method which will be called when we run del.invoke("Red"). Note that invoke automatically knew what parameters were required (in this case a color string).

Delegates can also be used to invoke multiple methods with one invoke call. This is often refered to as a MultiCast Delegate.

Public Delegate Sub TrafficLightChangedEventHandler(ByVal color As String)

Private Sub TrafficLightChanged(ByVal color As String)
MsgBox(color)
End Sub

Private Sub TrafficLightChanged2(ByVal color As String)
MsgBox("2: " & color)
End Sub

Private Sub StartDelegate()
Dim del As TrafficLightChangedEventHandler
del = New TrafficLightChangedEventHandler(AddressOf TrafficLightChanged)
del = TrafficLightChangedEventHandler.Combine(New TrafficLightChangedEventHandler (AddressOf TrafficLightChanged), New TrafficLightChangedEventHandler(AddressOf TrafficLightChanged2))
del.Invoke("Red")
End Sub

When we call Invoke() the delegate will execute both TrafficLightChanged and TrafficLightChanged2.

This is exactly how events work in .NET. Raising one event could cause multiple methods to execute that are wired to handle the event.

Friday, January 13, 2006

Overlaying an image overtop of another image

Recently I was asked to build a website that when a property got sold a little sold sticker appeared in the upper corner of the image. I thought this would be really hard based on my experience with php image manipulation. With .NET and GDI+ it was so easy. Here it is:

Dim baseImage As Image
Dim overlayImage As Image
baseImage = Image.FromFile("c:\house.jpg")
overlayImage = Image.FromFile("c:\loginButton.jpg")

Dim gr As Graphics
gr = Graphics.FromImage(baseImage)
gr.DrawImage(overlayImage, 10, 10)

Me.PictureBox1.Image = baseImage

This is easy to change to load from a stream / save it to a web output stream (baseImage.Save(Response.OutputStream)).