John McIlhinney's .NET Developer Blog

Keyboard Events in Windows Forms

2010-08-11T21:10:00.008+10:00

When the user presses a key on the keyboard, active WinForms controls raise multiple events. In the order they are raised, those events are PreviewKeyDown, KeyDown, KeyPress and KeyUp. Following is an explanation of when and how to use each one.

The first event raised by a control is the PreviewKeyDown event. As with all events raised by the Framework, the data for the event is provided by the e parameter; in this case, a PreviewKeyDownEventsArgs object. The purpose of this event is basically the IsInputKey property of that object.

The PreviewKeyDown event was added to the .NET Framework in version 2.0. Prior to that, the only way to specify whether a key is a regular input key or not was to derive your own custom control and override the IsInputKey method. With the addition of the PreviewKeyDown event, we can now handle it for a standard control and specify whether or not the key that was depressed is a regular input key. This way we can more easily provide one-off custom behaviour. For more information on treating a key as a regular input key, consult the MSDN documentation for the Control.IsInputKey method.

There are some similarities between the PreviewKeyDownEventArgs received by the PreviewKeyDown event handler and the KeyEventArgs received by the KeyDown and KeyUp event handlers, so it’s worth covering them altogether now. The way in which you determine the key, or key combination, that was depressed (PreviewKeyDown and KeyDown) or released (KeyUp) is the same for all three events.

The first common property of note is KeyCode. It returns a value from the Keys enumeration that corresponds to the key that was just depressed or released. The KeyValue property is similar, but it returns an Int32 containing the numeric value of the key. The Alt, Control and Shift properties are all Booleans and indicate whether the corresponding modifier key was down when the key contained in KeyCode was depressed or released. The Modifiers property provides the state of all three modifiers together in a bitwise combination of Keys.Alt, Keys.Control and Keys.Shift values, depending on which modifiers are down. The KeyData property is a bitwise combination of KeyCode and Modifiers.

The next event to be raised is the KeyDown event. Note that, if the key pressed is a special key and the PreviewKeyDown event handler does not set e.IsInputKey to True, the KeyDown event and all subsequent events will never be raised. When a control does raise the KeyDown event, by default, neither its parent control nor any other ancestor controls will raise a KeyDown event. The same is true of the KeyPress and KeyUp events. You can override this default behaviour by setting the KeyPreview property of the form containing the control to True. The form will now raise the corresponding event before the control does. In this case, we can set e.Handled to True in the form’s event handler to prevent the control from also raising the same event.

The purpose of the KeyDown event is to trap specific keys, or key combinations, as they are depressed. As mentioned earlier, the KeyDown event receives a KeyEventArgs object as an argument, which has KeyCode, KeyValue, Alt, Control, Shift, Modifiers and KeyData properties with which to determine which key or keys were depressed. By way of example, here are multiple ways that you could detect the Ctrl+W and Ctrl+Shift+W key combinations:

C#

// Determine whether the W key was just depressed.
if (e.KeyCode == Keys.W)
{
    // Determine whether the desired modifier keys were down at the time.
    if (e.Control && !e.Alt)
    {
        if (e.Shift)
        {
            // Ctrl+Shift+W
        }
        else
        {
            // Ctrl+W
        }
    }
}

// Determine whether the W key was just depressed.
if (e.KeyCode == Keys.W)
{
    // Determine whether the desired modifier keys were down at the time.
    switch (e.Modifiers)
    {
        case Keys.Control:
            // Ctrl+W
            break;
        case Keys.Control | Keys.Shift:
            // Ctrl+Shift+W
            break;
    }
}

// Determine whether the W key was just depressed while the desired modifier keys were down.
switch (e.KeyData)
{
    case Keys.Control | Keys.W:
        break;
        // Ctrl+W
    case Keys.Control | Keys.Shift | Keys.W:
        break;
        // Ctrl+Shift+W
}

'Determine whether the W key was just depressed.
If e.KeyCode = Keys.W Then
    'Determine whether the desired modifier keys were down at the time.
    If e.Control AndAlso Not e.Alt Then
        If e.Shift Then
            'Ctrl+Shift+W
        Else
            'Ctrl+W
        End If
    End If
End If

'Determine whether the W key was just depressed.
If e.KeyCode = Keys.W Then
    'Determine whether the desired modifier keys were down at the time.
    Select Case e.Modifiers
        Case Keys.Control
            'Ctrl+W
        Case Keys.Control Or Keys.Shift
            'Ctrl+Shift+W
    End Select
End If

'Determine whether the W key was just depressed while the desired modifier keys were down.
Select Case e.KeyData
    Case Keys.Control Or Keys.W
        'Ctrl+W
    Case Keys.Control Or Keys.Shift Or Keys.W
        'Ctrl+Shift+W
End Select

Hopefully this demonstrates that, generally speaking, when you’re interested in specific key combinations, using the KeyData property produces the most succinct code.

Another important note is how to detect the Ctrl, Shift and Alt keys. The keys are treated differently when you want to detect that the key itself was just depressed compared to when you want to detect that the key was already down when another key was depressed. The following code detects when each of the Ctrl, Shift and Alt keys themselves are depressed:

C#

switch (e.KeyCode)
{
    case Keys.LControlKey:
        // The left Ctrl key was just depressed.
        break;
    case Keys.RControlKey:
        // The right Ctrl key was just depressed.
        break;
    case Keys.LShiftKey:
        // The left Shift key was just depressed.
        break;
    case Keys.RShiftKey:
        // The right Shift key was just depressed.
        break;
    case Keys.LMenu:
        // The left Alt key was just depressed.
        break;
    case Keys.RMenu:
        // The right Alt key was just depressed.
        break;
}

switch (e.KeyCode)
{
    case Keys.ControlKey:
        // Either Ctrl key was just depressed.
        break;
    case Keys.ShiftKey:
        // Either Shift key was just depressed.
        break;
    case Keys.Menu:
        // Either Alt key was just depressed.
        break;
}

Select Case e.KeyCode
    Case Keys.LControlKey
        'The left Ctrl key was just depressed.
    Case Keys.RControlKey
        'The right Ctrl key was just depressed.
    Case Keys.LShiftKey
        'The left Shift key was just depressed.
    Case Keys.RShiftKey
        'The right Shift key was just depressed.
    Case Keys.LMenu
        'The left Alt key was just depressed.
    Case Keys.RMenu
        'The right Alt key was just depressed.
End Select

Select Case e.KeyCode
    Case Keys.ControlKey
        'Either Ctrl key was just depressed.
    Case Keys.ShiftKey
        'Either Shift key was just depressed.
    Case Keys.Menu
        'Either Alt key was just depressed.
End Select

Note that you can detect either that specifically the left or right key of the two was depressed, or just that either of them was. Contrast the code above with that below, which detects whether the Ctrl, Shift or Alt keys were already down when some other key was depressed:

C#

switch (e.Modifiers)
{
    case Keys.Control:
        // Ctrl
        break;
    case Keys.Shift:
        // Shift
        break;
    case Keys.Alt:
        // Alt
        break;
    case Keys.Control | Keys.Shift:
        // Ctrl+Shift
        break;
    case Keys.Control | Keys.Alt:
        // Ctrl+Alt
        break;
    case Keys.Shift | Keys.Alt:
        // Shift+Alt
        break;
    case Keys.Control | Keys.Shift | Keys.Alt:
        // Ctrl+Shift+Alt
        break;
}

Select Case e.Modifiers
    Case Keys.Control
        'Ctrl
    Case Keys.Shift
        'Shift
    Case Keys.Alt
        'Alt
    Case Keys.Control Or Keys.Shift
        'Ctrl+Shift
    Case Keys.Control Or Keys.Alt
        'Ctrl+Alt
    Case Keys.Shift Or Keys.Alt
        'Shift+Alt
    Case Keys.Control Or Keys.Shift Or Keys.Alt
        'Ctrl+Shift+Alt
End Select

Note the difference between the Keys values used to detect the keys themselves, i.e. ControlKey, ShiftKey and Menu, and the corresponding modifier keys, i.e. Control, Shift and Alt.

Now that we’ve seen the KeyDown event, let’s revisit the PreviewKeyDown event for a moment with an example that involves both. As mentioned earlier, when a special key is detected, no KeyDown event is raised. The example provided in the MSDN documentation for the Control.IsInputKey method shows how create a custom TextBox control that overrides the IsInputKey method and treats the Tab key as a regular input key. This allows the Tab key to raise a KeyDown event, upon which the control replaces the selected text with four spaces. Following is that example reworked using the PreviewKeyDown and KeyDown events of a standard TextBox, rather than the IsInputKey and OnKeyDown methods of a custom control.

C#

private void textBox1_PreviewKeyDown(object sender, PreviewKeyDownEventArgs e)
{
    if (e.KeyData == Keys.Tab)
    {
        e.IsInputKey = true;
    }
}

private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
    if (e.KeyData == Keys.Tab)
    {
        this.textBox1.SelectedText = "    ";
    }
}

Private Sub TextBox1_PreviewKeyDown(ByVal sender As Object, _
                                    ByVal e As PreviewKeyDownEventArgs) Handles TextBox1.PreviewKeyDown
    If e.KeyData = Keys.Tab Then
        e.IsInputKey = True
    End If
End Sub

Private Sub TextBox1_KeyDown(ByVal sender As Object, _
                             ByVal e As KeyEventArgs) Handles TextBox1.KeyDown
    If e.KeyData = Keys.Tab Then
        Me.TextBox1.SelectedText = "    "
    End If
End Sub

Normally, the Tab key would be consumed by the system, causing focus to shift to the next control and no KeyDown event to be raised. The code above detects the Tab key when it is depressed in the absence of modifier keys and specifies that it should be treated as a regular input key. As a result, focus does not shift and the KeyDown event is raised, when four spaces are entered into the TextBox in place of the currently selected text.

The other property of interest provided by the KeyEventArgs class is SupressKeyPress, which prevents keyboard input being passed on to the control. The following example uses the SuppressKeyPress property to ignore all upper case input.

C#

private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
    if (e.Modifiers == Keys.Shift)
    {
        e.SuppressKeyPress = true;
    }
}

Private Sub TextBox1_KeyDown(ByVal sender As Object, _
                             ByVal e As KeyEventArgs) Handles TextBox1.KeyDown
    If e.Modifiers = Keys.Shift Then
        e.SuppressKeyPress = True
    End If
End Sub

That brings us to the KeyPress event, which is raised after KeyDown and before KeyUp if and only if the key or key combination depressed should result in input being entered into the control. Unlike the other events mentioned here, which are about actual keys on the keyboard, KeyPress is about the text that those keys produce.

The KeyPress event handler receives a KeyPressEventArgs object. This object has a Handled property that behaves just like the KeyEventArgs.Handled property in the KeyDown and KeyUp event handlers. The only other property is KeyChar, which contains the Char that is actually entered into the control. Following is an example using the KeyPress event to ignore upper case input, as the previous example does using KeyDown.

C#

private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{
    if (char.IsUpper(e.KeyChar))
    {
        e.Handled = true;
    }
}

Private Sub TextBox1_KeyPress(ByVal sender As Object,
                              ByVal e As KeyPressEventArgs) Handles TextBox1.KeyPress
    If Char.IsUpper(e.KeyChar) Then
        e.Handled = True
    End If
End Sub

It’s worth noting that the KeyChar property was read-only in early versions of .NET but, from .NET 2.0, was made read/write. This means that you can change the input character in the KeyPress event handler if desired. The following example converts all upper case input to lower case.

C#

private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{
    if (char.IsUpper(e.KeyChar))
    {
        e.KeyChar = char.ToLower(e.KeyChar);
    }
}

Private Sub TextBox1_KeyPress(ByVal sender As Object, _
                              ByVal e As KeyPressEventArgs) Handles TextBox1.KeyPress
    If Char.IsUpper(e.KeyChar) Then
        e.KeyChar = Char.ToLower(e.KeyChar)
    End If
End Sub

The last event in the sequence is KeyUp, which is raised when a key is released. The KeyUp event handler also receives a KeyEventArgs object and, besides the fact that SuppressKeyPress isn't really of use, all properties behave as they do for KeyDown. Generally speaking, the KeyDown and KeyPress events get used more than KeyUp, but there are times when you want to perform an action when a key is released rather than when it’s depressed.

Finally, it’s worth noting how these events behave when a key is held down. When a key is first depressed, the PreviewKeyDown event is raised first, followed by the KeyDown event and then, if applicable, the KeyPress event. If the key is held down then this sequence will be repeated over and over. When the key is released, a single KeyUp event is raised.

Hopefully this information makes it clear how, when and why to make use the four events associated with .NET keyboard input.

Referencing Assemblies and Importing Namespaces

2009-12-19T16:05:00.005+11:00

I often see people confused about the difference between referencing an assembly and importing a namespace, and why we need to do either. I’ll try to address that confusion here.

First, what is an assembly? An assembly is a .NET executable file, i.e. EXE or DLL. Whenever you build a .NET project, you are compiling your source files into an assembly.

Think of an assembly as a physical collection of .NET types. A type is a class, structure, enumeration, delegate, etc. An assembly is a physical file that contains the definitions of one or more types. You can move that assembly around and thereby move those types around. I can create an assembly here and then pass you the file and you can then use the types it contains.

Now, by default, each project pretty much only knows about the types for which it contains the definitions. Such a project would be useless though. There are certain parts of the .NET Framework that are pretty much essential to all projects. The assemblies containing these essential types, e.g. String and Int32, are referenced by default in every project.

To see this, create a new project in Visual Studio. Under your preferred language in the New Project dialogue, choose Windows and then select the Empty Project template. Once the project is created, open the Object Browser window and select My Solution in the Browse field at the top. Each root node in the tree is an assembly that your project knows about. You’ll see your project itself and mscorlib at least. If you’re targeting .NET 3.5 or later you will also see System.Core and if you’re language is VB then you will also see System and Microsoft.VisualBasic. These are the assemblies that your project references by default. Basically, there’s a line written in a file in your project that says “mscorlib.dll exists and this is where to find it”.

At this point, your project knows about ALL those assemblies and ONLY those assemblies, so it knows about ALL the types declared in those assemblies and ONLY the types declared in those assemblies. Any type that isn’t declared in one of those assemblies is unknown to your project and therefore unusable. Let’s say, for instance, that you wanted to connect to a SQL Server database. For that you would need to use the SqlConnection class. That class is not declared in any of the assemblies your project references by default so your project doesn’t know that the class exists. The SqlConnection class is declared in the System.Data.dll assembly, so we must reference this assembly in our project in order to use the SqlConnection class. Let’s now look at how to do that and how the process differs between VB and C# projects.

