Pages

7/23/2009

SQL DATEDIFF Function

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate

Return Value = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter


SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate

Return Value = 86400000 millisecon

7/19/2009

Image Processing in C#

If you've read our previous articles on GDI+, you know how to display images in C#. But how can you manipulate them? Rick Leinecker shows you how!
Displaying images in your programs opens up enormous possibilities. You can display photographs, enhance your user interface with catchy icons, and render data in a graphical format—only to name a few cool things that you can do. This article shows you how to go beyond simple image display by getting you started with image processing.
Images are easy to use in C#. Click here for an article that gets you started with handling images, called GDI+ Image Handling in C#.
We'll start off with some baby steps and create three filters that can be applied to an image. Staying within the RGB color space will make sure that it's as simple as possible—at a later time we'll move into HSV color space because it gives us a wider range of filters that we can apply.
For more information on colors and color spaces, read Color Theory for Developers here on DevSource.com
Pixels and Colors
To get started we need to talk about pixels and colors. Pixels represent a single unit of a display bitmap. Each pixel is a discreet dot on the screen. Images contain thousands of pixels, actually more like hundreds of thousands of pixels. A screen that's 1024 by 800 is composed of 819,200 pixels.
Images achieve their visual perception based on the colors of all of the pixels. Although it would be difficult since pixels are so small, if you could paint each pixel with a paintbrush, then you could create an image. It's kind of like the pointillistic artists who painted in small dots, which in turn created the gestalt of the entire painting. If those artists could have painted small enough dots, then they could have achieved the resolution of modern computer images.
A pixel is represented by three different color components: red, green, and blue. In today's PC graphics, each of these values ranges from 0 to 255. By mixing the three values, you get the visible color. Since each component has a value from 0 to 255, we can mathematically calculate the number of colors that result from the mixing of these three components as 16,777,216.
Bitmap Objects
Images are usually encapsulated in a Bitmap object. This .NET object makes image handling extremely simple. As an example, to load and display an image to a form window, all you need to do is use code similar to the following snippet.
Bitmap objBitmap = new Bitmap( "SomeFile.jpg" );

Graphics g = Graphics.FromHwnd( Handle );

g.DrawImage( objBitmap, 0, 0 );
While the Bitmap object makes image handling easy, it doesn't make image processing easy because it doesn't give you direct access to the image data. Of course, you can loop through the image using the Bitmap object's GetPixel and SetPixel methods to get the color value for a pixel, alter it, and update the image pixel at that location. The following code snippet does this.
Bitmap objBitmap = new Bitmap( "SomeFile.jpg" );

