Instantiating ADO.NET Objects

When it comes time to build your ADO.NET application, you'll spend considerable time constructing instances of the objects, populating their properties, and coding the method arguments. Again, Intellisense can help quite a bit here, but let's go over a few guidelines to help you get through the initial learning phase.

Understanding Code Scope

First, you'll need to choose where to instantiate your ADO.NET objects. For example, if you want the object visible to all Windows Forms in the project or all ASP pages, you'll need to create the object in a global "scope". The scope of an object is the region in which it is visible (addressable) in code. For example, in Figure 8.9, I've defined several objects that are visible in selected parts of the application. Note those objects created within a scope boundary are not visible outside that boundary. For Visual Basic 6.0 developers, this is a radical change, as the object scope rules have changed quite a bit from what they are accustomed to using.

Figure 8.9. Object scope determines where an object is visible.

In Visual Basic .NET, scope boundaries include (but are not limited to) the Module, Class (in this case, the Form1 class), Function or Sub declaration, the TRy/Catch block, and If, Case, For/Next, Do/While, and other code blocks. In C#, it's a bit simplerobjects created inside curly braces ({}) are not visible outside the braces.

In the example shown in Figure 8.9, I create myTRyObject within a TRy/Catch block. This means the object will not be visible in the Finally/End Try block or outside the ShowScope Subroutine. On the other hand, the mySubObject is visible to all code blocks between the Sub and End Sub scope delimiters, but not outside of the Sub. The myGlobalObject SqlConnection object is visible to any routine in the form, but not outside the form. Likewise, if the application includes a Module, any variables declared as Public would be visible from anywhere in the application.

No, it's not a good idea to place your objects just anywhere. As I've discussed before and I'll discuss again, object placement is critical to scalability and the ability of the Framework to correctly construct and tear down the objects when they are no longer needed. For example, while it's possible to create an application-global SqlConnection object, it's not a great idea (as I'll illuminate in Chapter 9, "Getting Connected"). In ASP applications, it's usually best to create the SqlConnection just-in-time, so as to best leverage the power of the connection pool. Just remember, you can't depend on the .NET garbage collector to tear down and dispose of your orphaned objects, as you could with Visual Basic 6.0. Again, the concept of an application-global SqlConnection is generally not a good practice unless your application is very simpleand not an ASP.NET design. Another nuance I'll discuss in Chapter 9 concerns closing the SqlConnection object. Unlike ADOc, you should not depend on the .NET garbage collector to tear down objects as they fall from scope. Sure, the GC eventually disposes trashed objects, but by the time it does, your connection pool might be overflowing on the lawn.

Tip

Don't depend on the .NET garbage collector to tear down and dispose of orphaned SqlConnection or any other instantiated objects.

Understanding Object Instantiation

Once it's decided where the object is to be created (the breadth of its visibility), you need to decide how you want to instantiate it. Generally, I like to position an object in the correct scope but delay instantiating the object until just before it's needed. Instantiating an object simply means creating an "instance" or clone of the object's class. Think of it this way: A class is kinda like a cookie cutteryou know, those little trees, houses, and gingerbread men (people?) you used to cut out of sugar-cookie dough during the holidays. Each time you use the cutter to make a tree, you're instantiating an instance of the tree "class". When a class is defined, one or more New methods are included in the class code. These are used to generate and initialize the new instance of the classthis becomes your new ADO.NET "object". I could go into more detail about how the object properties are like candy sprinkles, but this is making me too hungry.

The New method (new in C#) is called a "constructor," and in the case of ADO.NET classes, it usually exposes a number of arguments used to set the properties of the newly born object instance. For example, when you want to create a new SqlConnection object, you can use one of two constructors exposed by the SqlConnection class. As you type the open parenthesis (as shown in Figure 8.13), Visual Studio's Intellisense kicks in and prompts for any arguments exposed by the implemented constructors (as shown in Figure 8.10).

Figure 8.10. Using Intellisense to choose a constructor.

Tip

Unlike Visual Basic 6.0, where I encourage you to avoid use of "Dim X as New Y" syntax, in the Framework, the New constructor is your friend.

In this case, the only constructors exposed by the SqlConnection class create the object with no arguments (option 1 of 2) or pass in a properly formatted ConnectionString as a string. If you choose option 2, the value supplied to the constructor as an argument is used to set the new object's ConnectionString property. Actually, the constructor you use is chosen automatically for you. Visual Studio matches up the values you supply by type to the most appropriate constructor.

You'll find that most ADO.NET objects have constructors to help you initialize (construct) the object without having to make unnecessary property settings. Those properties not provided by the constructor must be set manually. It's not unusual to code a New constructor and have to immediately add initialization code to set additional properties not exposed by any of the constructors.

Another nuance you might not be prepared for is the fact that the New constructor can throw an exception if its code determines that one or more of the arguments passed are incorrect. In the example, if the key/values pairs supplied at runtime in the ConnectionString string are invalid, the constructor throws an exception. This means you can place these object instantiation statements almost anywherein or outside the code path. However, if you expect that an exception can be thrown, the object should be instantiated inside a TRy/Catch block to prevent an unhandled exception from bringing down your application. This issue leads us back to the quandary of where you can (or should) scope the object. Again, this is another reason to set the scope of the object by properly positioning the object declaration in the right scope and only using the "as New" constructor in a scope that can trap an exception at runtime.

The Using Block in Visual Basic.NET

An alternative to coding the Dim statement in Visual Basic .NET has just been introduced with the 2.0 Framework. Visual Basic .NET 2005 now supports the Using block, as supported in C# from the beginning. The Using block can make sense when dealing with "unmanaged" resources, such as a file handle or a SqlConnection object. It's not necessary to code a Using block for managed resources, as the .NET Framework garbage collector (GC) handles disposal of these objects on its ownat least, eventually. The SqlConnection can be a good candidate for the Using block when its associated connection might be orphaned and left to rot in the connection pool while waiting for the GC disposal.

The beauty (and beast) of the Using block is that it not only defines the scope for an object, but it ensures that the object's Dispose method is called when the logic reaches the End Using or you branch out of the Using block. This can make it easier (and safer) to instantiate objects that must be torn down before leaving the current code scope. The problem is, if you plan to reuse the SqlConnection object later in your application, you might not want to execute its Dispose methodjust the Close method. Remember, Dispose flushes the state of all of an object's properties and releases the object's resources to the GC.

Figure 8.11 illustrates the Using block. As I get into the later chapters, I'll show plenty of examples where it makes sense to instantiate objects with a Using blockand even more where it does not.

Figure 8.11. Coding a Using block to manage a SqlConnection object.

Notice that the example code passes the SqlConnection object to another routine, but in this case, the DoSomething routine is called, so the logic must return to ShowUsing, where the object is closed and disposed. However, if you try to branch out of the Using block as shown in Figure 8.12, the object is closed and the CLR disposes of the SqlConnection object as it should be. That's good because this eliminates the chance that the SqlConnection object would be orphaned, but it's bad in the sense that the routine that called ShowImproperUsing won't get a valid object.

Figure 8.12. Improper use of the Using block.

I suspect that your solution to this Using block problem would be to eliminate the Using block. This would be a mistake because it opens up an opportunity to orphan the connection object. An "orphaned" object is one that can no longer be addressed and is waiting for the GC disposal. When this object is a SqlConnection object, it also means a connection is orphaned in the connection pool and on the SQL Serverthat's not good. I'll discuss connection object management in Chapter 9, where it will be clear how to get around (or, better yet, avoid) this issue.