In C#, references are displayed in the Solution Explorer by default. Open the Solution Explorer and expand the node for your C# project. Try to expand the References node and notice that you can’t, which is because it contains no references. The default references of mscorlib.dll and System.Core.dll are considered so fundamental that they are not listed with other references, so that you cannot remove them. Right-click the References node and select Add Reference to open the Add Reference dialogue. Alternatively, select Project -> Add Reference from the main menu.

In VB, references are not displayed in the Solution Explorer by default. You must first click the Show All Files button in the Solution Explorer to reveal the References node, at which point you can right-click it as mentioned above. VB also supports adding a reference from the Project menu, as well as a third option that is not available to C# developers. You can first open the project properties, by double-clicking the My Project node in the Solution Explorer or any of a variety of other ways, and then select the References page. The top half of the page lists the assemblies referenced by the project. Click the Add button under the list to open the Add Reference dialogue.

Once you have opened the Add Reference dialogue, you have several options. The dialogue layout changed considerably between VS 2008 and 2010 but the options available are basically the same. The most common option will be to reference an assembly installed in the Global Assembly Cache (GAC). This includes any assemblies from the .NET Framework itself and any other .NET components that you have installed. The GAC is a location for common assemblies, so that there is only one copy for all projects that use them. You also have the option to browse for an assembly. You would do this for libraries that you have previously created in other projects as well as .NET components that you have downloaded but have no installation routine. Such assemblies will be copied into the output folder of each project that references them. A third option is to reference other projects in the same solution. Only projects that output a library, i.e. DLL, can be referenced. Referencing a project is like referencing the assembly that it outputs except that it allows you to update the assembly without having to recreate the reference.

That’s all there is to referencing assemblies: open the Add Reference dialogue and navigate to the desired assembly. That’s all that’s required to use a type declared in an external assembly. If you ever need to use a type from the .NET Framework but you don’t know where it’s declared, simply open the MSDN documentation for that type. At the very top of every type’s overview topic is the assembly that it’s declared in. Reference that assembly and you’re good to go.

Immediately under the assembly is the namespace that the type is a member of. You never actually need to import a namespace to use a type. Importing namespaces is simply a convenience that makes code easier to write and read. Let’s now look at what a namespace is, as well as how and why you might import one.

Unlike an assembly, which is a physical collection of types, a namespace is a logical collection of types. That means that, while every type is a member of a namespace, a namespace has no physical boundary. You can point to a DLL file and say “there’s the SomeAssembly.dll assembly” but you can never point to a namespace because it doesn’t physically exist.

The reason a namespace is called a namespace is because it is a logical space within which every type name is unique. For instance, there are several classes named TextBox in the .NET Framework alone, not to mention all those that developers around the world have created. When you refer to the TextBox type in code, how do you know which one you’re referring to? You know because each type is qualified by its namespace. For instance, there is the System.Windows.Forms.TextBox class and there is the System.Web.UI.WebControls.TextBox class, plus others besides. It’s similar to there being two people named John at your work place but you know which one someone is talking about because they qualify the name, e.g. John Smith and John Williams or Big John and Little John.

Now, it’s not strictly true that every type in the same namespace must be unique. There’s nothing to stop me declaring the MyControl class as a member of the MyControls namespace and someone else doing the same on the other side of the world. In fact, there’s nothing to stop me doing it in two different projects on the same machine, or even in the same solution. What is true is that you can never have two types with the same name in the same namespace in the same project. That will cause a compilation error.

We don’t actually have to import any namespace to use its member types. You can happily use the fully qualified name of every type in your code, e.g.

C#

string str = ((System.Windows.Forms.TextBox) sender).Text;

Dim str As String = DirectCast(sender, System.Windows.Forms.TextBox).Text

If you do that though, your code will tend to look rather cluttered and will be less comfortable to read. Generally speaking, you should import the namespaces containing the types you use and use the unqualified type names in your code. So, if you import the System.Windows.Forms namespace, the code above can be replaced with the following:

C#

string str = ((TextBox) sender).Text;

Dim str As String = DirectCast(sender, TextBox).Text