for( int y=0; y{

 for( int x=0; x {

   Color col = objBitmap.GetPixel( x, y );

   // Perform an operation on the Color value here.

   objBitmap.SetPixel( x, y, col );

 }

}
In spite of how easy the previous code snippet is, it's slow. The GetPixel and SetPixel methods are not very fast. On my 2.4 GHz dual core machine, the GetPixel/SetPixel combination code for an image that was 300x300 took 281 milliseconds.
What we need is a good way to get access to the actual data that represents the image. Then we don't have to rely on the GetPixel and SetPixel methods. If we can simply perform image processing on the actual image data, then we can do it with very little perceptible slowness.
There is a way. We can save the image as a BMP file into memory. All we need to do is create a MemoryStream object, let the Bitmap object save into the MemoryStream object, and then get the bytes from the MemoryStream object. The bytes we get are the raw image data, and we can process in whatever way is appropriate. Then, we reverse the process by creating a MemoryStream object that contains the modified image data, and read from it into a Bitmap object.
I wrote a helper class with methods that perform both of the required operations. A method named BitmapDataFromBitmap returns a byte array from a Bitmap object. A method named BitmapFromBitmapData returns a Bitmap object from a byte array. And when I timed using both methods to get data from a Bitmap object and then put it back, it took less than a single millisecond—much better than the 281 milliseconds from the GetPixel/SetPixel combination. The two methods can be seen below. (Please note that you'll need to add using statements for System.Drawing, System.Drawing.Imaging, and System.IO.)
static public Bitmap BitmapFromBitmapData(byte[] BitmapData)

{

    MemoryStream ms = new MemoryStream(BitmapData);

    return (new Bitmap(ms));

}



static public byte[] BitmapDataFromBitmap(Bitmap objBitmap)

{

    MemoryStream ms = new MemoryStream();

    objBitmap.Save(ms, ImageFormat.Bmp);

    return (ms.GetBuffer());

}
Okay, now you get to use the helper class (which in the demonstration program is named BitmapHelper.) The following code shows how to use the two methods.
Bitmap objBitmap = new Bitmap( "SomeFile.jpg" );

byte[] BitmapData = BitmapHelper.BitmapDataFromBitmap(objBitmap);

// Perform image processing on the raw data here…





objBitmap = BitmapHelper.BitmapFromBitmapData(BitmapData);









The ImageProcessingDemo Program
I wrote a simple program named ImageProcessingDemo to demonstrate these concepts. You can download the code here. When the program first runs, it displays a default image that can be found in the program directory as shown in the figure below. You can click the browse button if you want to load a different image.

There are only four operations that can be performed on an image: darken, lighten, restore, and invert. The button with the '<' character darkens the image a bit. The button with the '>' character lightens the image a bit. The button with the 'R' character restores the image to its original state. And finally the button with the 'I' character inverts the image.
Before jumping into the processing code, we need to talk about BMP headers and the data format. BMP files have two headers: the file header and the information header. The file header is the first 14 bytes of the BMP file and contains the string 'BM', a long specifying the size of the file in bytes, two reserved words that must have a value of 0, and finally the offset from the beginning of the file to where the actual image data is. The following chart shows the BMP file header.
Start Size Name Example Value Purpose
0 2 bfType 19778 or 'BM' Identifies the file type.
2 4 bfSize ?? Specifies the size of the file in bytes.
6 2 bfReserved1 0 Must always be set to zero.
8 2 bfReserved2 0 Must always be set to zero.
10 4 bfOffBits 54 Specifies the offset from the beginning of the file to the bitmap data.
The information header contains information about the bitmap including its size, width, height, and format. The following chart shows the BMP information header.
Start Size Name Example Value Purpose
14 4 biSize 40 Specifies the size of the BITMAPINFOHEADER structure, in bytes.
18 4 biWidth 100 Specifies the width of the image, in pixels.
22 4 biHeight 100 Specifies the height of the image, in pixels.
26 2 biPlanes 1 Specifies the number of planes of the target device, must be set to zero.
28 2 biBitCount 24 Specifies the number of bits per pixel.
30 4 biCompression 0 Specifies the type of compression, usually set to zero (no compression).
34 4 biSizeImage ?? Specifies the size of the image data, in bytes. If there is no compression, it is valid to set this member to zero.
38 4 biXPelsPerMeter 0 Specifies the horizontal pixels per meter on the designated targer device, usually set to zero.
42 4 biYPelsPerMeter 0 Specifies the vertical pixels per meter on the designated targer device, usually set to zero.
46 4 biClrUsed 0 Specifies the number of colors used in the bitmap, if set to zero the number of colors is calculated using the biBitCount member.
50 4 biClrImportant 0 Specifies the number of color that are 'important' for the bitmap, if set to zero, all colors are important.

The BMP header information is helpful in some

situations, but we'll ignore the data in it for now. All we really need

to remember is that we have to skip the first 54 bytes of the image

data before doing any processing. Not only is it a waste of time to

operate on the header data, but when we restore the data back into the

Bitmap object there will be many errors introduced.
The data comes in sets of four bytes for each pixel. The first byte is the blue value, the second the green, the third the red, and the fourth is an alpha value that some programs may or may not use.
I have to point out that this is not the format for all BMP files. This is just how the .NET runtime saves BMP files. There are lots of other ways that the data can be organized, including a palette-indexed system. But since the .NET runtime saves in the way that I've described, then we can simplify things by limiting our discussion to that particular format.
I'll start with the code for darkening and lightening the image. First, there's a helper method that adds a value to the current image data value. The value can be positive or negative. The helper method checks to make sure that the value isn't less than 0 or that it isn't greater than 255 since these are the far extremes of the RGB values. The ChangeData method follows.
byte ChangeData(byte DataValue, int AddValue)

{

    if (AddValue + (int)DataValue > 255)

    {

         return (255);

    }

    else if (AddValue + (int)DataValue < 0)

    {

         return (0);

    }

    return( (byte)((int)DataValue+AddValue) );

}
There are two methods that are almost identical. One darkens (btnLess_Click) and one lightens (btnMore_Click). The only difference in these two methods is that one sends the value of -25 (to darken) to the ChangeData method, while the other sends the value of 25 (to lighten). Both methods can be seen here.
private void btnLess_Click(object sender, EventArgs e)

{

    byte[] BitmapData = BitmapHelper.BitmapDataFromBitmap(m_objBitmap);

    int nPixels = m_objBitmap.Width * m_objBitmap.Height;

    for (int i=0; i    {

         BitmapData[54+i] = ChangeData(BitmapData[54+i], -25);

    }

    m_objBitmap = BitmapHelper.BitmapFromBitmapData(BitmapData);

    Invalidate();

    Update();

}

private void btnMore_Click(object sender, EventArgs e)

{

    byte[] BitmapData = BitmapHelper.BitmapDataFromBitmap(m_objBitmap);

    int nPixels = m_objBitmap.Width * m_objBitmap.Height;

    for (int i=0; i    {

         BitmapData[54+i] = ChangeData(BitmapData[54+i], 25);

    }

    m_objBitmap = BitmapHelper.BitmapFromBitmapData(BitmapData);

    Invalidate();

    Update();

}
Conclusion
Once you get past the hurdle of getting started, image processing in C# is easy. In future articles, I'll use the same BitmapHelper class and then do some more interesting filtering.
REF: http://www.devsource.com/c/a/Languages/Image-Processing-in-C/2/

Object-Oriented State Machines - Techniques - Microsoft DevSource

Maybe you haven't worked with state machines since your college computer science courses. Jon Shemitz offers a reason to dust off the technique with .NET: object-oriented state machines can be easier to read and debug than their enum and

State machines are old technology, but they have many uses even for modern programmers. Probably the most common reason to build a state machine on .NET is emulating coroutines in recursive IEnumerator, such as one that must return a FileSystemInfo for every file and directory under a starting directory. Since this only takes three states, that's exactly what the sample code for this article (StateMachines.zip) can do. In Whidbey, iterators will eliminate the need to emulate coroutines, but Whidbey may still be as much as a year out—and even when Whidbey arrives, there will still be uses for state machines in .NET programming.

The basic idea is that you execute a state machine iteratively, when you need to extract the next value from an input stream, or when a new input comes in. The Current state controls which code is executed on each iteration; the Current state's handler may or may not change the Current state. If the Current state handler doesn't change the Current state, the same handler is invoked each time the state machine is iterated, until something changes.

The canonical way to implement a state machine is via an enum containing the name of every state, and a giant switch statement containing the handler for each enum. This compiles to a nice efficient jump table, but it's hard to maintain. When you add a new state, you have to change both the enum and the switch statement; plus, tracing execution means lots of jumping around the switch statement.

After writing an IEnumerator as an enum and switch state machine, I got to wondering: if an object is just a state packet, could you swap state by changing the CurrentState object, instead of the CurrentEnum value? Each state's handler would stand by itself as a standard method, which is easier to read than a long switch statement. Adding a new state just means adding a new class, which is easier than editing both the enum and the switch. And when a handler changes the CurrentState, you can use F12 to jump to the implementation of the next state, which is easier than having to find it by hand in a long switch statement.

The interface between IEnumerator and a state machine is pretty obvious: Reset() sets CurrentState to an initial state. MoveNext() iterates the state machine, either setting an internal CurrentValue field and returning true, or returning false to indicate that there are no (more) items. And the Current property exposes the internal CurrentValue field.

Each state handler needs to be able to change the enumerator's private CurrentState field. That is, we need a circular reference where the state machine refers to the current state object, and the state handler has a reference back to the state machine.


A Simple Implementation


A simple implementation gives the Enumerator object a private reference to a CurrentState object; each CurrentState object is an instance of a private class, nested within the Enumerator class, that has a reference to the Enumerator instance so that it can change the CurrentState.

The only problem, here, is that each different enumeration needs to build the state machine infrastructure all over again: reusability demands looser coupling.

Similarly, you could implement the current state handler as a virtual method, but this requires that all state classes descend from a common, abstract ancestor. While this is probably fine in at least 99.9% of all cases, using interfaces presents a bit more flexibility.

Thus, StateMachines.cs includes an abstract Enumerator class which expects state handler classes to support IStateHandler:

public interface IStateHandler
{
bool MoveNext(IStateMachine StateMachine) ;
}

The abstract Enumerator class supports IEnumerator via a generic state machine:

private IStateHandler  CurrentState;
private object CurrentValue ;

protected abstract IStateHandler StartState() ;

public void Reset()
{
CurrentState = /*abstract*/ StartState();
CurrentValue = null;
}

public object Current
{
get { return CurrentValue ; }
}

public bool MoveNext()
{
return CurrentState.MoveNext(this);
}

Reset() sets an initial state handler, and MoveNext() iterates the state machine by calling the current state's handler. Now, sometimes you have to cycle the state machine through a few iterations before you get to an output state. For example, a given directory may have subdirectories but no files, or you may have just processed the last subdirectory of a last subdirectory, and so on. In an enum and switch implementation, this might be done with code like this:

bool Deliver = false;
do
switch (CurrentState)
{
}
while (! Deliver);

This loops until a state handler sets Deliver to true.

In my loosely-coupled, object-oriented implementation, the abstract Enumerator class supports the IStateMachine interface, which provides state classes with a couple of utilities: bool Yield() sets the internal CurrentValue (and, optionally, the CurrentState) and returns true; bool Recurse() sets the CurrentState, and recursively returns whatever the new state's IMoveNext implementation returns:

bool IStateMachine.Yield(object NewValue)
{
CurrentValue = NewValue;
return true;
}

bool IStateMachine.Yield(object NewValue, IStateHandler NextState)
{
CurrentValue = NewValue;
CurrentState = NextState;
return true;
}

bool IStateMachine.Recurse(IStateHandler NextState)
{
if (NextState == null)
return false; // no next state, no next item
CurrentState = NextState;
return NextState.MoveNext(this); // recurse
}

That is, return Yield() sets a new CurrentValue and suspends the state machine; return Recurse() passes control to a new state handler (which may itself either Yield() or Recurse()) and passes the result back out to the "outer process."

The actual state classes are nested two deep in the AllFiles class, which implements IEnumerable by returning a new instance of the nested private class DirectoryEnumerator: Enumerator.DirectoryEnumerator overrides StartState() and contains the actual state handler classes, ShowDirectory, ShowFiles, and ShowSubdirectories.

The start state is ShowDirectory, with a null next state, and the DirectoryInfo passed to the AllFiles constructor:

protected override IStateHandler  StartState()
{
return new ShowDirectory(null, RootDirectory);
}

Thus, calling Enumerator.MoveNext() after a Reset() calls the ShowDirectory state handler:

public bool MoveNext(IStateMachine StateMachine)
{
IStateHandler subdirectories =
new ShowSubdirectories(NextState, Directory.GetDirectories());
IStateHandler files =
new ShowFiles(subdirectories, Directory.GetFiles());

// set Current to this directory; next time, scan files
return StateMachine.Yield(Directory, files);
}

This first creates a ShowSubdirectories state that "returns" to the ShowDirectory state's NextState. Then, it creates a ShowFiles state that "forwards" to the ShowSubdirectories state. Finally, it sets the CurrentValue to Directory and returns true.

The ShowFiles constructor calls GetEnumerator() on its second, IEnumerable parameter, and ShowFiles.MoveNext() either steps this array enumerator or passes control to the next, ShowSubdirectories state:

public bool MoveNext(IStateMachine StateMachine)
{
if (ArrayEnumerator.MoveNext())
return StateMachine.Yield((FileInfo) ArrayEnumerator.Current);
else
return StateMachine.Recurse(NextState);
}

The ShowSubdirectories constructor similarly calls GetEnumerator() on its second, IEnumerable parameter, and ShowSubdirectories.MoveNext() either steps this array enumerator and recursively sets the NewDirectory state to iterate the subdirectory, or "returns" to the parent directory state, which may be null:

public bool MoveNext(IStateMachine StateMachine)
{
if (ArrayEnumerator.MoveNext())
// at least one more subdirectory - recurse
return StateMachine.Recurse(
new ShowDirectory(this,
(DirectoryInfo) ArrayEnumerator.Current));
else
// no (more) subdirectories - pop
return StateMachine.Recurse(NextState);
}

As you can see, ShowSubdirectories is a strictly internal state that never yields control to the "outer process." It either invokes the ShowDirectory state with itself as the parent state, or it returns control to its own parent.


Now You Can Take

State machines often require more state data than simply which handler to invoke next. For example, the whole point of IEnumerator is the CurrentValue. In a one-off state machine, state handlers might be nested within the class that implements IEnumerator, and can thus freely access private fields and private properties. In a more loosely-coupled implementation, you can do as I've done, and build access to the extra state variables into the handlers' interface to the state machine.

One thing you don't need to do is to build an explicit state stack, as you often need to do in an an enum and switch implementation. Each state class contains a NextState field, which can be a "return address" (as in the ShowDirectory and ShowSubdirectories states) or a "forwarding address" (as in the ShowFiles state). In effect, the state classes maintain a sort of distributed stack, in the linked list of ShowDirectory state objects.

Overall, I find that object-oriented state machines are a lot easier to read and debug than their enum and switch equivalents. While there's a certain amount of programming overhead involved in creating a class for each state, this seems to me to be roughly comparable to the amount of overhead involved in updating both an enum and a switch.

While implementing a state machine via state objects is a bit more expensive than using a switch statement, I don't think this matters all that much since most uses of state machines focus on their ability to maintain context across multiple invocations, and speed is not a primary concern.