Introduction to DBMethods

Copyright © 2001-2003 Atif Aziz, Skybow AG

The use and distribution terms for this software are contained in the file named LICENSE.RTF, which can be found in the root of this distribution. By using this software in any fashion, you are agreeing to be bound by the terms of this license.

This document applies to DBMethods version 1.0.3910.0 (also known as build 3910).

Introduction

DBMethods is a library that allows you to express parameterized SQL and stored procedures as managed code functions in your language of choice (Visual C#, Visual Basic .NET, Visual J# .NET and others) using Microsoft's .NET Framework. Just as Platform Invoke (P/Invoke) allows you to call unmanaged code APIs in platform libraries (DLLs) by supplying a compatible method definition in managed code, DBMethods allows you call stored procedures and parameterized SQL in a database by defining a compatible method definition in managed code.

To give you an upfront idea of DBMethods very simply, here's a simple example. If you would like to be able to call a stored procedure such as the following (or one of any magnitude of complexity):

T-SQL

as simply and naturally as writing this:

C#
VB.NET

without coding a single line of command creation and parameter population code then read on. Unlike many products already on the market, DBMethods does not rely on templates or scriptable code generators. Instead, it simply makes use of the innovative features of the Common Language Runtime (CLR) like metadata, reflection services, custom attributes and dynamic code emission. The biggest benfit of DBMethods over classic code generation is that it puts the developer in full control of the code. In fact, since DBMethods approaches the problem at a farily low level, it can even be used as a complementary approach to wizards and scriptable code generators.

Since all concepts and functionality in DBMethods apply equally to parameterized SQL and stored procedures, this document will simply refer to both as a database command. In .NET Framework, a database command is invoked using an implementation of the System.Data.IDbCommand interface provided by a .NET Data Provider (also known as a managed data provider).

Quick Start

This section will help you to get started quickly with trying Skybow.DbMethods. You do not need any previous knowledge about the library or to have read this document in its entirety. The only thing this section assumes is that you have downloaded and installed the software and that you have the sample Pubs database that comes with SQL Server or MSDE installations.

Command Line Quick Start

  1. Open a command prompt window. Make sure that its environment is set up to include the .NET Framework path. If not, add %SystemRoot%\Microsoft.NET\Framework\v1.0.3705 to the environment PATH variable.
  2. Change the directory to where the Skybow.DbMethods software was installed. The default installation folder is \Program Files\Skybow\DbMethods\vXXXX, where XXXX is the version number. If you don't have write access to this installation folder, then copy the files from the installation folder to a scrap location where you do have write access before proceeding.
  3. Type SqlMethods Pubs and press ENTER (for VB.NET, type instead SqlMethods Pubs /l:vb). This assumes that you have the sample SQL Server Pubs database available on the local machine and are using Windows Authentication. If you wish to specify an alternate server or SQL user credentials, type SqlMethods alone to see the usage of the tool. If this step is successful, then you should have a file called PubsCommands.cs for C# or PubsCommands.vb for VB.NET.
  4. Open Notepad in the same directory by typing notepad Pubs.cs (or notepad Pubs.vb for VB.NET) and pressing ENTER.
  5. Copy and paste this code into Notepad, changing the value of the ConnectionString constant if needed.
  6. Save the file and return to the Command Prompt.
  7. If you are using C#, compile the program using, csc Pubs.cs PubsCommands.cs /r: Skybow.DbMethods.dll /r: Skybow.CodeProvisioning.dll. If you are using VB.NET, compile the program using, vbc Pubs.vb PubsCommands.vb /r: Skybow.DbMethods.dll /r: Skybow.CodeProvisioning.dll /r: System.dll /r: System.Data.dll
  8. Run the compiled executable by simply typing pubs and pressing ENTER. If all goes well, then you should see a number of author identifiers written out to the console. The author identifiers are returned by the byroyalty stored procedure of the Pubs database.

Visual Studio .NET 2002 Quick Start

  1. Start Microsoft Visual Studio .NET.
  2. Create a new C# or VB.NET Console Application project.
  3. Add a reference to Skybow.DbMethods.dll and Skybow.CodeProvisioning.dll, both of which should be located in the installation directory. The default installation folder is \Program Files\Skybow\DbMethods\vXXXX, where XXXX is the version number.
  4. Add a XML file to the project and call it PubsCommands.xml.
  5. Replace the contents with the XML shown here and save the file. Change the connectionString attribute if needed.
  6. Right-click the XML file in the Solution Explorer and select Properties from the context-menu.
  7. In the Properties window, set the property Custom Tool to Skybow.DbMethods.3910 (the number 3910 is the linear build version of DBMethods as is required here to enable side-by-side installation).
  8. In the Solution Explorer, open the file Class1.cs (or Class1.vb if you are in a VB.NET project) and replace all its contents with this code. Change the value of the ConnectionString constant if needed.
  9. Select Start from Debug Without Debugging menu. If all goes well, then you should see a console window open up and a number of author identifiers written out. The author identifiers are returned by the byroyalty stored procedure of the Pubs database.

Overview

MSDN Magazine, August 2002

DBMethods is an evolution of some very early work done during the Beta 2 cycle of the .NET Framework. The best place to start is by reading the article Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET Metadata and Reflection that was originally published in the August 2002 issue of MSDN Magazine. It lays the basic foundations for the remainder of this document and also provides the main problem that this library is designed to address. The concepts presented in the article still apply, but much of the code has been refactored since to better represent the problem domain model as well as provide points of extensibility. Please return to this document after you have read the article in its entirety.

What Has Changed

The central class of the library presented in the article was SqlCommandGenerator. It had a single public method called GenerateCommand that did all the hard work in one full swing. Although it got the job done, it had drawbacks like having a procedural approach and being very specific to Microsoft SQL Server. Since then, SqlCommandGenerator has been refactored completely, yielding newer classes that provide a more flexibile architecture and also a framework for future extensions and integration of other .NET Data Providers. The new generator for SQL commands class is called SqlMethod and its CreateCommand methods provide the same overall functionality as SqlCommandGenerator.GenerateCommand.

The attributes have remained the same on the surface although somewhat refactored internally. SqlParameterAttribue now inherits from a base class called DbParameterAttribute that contains properties expected to be common across various managed data providers. This makes SqlParameterAttribue so lean that it now extends the base class with only one property that is specific to SQL Server: SqlDbType. Have the remaining properties like Name, Size, Direction, Precision and Scale in a separate base class therefore makes it dead easy to create parameter attributes for other managed providers. NonCommandParameterAttribute has been renamed to NonDbParameterAttribute, and although it still serves the same purpose, its importance has diminished. Finally, SqlCommandAttribue has been renamed to SqlMethodAttribute and extends a base class called DbMethodAttribute that now bears the CommandText and CommandType properties. Like DbParameterAttribute, DbMethodAttribute was created to hold properties expected to be common across command implementations from various managed data providers.

The remaining sections of this document will describe the design of the DBMethods library in great detail, picking up from the context of the article.

A Design Pattern for Data Access Methods

To get the maximum benefit out of the DBMethods library, you need to follow a certain design pattern for your data access methods. This design pattern has many advantages over conventional approaches and is recommended as a good practice whether you eventually end up using DBMethods or not. The central idea of the design pattern is to prevent your data access method from hardwiring the various data acquisition patterns of your application and those exposed by managed data providers. For example, given a SqlCommand object, a developer can retrieve the results of a query using either a SqlDataAdapter, a SqlDataReader or an XmlReader object. As a first rule of the thumb, therefore, the design pattern dictates that a method representing a database command never takes the responsibility of deciding how its caller would like to acquire the data. Instead, it simply returns a command object that is fully initialized and ready to be executed. The caller is then free to choose, depending on the context, whether it is more appropriate and optimal to obtain a reader via the ExecuteReader method of a command object or fill a DataSet via an adapater class. This first rule will have even more important ramifications as we will consider other scenarios later. For now, however, here is how you would write a method that creates a command object to call the GetCustomer stored procedure introduced at the beginning of this document:

C#
VB.NET

Note that it may seem somewhat misleading to think of GetCustomers as a data access method since it does not provide access to data, but rather just to the command object. It may be more appropriate to call such a method a command method or a database method, but for the sake of simplicity, this document will continue to refer to such methods as data access methods. The DBMethods library does however call such methods db-methods to make the overall concept more tangible and to make it easier to name classes after them.

A consequence of the first rule is that if a data access method cannot execute the command then it possibly cannot deal with output parameters since these can only be captured once the command has executed; and in the case of a data reader, only once all that resultsets have been read in their entirety. Herein lies the second aspect of the design pattern. Much like the asynchronous design pattern in the .NET Framework, commands that have output parameters require two methods. One that deals with the fabrication of the command object and input parameters and another that retrieves the output parameters for the caller. The first parameter of the second method must be a command object, followed by by-reference parameters that will receive the values from the command's output parameters. Consider the following stored procedure with input and output parameters:

T-SQL

A GetCustomerName and its counterpart method implementation could look like this:

C#
VB.NET

From this point forth, a method like GetCustomerName will be referred to as the input method whereas a method like EndGetCustomerName will be referred to as the output method. This is how the caller's code would work with these functions:

C#
VB.NET

So to get at the output parameters of a command, the caller has to call the output method after having finished with executing the command and retrieving any resultsets. It is worth noting that unlike the asynchronous programming pattern in the .NET Framework, this pattern does not oblige a client to call the output method if the output parameters are not needed for a particular invocation. Eventually the command object will go out of scope and become a candidate for garbage collection, as well as its parameters and their values.

For input-output parameters, the story remains much the same. An input-output parameter must be passed in by-value to the input method and by-reference to the output method. For reasons that will become obvious a little later, it is imperative that the name of the parameter be kept the same in both methods. Avoid calling it, for example, balanceIn in the input method and balanceOut in the output method.

To summarize the overall pattern, here are the ground rules for the input method:

Here are the ground rules for the output method:

The IDbMethod and IDbOutMethod Interfaces

Input and output data access methods are represented in code and programmatically by the IDbMethod and IDbOutMethod interfaces, respectively:

C#
VB.NET
(To be provided)

The IDbMethod abstraction is the principal replacement for the SqlCommandGenerator class from the article. An IDbMethod implementation is fundamentally a factory for creating and initializing command objects based on the metadata of an input and optionally an output method. The CreateCommand method takes two parameters: a connection object and an array of values to be assigned to the input parameters. Both parameters can be null (Nothing in VB.NET), in which case an IDbMethod implementation will return a command object whose CommandText, CommandType and Parameters will be defined. However, before the command can be executed, its Connection property must be set to a valid connection object and the Value property of required parameters must be set.

The IDbOutMethod takes as its only input a valid command object that has been fully executed and returns an array of the output and input/output parameter values. The order of the values in the array will be the same as the parameters defined on the output method represented by the IDbOutMethod instance.

SqlMethod: An IDbMethod Implementation

The SqlMethod class in the DBMethods library is an implementation of IDbMethod for the SQL Server .NET Data Provider.SqlMethod exposes several strongly-typed variations of CreateCommand that provide various ways to create and initialize a SqlCommand object:

C#
VB.NET
(To be provided)

The first one creates a command object whose parameters are defined but with uninitialized values; that is, the Value property of each parameter is null (or Nothing in VB.NET). The command object is not connected and does not participate in any explicit transaction, meaning that its Connection and Transaction properties read null (Nothing in VB.NET). The remaining overloads allow incremental control over which aspects of the command object will be initialized in addition to the basic configuration. Hence the second overload returns a command object that is connected but whose parameter values are still not set. The third overload return a connected command object and loads a given array of values into the input parameters. Finally, the fourth overload does all of the previous and also assigns a transaction object to the command. With the third and fourth overload, the command object is read to be executed immediately provided you supplied the values for the required set of input parameters. At any time, the caller of any CreateCommand overload is free to change the returned command object in any way. This flexibility is particularly useful for parameterized SQL where you may wish to further modify the command text before execution, as in adding an ORDER BY clause.

During construction, SqlMethod uses reflection to read the method's metadata and constructs a command object that acts as a template. When you call one of the CreateCommand methods, it creates a new command from the internal template and returns the copy. Here is an example of how SqlMethod can be used directly to the implement GetCustomer example seen earlier:

C#
VB.NET
(To be provided)

The main advantage of using SqlMethod is to completely eliminate the mundane and error-prone code to create a command object, define parameters and assign values. All of the information can be inferred from the metadata (including customization via attributes) of the data access method. By default, SqlMethod will assume that the name of the method identifies a stored procedure for which to generate command objects, unless of course you specify otherwise using the attribute SqlMethodAttribute.

Caching and Dispensing

Although you can instantiate and work with the SqlMethod class directly, it is extremely inefficient to do so. Imagine that for every call to GetCustomer, SqlMethod would end up creating two command objects. One that is maintained as a template internally and another when CreateCommand is called. This approach is mandated by the fact that IDbMethod implementations must be immutable objects. Moreover, all the code that reflects over the method, its parameters and attributes during the construction of a SqlMethod would not help performance either with each call. To avoid such overhead and amortize other fundamentally one-time initialization costs, you should instead use the GetImplementation method of MethodImplBinder.

GetImplementation first looks in its cache to see if the method supplied to it has been served before. If not, it uses the attribute (e.g. SqlMethodAttribute) on the method to create the IDbMethod implementation, caches it, and then returns it to the caller. With this infrastructure in place, the overhead of reflection and maintaining a template command object is reduced to initialization time only. That is, the first time each data access method is called. This also means that setting up a command object by hand is only going to be marginally faster and at the cost of writing and maintaining a lot of boilerplate code. GetCustomer can now be implemented like this:

C#
VB.NET
(To be provided)

MethodImplBinder has been deliberately designed as a very simple caching mechanism. If you require a more complex and intelligent caching policy, you are more than welcome to change its implementation. In the future, it may allow the caching policy to be provided by an external object.

Binding Output Methods

Consider now how GetCustomerName, which exposes output parameters as well, would be implemented:

C#
VB.NET
(To be provided)

The code in the input method is strikingly identical to that of GetCustomer. In fact it will always be the same for all input methods. The interesting piece is how GetCustomerName gets paired off with its counterpart EndGetCustomerName. When the SqlMethod instance is created for the input method, it looks if a corresponding output method can be found on the same type. The probing algorithm (a service provided by the FindOutMethod of the DbMethodReflector class) takes the name of the input method and prefixes it with End. It then queries the type for all matching methods and selects the first one bearing the custom attribute SqlOutMethodAttribute. This probing is vital because without it, the command object that would be generated by GetCustomerName would only contain the @CustomerID parameter since only the output method's signature contains firstName and lastName.

The implementation of EndGetCustomerName starts the same way as for the input parameter. It binds to the object providing its implementation; SqlOutMethod in this case. The command object passed into EndGetCustomerName is subsequently forwarded to GetCommandOutput and the resulting object array is used to push the output values back to the caller.

Working with Input-Output Parameters

An input-output parameter requires some special care. It must appear on the input method as a by-value parameter and on the output method as a by-reference parameter. The name of the parameter must obviously match across the two methods for an IDbMethod implementation to detect them. In essence, an input-output parameters is treated just like an input parameter, except its direction is forced to ParameterDirection.InputOutput if a parameter with a matching name is found on the output method.

Consider the following stored procedure:

T-SQL
C#
VB.NET
(To be provided)

Note how the attribute SqlParameterAttribute is only specified on the text parameter of the InOutExample method. It is not necessary to repeat it on the output method, except in one case. If the name of the method parameter differs from the one in the command object, then the alternate name needs to be repeated on the parameter in the input and output method. Suppose for a moment that the @Text parameter in the stored procedure was acutally called @Txt, yet we still wanted to name the parameter nicely as text in code. This is how you would then implement the methods:

C#
VB.NET
(To be provided)

Note further that when the SqlParameter attribute is repeated on the output method, then only the alternate name is defined. The remaining parts of the definition are not needed, like the data type and size. This is because GetCommandOutput only uses the name of the parameter to lookup the output value. It does not care about the definition of the parameter since that is more important during the construction of the command in the input method.

Eliminating Boilerplate Code Entirely

Since the code that goes inside all data access methods is going to be fundamentally the same, the DBMethods library can supply the entire implementation provided that you follow the design pattern outlined so far. To take advantage of this facility, all you have to do is remove the body of your data access methods and mark them abstract methods (using abstract in C# and MustOverride in VB.NET). As a result, the class that holds them becomes an abstract base class that can no longer be instantiated. To create an instance nonetheless, all you have to do is call DynamicTypeImpl.CreateInstance and supply the type of the class as its only parameter. DynamicTypeImpl dynamically builds a new class on-the-fly that inherits from your abstract base class and implements all the data access methods. You can therefore write the GetCustomer and GetCustomerName data access methods as pure abstract declarations:

C#
VB.NET
(To be provided)

A good idea is always to provide a factory method directly on the type containing the data access methods so that the client does not have to bother with DynamicypeImpl at all. Here's how:

C#
VB.NET
(To be provided)

The client code will not be completely oblivious to all details of the Commands class:

C#
VB.NET

For DynamicTypeImpl to work, it is important that the following rules be strictly observed about the class containing the data access methods:

Failure to comply with any of these rules will result in DynamicTypeImpl being unable to instaniate an object of the type.

Compiling to Assemblies

In beta version 1.0.3504.0 of DBMethods, TypeImplCompiler allowed creating assemblies containing implementation for one or more types. TypeImplCompiler has since been replaced with the command-line compiler called the Code Provisioning Compiler (cpc.exe). The Code Provisioning Compiler (CPC) takes an assembly containing your abstract types with data access methods as the input and produces another assembly containing the concrete implementations. The output assembly can subsequently be used in projects to instantiate and work with the concrete types directly rather than the abstract ones. However, this does add an additional compilation step to a project's overall build process, so unless there are significant benefits, dynamic types created via DynamicTypeImpl should be sufficient except for a few exotic scenarios.

The following figure shows the usage of the Code Provisioning Compiler:

If you compile the Commands class discussed so far into a library assembly called Commands.dll and then run it through the Code Provisioning Compiler, you should see the following output:

By default, CPC will create concrete types with the same name and namespace as the base type plus a suffix of Impl. So Commands becomes CommandsImpl. The output assembly will also have a suffix of Impl by default unless specified otherwise using the /oa switch. At the end of the compilation step, the output assembly is also tested against the standard verification process of the Common Language Runtime (CLR) by invoking the PEVerify (peverfiy.exe) tool from the .NET Framework SDK. If the PEVerify tool cannot be found, the compilation still succeeds and the resulting assembly will be verified at run-time when the CLR loads (unless verification has been disabled by the policy).

Generating Data Access Methods for SQL Server

Although DBMethods allows you to map methods to SQL commands by simply declaring compatible method signatures and applying attributes, the situation is far from ideal if you have a database with lots of stored procedures. To aid with this last mile, DBMethods comes with a tool to generate method signatures automatically from metadata about stored procedures available in SQL Server. The usage of the tool is as follows:

By default, the SqlMethods tool will connect to the database on the local machine using Windows Authentication and generate abstract C# methods for all user stored procedures. The type containing the data access methods will be called PubsCommands wrapped in a namespace called PubsDb. The output file will be called PubsCommands.cs. The generator will also produce a factory method called CreateInstance in PubsCommands.

If you want to generate VB.NET code for the Northwind database on a server called ORION, then the command line would look like this:

The remaining command line arguments are fairly self-explanatory and allow you to customize varios aspects of the generated class such as its name (/t), namespace (/ns), the base type it inherits from (/b), name of the factory method (/f) and whether the methods will have nullable parameter types or not (/nulls). If you specify the special value of dash (-) to the /f switch, then no factory method will be generated. If code should only be generated for specific stored procedures, then use the /sp switch to specify the procedure names. The /sp must be repeated for each name, as in:

The SqlMethods tool also allows you to quickly compile the generated source code into a library assembly (DLL) using the /cc switch. The assembly is usually good enough for prototyping or testing purposes only since you do not have a great deal of control on how it is compiled and generated. For example, it cannot be given a strong name or any assembly-level attributes. The compiled assembly will bear the same name as the source code file, except when specified otherwise using the /oa switch. You can also reference other assemblies using the /r switch that may be needed to compile the assembly successfully. You can repeat the /r switch multiple times, once for each assembly to reference. Normally, the /r switch is not needed as the set of required assembly references are already listed in the configuration file (see SqlMethods.exe.config).

Here is an example of compiling an assembly for the Pubs database:

By default the source code file is always generated. If you just want an assembly without having any source code generated, then supply the /gc- switch along with /cc.

Visual Studio .NET Support

DbMethods also comes with a code generator for Visual Studio .NET that allows you to generate the data access methods without having to leave the IDE and running the SqlMethods tool from the command line. All you have to do is add a XML file to your project and set its Custom Tool property to Skybow.DbMethods.3910, as illustrated below:

At a minimum, all that the XML file needs to supply is the connection string using the same specification as expected by the SQL Server .NET Data Provider. Here is how the XML would look like for the Pubs database:

XML

As soon as you save the XML file and set its Custom Tool property, Visual Studio .NET will automatically invoke the tool in Skybow.DbMethod.VsSupport.dll and have it generate the data access code. The connection string is used to contact the database and read the definition of all user-defined stored procedures and create corresponding abstract method signatures. Much like in ASP.NET or when you generate a typed data set from a XSD, the actual code file is hidden behind and does not appear in the Solution Explorer window unless you choose to by selecting Show All Files from the Project menu. The following figure shows the C# code file behind the XML used to configure the generated code for stored procedures.

If the only setting you supply in the XML file is a connection string, then the custom tool will do the following:

You can control the generated code using a number of additional attribues supported in the XML file. The following example shows the complete set of options available:

XML

The following table describes the attribues of the type element.

Attributes of the type element
Element Attribute Description
type name Name of the new type to be generated.
inherits Name of the base type that the new type will inherit from. The default is System.Object.
namespace The namespace within which the new type will be scoped. By default the new type will be placed in the global namespace.
factoryName The name of the factory method that is responsible for creating instances of the new type. The default value is CreateInstance. This attribute only makes sense when noFactory is 0 or false. Use this attribute to change the name of the method to, for example, CreateObject.
noFactory Specifies whether a factory method will be added as a static (Shared in VB.NET) member to the new type or not. A value of 0 or false means that a factory method will be generated. Otherwise, a value of 1 or true means that a factory method will be omitted from the type. The default value is 0.

A type element can contain one or more sqlMethods elements, although most of the time you will only want one. Having more than one sqlMethods allows you to essentially place stored procedures from various SQL Server databases under one type. The following table describes the attributes of the sqlMethods element:

Attributes of the sqlMethods element
Element Attribute Description

sqlMethods

 connectionString The connection string that will be used to connect to the SQL Server 7.0 or 2000 database to retrieve the metadata of user-defined stored procedures. This attribute uses the same connection string syntax and specification as the SQL Server .NET Data Provider.
useSqlTypes

Specifies whether the parameters of all data acesss methods will use native .NET Framework types or data types from the System.Data.SqlTypes namespace. For example, if a stored procedure has an INT parameter, then the corresponding method parameter will either have an int or a SqlInt32 data type depending on the value of this attribute. SqlInt32 is required if you want to be able to specify a NULL value, as in SqlInt32.Null.

A value of 1 or true for this attribute means that SQL data types will be used for parameters. A value of 0 of false means .NET Framework data types will be used for parameters. The default value is 0.

A sqlMethods element can contain a single procedures element that allows you to specify a subset of stored procedures for which methods will be generated. In the absence of a procedures element, methods for all user-defined stored procedures in the source database will be generated. The procedures element is only a container for one or more procedure elements. You must add a procedure element for each stored procedure to be imported into the type. The following table describes the attributes of the procedure element.

Attributes of the procedure element
Element Attribute Description
procedure name The name of the stored procedure in the source database for which to generate a data access method. The name of the data access method will be the same as the stored procedure. If the name of the stored procedure contains any character that is not alphanumeric or an underscore, then it is replaced by an underscore in the method's name. For example, the Sales By Year stored procedure in the Northwind database contains spaces, so the generated method will be called Sales_By_Year instead. The method will then have a SqlMethod attribute applied to it that specifies the original name in the database so that the generated command will still be correct. The same naming scheme is applied to stored procedure parameter names.

One last aspect of the XML file that is not always obvious is that it is completely independent of the programming language of the project. In other words, you can copy or share the XML file among projects in different languages without changing anything. The custom tool will always produce code in the language of the project. So you could prepare the XML file in a C# project and send it over to someone who develops in VB.NET. As far as the other person has the custom tool installed, all he or she has to do is add the XML file to their project and set its Custom Tool property to Skybow.DbMethods.3910. When the tool is invoked, it will detect the language of the project and generate code in VB.NET. If the other person does not have the custom tool, then you must generate the code file first and then send it over.

Related Documents

Frequently Asked Questions (FAQ)