Note that, even though the String class is a member of the System namespace, there’s never a need to use System.String, even if you haven’t imported the System namespace. That’s because string (C#) and String (VB) are native data types that you can use to alias the .NET System.String class. The same goes for the int (C#) and Integer (VB) data types and the System.Int32 structure, amongst others.

Getting back to namespaces, how exactly do we import one? There is only one way in C# and that is with the using directive. At the top of a code file you specify a namespace preceded by the using keyword. For instance, to be able to use the WinForms TextBox class unqualified, as was done above, we must import the System.Windows.Forms namespace:

C#

using System.Windows.Forms;

When you create a new code file in C#, you’ll normally find a few using directives added by default. Which ones they are depends on the item template used to create the file. When you create a form in a WinForms project, the System.Windows.Forms namespace is one of those imported by default, because its use is expected to be very common. Note that a using directive applies only to the file it appears in.

Importing a namespace in VB can be similar, except that you use the Imports keyword. The equivalent of the C# using directive above would be:

VB

Imports System.Windows.Forms

When you create a code file in VB, notice that there are no Imports statements added by default, yet you are still able to refer to many types unqualified. This is because VB provides a second option for importing namespaces and it’s that method that’s used by default. Going back to the References page of the project properties, notice that the bottom half of the page contains a list of namespaces. That list contains all the namespaces that have members declared in your project and the assemblies referenced by your project. You simply have to check the box next to a namespace to import it, and some are already checked by default.

Importing a namespace on the References page differs from using the Imports keyword because it is project-wide. Just like the using directive in C#, the VB Imports keyword affects only the code file it appears in, while the References page imports namespaces for every code file in a project.

So, what are my recommendations for importing namespaces? I used the following rules when I was writing all my code myself:

If a type appears only once in code, use the fully-qualified name.
In VB, import a namespace project-wide by default.
In VB, choose file-level imports over project-wide imports if you want to refer to two like-named types from different namespaces in two different code files.
In the case of a name clash, i.e. using two like-named types in the same code file, use the fully qualified name of both types.

Now that I use ReSharper, those rules have changed a little bit because, when an unqualified name is typed, ReSharper will offer to create a file-level import of the appropriate namespace in both C# and VB. Other code-generation tools may be similar.

Another notable difference between VB and C# is support for partial qualifying namespaces. For instance, let’s say that you have imported the System namespace. In C#, if you want to refer to a type in a child namespace, you must still use the fully-qualified name, e.g.

C#

var field = new System.Windows.Forms.TextBox();

In VB, on the other hand, you can use a partially-qualified name, omitting the imported System namespace:

VB

Dim field As New Windows.Forms.TextBox

So, in summary, you must reference an assembly, i.e. a DLL, if you want to use any type declared within it. Importing a namespace is never necessary and simply allows you to use types that are members of that namespace without qualifying the name. If you ever want to use a type but you don’t know what assembly it’s declared in or what namespace it’s a member of, simply consult the MSDN documentation for that type. It will display both that the very top of the page.

Defining and Raising Custom Events

2009-11-01T18:41:00.009+11:00

We’ve all handled events before, e.g. the Load of a Form, the Click of a Button or the TextChanged of a TextBox. Many, even relatively experienced, developers aren’t too sure on how to raise events from their own classes though.

The .NET Framework provides a fairly simple mechanism for events but, more than that, a convention is used throughout the Framework for employing that mechanism. While you don’t have to, it’s a good idea to stick to that convention in your own code. Doing so means that your interface will be consistent with the rest of the Framework, and consistency is a good thing. Using your types will feel familiar to yourself and others because they will behave just like the types you’re used to using from the Framework.

First up, let’s define exactly what an event is. In conceptual terms, an event is a notification that something has happened. Just like your microwave oven makes a “beep” or “ding” sound to notify you that it has finished cooking your food, so a .NET object raises an event to notify any other objects that are listening that it has done something or something has been done to it.

Technically, an event is a member of a type, just like properties and methods, except its type is a delegate rather than a class or structure. A delegate, or at least an instance of a delegate, is an object that contains a reference to a method. In the case of an event, the method the delegate refers to is the event handler. As an example, the Button class has a Click event defined as type EventHandler. The EventHandler delegate is defined like so:

C#

public delegate void EventHandler(object sender, EventArgs e);

Public Delegate Sub EventHandler(ByVal sender As Object, ByVal e As EventArgs)

That method signature should look familiar because it looks a lot like the signature of an event handler method, e.g.

C#

private void button1_Click(object sender, EventArgs e)

    // ...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

    '...

End Sub

When you create a handler for the Click event of a Button in your form, what you’re actually doing is creating an instance of the EventHandler delegate, providing it a reference to your method and then assigning it to the Click member of the Button. When the Button is clicked, it goes to its Click event and invokes the delegate it finds there, which in turn invokes your method.

This post isn’t about delegates though, so if you want more information on their inner workings you should look for that elsewhere. This post is about defining and raising custom events, so let’s get on with that. The first thing we need is a class that will raise an event.

C#

public class Person

    private string _firstName;

    private string _lastName;

    public string FirstName

get

            return this._firstName;

set

            this._firstName = value;

    public string LastName

get

            return this._lastName;

set

            this._lastName = value;

Public Class Person

    Private _firstName As String

    Private _lastName As String

    Public Property FirstName() As String

Get

            Return Me._firstName

        End Get

        Set(ByVal value As String)

            Me._firstName = value

        End Set

    End Property

    Public Property LastName() As String

Get

            Return Me._lastName

        End Get

        Set(ByVal value As String)

            Me._lastName = value

        End Set

    End Property

End Class

So, we have a class with two properties, which we can get and set. It might be convenient for us to receive a notification from an instance of this class when those property values change. For instance, if you are displaying the person’s name in some TextBoxes and the name gets changed in code, you’d want to know about it so that you could update the TextBoxes, right? For that we need an event to be raised when the property value changes.

The first thing to do is to declare the events as members in the Person class. Considering that these events are notifications of the FirstName and LastName property values being changed, it’s most appropriate that they be named “FirstNameChanged” and “LastNameChanged”, which is convention throughout the Framework.

C#

public event EventHandler FirstNameChanged;

public event EventHandler LastNameChanged;

Public Event FirstNameChanged As EventHandler

Public Event LastNameChanged As EventHandler

Once the events are declared, you’ll find that the Person class now has events you can handle. That doesn’t do us any good if the events are never raised though, so let’s add some basic code to raise those events when the corresponding property values change.

C#

public string FirstName

get

        return this._firstName;

set

        this._firstName = value;

        if (this.FirstNameChanged != null)

            this.FirstNameChanged(this, EventArgs.Empty);

public string LastName

get

        return this._lastName;

set

        this._lastName = value;

        if (this.LastNameChanged != null)

            this.LastNameChanged(this, EventArgs.Empty);

Public Property FirstName() As String

Get

        Return Me._firstName

    End Get

    Set(ByVal value As String)

        Me._firstName = value

        RaiseEvent FirstNameChanged(Me, EventArgs.Empty)

    End Set

End Property

Public Property LastName() As String

Get

        Return Me._lastName

    End Get

    Set(ByVal value As String)

        Me._lastName = value

        RaiseEvent LastNameChanged(Me, EventArgs.Empty)

    End Set

End Property

Note that, when the event is raised, the object passes itself as the first argument. The first argument is passed to the sender parameter, so the object is identifying itself as the sender, i.e. the object that raised the event.

The second argument is EventArgs.Empty, which is part of the standard pattern. You should be fairly used to your event handlers having a sender parameter of type Object and an e parameter of type EventArgs or something like it. The second parameter is the event’s data. The EventArgs class acts as a place-holder for events that don’t have any data and as a base class for the types used by events that do have data. Rather than create a new EventArgs object each time, we use the static/Shared Empty field, which returns an empty EventArgs object.

Now, the code we have will do the job but we can make it better. First of all, the event will be raised every time the corresponding property is set, whether the value actually changes or not. We should actually test the new value and only raise the event if it’s different to the old value.

C#

public string FirstName

get

        return this._firstName;

set

        if (value != this._firstName)

            this._firstName = value;

            if (this.FirstNameChanged != null)

                this.FirstNameChanged(this, EventArgs.Empty);

public string LastName

get

        return this._lastName;

set

        if (value != this._lastName)

            this._lastName = value;

            if (this.LastNameChanged != null)

                this.LastNameChanged(this, EventArgs.Empty);

Public Property FirstName() As String

Get

        Return Me._firstName

    End Get

    Set(ByVal value As String)

        If value <> Me._firstName Then

            Me._firstName = value

            RaiseEvent FirstNameChanged(Me, EventArgs.Empty)

        End If

    End Set

End Property

Public Property LastName() As String

Get

        Return Me._lastName

    End Get

    Set(ByVal value As String)

        If value <> Me._lastName Then

            Me._lastName = value

            RaiseEvent LastNameChanged(Me, EventArgs.Empty)

        End If

    End Set

End Property

The next step is to implement the common pattern that is used throughout the Framework for raising events. That involves declaring a method whose purpose in life it is to raise the event.

C#

protected virtual void OnFirstNameChanged(EventArgs e)

    if (this.FirstNameChanged != null)

        this.FirstNameChanged(this, e);

protected virtual void OnLastNameChanged(EventArgs e)

    if (this.LastNameChanged != null)

        this.LastNameChanged(this, e);

Protected Overridable Sub OnFirstNameChanged(ByVal e As EventArgs)

    RaiseEvent FirstNameChanged(Me, e)

End Sub

Protected Overridable Sub OnLastNameChanged(ByVal e As EventArgs)

    RaiseEvent LastNameChanged(Me, e)

End Sub

Such a method is used basically so that the event is only ever raised in one place. If you ever want to raise the event you simply call this method.

C#

public string FirstName

get

        return this._firstName;

set

        if (value != this._firstName)

            this._firstName = value;

            this.OnFirstNameChanged(EventArgs.Empty);

public string LastName

get

        return this._lastName;

set

        if (value != this._lastName)

            this._lastName = value;

            this.OnLastNameChanged(EventArgs.Empty);

Public Property FirstName() As String

Get

        Return Me._firstName

    End Get

    Set(ByVal value As String)

        If value <> Me._firstName Then

            Me._firstName = value

            Me.OnFirstNameChanged(EventArgs.Empty)

        End If

    End Set

End Property

Public Property LastName() As String

Get

        Return Me._lastName

    End Get

    Set(ByVal value As String)

        If value <> Me._lastName Then

            Me._lastName = value

            Me.OnLastNameChanged(EventArgs.Empty)

        End If

    End Set

End Property

The primary advantage of this is linked to the way the method is declared. Notice that the methods above are declared protected/Protected and virtual/Overridable. This means that any derived classes can override these methods and change the type’s behaviour when the events are raised. The derived class simply calls the base implementation to raise the event, so code can be added before that call or after it to add new behaviour. This new behaviour will be invoked even if the method is called from the base class, such is the behaviour of overridden members. If the event was raised directly in the property setters of the base class then derived classes wouldn’t be able to add new behaviour because the properties are not declared virtual/Overridable.

It’s also worth noting that another part of the pattern is naming the method that raises an event the same as the event it raises, but with the “On” prefix added. Note that in .NET there is no OnLoad or OnClick event. The events are named Load and Click and the methods that raise them are name OnLoad and OnClick.

So, we now have a full, working implementation that follows the standard .NET pattern. We’ve declared the events, declared methods to raise them and then called those methods when the event notification is required. But wait; there’s more!

I said earlier that events may or may not have data associated with them. In the case of our FirstNameChanged and LastNameChanged events there is no data. Listeners are simply being notified that a property value has changed. If they want to know the new value they can simply get the property. As such an EventArgs object is used as a place-holder for the event handlers. Now let’s consider a situation where the event handlers will require some information that they cannot otherwise access themselves.

In that situation the object raising the event needs to pass that data to the event handler. It does this through the e parameter. In such cases the e parameter cannot be type EventArgs because the EventArgs class has no members that can store such data. As a result, we need to use a class that inherits EventArgs and then adds the required members. The Framework already contains numerous such class, e.g. MouseEventArgs and PaintEventArgs. You should use one of those existing classes if it’s appropriate to your event, otherwise you should define your own class.

For this example, let’s consider the situation where we want to notify listeners that the property value is going to change before it happens, in addition to notifying them that the property value has changed after it happens. If the property value hasn’t actually changed yet then there’s no way an event handler can get the new value, unless that data is passed to the event handler explicitly. For this we’ll define our own derived EventArgs class. We could define one for each event but they are both notifying about a String property changing so we can use the same class for both.

C#

public class StringPropertyChangingEventArgs : EventArgs

    private readonly string _proposedValue;

    public string ProposedValue

get

            return this._proposedValue;

    public StringPropertyChangingEventArgs(string proposedValue)

        this._proposedValue = proposedValue;

Public Class StringPropertyChangingEventArgs

    Inherits EventArgs

    Private ReadOnly _proposedValue As String

    Public ReadOnly Property ProposedValue() As String

Get

            Return Me._proposedValue

        End Get

    End Property

    Public Sub New(ByVal proposedValue As String)

        Me._proposedValue = proposedValue

    End Sub

End Class

Note that the StringPropertyChangingEventArgs class inherits the EventArgs class and then adds a property for the new data we need: the proposed value of the property. There’s no need to include the current value of the property because the event handler can get that itself.

There are two more points to note here. The name of the class ends with “EventArgs”, which is part of the convention. Another is using the term “Changing” for an event related to a proposed change to a property value. This goes along with the convention of using “Changed” for an event related to a property that has changed already. For the events you would normally prefix the “Changing” with the name of the property. We can’t do that for this class though, because it’s to be used by more than one property/event.

Now that we have a type to pass the event data to the event handler, the next thing we need is an event. In the case of the FirstNameChanged and LastNameChanged events, we declared them as type EventHandler. That’s not possible for our FirstNameChanging and LastNameChanging events though because they will require a StringPropertyChangingEventArgs parameter rather than an EventArgs parameter, so their signatures will not match that of the EventHandler delegate. We need a different delegate. For this we have two choices. Firstly, we could define our own delegate with a signature that matches that of our event handlers. This is good practice if you’re exposing an event outside the current assembly. We’ll look at that option later but, if the event is only going to be used within the current project, it’s considered good practice to use the generic EventHandler(TEventArgs) delegate.

C#

public event EventHandler<StringPropertyChangingEventArgs> FirstNameChanging;

public event EventHandler<StringPropertyChangingEventArgs> LastNameChanging;

Public Event FirstNameChanging As EventHandler(Of StringPropertyChangingEventArgs)

Public Event LastNameChanging As EventHandler(Of StringPropertyChangingEventArgs)

In this case the generic type of the delegate specifies the type of the second parameter of the event handler. Note that this type must be EventArgs or derived from EventArgs.

The next step is to declare methods to raise the events. They will be much as were those for the other events but with a different parameter type.

C#

protected virtual void OnFirstNameChanging(StringPropertyChangingEventArgs e)

    if (this.FirstNameChanging != null)

        this.FirstNameChanging(this, e);

protected virtual void OnLastNameChanging(StringPropertyChangingEventArgs e)

    if (this.LastNameChanging != null)

        this.LastNameChanging(this, e);

Protected Overridable Sub OnFirstNameChanging(ByVal e As StringPropertyChangingEventArgs)

    RaiseEvent FirstNameChanging(Me, e)

End Sub

Protected Overridable Sub OnLastNameChanging(ByVal e As StringPropertyChangingEventArgs)

    RaiseEvent LastNameChanging(Me, e)

End Sub

All that remains is for us to call the methods to actually raise the events. Remember that these events are intended to notify our listeners that a property value is going to change, so they must be raised before the actual value changes.

C#

public string FirstName

get

        return this._firstName;

set

        if (value != this._firstName)

            this.OnFirstNameChanging(new StringPropertyChangingEventArgs(value));

            this._firstName = value;

            this.OnFirstNameChanged(EventArgs.Empty);

public string LastName

get

        return this._lastName;

set

        if (value != this._lastName)

            this.OnLastNameChanging(new StringPropertyChangingEventArgs(value));

            this._lastName = value;

            this.OnLastNameChanged(EventArgs.Empty);

Public Property FirstName() As String

Get

        Return Me._firstName

    End Get

    Set(ByVal value As String)

        If value <> Me._firstName Then

            Me.OnFirstNameChanging(New StringPropertyChangingEventArgs(value))

            Me._firstName = value

            Me.OnFirstNameChanged(EventArgs.Empty)

        End If

    End Set

End Property

Public Property LastName() As String

Get

        Return Me._lastName

    End Get

    Set(ByVal value As String)

        If value <> Me._lastName Then

            Me.OnLastNameChanging(New StringPropertyChangingEventArgs(value))

            Me._lastName = value

            Me.OnLastNameChanged(EventArgs.Empty)

        End If

    End Set

End Property

That’s done but, really, what use is that event? It tells our listeners that the property value is about to change and what it’s about to change to, but to what use can that information be put? Normally, the reason you want to know that a property is about to change is so that you can abort the change if the value is unacceptable for some reason. That can’t be done in this case though, because the property value changes after the event has been handled no matter what.

The ability to cancel an action from an event handler already exists in the Framework. Consider the FormClosing event. You can ask the user for confirmation at that stage and, if they decide they don’t want to close the form, you simply set the e.Cancel property to True. This property is available because the e parameter is type CancelEventArgs. We can’t use CancelEventArgs for our methods though, because we need to provide the extra data consisting of the proposed property value. The solution is to have our StringPropertyChangingEventArgs class inherit CancelEventArgs instead of EventArgs. That way we get the Cancel property and we can add our own data to that.

C#

public class StringPropertyChangingEventArgs : CancelEventArgs

    private readonly string _proposedValue;

    public string ProposedValue

get

            return this._proposedValue;

    public StringPropertyChangingEventArgs(string proposedValue)

        this._proposedValue = proposedValue;

Public Class StringPropertyChangingEventArgs

    Inherits CancelEventArgs

    Private ReadOnly _proposedValue As String

    Public ReadOnly Property ProposedValue() As String

Get

            Return Me._proposedValue

        End Get

    End Property

    Public Sub New(ByVal proposedValue As String)

        Me._proposedValue = proposedValue

    End Sub

End Class

The class name hasn’t changed so we don’t need to change any of the event and method declarations. We do, however, have to change the code in the property to handle the situation where the listener cancels the action. In that case the property value shouldn’t change.

C#

public string FirstName

get

        return this._firstName;

set

        if (value != this._firstName)

            StringPropertyChangingEventArgs e = new StringPropertyChangingEventArgs(value);

            this.OnFirstNameChanging(e);

            if (!e.Cancel)

                this._firstName = value;

                this.OnFirstNameChanged(EventArgs.Empty);

public string LastName

get

        return this._lastName;

set

        if (value != this._lastName)

            StringPropertyChangingEventArgs e = new StringPropertyChangingEventArgs(value);

            this.OnLastNameChanging(e);

            if (!e.Cancel)

                this._lastName = value;

                this.OnLastNameChanged(EventArgs.Empty);

Public Property FirstName() As String

Get

        Return Me._firstName

    End Get

    Set(ByVal value As String)

        If value <> Me._firstName Then

            Dim e As New StringPropertyChangingEventArgs(value)

            Me.OnFirstNameChanging(e)

            If Not e.Cancel Then

                Me._firstName = value

                Me.OnFirstNameChanged(EventArgs.Empty)

            End If

        End If

    End Set

End Property

Public Property LastName() As String

Get

        Return Me._lastName

    End Get

    Set(ByVal value As String)

        If value <> Me._lastName Then

            Dim e As New StringPropertyChangingEventArgs(value)

            Me.OnLastNameChanging(e)

            If Not e.Cancel Then

                Me._lastName = value

                Me.OnLastNameChanged(EventArgs.Empty)

            End If

        End If

    End Set

End Property

This is as much as most people will usually need to do when it comes to custom events. There are a couple more points to consider though. As I said earlier, if your event is only going to be handled within your own project then it’s considered good practice to use the generic EventHandler(TEventArgs) delegate as your event’s type. If you’re exposing your event outside your assembly though, it’s considered good practice to declare your own delegate and declare your event as that type. This is in much the same vein as declaring properties that expose a collection. In that case, properties that will be accessible only within the assembly can use the generic List(T) class while, for properties exposed outside the assembly, you should declare your own custom collection type.

In this case we have two choices if we want to declare our own custom delegate. We can either declare a single delegate, just as we’ve declared a single EventArgs class, or declare a delegate for each event. Good practice dictates that we choose the latter.

C#

public delegate void FirstNameChangingEventHandler(object sender, StringPropertyChangingEventArgs e);

public delegate void LastNameChangingEventHandler(object sender, StringPropertyChangingEventArgs e);

Public Delegate Sub FirstNameChangingEventHandler(ByVal sender As Object, ByVal e As StringPropertyChangingEventArgs)

Public Delegate Sub LastNameChangingEventHandler(ByVal sender As Object, ByVal e As StringPropertyChangingEventArgs)

Note that our delegates’ signatures match those of the methods that will be used to handle the events. Notice, also, that the names follow convention: the name of the event with an “EventHandler” suffix. Now we simply declare our events as these types instead of type Eventhandler(TEventArgs).

C#

public event FirstNameChangingEventHandler FirstNameChanging;

public event LastNameChangingEventHandler LastNameChanging;

Public Event FirstNameChanging As FirstNameChangingEventHandler

Public Event LastNameChanging As LastNameChangingEventHandler

Finally, consider how our new cancellable event works. The caller sets the property and the appropriate “Changing” event is raised. The caller can either cancel the event, in which case the new property value is never committed and the corresponding “Changed” event is never raised, or else the event can be accepted, in which case the new property value is committed and the “Changed” event is raised.

That’s just what we want, but consider what happens if we have two event handlers for the same “Changing” event. Let’s say that the property is set and the “Changing” event is raised, invoking the first event handler. If e.Cancel is set to True in that event handler, what should happen? If the event has been cancelled then that should be the end of it, right? That’s not what will happen though. As it stands, all event handlers will be invoked no matter what. That means that the first event handler to get invoked might set e.Cancel to True, but then the second event handler can set it back to False again. That would mean that the property value change would be committed, even though it was cancelled by the first listener. It would depend on the circumstances but, more often than not, I would think that this would be undesirable behaviour.

To remedy this we need to create truly custom events, which means providing our own implementation to handle an event handler being added, an event handler being removed and the event being raised. The first step is to create a collection for our delegates so that we can loop through them and invoke each one individually rather than invoking them all as a group.

C#

private List<FirstNameChangingEventHandler> firstNameChangingHandlers = new List<FirstNameChangingEventHandler>();

private List<LastNameChangingEventHandler> lastNameChangingHandlers = new List<LastNameChangingEventHandler>();

Private firstNameChangingHandlers As New List(Of FirstNameChangingEventHandler)

Private lastNameChangingHandlers As New List(Of LastNameChangingEventHandler)

Next we need to provide a custom implementation for our events that handles adding and removing event handlers.

C#

public event FirstNameChangingEventHandler FirstNameChanging

add

        this.firstNameChangingHandlers.Add(value);

    remove

        this.firstNameChangingHandlers.Remove(value);

public event LastNameChangingEventHandler LastNameChanging

add

        this.lastNameChangingHandlers.Add(value);

    remove

        this.lastNameChangingHandlers.Remove(value);

Public Custom Event FirstNameChanging As FirstNameChangingEventHandler

    AddHandler(ByVal value As FirstNameChangingEventHandler)

        Me.firstNameChangingHandlers.Add(value)

    End AddHandler

    RemoveHandler(ByVal value As FirstNameChangingEventHandler)

        Me.firstNameChangingHandlers.Remove(value)

    End RemoveHandler

    RaiseEvent(ByVal sender As Object, ByVal e As StringPropertyChangingEventArgs)

        For Each handler As FirstNameChangingEventHandler In Me.firstNameChangingHandlers

            handler(sender, e)

            If e.Cancel Then Exit For

        Next

    End RaiseEvent

End Event

Public Custom Event LastNameChanging As LastNameChangingEventHandler

    AddHandler(ByVal value As LastNameChangingEventHandler)

        Me.lastNameChangingHandlers.Add(value)

    End AddHandler

    RemoveHandler(ByVal value As LastNameChangingEventHandler)

        Me.lastNameChangingHandlers.Remove(value)

    End RemoveHandler

    RaiseEvent(ByVal sender As Object, ByVal e As StringPropertyChangingEventArgs)

        For Each handler As LastNameChangingEventHandler In Me.lastNameChangingHandlers

            handler(sender, e)

            If e.Cancel Then Exit For

        Next

    End RaiseEvent

End Event

Notice that now, when a handler is added for the event, we store it in our own collection. We remove the handler from the collection when it’s removed from the event as well. In the case of VB, we also add extra code to control what happens when we call RaiseEvent. That allows the VB code that raises the event to remain unchanged, while the C# code that raises the event must provide the extra functionality for allowing the event to be cancelled before all event handlers have been executed.

C#

protected virtual void OnFirstNameChanging(StringPropertyChangingEventArgs e)

    foreach (FirstNameChangingEventHandler handler in this.firstNameChangingHandlers)

        handler(this, e);

        if (e.Cancel) break;

protected virtual void OnLastNameChanging(StringPropertyChangingEventArgs e)

    foreach (LastNameChangingEventHandler handler in this.lastNameChangingHandlers)

        handler(this, e);

        if (e.Cancel) break;

Protected Overridable Sub OnFirstNameChanging(ByVal e As StringPropertyChangingEventArgs)

    RaiseEvent FirstNameChanging(Me, e)

End Sub

Protected Overridable Sub OnLastNameChanging(ByVal e As StringPropertyChangingEventArgs)

    RaiseEvent LastNameChanging(Me, e)

End Sub

In both cases, raising the event now consists of looping through the registered event handlers one by one. As soon as one of the event handlers cancels the event, no more event handlers are executed.

That’s everything. We’ve covered declaring our own events that have no data, defining our own custom EventArgs class, declaring events that use that custom class using the generic EventHandler(TEventArgs) delegate as well as our own custom delegates, passing data to the event handler and back again and, finally, defining custom events that provide their own implementation for adding and removing event handlers as well as raising the event itself. There’s now nothing you can’t do with events of your own. Here’s hoping you have an eventful future. ;-)

