One of the all new pieces of SQL Server 2005 is Common Language Runtime (CLR) integration. CLR integration extends the database to improve computational performance, text manipulation, and access to system resources outside of the database engine. Stored procedures, functions, triggers, aggregations, and possibly even data types can now be compiled into the SQL Server engine to provide a new dimension of possibilities for the high performance database developer.
As with anything that is new, there is much to learn. Most of what must be learned to design, write and deploy CLR integration components has yet to be surfaced to you and I, the user community. Gurus, pundits, insiders, and other early adopters are just now going to publication on with the “how to” books and blogs
A thorough introduction to Object Oriented Programming (OOP) is the subject of several books. At the same time it is recognized that PASS readers are coming from a SQL Server environment where the language is this thing called T-SQL. This article is intended to help those readers with limited programming experience outside of SQL Server to get started on the path to producing rich and powerful OOP code using the .NET Framework.
Readers with little or no OOP background should not be discouraged by the amount technology new to server side SQL Server development that accompanies the CLR. Understanding how the database engine works will continue to be most the useful skill for Database developers and administrators. This is true whether database components are compiled in the .NET Framework or loaded as conventional T-SQL objects. On the other hand, all the old adages about a little knowledge are well heeded when it comes time to build production quality CLR components.
Application developers more familiar with .NET and database developers more familiar with SQL Server now have a better reason than ever to work collaboratively. When possible, server side developers considering the use of CLR integration should consult with front end and mid tier programmers. At the same time, experienced .NET developers would be wise to seek knowledge from T-SQL folks concerning SQL Server's behaviors and internals as they look to creating managed code in the SQL Server integrated CLR. Through cooperation, everybody wins.
A SQL Server developer that has a modest background with any real programming language – that would exclude T-SQL, HTML, VBScript and even (no tomato throwing please) VB6 – may already understand .NET and basic OOP concepts. If even a small amount of time has been invested in design and coding any OOP language, the developer is well on the way to mastery of .NETs OOP technology. To avoid confusing semantics by describing more than one language implementation, this article will focus only on VB.NET. The concepts are the same and the syntax is similar for the other Visual Studio 2005 managed languages such as C# or C++. For readers already focused on another language, the article can be used as an exercise in extrapolating OOP basics in the VB.NET context.
(Note: Readers not yet settling into a favorite .NET language are encouraged to consider VB.NET. In my opinion, VB.NET is faster and easier to code than other .NET Framework programming languages. In addition the VB.NET compiler is the only compile in Visual Studio 2005 that always verifies code type safety. This is crucial to assure that the stability of the SQL Server is not undermined by CLR integration component code. See the Code Access Security (CAS) section in the .NET Framework Developer’s Guide for full details.)
Two important principals of OOP are inheritance and encapsulation. These concepts help achieve polymorphism or using one thing for more than one discrete purpose. Inheritance provides a mechanism for code reuse. Encapsulation helps creates boundaries within which reuse is known to be safely isolated from other efforts at reuse or the original code being reused.
There are a number of ways that code can be reused. Classes can be derived from another class through inheritance. The types from an inherited class can be overloaded in the derived class to modify the number and type of arguments accepted by the original type to properly work in the new context. Alternately the type could be shadowed to completely replace the original type with a new local type implementation - that by the way is not inheritable. If the class that defines the type is subsequently derived, the type can be overridden to completely replace the original type with a new inheritable type of the same member type in the derived class. For example a class can either overload or override a class, a method override a method and so on. A shadowed type need not be the same member type. For example a field or property can shadow a method of the same name.
Shadowed members are not inherited by classes derived from the class where the shadowed member is created. If it is desired to replace a type from the base class with a new implementation - not merely overload it and add functionality but completely replace the original type - and for that new implementation to be inheritable then the base type should be overridden rather than overloaded or shadowed. The override cannot change the kind of the member like the shadow. Interfaces can be used to define member types that the class must contain without saying how those types will be implemented (more on interfaces below). Each of these ways to reuse code offers the developer an advantage of having to write fewer lines of code to get to the end result provided the correct reuse strategy is pursued.
A .NET class is the definition of an object that includes the complete implementation instructions for the methods, properties, fields and events of that class. Instances of the object are created – or instantiated - by calling the specialized constructor method that must exist for every class. The method name for the constructor is “New”, as in:
Dim Name As SqlMetaData = __
New SqlMetaData("Name", SqlDbType.NVarChar, 128)
It follows that an instance of a class is destroyed by a destructor. As it happens, getting rid of a class instance is not as easy as creating one. Some good news is that .NET helps you with destruction through built in garbage collection methods.
A class can inherit from one and only one other class - provided that other class is inheritable. In extension of that concept, it is possible to declare a class as not inheritable though by default all classes can be inherited. The class being inherited is a base class and the class inheriting the base class is a derived class. A class can implement one or more interfaces (see below).
Note that in order for an assembly to instantiate an object in the ,NET Framework, it must contain a reference to the assembly containing that class and it must call the constructor for the class. The reference is not an object oriented concept though is necessary for the compiler to have knowledge an assembly in support of reusability.
An interface provides type declarations but no implementation details. As the name implies, interfaces serve to help different component classes interact. An interface is typically considered a contract or prototype. It determines what types must be implemented but it does not force any particular implementation details. One interesting interface for SQL Server CLR integrations is INullable. INullable is a system-defined interface in the System.Data.SQLTypes namespace that CLR Integration developers will get plenty of opportunities to use. INullable helps SQL Server - a null aware component - interact with virtually any other .NET Framework common base class because the .NET Framework has no understanding of null. INullable defines one property: IsNull. We know from the interface that IsNull is a Boolean but it is up to the class that is using the interface to determine when IsNull is true and when IsNull is false. The interface is very necessary even if quite simple. The same interface can be used across many components. A word of warning: all interfaces are not a simple as INullable.
Namespaces in the .NET Framework can be useful to help prevent naming conflicts of methods and properties. For example a commonly used generic name such as "User" can occur more than once in an Assembly or class provided the different usages are encapsulated within different namespaces. Actually this term shows how XML is blending with OOP since the notion of namespaces is fundamental to XML syntax. Namespaces are primarily a hierarchical mechanism of design time encapsulation. For the database professional it may be useful to think of namespaces similarly to schemas in SQL Server 2005 or object owners in earlier SQL Server releases.
Abstract classes provide a more sophisticated way to support component interaction. Like interfaces abstract classes cannot be instantiated and can include type declarations without implementation details. Like concrete classes, abstract classes can include types with implementation details but are bound by the single inheritance class restriction. It will require some experience and design time thought to determine when an abstract class is preferable over an interface. Generally speaking, the fact that a class can only inherit one class - even if it is an abstract class may make the determination obvious. If the rules for how two components will interact are complex and the need is only to define how two specific components will interact then an abstract class is preferable. If the rules for how two components will interact are simple or the need is to create a way for many different types of components to interact then interfaces are more likely to fill the bill. Abstract classes are also useful in some cases to define the root class of an object hierarchy such as part or person. In this case the knowledge and thought will be necessary to determine if the root object should be a concrete class or an abstract class. For example, in the application can people exist only as people or must they be employees or customers - both inheriting from person - before they can exist in the object hierarchy? If yes then person would be a concrete class and the code would carry the additional complexity. If no then person could acceptably be coded as an abstract class and employee and customer could be derived from the abstract person class. Deciding when to use and abstract class and when to use an interface is not an easy choice. Taking the wrong path can be a big problem down the road.
Delegate classes also share some similarities with interfaces. Delegates are basically classes waiting in the wings for some event to happen. An event is a notification message that some identifiable action has occurred. For example hitting enter is an event or completion of a bulk load into SQL Server is an event. The delegate is like an interface in that it defines the event handler that will execute in response to an event but does not include the implementation details for that handler. While the interface implementation detail must be supplied by the developer in the class that implements the interface, the implementation details for a delegate are provided by the CLR. The developer specifies a delegate declaration; the CLR creates a class from that declaration. The listener or callback roles are common implementations of a delegate. Understanding delegates is not simple but once understood the power and coding efficiency realized is tremendous.
The .NET Declaration Context establish the access level and inheritability of a class or member type. The access levels in VB.NET serve to control the level of exposure of the class or member. The most open is Public meaning anything that can see the type can use it. At the other end of the access level spectrum, Private indicates that only the container method or class has visibility to an item. In between are Protected meaning that all code within the class and derived classes can access the member, Friend meaning that other classes within an assembly are able to use the item and ProtectedFriend which is the union of you guessed it protected and friend.
The declaration context also includes such directives as shared and static. The shared context creates a persistent type that is shared by all instances - akin to global object variable but without violating the encapsulation condition as would a global variable. A static declaration context defines a fixed and unchanging value across all instances of a class. The Vb.NET static is easily confused with the C# and C++ static in developer communications. This is probably the most unfortunate semantically confusing inter-language conflict in all .NET languages. A static in C# or C++ is the same thing as shared in VB.NET. Go figure.
The reader is encouraged to seek additional references on this topic as warranted by the requirements of the particular CLR integration programming attempted. In fact, if a reade has the time and inclination to follow all the links in this article a very well rounded understanding of OOP and .NET could be he result. While not essential for creating rudimentary CLR database components, a strong conceptual understanding of these elemental concepts will become more valuable as the developer gains experience and as CLR integration within SQL Server gains maturity.
For those that prefer book learnin’, a couple of good OOP primers to consider might be the classic, Object-Oriented Programming by Peter Coad and Jill Nicola (ISBN 013032616X) or the quick reading, and more recent OOP Demystified by Jim Koegh and Mario Davidson (ISBN 0072253630). For VB.NET focused explorations of OOP consider Building Applications and Components with Visual Basic .NET by Ted Pattison and D. Joe Hummel (ISBN 0201734958).
The Visual Studio 2005 Documentation is also a good place to find additional details on the .NET specific OOP implementation, even if not particularly well organized for the learner. To get the Visual Studio lessons available for any of the key terms (all in bold) in this article go to http://msdn.microsoft.com/library/default.asp and enter the keyword followed by “.NET” in the search box. Even those of us that have been long time champions of SQL Server Books Online must concede that the SQL Server documentation folks at Microsoft have not given us what we need in the way of good CLR integration information… yet.
The best single resource to date available from Microsoft on CLR integration is the white paper, “Using CLR Integration in SQL Server 2005” first published in May 2005.
Anything from blogs to books by Bob Beauchemin or Peter DeBetta on the topic of CLR Integration will be worth the read.
Keep an eye on the PASS Special Interest Groups (SIG) web site (http://sigs.sqlpass.org) for:
Developing in .NET is not trivial. Reading this article won’t make anyone a capable CLR database component developer. The article is intended to help readers identify where to go next in the processes of learning how to effectively use the .NET 2.0 Framework and OOP technology to create CLR integration components in SQL Server 2005.