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).
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):
as simply and naturally as writing this:
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
System.Data.IDbCommand interface provided by a
.NET Data Provider (also known as a managed data provider).
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.
%SystemRoot%\Microsoft.NET\Framework\v1.0.3705to the environment
PubsCommands.csfor C# or
ConnectionStringconstant if needed.
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
pubsand 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
byroyaltystored procedure of the Pubs database.
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.
connectionStringattribute if needed.
Class1.vbif you are in a VB.NET project) and replace all its contents with this code. Change the value of the
ConnectionStringconstant if needed.
byroyaltystored procedure of the Pubs database.
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.
The central class of the library presented in the article was
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,
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
CreateCommand methods provide the same overall functionality
The attributes have remained the same on the surface although somewhat refactored
SqlParameterAttribue now inherits from a base class
DbParameterAttribute that contains properties expected to
be common across various managed data providers. This makes
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
Scale in a separate base class therefore makes it dead easy
to create parameter attributes for other managed providers.
has been renamed to
NonDbParameterAttribute, and although it still
serves the same purpose, its importance has diminished. Finally,
has been renamed to
SqlMethodAttribute and extends a base class
DbMethodAttribute that now bears the
CommandType properties. Like
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.
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
SqlCommand object, a developer can retrieve the results of a
query using either a
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
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:
Note that it may seem somewhat misleading to think of
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:
GetCustomerName and its counterpart method implementation could
look like this:
From this point forth, a method like
GetCustomerName will be referred
to as the input method whereas a method like
will be referred to as the output method. This is how the caller's code
would work with these functions:
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
the output method.
To summarize the overall pattern, here are the ground rules for the input method:
IDbCommandor an implementation of).
IDbConnectionor an implementation of) and must be able to accept a
CommandTypeproperties of the command object.
Parameterscollection of the command object.
Parameterscollection command object. Needless to say, the direction of these parameters must be set to
Here are the ground rules for the output method:
IDbCommandor an implementation of) whose value cannot be
Input and output data access methods are represented in code and programmatically
(To be provided)
IDbMethod abstraction is the principal replacement for the
SqlCommandGenerator class from the article. An
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
Nothing in VB.NET), in which case an
implementation will return a command object whose
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.
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
SqlMethod class in the DBMethods library is an implementation
IDbMethod for the SQL Server .NET Data Provider.
exposes several strongly-typed variations of
provide various ways to create and initialize a
(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
Nothing in VB.NET). The command object is not connected and
does not participate in any explicit transaction, meaning that its
Transaction properties read
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.
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
can be used directly to the implement
GetCustomer example seen
(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,
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
Although you can instantiate and work with the
directly, it is extremely inefficient to do so. Imagine that for every call to
SqlMethod would end up creating two command
objects. One that is maintained as a template internally and another when
is called. This approach is mandated by the fact that
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
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.
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.
can now be implemented like this:
(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.
Consider now how
GetCustomerName, which exposes output parameters
as well, would be implemented:
(To be provided)
The code in the input method is strikingly identical to that of
In fact it will always be the same for all input methods. The interesting piece
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
class) takes the name of the input method and prefixes it with
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
would only contain the
@CustomerID parameter since only the output
method's signature contains
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
is subsequently forwarded to
GetCommandOutput and the
resulting object array is used to push the output values back to the caller.
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
ParameterDirection.InputOutput if a parameter with a matching
name is found on the output method.
Consider the following stored procedure:
(To be provided)
Note how the attribute
SqlParameterAttribute is only specified
text parameter of the
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
text in code. This is how you would then implement the
(To be provided)
Note further that when the
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.
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
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.
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
GetCustomerName data access methods
as pure abstract declarations:
(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
at all. Here's how:
(To be provided)
The client code will not be completely oblivious to all details of the
DynamicTypeImpl to work, it is important that the following
rules be strictly observed about the class containing the data access methods:
abstractin C# and
structin C# or
sealedin C# or
DynamicTypeImplwill fail to instantiate the class.
IDbConnectionor an implementation of.
IDbCommandor an implementation of.
Failure to comply with any of these rules will result in
being unable to instaniate an object of the type.
In beta version 1.0.3504.0 of DBMethods,
creating assemblies containing implementation for one or more types.
has since been replaced with the command-line compiler called the Code Provisioning
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
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
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
CommandsImpl. The output assembly will also have a suffix of
Impl by default unless specified otherwise using the
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
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).
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
wrapped in a namespace called
PubsDb. The output file will be called
PubsCommands.cs. The generator will also produce a factory method
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 (
/ns), the base type it inherits from (
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
/oa switch. You can also reference other assemblies using
/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
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
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:
As soon as you save the XML file and set its Custom Tool property,
Visual Studio .NET will automatically invoke the tool in
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:
System.Objectand has the same name as the XML file (minus the extension, of course). The new type will not be wrapped in any namespace. Currently, there is no check made to make sure that the name of the file makes for a valid class name in the target language.
Sharedin VB.NET) factory method called
CreateInstancethat can be used to instantiate the class. The return type of this method is the abstract base class itself.
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:
The following table describes the attribues of the
||Name of the new type to be generated.|
Name of the base type that the new type will inherit from. The default is
||The namespace within which the new type will be scoped. By default the new type will be placed in the global namespace.|
The name of the factory method that is responsible for creating instances of the
new type. The default value is
Specifies whether a factory method will be added as a
type element can contain one or more
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
||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.|
Specifies whether the parameters of all data acesss methods will use native .NET
Framework types or data types from the
A value of
sqlMethods element can contain a single
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.
procedures element is only a container for one or more
procedure elements. You must add a
for each stored procedure to be imported into the type. The following table
describes the attributes of the
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
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.