Manipulating GDI+ Drawings

2009-10-18T23:01:00.007+11:00

I've seen many people ask how to manipulate the drawing they produce using GDI+. This is no trivial thing because, as you'll know if you've used GDI+ much at all, your drawing is simply pixels on the screen. There are no objects that you can get references to, set properties on or call methods of.

If you want to use GDI+ to draw on your form and/or controls and you want to be able to change what's drawn at all then you really only have one course of action available. You need to declare one or more member variables, store data in those variables that describe your drawing and then read that data on the Paint event. If you want to be able manipulate your drawing, e.g. drag items around, then you need to use that same data to determine how the mouse state relates to your drawing and then make changes accordingly.

For this example, we're going to create a simple Windows Forms application that behaves, in a rudimentary way, like the VS design window. Just as you can draw controls onto a form, drag them around and use their right-click menu to change the z-order on a Windows Form, so our app will allow you to draw boxes by clicking and dragging, move boxes around by dragging and dropping and also to change the z-order using a right-click.

The app will draw white rectangles with a black border, which will make it easy to see which is in front of which. The boxes will have an apparent z-order, much as controls do on a form. We will add right-click functionality that will allow us to send a box to the back, making it appear to be behind the other boxes, or to bring it to the front, making it appear to be in front of all others. We will also add functionality to enable dragging and dropping boxes around the form.

So, first things first, you'll need to open VS and create a new Windows Forms Application project. I've tested this in VS 2008 but the code I'll provide should also work in VS 2005. I'll also provide both C# and VB code so you can use whichever language you like.

Let's start by adding a PictureBox to the form and setting its Dock property to Fill, so it fills the entire form. We're going to need handle some events of that PictureBox so let's create those event handlers now. We'll only consider the drawing part for now so what events will we need to handle? The user is going to depress the left mouse button to start drawing, drag the mouse to the opposite corner of the box they want to draw, then release the mouse button. To be able to pick up all of that activity we will need to handle the MouseDown, MouseMove and MouseUp events of the PictureBox. Of course, we're using GDI+ to draw on the PictureBox so we'll obviously need to handle its Paint event too.

As I said earlier, we need to store data that describes our drawing in one or more member variables that we can edit and then read in the Paint event handler. In this case we will need to store the start point and the end point of the box we're currently drawing. Two Point fields will do for that. We'll also need to store data for each box we'vew previously drawn. We'll use instances of the Rectangle structure to represent a box and we'll use a generic List to store a Rectangle for each box we've previosuly drawn.

C#

private Point startPoint;

private Point endPoint;

private List<Rectangle> boxes = new List<Rectangle>();

Private startPoint As Point

Private endPoint As Point

Private boxes As New List(Of Rectangle)

First of all, let’s think about how we want the drawing done. That way we can implement the body of our Paint event handler and then essentially forget about it. So, all previously drawn boxes will be recorded as a Rectangle in our generic list while, if we are currently drawing a box, it will be defined by the start point and end point values.

C#

private void pictureBox1_Paint(object sender, PaintEventArgs e)

    Graphics g = e.Graphics;

    foreach (Rectangle box in this.boxes)

        g.FillRectangle(Brushes.White, box);

        g.DrawRectangle(Pens.Black, box);

    if (Control.MouseButtons == MouseButtons.Left)

        Rectangle box = this.GetRectangle(this.startPoint, this.endPoint);

        g.FillRectangle(Brushes.White, box);

        g.DrawRectangle(Pens.Black, box);

Private Sub PictureBox1_Paint(ByVal sender As Object, _

                              ByVal e As PaintEventArgs) Handles PictureBox1.Paint

    With e.Graphics

        For Each box As Rectangle In Me.boxes

            .FillRectangle(Brushes.White, box)

            .DrawRectangle(Pens.Black, box)

        Next

        If Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

            Dim box As Rectangle = Me.GetRectangle(Me.startPoint, Me.endPoint)

            .FillRectangle(Brushes.White, box)

            .DrawRectangle(Pens.Black, box)

        End If

    End With

End Sub

Let’s look at a few points of interest in that code. First, it uses a foreach/For Each loop to enumerate the existing boxes for drawing. As such it will draw the oldest first, thus placing newer boxes in front of older, which is the desired behaviour. If there is a box currently being drawn it gets drawn last of all, in front of all other boxes. Note that the test for whether or not a box is currently being drawn is that the left mouse button and only the left mouse button is depressed. Finally, note that that code calls a GetRectangle method that takes two Point arguments and returns a Rectangle. It’s our next job to write that method.

So, if you’re given two Points, how will you create a Rectangle from them? To create a Rectangle we need the coordinates of the top, left corner and the dimensions. We’ll obviously assume that our two Points are diagonally opposite but we don’t which diagonal or in which order. We don’t have to care though. We know that, whatever combination of corners we have, the top, left corner will be defined by the lower of the two X values and the lower of the two Y values. The dimensions will just be the difference between the X values and the Y values, although we’ll have to take the absolute value to allow for either order.

C#

private Rectangle GetRectangle(Point startPoint, Point endPoint)

    return new Rectangle(Math.Min(startPoint.X, endPoint.X),

                         Math.Min(startPoint.Y, endPoint.Y),

                         Math.Abs(startPoint.X - endPoint.X),

                         Math.Abs(startPoint.Y - endPoint.Y));

Private Function GetRectangle(ByVal startPoint As Point, _

                              ByVal endPoint As Point) As Rectangle

    Return New Rectangle(Math.Min(startPoint.X, endPoint.X), _

                         Math.Min(startPoint.Y, endPoint.Y), _

                         Math.Abs(startPoint.X - endPoint.X), _

                         Math.Abs(startPoint.Y - endPoint.Y))

End Function

Next we need to look at what we’re going to do when the user depresses the mouse button. That location will become the start point for the new box, and it will also be the initial value for the end point, given that the mouse hasn’t moved from the start point yet.

C#

private void pictureBox1_MouseDown(object sender, MouseEventArgs e)

    if (Control.MouseButtons == MouseButtons.Left)

        Point location = e.Location;

        this.startPoint = location;

        this.endPoint = location;

Private Sub PictureBox1_MouseDown(ByVal sender As Object, _

                                  ByVal e As MouseEventArgs) Handles PictureBox1.MouseDown

    If Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

        Dim location As Point = e.Location

        Me.startPoint = location

        Me.endPoint = location

    End If

End Sub

Note that the value tested is Control.MouseButtons rather than e.Button. This because we want to start drawing if the left mouse button and only the left button is depressed. If the user has already depressed the right mouse button and then depresses the left, e.Button would still have the value Left, while Control.MouseButtons would not.

Next, let’s consider what we want to do when the user releases the mouse button. Let’s keep it simple to begin with. We first need to create a Rectangle from the start point and end point. We can do that courtesy of our GetRectangle method. We then need to add that to our List of Rectangles so that it gets drawn. Finally, we need to invalidate the area occupied by that Rectagle and then tell the PictureBox to repaint it. We invalidate only the area that has changed, for efficiency.

C#

private void pictureBox1_MouseUp(object sender, MouseEventArgs e)

    if (e.Button == MouseButtons.Left &&

        Control.MouseButtons == MouseButtons.None)

        this.endPoint = e.Location;

        Rectangle box = this.GetRectangle(this.startPoint, this.endPoint);

        this.boxes.Add(box);

        this.pictureBox1.Invalidate(box);

        this.pictureBox1.Update();

Private Sub PictureBox1_MouseUp(ByVal sender As Object, _

                                ByVal e As MouseEventArgs) Handles PictureBox1.MouseUp

    If e.Button = Windows.Forms.MouseButtons.Left AndAlso _

       Control.MouseButtons = Windows.Forms.MouseButtons.None Then

        Me.endPoint = e.Location

        Dim box As Rectangle = Me.GetRectangle(Me.startPoint, Me.endPoint)

        Me.boxes.Add(box)

        Me.PictureBox1.Invalidate(box)

        Me.PictureBox1.Update()

    End If

End Sub

We’re actually at the point where our application can do something useful, so let’s give it a try. Run your project and try creating a few boxes. Just push the left mouse button, drag the mouse and then release. You’ll see that nothing gets drawn while we’re dragging yet, but a box appears when we release the mouse.

There’s a bit of a problem though. Notice that the black border only gets drawn on the top and left sides, not on the right and bottom. That’s actually not an issue with the drawing itself but rather an issue with the invalidation. When you call Invalidate and pass a Rectangle, the right and bottom edges are exclusive. As such, if we want that last row and column of pixels to be drawn when we call Update, we need to enlarge the area we invalidate at least one pixel further right and one pixel further down. This is something that we’ll need to do again so let’s put it into its own method.

C#

private void InvalidateRectangle(Rectangle box)

    box.Inflate(1, 1);

    this.pictureBox1.Invalidate(box);

Private Sub InvalidateRectangle(ByVal box As Rectangle)

    box.Inflate(1, 1)

    Me.PictureBox1.Invalidate(box)

End Sub

The Inflate method will increase the width and the height of the Rectangle by the specified amounts in both directions. As such, we’ll end up invalidating one row of pixels above and one column of pixels to the left that haven’t actually changed. That’s only a small number though and this code is more succinct than what we would write to get the size increase just to the right and down. It’s not a big deal though so, by all means, write that extra bit of code if want to be as efficient as possible. Note also that it’s safe to inflate the parameter directly because it’s just a copy of the Rectangle in the List, owing to Rectangle being a value type.

We can now call our InvalidateRectangle method from our MouseUp event handler.

C#

private void pictureBox1_MouseUp(object sender, MouseEventArgs e)

    if (e.Button == MouseButtons.Left &&

        Control.MouseButtons == MouseButtons.None)

        this.endPoint = e.Location;

        Rectangle box = this.GetRectangle(this.startPoint, this.endPoint);

        this.boxes.Add(box);

        this.InvalidateRectangle(box);

        this.pictureBox1.Update();

Private Sub PictureBox1_MouseUp(ByVal sender As Object, _

                                ByVal e As MouseEventArgs) Handles PictureBox1.MouseUp

    If e.Button = Windows.Forms.MouseButtons.Left AndAlso _

       Control.MouseButtons = Windows.Forms.MouseButtons.None Then

        Me.endPoint = e.Location

        Dim box As Rectangle = Me.GetRectangle(Me.startPoint, Me.endPoint)

        Me.boxes.Add(box)

        Me.InvalidateRectangle(box)

        Me.PictureBox1.Update()

    End If

End Sub

Now let’s try running our project again. This time notice that the black border is drawn on all four sides. Try drawing boxes from using both diagonals in both directions and you’ll see it works correctly for all four cases. Also notice that new boxes are drawn in front of old ones.

The next order of business is getting the boxes to draw as we drag the mouse, rather than just appearing when we release the mouse button. For that to happen we need to tell the PictureBox to repaint from the MouseMove event handler. In this case we want to, again, check that the left mouse button and only the left mouse button is depressed. We’ll need to invalidate the area previously occupied by the box being drawn, in case it has shrunk, then invalidate the new area occupied by the box, then force a repaint.

C#

private void pictureBox1_MouseMove(object sender, MouseEventArgs e)

    if (Control.MouseButtons == MouseButtons.Left)

        this.InvalidateRectangle(this.GetRectangle(this.startPoint,

                                                   this.endPoint));

        this.endPoint = e.Location;

        this.InvalidateRectangle(this.GetRectangle(this.startPoint,

                                                   this.endPoint));

        this.pictureBox1.Update();

Private Sub PictureBox1_MouseMove(ByVal sender As Object, _

                                  ByVal e As MouseEventArgs) Handles PictureBox1.MouseMove

    If Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

        Me.InvalidateRectangle(Me.GetRectangle(Me.startPoint, Me.endPoint))

        Me.endPoint = e.Location

        Me.InvalidateRectangle(Me.GetRectangle(Me.startPoint, Me.endPoint))

        Me.PictureBox1.Update()

    End If

End Sub

Try running the project and drawing some boxes again. You’ll see that now the box gets drawn on the form as we’re dragging the mouse. Try dragging the mouse in a circle around the start point and see how it gets drawn correctly in all four directions. Try doing the same when drawing a box over some others and see how those underneath get redrawn correctly as they reappear, thanks to our invalidating the old area of the box as well as the new.

That’s the end of the first stage of the project: drawing the boxes. The next stage is to be able to right-click a box and select an option to send it to the back of the z-order or bring it to the front. In order to do that, we’re obviously going to have to add a ContextMenuStrip to the form. We won’t be able to just assign it to the PictureBox’s ContextMenuStrip property though, because we’ll only want to show it when the user right-clicks on a box.

Go ahead and add a ContextMenuStrip to your form. Add two items to the menu with text “Bring to Front” and “Send to Back”. You might as well create handlers for their Click events now, although we won’t add any code to them just yet. Before we do that we need to add a handler for the PictureBox’s MouseClick event, to detect tight-clicks. In order to determine whether the mouse pointer was in a box when the right-click occurred, we will need to loop through our List of Rectangles and check whether each one contains the mouse pointer. This is something we’ll have to do again so let’s put it into its own method.

C#

private int GetRectangleIndexAtPoint(Point location)

    Rectangle box;

    int result = -1;

    for (int index = this.boxes.Count - 1; index >= 0; index--)

        box = this.boxes[index];

        if (box.Contains(location))

            result = index;

            break;

    return result;

Private Function GetRectangleIndexAtPoint(ByVal location As Point) As Integer

    Dim box As Rectangle

    Dim result As Integer = -1

    For index As Integer = Me.boxes.Count - 1 To 0 Step -1

        box = Me.boxes(index)

        If box.Contains(location) Then

            result = index

            Exit For

        End If

    Next

    Return result

End Function

Notice that that code loops through the List backwards. That’s because, if two boxes overlap, we want to detect the front-most one, which will have a higher index.

Now let’s use that method to detect whether or not we should display the ContextMenuStrip on a right-click.

C#

private void pictureBox1_MouseClick(object sender, MouseEventArgs e)

    if (e.Button == MouseButtons.Right &&

        Control.MouseButtons == MouseButtons.None)

        Point location = e.Location;

        this.selectedBoxIndex = this.GetRectangleIndexAtPoint(location);

        if (this.selectedBoxIndex != -1)

            this.contextMenuStrip1.Show(this.pictureBox1, location);

Private Sub PictureBox1_MouseClick(ByVal sender As Object, _

                                   ByVal e As MouseEventArgs) Handles PictureBox1.MouseClick

    If e.Button = Windows.Forms.MouseButtons.Right AndAlso _

       Control.MouseButtons = Windows.Forms.MouseButtons.None Then

        Dim location As Point = e.Location

        Me.selectedBoxIndex = Me.GetRectangleIndexAtPoint(location)

        If Me.selectedBoxIndex <> -1 Then

            Me.ContextMenuStrip1.Show(Me.PictureBox1, location)

        End If

    End If

End Sub

Notice that the result of the GetRectangleIndexAtPoint method is assigned to a field that we are yet to declare.

C#

private int selectedBoxIndex;

Private selectedBoxIndex As Integer

That field is required because we’ll need to its value in order to identify which box to manipulate when the user selects a menu item.

The code first checks that the right mouse button was released and there are no other buttons currently depressed. In that case it gets the index of the front-most box that contains the mouse pointer. If there is such a box the menu is displayed at the mouse pointer location.

Now it’s time to implement the actions associated with the menu items. If you haven’t already, you should add Click event handlers for both menu items now. In each case we will want to move the box that was right-clicked, which is identified by the selectedBoxIndex field, from its current position to one end or the other. If we’re bringing the box to the front then it needs to be drawn last, so it should be placed at the end of the List, while if we’re sending it to the back it should be at the beginning of the List, to get drawn first.

C#

private void bringToFrontToolStripMenuItem_Click(object sender, EventArgs e)

    this.ChangeRectangleIndex(this.selectedBoxIndex, this.boxes.Count);

private void sendToBackToolStripMenuItem_Click(object sender, EventArgs e)

    this.ChangeRectangleIndex(this.selectedBoxIndex, 0);

private void ChangeRectangleIndex(int oldIndex, int newIndex)

    Rectangle box = this.boxes[oldIndex];

    if (oldIndex < newIndex)

        newIndex--;

    this.boxes.RemoveAt(oldIndex);

    this.boxes.Insert(newIndex, box);

    this.InvalidateRectangle(box);

    this.pictureBox1.Update();

Private Sub BringToFrontToolStripMenuItem_Click(ByVal sender As Object, _

                                                ByVal e As EventArgs) Handles BringToFrontToolStripMenuItem.Click

    Me.ChangeRectangleIndex(Me.selectedBoxIndex, Me.boxes.Count)

End Sub

Private Sub SendToBackToolStripMenuItem_Click(ByVal sender As Object, _

                                              ByVal e As EventArgs) Handles SendToBackToolStripMenuItem.Click

    Me.ChangeRectangleIndex(Me.selectedBoxIndex, 0)

End Sub

Private Sub ChangeRectangleIndex(ByVal oldIndex As Integer, ByVal newIndex As Integer)

    Dim box As Rectangle = Me.boxes(oldIndex)

    If oldIndex < newIndex Then

        newIndex -= 1

    End If

    Me.boxes.RemoveAt(oldIndex)

    Me.boxes.Insert(newIndex, box)

    Me.InvalidateRectangle(box)

    Me.PictureBox1.Update()

End Sub

While it would be possible for the bring-to-front functionality to use Add instead of Insert, I’ve used Insert in both cases for the sake of code reuse. The ChangeRectangleIndex function first gets the box that selected by the right-click. It then adjusts the new index if necessary, because removing an item will decrement the index of all subsequent items. The box is then removed from the list and re-inserted at the new index. Finally, the area occupied by the box in question is invalidated and a repaint is forced.

Try running the project again and drawing a few boxes. Now try right-clicking in various places, some on a box and some not. You’ll see that the menu is only displayed when the click is on a box, as it should be. Now try selecting the two menu options and see the apparent z-order of the boxes change accordingly. Also note that, if you right-click on an area where two boxes overlap, it’s always the one in front that receives the command from the menu item.

OK, that’s the setup over. Now let’s get down to the business at hand: implementing drag-and-drop functionality for our boxes. First of all, let’s add a ToolStrip to our form and then add one button. That button will be used for switching our app from drawing mode to dragging mode. Set the Text of the button to “Click to Drag” and set its DisplayStyle to “Text”. Now, at this point you’ll want to right-click on your PictureBox and select “Bring to Front”, to prevent it filling the entire form and going behind the ToolStrip. Finally, double-click the button to create a handler for its Click event. We now want to add a flag that will indicate to the app whether it is in drawing mode or dragging mode and have that flag toggled when the tool bar button is clicked.

C#

private bool drawMode = true;

private void toolStripButton1_Click(object sender, EventArgs e)

    this.drawMode = !this.drawMode;

    if (this.drawMode)

        this.toolStripButton1.Text = "Click to Drag";

    else

        this.toolStripButton1.Text = "Click to Draw";

Private drawMode As Boolean = True

Private Sub ToolStripButton1_Click(ByVal sender As Object, _

                                   ByVal e As EventArgs) Handles ToolStripButton1.Click

    Me.drawMode = Not Me.drawMode

    If Me.drawMode Then

        Me.ToolStripButton1.Text = "Click to Drag"

    Else

        Me.ToolStripButton1.Text = "Click to Draw"

    End If

End Sub

Note that the flag is true by default, indicating that we are in draw mode to begin with. The text displayed on the button indicates what the next click will do, which is why we set the Text to “Click to Drag” in the designer.

At the moment, all the PictureBox’s event handlers assume that we are drawing a new box if the left mouse button is depressed. We’re going to have to go through all of them and change that behaviour so that the code will only draw a new box if we are in draw mode. In drag mode something new will happen.

Let’s start with the Paint event handler. At the moment the code is drawing all the boxes in the list and then, if the left mouse button is down, it draws the new box. Now, we will only want to draw the new box if the left mouse button is down AND we are in draw mode.

C#

private void pictureBox1_Paint(object sender, PaintEventArgs e)

    Graphics g = e.Graphics;

    foreach (Rectangle box in this.boxes)

        g.FillRectangle(Brushes.White, box);

        g.DrawRectangle(Pens.Black, box);

    if (this.drawMode && Control.MouseButtons == MouseButtons.Left)

        Rectangle box = this.GetRectangle(this.startPoint, this.endPoint);

        g.FillRectangle(Brushes.White, box);

        g.DrawRectangle(Pens.Black, box);

Private Sub PictureBox1_Paint(ByVal sender As Object, _

                              ByVal e As PaintEventArgs) Handles PictureBox1.Paint

    With e.Graphics

        For Each box As Rectangle In Me.boxes

            .FillRectangle(Brushes.White, box)

            .DrawRectangle(Pens.Black, box)

        Next

        If Me.drawMode AndAlso _

           Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

            Dim box As Rectangle = Me.GetRectangle(Me.startPoint, Me.endPoint)

            .FillRectangle(Brushes.White, box)

            .DrawRectangle(Pens.Black, box)

        End If

    End With

End Sub

Next, let’s look at the MouseDown event. At the moment the code will store the drawing start point and the current drawing end point. If we’re dragging we are still going to want to remember the start point because we need to know where we’re dragging from, but the end point isn’t really relevant. What will need to remember though is which box, if any, we are dragging.

C#

private void pictureBox1_MouseDown(object sender, MouseEventArgs e)

    if (Control.MouseButtons == MouseButtons.Left)

        Point location = e.Location;

        this.startPoint = location;

        if (this.drawMode)

            this.endPoint = location;

        else

            this.selectedBoxIndex = this.GetRectangleIndexAtPoint(location);

Private Sub PictureBox1_MouseDown(ByVal sender As Object, _

                                  ByVal e As MouseEventArgs) Handles PictureBox1.MouseDown

    If Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

        Dim location As Point = e.Location

        Me.startPoint = location

        If Me.drawMode Then

            Me.endPoint = location

        Else

            Me.selectedBoxIndex = Me.GetRectangleIndexAtPoint(location)

        End If

    End If

End Sub

When the mouse moves, we need to determine whether we’re dragging a box and, if we are, how far we’ve dragged it. We then need to move the box that distance and set the current location as the new starting point for the next drag. Because the box will have moved we need to tell the PictureBox to repaint. We’ll need to invalidate the area the box occupied before the move and the area it occupies afterwards.

C#

private void pictureBox1_MouseMove(object sender, MouseEventArgs e)

    if (Control.MouseButtons == MouseButtons.Left)

        if (this.drawMode)

            this.InvalidateRectangle(this.GetRectangle(this.startPoint,

                                                       this.endPoint));

            this.endPoint = e.Location;

            this.InvalidateRectangle(this.GetRectangle(this.startPoint,

                                                       this.endPoint));

            this.pictureBox1.Update();

        else if (this.selectedBoxIndex != -1)

            Rectangle box = this.boxes[this.selectedBoxIndex];

            Point location = e.Location;

            this.InvalidateRectangle(box);

            box.Offset(location.X - this.startPoint.X,

                       location.Y - this.startPoint.Y);

            this.boxes[this.selectedBoxIndex] = box;

            this.startPoint = location;

            this.InvalidateRectangle(box);

            this.pictureBox1.Update();

Private Sub PictureBox1_MouseMove(ByVal sender As Object, _

                                  ByVal e As MouseEventArgs) Handles PictureBox1.MouseMove

    If Control.MouseButtons = Windows.Forms.MouseButtons.Left Then

        If Me.drawMode Then

            Me.InvalidateRectangle(Me.GetRectangle(Me.startPoint, Me.endPoint))

            Me.endPoint = e.Location

            Me.InvalidateRectangle(Me.GetRectangle(Me.startPoint, Me.endPoint))

            Me.PictureBox1.Update()

        ElseIf Me.selectedBoxIndex <> -1 Then

            Dim box As Rectangle = Me.boxes(Me.selectedBoxIndex)

            Dim location As Point = e.Location

            Me.InvalidateRectangle(box)

            box.Offset(location.X - Me.startPoint.X, _

                       location.Y - Me.startPoint.Y)

            Me.boxes(Me.selectedBoxIndex) = box

            Me.startPoint = location

            Me.InvalidateRectangle(box)

            Me.PictureBox1.Update()

        End If

    End If

End Sub

Notice that, after offsetting the box the distance that the mouse pointer has moved, the box is assigned box to the List item. That’s because, again, Rectangle is a value type so the code retrieves and edits a copy of the Rectangle in the list. After making changes we must overwrite the original with that copy so the changes are persisted.

Nothing needs to happen on the MouseUp event when dragging so the only change we need to make to that handler is a check for draw mode.

C#

private void pictureBox1_MouseUp(object sender, MouseEventArgs e)

    if (this.drawMode &&

        e.Button == MouseButtons.Left &&

        Control.MouseButtons == MouseButtons.None)

        this.endPoint = e.Location;

        Rectangle box = this.GetRectangle(this.startPoint, this.endPoint);

        this.boxes.Add(box);

        this.InvalidateRectangle(box);

        this.pictureBox1.Update();

Private Sub PictureBox1_MouseUp(ByVal sender As Object, _

                            ByVal e As MouseEventArgs) Handles PictureBox1.MouseUp

    If Me.drawMode AndAlso _

       e.Button = Windows.Forms.MouseButtons.Left AndAlso _

       Control.MouseButtons = Windows.Forms.MouseButtons.None Then

        Me.endPoint = e.Location

        Dim box As Rectangle = Me.GetRectangle(Me.startPoint, Me.endPoint)

        Me.boxes.Add(box)

        Me.InvalidateRectangle(box)

        Me.PictureBox1.Update()

    End If

End Sub

Believe it or not, that’s it! Run the project and draw a few boxes, as you did before, and then click the button on the tool bar to switch to drag mode. Try clicking and dragging in some empty space and notice that no new box gets drawn. Now try clicking and dragging on a box and notice that the box follows the mouse pointer. You should also note that the z-order is always maintained during drag operations. You can still use the context menu to change the z-order while in drag mode so you might like to play around with that to confirm that it works with dragging too. If you want to draw a few more boxes then just click the tool button again to switch back to draw mode.

So, that’s all there is to it. There may not be any actual objects on-screen to manipulate directly, but there are always objects. With GDI+ we must have data stored somewhere that represents the drawing in some way, so we can simply manipulate those objects appropriately and force the UI to repaint to see the new drawing. We just need to do a little bit more work to calculate where we are and what we’re moving. We’re developers though: we love that stuff!

Using Parameters in ADO.NET

2009-08-12T23:54:00.017+10:00

As a general rule, parameters should always be used when inserting values into SQL statements. Many people, generally new developers, don't follow this rule because they either don't realise that parameters exist or they don't understand the issues that their use helps to avoid. When I was new to .NET programming, I fell into the former category. Not having read extensively on ADO.NET at the time, I didn't know that parameters could be used to insert field values into SQL code. I started out writing code like this:

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES ('" + this.firstNameField.Text +

             "', '" + this.lastNameField.Text +

             "', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +

             "', " + this.childrenSpinner.Value + ")";

SqlCommand myCommand = new SqlCommand(sql);

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES ('" & Me.firstNameField.Text & _

                    "', '" & Me.lastNameField.Text & _

                    "', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _

                    "', " & Me.childrenSpinner.Value & ")"

Dim myCommand As New SqlCommand(sql)

N.B. I will initially write all examples using the SqlClient ADO.NET provider for SQL Server and later I will discuss the adjustments required when using other providers and other data sources.

The previous example demonstrates various issues associated with this type of code but let’s start with the most obvious: it’s hard to read. With all the various literals and property values being concatenated it’s a bit hard to immediately pick out what’s what. As a result, it’s very easy to make mistakes in such code. It’s very easy to miss a single quote here or a comma there. Of course, it’s not too hard to actually view the end result to see if it is what you intended it to be. I’m constantly surprised, though, by the number of people who build such strings and still don’t actually look at their contents, even when an error occurs.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES ('" + this.firstNameField.Text +

             "', '" + this.lastNameField.Text +

             "', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +

             "', " + this.childrenSpinner.Value + ")";

MessageBox.Show(sql);

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES ('" & Me.firstNameField.Text & _

                    "', '" & Me.lastNameField.Text & _

                    "', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _

                    "', " & Me.childrenSpinner.Value & ")"

MessageBox.Show(sql)

While there are other, possibly better ways, this is the first thing that should occur to a new developer: a simple message box to display the string that was constructed. This will make it much easier to see if you’ve simply missed some punctuation. This is a lesson that should be taken into all development: always view your data and don’t just assume that it is what it’s supposed to be. If an error occurs then you’ve done something wrong and invalid or incorrect data is always a likely candidate.

Now, I’ve always been a big fan of the String.Format method in preference to straight string concatenation when I need to join more than two or three substrings together. Building SQL statements was one of the first scenarios in which I put it to work. It makes such code easier to read and, therefore, less error-prone.

string sql = string.Format("INSERT INTO User (FirstName, LastName, " +

                           "DateOfBirth, ChildCount) " +

                           "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})",

                           this.firstNameField.Text,

                           this.lastNameField.Text,

                           this.dateOfBirthPicker.Value,

                           this.childrenSpinner.Value);

SqlCommand myCommand = new SqlCommand(sql);

Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _

                                  "DateOfBirth, ChildCount) " & _

                                  "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _

                                  Me.firstNameField.Text, _

                                  Me.lastNameField.Text, _

                                  Me.dateOfBirthPicker.Value, _

                                  Me.childrenSpinner.Value)

Dim myCommand As New SqlCommand(sql)

As you can see, it’s much easier to tell where each single quote and comma is and, therefore, whether any are missing. It also makes formatting values such as dates a bit neater.

I thought that this was an improvement, and it was, but it brings me to the next issue: inserting dates and times into SQL code. As you can see in the last two examples, I’ve had to explicitly format the date of birth. That’s because you cannot simply rely on a date and/or time value to be formatted by the .NET Framework in a way that is valid for your database. Even if the format is valid you may end up using a different value to the one you expected because of regional settings. If your month and day values get transposed between your application and your database then you may end up saving incorrect data, which is even worse than your application failing with an error message. We can always do as I’ve done above and use a format that will always work. It’s not a big deal to find out how each database you use formats date literals and then explicitly create that format, but wouldn’t it be better to not have to worry about format at all? When you use parameters you don’t have to.

Before I provide a code example that does use parameters I will touch on another common issue: strings containing apostrophes. This is a very common problem which, again, can be diagnosed by actually viewing the string you build. Doing so should make it obvious that you end up with mismatched single quotes. The question is, how do you prevent the issue. To include a single quote in a text literal you escape it with another single quote. As string concatenation is creating a SQL statement containing literals, that’s what needs to be done:

string sql = string.Format("INSERT INTO User (FirstName, LastName, " +

                           "DateOfBirth, ChildCount) " +

                           "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})",

                           this.firstNameField.Text.Replace("'", "''"),

                           this.lastNameField.Text.Replace("'", "''"),

                           this.dateOfBirthPicker.Value,

                           this.childrenSpinner.Value);

SqlCommand myCommand = new SqlCommand(sql);

Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _

                                  "DateOfBirth, ChildCount) " & _

                                  "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _

                                  Me.firstNameField.Text.Replace("'", "''"), _

                                  Me.lastNameField.Text.Replace("'", "''"), _

                                  Me.dateOfBirthPicker.Value, _

                                  Me.childrenSpinner.Value)

Dim myCommand As New SqlCommand(sql)

Now, that’s not a big deal but why do it if it’s not necessary? Also, what about other unusual characters like line breaks? The simple fact is that, if you use parameters, all these considerations go away. The values never become part of the literal string so there’s no need to escape single quotes. Values remain in binary form rather than being converted to strings, so there’s no need to worry about format. There’s no need to worry about what values need to be wrapped in single quotes or other delimiters and which don’t. None of that is a concern.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";

SqlCommand myCommand = new SqlCommand(sql);

SqlParameterCollection parameters = myCommand.Parameters;

parameters.AddWithValue("@FirstName", this.firstNameField.Text);

parameters.AddWithValue("@LastName", this.lastNameField.Text);

parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);

parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"

Dim myCommand As New SqlCommand(sql)

With myCommand.Parameters

   .AddWithValue("@FirstName", Me.firstNameField.Text)

   .AddWithValue("@LastName", Me.lastNameField.Text)

   .AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)

   .AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))

End With

The first point to note here is how much easier the SQL code is to read. There’s no need for any string concatenation (other than what I’ve used to fit the code within the width of this page), punctuation or formatting, so the opportunity for errors is greatly reduced. In this case (but not all), each value has a name that makes its purpose obvious. Those names match up with the names of the parameters that are added to the command, so it’s easy to trace exactly what’s happening.

Now, let’s examine how the parameters are added to the command. In cases where the SQL code will be executed only once the parameters will only ever have one value. In such cases it’s advisable to call the AddWithValue method as it’s the simplest way to add a parameter and set its value.

The SqlClient ADO.NET provider supports named parameters, so notice that the parameter names in the SQL code match the parameter names passed to AddWithValue. We’ll look at positional parameters later but, in this case, the names must match or you’ll either send the wrong data to the wrong place or an exception will be thrown, which are obviously both poor outcomes.

When calling AddWithValue, the data type of the parameter is inferred from the type of the specified value. That’s why, in the case of the parameter named @ChildCount, the Value property of the NumericUpDown control is converted to an Int32 before being assigned. If the Value property was used as is then the type of the parameter would be inferred from a Decimal instead of an Int32 and a data type mismatch would occur at the database.

Note also that, in the case of the parameter named @DateOfBirth, a DateTime is retrieved from the DateTimePicker’s Value property and then the Date property of that value is used. Because the code is not using formatting to create a date-only string, if we want only the date and not the time then we must explicitly assign only the date and not the time.

As mentioned previously, if the SQL code is being executed only once then the value of each parameter doesn’t need to change. In such cases it is easiest to specify the value when creating the parameter and let the data type be inferred. In cases where the SQL code will be executed multiple times with different parameter values each time we need to take a different approach. In such cases we need to specify the data type when creating the parameters, then set their Value properties each time we want to use new data.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";

SqlCommand myCommand = new SqlCommand(sql, myConnection);

SqlParameterCollection parameters = myCommand.Parameters;

parameters.Add("@FirstName", SqlDbType.VarChar, 50);

parameters.Add("@LastName", SqlDbType.VarChar, 50);

parameters.Add("@DateOfBirth", SqlDbType.DateTime);

parameters.Add("@ChildCount", SqlDbType.Int);

foreach (ListItem item in myList)

    parameters["@FirstName"].Value = item.FirstName;

    parameters["@LastName"].Value = item.LastName;

    parameters["@DateOfBirth"].Value = item.DateOfBirth;

    parameters["@ChildCount"].Value = item.ChildCount;

    myCommand.ExecuteNonQuery();

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"

Dim myCommand As New SqlCommand(sql, myConnection)

With myCommand.Parameters

    .Add("@FirstName", SqlDbType.VarChar, 50)

    .Add("@LastName", SqlDbType.VarChar, 50)

    .Add("@DateOfBirth", SqlDbType.DateTime)

    .Add("@ChildCount", SqlDbType.Int)

    For Each item As ListItem In myList

        .Item("@FirstName").Value = item.FirstName

        .Item("@LastName").Value = item.LastName

        .Item("@DateOfBirth").Value = item.DateOfBirth

        .Item("@ChildCount").Value = item.ChildCount

        myCommand.ExecuteNonQuery()

    Next

End With

The parameters are created and added to the command once only and then their Value properties are set each time new values are needed. The data source-specific data types of the parameters are specified when they are created because they cannot be inferred. Finally, the size is also specified for those parameters whose type requires it. Scale and precision can also be set if required.

There is another way to set parameter values also. Using a DataAdapter you might save data directly from a DataTable. In that case you won’t be looping through the records and setting parameter values yourself. It still needs to be done but the DataAdapter will do it for you. In that case though, you need to tell it exactly where to get the value for each parameter. You do this by specifying the SourceColumn of each parameter, which is the name of the column from which the parameter’s Value will be taken for each DataRow.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";

SqlCommand myCommand = new SqlCommand(sql);

SqlParameterCollection parameters = myCommand.Parameters;

parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");

parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");

parameters.Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth");

parameters.Add("@ChildCount", SqlDbType.Int, 0, "ChildCount");

SqlDataAdapter myAdapter = new SqlDataAdapter();

myAdapter.InsertCommand = myCommand;

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"

Dim myCommand As New SqlCommand(sql)

With myCommand.Parameters

   .Add("@FirstName", SqlDbType.VarChar, 50, "FirstName")

   .Add("@LastName", SqlDbType.VarChar, 50, "LastName")

   .Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth")

   .Add("@ChildCount", SqlDbType.Int, 0, "ChildCount")

End With

Dim myAdapter As New SqlDataAdapter

myAdapter.InsertCommand = myCommand

In this case the fourth parameter of the Add method is the parameter’s SourceColumn. Note that this is the name of a DataColumn in the DataTable that will be passed to the DataAdapter’s Update method, i.e. it is the name of the column the data is coming from, not the name of the column in the database the data is going to. In the majority of cases those two column names will be the same but certainly not in all.

It’s also important to note that, if we are passing a fourth argument to the Add method, we must be passing a third argument too. That third argument is the maximum size of the data. For variable-size data types, like VarChar, this value is important but for fixed-size data types, like Int, it is not. If you know the specific size of a fixed-size data type, e.g. 4 bytes for type Int, then you can specify that value but there’s really no need. All values of a fixed-size data type will be the same size so there’s no need to specify the maximum size. As such you can just pass zero for fixed-size data types.

Now, let’s look at how using parameters differs amongst ADO.NET providers and data sources. First up, let’s look using OleDb and an Access database. The code should look almost exactly the same, except using OleDb classes instead of SqlClient classes.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";

OleDbCommand myCommand = new OleDbCommand(sql);

OleDbParameterCollection parameters = myCommand.Parameters;

parameters.AddWithValue("@FirstName", this.firstNameField.Text);

parameters.AddWithValue("@LastName", this.lastNameField.Text);

parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);

parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"

Dim myCommand As New OleDbCommand(sql)

With myCommand.Parameters

    .AddWithValue("@FirstName", Me.firstNameField.Text)

    .AddWithValue("@LastName", Me.lastNameField.Text)

    .AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)

    .AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))

End With

It’s important to understand that there’s a subtle difference in the way the code behaves though. As I said earlier, SqlClient supports named parameters. That is to say that when you add each parameter to a SqlCommand its name is used as a key to identify it. When it comes time to insert the values into the SQL code, every instance of that name is replaced with the value of the parameter with that name. Even though the code in the preceding example uses the same parameter names in the SQL code and the same names when adding the parameters, those names are not actually related.

The Jet OLEDB provider uses the position of each parameter to decide what values to substitute into the SQL code. All the parameter names in the SQL code are numbered and all the parameters added to the OleDbCommand are numbered. Values are then substituted based on those positions, whether or not the names match. This leads to two important considerations when using OleDb and Access. Firstly, while it makes no difference what order the parameters are added to a SqlCommand, you must ensure that parameters are added to an OleDbCommand in the order they appear in the SQL code. Secondly, while the value of a single SqlParameter can be substituted into SQL code as many times as required, even if the same value is to be used more than once in an OleDbCommand, you must add a discrete parameter for each place it will be used.

Next, let’s look at using OleDb with a SQL Server database. This should rarely be done, given that SqlClient provides more features and better performance, but there may be occasions where it’s necessary. Like the Jet OLEDB provider, the SQL Server OLEDB provider supports only positional parameters. Unlike the Jet provider though, it also doesn’t support pseudo-names for the parameters in the SQL code. In this case you must use “?” symbols in the code to represent a parameter.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (?, ?, ?, ?)";

OleDbCommand myCommand = new OleDbCommand(sql);

OleDbParameterCollection parameters = myCommand.Parameters;

parameters.AddWithValue("FirstName", this.firstNameField.Text);

parameters.AddWithValue("LastName", this.lastNameField.Text);

parameters.AddWithValue("DateOfBirth", this.dateOfBirthPicker.Value.Date);

parameters.AddWithValue("ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (?, ?, ?, ?)"

Dim myCommand As New OleDbCommand(sql)

With myCommand.Parameters

   .AddWithValue("FirstName", Me.firstNameField.Text)

   .AddWithValue("LastName", Me.lastNameField.Text)

   .AddWithValue("DateOfBirth", Me.dateOfBirthPicker.Value.Date)

   .AddWithValue("ChildCount", CInt(Me.childrenSpinner.Value))

End With

The Jet OLEDB provider also supports this notation, as do many others.

There are various other ADO.NET providers that support named parameters however. One such example is the Connector/Net provider available for MySQL. It supports a similar notation to the SqlClient provider except that parameter names must be prefixed with “?” instead of “@”.

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +

             "VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)";

MySqlCommand myCommand = new MySqlCommand(sql);

MySqlParameterCollection parameters = myCommand.Parameters;

parameters.AddWithValue("?FirstName", this.firstNameField.Text);

parameters.AddWithValue("?LastName", this.lastNameField.Text);

parameters.AddWithValue("?DateOfBirth", this.dateOfBirthPicker.Value.Date);

parameters.AddWithValue("?ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _

                    "VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)"

Dim myCommand As New MySqlCommand(sql)

With myCommand.Parameters

   .AddWithValue("?FirstName", Me.firstNameField.Text)

   .AddWithValue("?LastName", Me.lastNameField.Text)

   .AddWithValue("?DateOfBirth", Me.dateOfBirthPicker.Value.Date)

   .AddWithValue("?ChildCount", CInt(Me.childrenSpinner.Value))

End With

You should check the documentation for other providers and data sources to see what notation they support.

I should also point out that the use of parameters is no different when using stored procedures. Whether your CommandType is Text or StoredProcedure, if your SQL code contains parameters then you add them to your command in exactly the same way.

Finally, let’s look at one of the most important reasons to use parameters in the first place: security. When you use string concatenation to build SQL statements you can insert anything into the code at any position. You may intend to insert just a single value but, if the data is provided by the user, it’s often difficult or impossible to ensure that that’s all you’re getting. This can allow malicious users to perform what’s called “SQL injection”, where they inject a chunk of SQL code into your statement where you intended to use only a value. For example, let’s consider a situation where you want to allow the user to search for an employee record by payroll number:

string sql = "SELECT * " +

             "FROM Employee " +

             "WHERE PayrollNumber = '" +

             this.payrollNumberField.Text +

             "'";

Dim sql As String = "SELECT * " & _

                    "FROM Employee " & _

                    "WHERE PayrollNumber = '" & _

                    Me.payrollNumberField.Text & _

"'"

In that case you expect the user to enter a payroll number and the system will find the matching record if such a record exists. For instance, if the user enters the value “12345” then the SQL statement that will eventually be executed will be:

SELECT * FROM Employee WHERE PayrollNumber = ‘12345’

That’s exactly as you intended, but what happens if a malicious user enters the value “12345’; DELETE FROM Employee; SELECT * FROM Employee WHERE PayrollNumber = ‘12345”? The actual SQL code that eventually gets executed will be:

SELECT * FROM Employee WHERE PayrollNumber = ‘12345’
DELETE FROM Employee
SELECT * FROM Employee WHERE PayrollNumber = ‘12345’

Congratulations, you just deleted the entire contents of your Employee table. By using a parameter to insert the payroll number you ensure that whatever the user enters gets treated as a value and cannot be interpreted as SQL code.

There will be situations where SQL injection will not be possible but you should still use parameters all the time. Doing so helps you to avoid various issues and is just a good habit to get into. If you always use parameters then you never have to decide whether you should or not and you can never make the wrong choice.

Retrieving Database Schema Information

2009-08-07T23:51:00.006+10:00

ADO.NET allows you to retrieve a great deal of schema information from a database without resorting to writing your own queries to access system tables. There will be times you still need to write your own queries but the majority of commonly used schema information can be retrieved using the GetSchema method of your connection object.

Exactly what's available and the details of how to retrieve it may vary from database to database but the code pattern remains the same. Note that GetSchema is a member of the DbConnection class, thus it is inherited and overridden in each class that is derived from DbConnection, e.g. SqlConnection and OleDbConnection.

So, the first step to using the GetSchema method is to determine what information your database and ADO.NET provider expose. To do that you call the overload of GetSchema that takes no arguments. I’ll use the SqlClient provider for these examples but, as I said, the pattern is the same no matter the provider and no matter the database. Start by creating a new Windows Forms Application project and adding a DataGridView to your form. In the form’s Load event handler, add the following code:

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema();

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema()

End Using

Note that you’ll have to provide your own connection string as appropriate for your database. When you run that project you should see your grid populated with information about the schema collections available. Let’s look at each column of that grid.

The first column is named “CollectionName” and it contains values such as “Tables”, “Views” and “Procedures”. Not surprisingly, those collections provide information about the tables, views and procedures in the database. When calling one of the overloads of GetSchema with parameters, the first argument must be one of the collection names from that column.

The second column is named “NumberOfRestrictions”. A restriction is basically a filter for a collection that you can specify to reduce the amount of information returned. For instance, many collections can be filtered by name of the schema they belong to, e.g. “dbo”. Note that one of the collections you can query is named “Restrictions” and it will give you information about the restrictions available for each collection. When calling the overload of GetSchema with two parameters, the second parameter must be an array of restriction values. We’ll look at restrictions more closely later.

The third column is named “NumberOfIdentifierParts”. It contains the number of columns required to uniquely identify a record in each collection.

Let’s look at getting information from a specific collection. One of the most commonly queried collections is Tables, so let’s take a look at that. Viewing the data in our grid after executing the previous code, we can see that the Tables collection has 4 restrictions and 3 identifier parts. Store that information away for future use. For now, edit the code in your form to get the schema for the Tables collection specifically:

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema("Tables");

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema("Tables")

End Using

Note that, as mentioned earlier, the first argument to GetSchema is the name of the collection you want to query. Executing that code populates our grid with information about all the tables in the database we’re connected to. The columns are named “TABLE_CATALOG”, “TABLE_SCHEMA”, “TABLE_NAME” and “TABLE_TYPE” and they contain the name of the database containing the table, the name of the schema that owns the table, the name of the table and the type of the table respectively.

Looking at the data, consider what would be required to uniquely identify a record. Recall that the NumberOfIdentifierParts for the Tables collection was 3. Those three identifier parts are the TABLE_CATALOG, TABLE_SCHEMA and TABLE_NAME, all required to uniquely identify a table within a server instance.

Now let’s look at the Restrictions collection, the contents of which are important when querying many other collections. Edit your code once again to query the Restrictions collection:

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema("Restrictions");

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema("Restrictions")

End Using

Having executed that code, we see our grid populated with information about all the restrictions that can be applied to schema queries. The first column is named “CollectionName” and contains the name of the collection that the restriction applies to. Note that there are four rows containing the collection name “Tables” and recall that the NumberOfRestrictions for the Tables collection was 4.

The restrictions for the Tables collection are named “Catalog”, “Owner”, “Table” and “TableType” with RestrictionOrder values of 1, 2, 3 and 4 respectively. Those restrictions relate to the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE columns of the Tables collection respectively.

Let’s say that we want to filter the Tables collection by database. When viewing the contents of the Restrictions collection you should have noticed that the restriction named “Catalog” had a RestrictionOrder of 1, meaning that that must be the first filter value we provide. Try changing the previous code to specify the Tables collection and an array of strings containing the name of the database you’re connected to as the value for the first restriction. For instance, if you don’t specify an Initial Catalog in your SqlClient connection string then you’ll be connected to the “master” database.

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema("Tables",

                                                         new string[]

                                                                 "master",

                                                                 null,

                                                                 null,

                                                                 null

});

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _

                                                       New String() {"master", _

                                                                     Nothing, _

                                                                     Nothing, _

                                                                     Nothing})

End Using

Note that a null reference is passed for each restriction that is not to be used. Restriction values must be provided in the order specified by the Restrictions collection. As such, a value must be specified for each restriction up to and including the last one you want to use. Any after that can be omitted if desired though. That means that the above code could be rewritten as:

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema("Tables",

                                                         new string[] {"master"});

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _

                                                       New String() {"master"})

End Using

Now, let’s say that we wanted to filter the Tables collection by TABLE_NAME, perhaps to check whether a specific table exists. The Table restriction has a RestrictionOrder of 3 so, to filter by TABLE_NAME, we must provide at least three restriction values. If we don’t want to filter by TABLE_CATALOG and TABLE_SCHEMA then we simply provide null references for those restrictions.

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.dataGridView1.DataSource = connection.GetSchema("Tables",

                                                         new string[]

                                                                 null,

                                                                 null,

                                                                 "SomeTable"

});

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _

                                                       New String() {Nothing, _

                                                                     Nothing, _

                                                                     "SomeTable"})

End Using

Run that code and the grid will contain only information about the table with the specified name.

Because the GetSchema method returns a DataTable, as I’ve shown, the result can be bound very simply, just like any other DataTable. An example of how it might be commonly used is to display a list of tables in a ComboBox. You could query the Tables collection using the GetSchema method and then display the TABLE_NAME values in the drop-down list.

C#

using (SqlConnection connection = new SqlConnection(connectionString))

    connection.Open();

    this.comboBox1.ValueMember = "TABLE_NAME";

    this.comboBox1.DataSource = connection.GetSchema("Tables");

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Me.ComboBox1.ValueMember = "TABLE_NAME"

    Me.ComboBox1.DataSource = connection.GetSchema("Tables")

End Using

I’m sure that, given time, you’ll be able to find other uses for the GetSchema method too.

Using Yes/No or OK/Cancel Buttons

2009-08-03T17:19:00.009+10:00

I see a great many people mistakenly using MessageBoxButtons.YesNo buttons when they are prompting the user for confirmation. In that case you should be using MessageBoxButtons.OKCancel buttons. Think of it like this: if the user asked you to do something and you're asking for confirmation then you should always use OKCancel.

To show the difference, consider the situation where the user closes a form with unsaved changes pending. You might use the following code to prompt the user to either close the form and lose the changes or keep the form open:

C#

if (MessageBox.Show("Are you sure you want to close?  " +

                    "If you do so will lose any pending changes",

                    "Confirm Close",

                    MessageBoxButtons.YesNo,

                    MessageBoxIcon.Warning) == DialogResult.No)

    // Cancel close here.

If MessageBox.Show("Are you sure you want to close?  " & _

                   "If you do so you will lose any pending changes", _

                   "Confirm Close", _

                   MessageBoxButtons.YesNo, _

                   MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then

    'Cancel close here.

End If

That would be incorrect though. The correct code would be:

C#

if (MessageBox.Show("Are you sure you want to close?  " +

                    "If you do so will lose any pending changes",

                    "Confirm Close",

                    MessageBoxButtons.OKCancel,

                    MessageBoxIcon.Warning) == DialogResult.Cancel)

    // Cancel close here.

If MessageBox.Show("Are you sure you want to close?  " & _

                   "If you do so you will lose any pending changes", _

                   "Confirm Close", _

                   MessageBoxButtons.OKCancel, _

                   MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Cancel Then

    'Cancel close here.

End If

To see why, consider what would happen if you wanted to expand the choices and prompt the user to either save and close, close without saving or not close at all. In that case you'd use this code:

C#

switch (MessageBox.Show("Are you sure you want to close?  " +

                        "If you do so will lose any pending changes",

                        "Confirm Close",

                        MessageBoxButtons.YesNoCancel,

                        MessageBoxIcon.Warning))

    case DialogResult.Yes:

        // Save changes and allow close.

        break;

    case DialogResult.No:

        // Allow close.

        break;

    case DialogResult.Cancel:

        // Cancel close here.

        break;

Select Case MessageBox.Show("Are you sure you want to close?  " & _

                            "If you do so you will lose any pending changes", _

                            "Confirm Close", _

                            MessageBoxButtons.YesNoCancel, _

                            MessageBoxIcon.Warning)

    Case Windows.Forms.DialogResult.Yes

        'Save changes and allow close.

    Case Windows.Forms.DialogResult.No

        'Allow close.

    Case Windows.Forms.DialogResult.Cancel

        'Cancel close here.

End Select

Comparing that to the first code example, notice that the meanings of the Yes and No buttons have now changed. Previously Yes meant close without saving and now it means save and close. Previously No meant don't close and now it means close without saving, which is what Yes meant before. Now Cancel means don't close, which is what No meant before. Do you see the inconsistency?

Now, consider the third case compared to the second. Previously Cancel meant don't close and it still does. Previously the OK option meant close and now it has been replaced by two new options, Yes and No, both of which still mean close but add the option of either saving first or not saving.

To generalise, if you are asking the user "do you really want to do what you just asked for" then you use an OK button and if you're asking "would you rather not do what you just asked for" then you use a Cancel button, hence MessageBoxButtons.OKCancel is the correct choice when asking for straight confirmation. When you want to break that first question into two: "do you want to do what you asked for and do this extra thing as well" and "do you want to do just what you asked and not anything extra", then you would use MessageBoxButtons.YesNoCancel. There are plenty of situations where the use of MessageBoxButtons.YesNo is appropriate but confirming an action is not one of them.

VB.NET Default Form Instances

2009-07-26T01:58:00.010+10:00

What is a default form instance?

VB.NET is a fully object oriented language so, as such, basically everything you use is an object. Each object is an instance of its type, e.g. a String object is an instance of the String class. Creating an instance of a type is also known as instantiating that type. When dealing with classes, with the exception of the String class, the only way to instatntiate a type is to invoke a constructor, which is done with the New keyword:

Dim someVariable As SomeType = New SomeType

That code declares a variable whose type is SomeType, creates an instance of the SomeType type and then assigns the object to the variable. VB.NET also supports the following abbreviated syntax, which is functionally equivalent:

Dim someVariable As New SomeType

In your code, you may use various means to get new objects, e.g. some classes may have a Create method that returns a new instance, but all such methods will be using constructors internally. Forms are a special case in VB.NET as of VB 2005, which introduced the concept of default instances.

Every time you add a new form to your project you are creating a new class that inherits the System.Windows.Forms.Form class. Like other classes, forms have constructors and you can create an instance by invoking a constructor, but you can also use the class’s default instance. The default instance is an object of that type that the VB application framework creates and manages for you. For instance, conventionally you would do this to show a form:

Dim myForm As New SomeForm

myForm.Show()

If you use the default instance then you don’t need to invoke a constructor explicitly. You simply access the default instance directly via the My.Forms object:

My.Forms.SomeForm.Show()

or just using the class name:

SomeForm.Show()

It should be noted that, once compiled, this code will still cause a constructor to be invoked in order to create the default instance. The significance of this will be explained later.

One of the main goals of VB has always been to provide power while make programming as easy as possible for as many people as possible. The introduction of default form instances is in furtherance of that goal. Many people new to OOP use objects in VB.NET but they don’t really comprehend them properly. As such, they can have a great deal of difficulty dealing with forms as objects and making forms interact with each other. This is also true of some more-experienced VB6 developers who have made the move to VB.NET. VB.NET default instances behave like VB6 default instances so they feel familiar to VB6 developers making the move to VB.NET.

When should you use default instances?

If you’re going to use default instances at all then you should use them pretty much all the time. I’ll qualify that statement later but, for the moment, let’s say that you should use them all the time or not at all.

One important point to note is that, if you leave the application framework enabled when you create a Windows Forms Application project, your startup form is the default instance of its type. If you don’t know what the application framework is then you can safely assume that it’s enabled and not worry about it.

The fact that your startup form is a default instance is quite useful. For instance, let’s say that you are displaying multiple records in a DataGridView in the main form and you open a new form for the user to edit one of those records. Once the editing is done, you want to update the main form with the new data before closing the dialogue. How do you do it? This is the sort of thing that causes great confusion to those new to OOP and VB.NET. In .NET OO terms, the dialogue needs a reference to the main form in order to update it. Providing that reference is actually not too difficult but the mechanism is not immediately obvious to many. With default instances it’s easy. The dialogue form can access the default instance of the main form’s type using the class name, so it can access the main form, e.g.

Private Sub okButton_Click(ByVal sender As Object, _

                           ByVal e As EventArgs) Handles okButton.Click

    With Form1.DataGridView1.CurrentRow.Cells

        .Item(0).Value = Me.TextBox1.Text

        .Item(1).Value = Me.TextBox2.Text

    End With

    Me.Close()

End Sub

In this case, when the user clicks the OK button, the dialogue will directly access the grid on the main form and update the cells of the current row.

Another example of where default instances are helpful is in a multiple-document interface (MDI) application. The child forms in an MDI application are not inherently aware of each other. Let’s say that your MDI application consists of a parent form and two child forms. Let’s say that making a change on one child form needs to cause a change on the other child form. If the children don’t inherently know about each other, how can this be done? The answer is, again, that the first child form simply references the default instance of the second child form’s type via the class name. This will allow changes to be made to the second child form, assuming that it is the default instance of its type.

That last sentence touches on an important point and a source of confusion for some. There’s no point updating the default instance of a form class if you haven’t actually displayed the default instance of that class in the first place. This is one of the reasons I say that, if you’re going to use default instances at all, you should use them all the time. For instance, if you create a form and display it like this:

Dim myForm As New SomeForm

myForm.Show()

then you can’t then refer to the default instance of the SomeForm class and expect the form on your screen to be affected. You’ve created one instance and displayed that, then the system creates the default instance. They are two different objects so making changes to one will not affect the other. If you’re going to make changes by referring to the default instance then you need to have displayed the default instance in the first place:

SomeForm.Show()

Now, default instances don’t hold much appeal for the experienced VB.NET developer. The situations I’ve used as examples up to now should never occur in a well-designed application. For instance, the first example involves a dialogue updating a DataGridView on the main form. In a well-designed application the dialogue shouldn’t have to even know that the main form exists. The dialogue should simply make the new data available via public properties and close. The main form would then retrieve the data itself and update its own UI. In the second example, where a change on one MDI child form causes a change in another child form, the child forms should, again, not have to have knowledge of each other. In a well-designed application the first child would raise an event that can be handled by the parent form, which can then update the other child. The parent form created both the children so it has to have knowledge of both, so it should have no problem accessing both.

There is one situation though, where using the default instance can provide a small benefit to the experienced developer. Because there is always one and only one default instance, they can be useful where you want your form to exhibit singleton-like behaviour. For instance, let’s say that you have a menu item that is supposed to display a form. If the form isn’t already open you want it to be displayed but, if it is already open, you want the existing form to receive focus. Normally you would have to retain a reference to the form when you open it and then, when the menu items is clicked, you need to check whether there is an existing form and, if there is, whether it has already been disposed:

Private toolWindow As SomeForm

Private Sub OpenToolWindowToolStripMenuItem_Click(ByVal sender As Object, _

                                                  ByVal e As EventArgs) _

Handles OpenToolWindowToolStripMenuItem.Click

    If Me.toolWindow Is Nothing OrElse _

       Me.toolWindow.IsDisposed Then

        'The tool window has not been opened or it has been opened and closed.

        Me.toolWindow = New SomeForm

        Me.toolWindow.Show()

    Else

        'A Tool window is currently open.

        Me.toolWindow.Activate()

    End If

End Sub

Using the default instance, that code simplifies to this:

Private Sub OpenToolWindowToolStripMenuItem_Click(ByVal sender As Object, _

                                                  ByVal e As EventArgs) _

Handles OpenToolWindowToolStripMenuItem.Click

    'Make sure the tool window is displayed.

    SomeForm.Show()

    'Make sure the tool window has focus.

    SomeForm.Activate()

End Sub

That does neaten the code a bit, although it’s not a huge improvement, especially if you need to handle events of the form and/or access its methods and/or properties. Personally, I’d just stick with the first style of code but it’s good to know your options.

When can’t you use default instances?

I said earlier that, if you’re going to use default instances at all, you should use them all the time. That is generally true but there are certain situations where you can’t use them. For less experienced developers particularly, these situations will be very much in the minority and may not even be encountered at all in many projects, but it’s important to be aware of them.

1. You need to display multiple instances of the same form class simultaneously.

An example of this would be an MDI application where multiple documents are opened at the same time, all using the same form class. I said earlier that there is always one and only one default instance so, obviously, that presents a problem if you need multiple instances open simultaneously. If you’re going to have to create one or more instances explicitly then you should create them all explicitly. Consistency is a good thing and, if all instances are equivalent, they should be treated in exactly the same way.

2. You need to pass data to the form when you create it.

When you access the default instance for the first time the system must create it and to do that it must invoke a constructor. Specifically, it invokes the constructor that has no parameters. If you want to create a form by invoking a constructor with parameters then you must do so explicitly. It’s also worth noting that if you remove the parameterless constructor and leave only one or more constructors with parameters then your form class will have no default instance. Trying to access it in that case will cause a compilation error.

3. You need to access a form from a secondary thread.

In order to access a form from a secondary thread you generally need to test its InvokeRequired property and then call its Invoke method. I said earlier that there is only ever one default instance of a form class. That’s not strictly true. In fact, default instances are thread-specific, so there is only ever one default instance per thread. As such, if you test the InvokeRequired property of the default instance you will always be accessing the default instance for the current thread, not the one that was displayed on the main thread.

While experienced .NET developers will probably never feel the need to use default instances, those new to OOP or VB.NET may find them useful. They do have their pitfalls though so, like anything, it’s good to understand what you’re dealing with.

Database-independent Data Access Layer

2009-07-06T22:09:00.005+10:00

We all know that well-written applications separate the various areas of responsibility into multiple layers, right? Generally speaking, the less each of those layers depend on specific functionality of the others the better. One of those areas of responsibility that warrants separation is data access. There are numerous tools about to generate data access code for you these days but, even if you're more of a roll-your-own kinda person, there's still help at hand. ADO.NET provides the tools to write data access code that is independent of the underlying data source and it has done since version 2.0.

The lynchpin of this functionality is the DbProviderFactory class. It provides methods to generate instances of all the usual ADO.NET objects, e.g. connections, commands and data adapters, for any specific ADO.NET provider.

Let’s start with a whirlwind tour of the System.Data and System.Data.Common namespaces. Amongst other things, the System.Data namespace contains the interfaces that declare the set of functionality required by ADO.NET data access objects, e.g. IDataReader for all data readers and IDbConnection for all connections. The System.Data.Common namespace contains classes that provide a basic concrete implementation of these interfaces, e.g. DbDataReader implements IDataReader and DbConnection implements IDbConnection. Each data source-specific ADO.NET provider will then declare a set of classes that inherit those from System.Data.Common to provide a data source-specific set of functionality, e.g. SqlClient.SqlDataReader inherits DbDataReader and OleDb.OleDbConnection inherits DbConnection.

The System.Data.Common namespace also includes the DbProviderFactory class, which provides an abstract implementation of an ADO.NET object factory. Each data source-specific ADO.NET provider should inherit this class and provide its own data source-specific implementation, e.g. SqlClientFactory and OleDbFactory. Third party ADO.NET providers can and should do the same thing, e.g. MySql.Data.MySqlClient.MySqlClientFactory.

Now, ADO.NET factory classes are singletons. Each concrete factory implementation will have a static/Shared Instance field that you can use to get the one and only instance of that type. Doing so directly defeats the purpose somewhat though. You will normally get a DbProviderFactory instance using the DbProviderFactories helper class, which is also a member of the System.Data.Common namespace. The DbProviderFactories class includes two static/Shared methods: GetFactoryClasses will return a DataTable containing information about all ADO.NET factories available and GetFactory will return an instance of a specific ADO.NET factory class.

To see how this works, try creating a new Windows Forms application, adding a DataGridView to the default form and then binding that grid to the result of the DbProviderFactories.GetFactoryClasses method:

C#

this.dataGridView1.DataSource = DbProviderFactories.GetFactoryClasses();

Me.DataGridView1.DataSource = DbProviderFactories.GetFactoryClasses()

If you run that application you will see a row each for factories named “Odbc Data Provider”, “OleDb Data Provider”, “OracleClient Data Provider” and “SqlClient Data Provider”. If you’ve installed Microsoft SQL Server CE then you’ll also see a row for a factory named “Microsoft SQL Server Compact Data Provider”. If you’ve installed some third party data provider then you’ll see a row for that too. For instance, if you’ve installed MySQL’s Connector/Net then you’ll see a row for a factory named “MySQL Data Provider”. If you’ve installed Oracle’s ODAC then you’ll see a row for a factory named “Oracle Data Provider for .NET”.

To get an appropriate instance of the DbProviderFactory class for your data source you would normally call the DbProviderFactories.GetFactory method. You can pass that method either a DataRow from the DataTable returned by the GetFactoryClasses method or else one of the values from the InvariantName column of that table. For instance, you could pass the string “System.Data.SqlClient” to the GetFactory method and it would return the value of the SqlClientFactory.Instance field.

This provides us with two simple and logical ways to create an ADO.NET factory. Firstly, we can call GetFactories and display the results to the user, e.g. in a ComboBox, for them to choose a provider:

C#

this.comboBox1.DisplayMember = "Name";

this.comboBox1.DataSource = DbProviderFactories.GetFactoryClasses();

Me.ComboBox1.DisplayMember = "Name"

Me.ComboBox1.DataSource = DbProviderFactories.GetFactoryClasses()

We can then call GetFactory and pass the row they selected as an argument:

C#

DataRow providerRow = ((DataRowView)this.comboBox1.SelectedItem).Row;

DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);

Dim providerRow As DataRow = DirectCast(Me.ComboBox1.SelectedItem, DataRowView).Row

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(providerRow)

Alternatively, we can store the invariant name of our desired provider somewhere, e.g. in the config file, and pass that to GetFactory as an argument:

C#

string invariantName = Properties.Settings.Default.ProviderFactoryInvariantName;

DbProviderFactory factory = DbProviderFactories.GetFactory(invariantName);

Dim invariantName As String = My.Settings.ProviderFactoryInvariantName

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(invariantName)

So, once we have our factory object, what do we do with it? Well, we use it to create all our data access objects, including connections, commands, parameters, data adapters and more. For instance, the following code might form part of your repository layer:

C#

private readonly DbProviderFactory _factory;

public string ConnectionString { get; set; }

public Repository(string factoryName)

    this._factory = DbProviderFactories.GetFactory(factoryName);

public Repository(DataRow factoryRow)

    this._factory = DbProviderFactories.GetFactory(factoryRow);

public DbConnection GetConnection()

    return this.GetConnection(this.ConnectionString);

public DbConnection GetConnection(string connectionString)

    DbConnection connection = this._factory.CreateConnection();

    connection.ConnectionString = connectionString;

    return connection;

public DbParameter GetParameter()

    return this._factory.CreateParameter();

public DbParameter GetParameter(string parameterName,

                                object value)

    DbParameter parameter = this.GetParameter();

    parameter.ParameterName = parameterName;

    parameter.Value = value;

    return parameter;

public DbCommand GetCommand()

    return this._factory.CreateCommand();

public DbCommand GetCommand(string commandText)

    DbCommand command = this.GetCommand();

    command.CommandText = commandText;

    return command;

public DbCommand GetCommand(string commandText,

                            IDictionary parameters)

    DbCommand command = this.GetCommand(commandText);

    foreach (string parameterName in parameters.Keys)

        command.Parameters.Add(this.GetParameter(parameterName,

                                                 parameters[parameterName]));

    return command;

public DataTable GetDataTable(string procName,

                              IDictionary parameters)

    DbCommand command = this.GetCommand(procName,

                                        parameters);

    DbConnection connection = this.GetConnection();

    command.Connection = connection;

    command.CommandType = CommandType.StoredProcedure;

    connection.Open();

    DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo |

                                                CommandBehavior.CloseConnection);

    DataTable table = new DataTable();

    table.Load(reader);

    reader.Close();

    return table;

Private ReadOnly _factory As DbProviderFactory

Private _connectionString As String

Public Property ConnectionString() As String

Get

        Return Me._connectionString

    End Get

    Set(ByVal value As String)

        Me._connectionString = value

    End Set

End Property

Public Sub New(ByVal factoryName As String)

    Me._factory = DbProviderFactories.GetFactory(factoryName)

End Sub

Public Sub New(ByVal factoryRow As DataRow)

    Me._factory = DbProviderFactories.GetFactory(factoryRow)

End Sub

Public Function GetConnection() As DbConnection

    Return Me.GetConnection(Me.ConnectionString)

End Function

Public Function GetConnection(ByVal connectionString As String) As DbConnection

    Dim connection As DbConnection = Me._factory.CreateConnection()

    connection.ConnectionString = connectionString

    Return connection

End Function

Public Function GetParameter() As DbParameter

    Return Me._factory.CreateParameter()

End Function

Public Function GetParameter(ByVal parameterName As String, _

                             ByVal value As Object) As DbParameter

    Dim parameter As DbParameter = Me.GetParameter()

    parameter.ParameterName = parameterName

    parameter.Value = value

    Return parameter

End Function

Public Function GetCommand() As DbCommand

    Return Me._factory.CreateCommand()

End Function

Public Function GetCommand(ByVal commandText As String) As DbCommand

    Dim command As DbCommand = Me.GetCommand()

    command.CommandText = commandText

    Return command

End Function

Public Function GetCommand( _

ByVal commandText As String, _

ByVal parameters As IDictionary(Of String, Object)) As DbCommand

    Dim command As DbCommand = Me.GetCommand(commandText)

    For Each parameterName As String In parameters.Keys

        command.Parameters.Add(Me.GetParameter(parameterName, _

                                               parameters(parameterName)))

    Next parameterName

    Return command

End Function

Public Function GetDataTable( _

ByVal procedureName As String, _

ByVal parameters As IDictionary(Of String, Object)) As DataTable

    Dim command As DbCommand = Me.GetCommand(procedureName, parameters)

    Dim connection As DbConnection = Me.GetConnection()

    command.Connection = connection

    command.CommandType = CommandType.StoredProcedure

    connection.Open()

    Dim reader As DbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or _

                                                       CommandBehavior.CloseConnection)

    Dim table As New DataTable()

    table.Load(reader)

    reader.Close()

    Return table

End Function

The GetDateTable method takes the name of a stored procedure and a dictionary of parameter values keyed on name as arguments. It then invokes other methods that use the factory to create a connection and a command with parameters. The DbProviderFactory class can also generate connection string builders, data adapters and command builders, so you can implement many other scenarios in a similar fashion. In this way you can build up a complete data access layer with nary a reference to any specific data source.