SQL to ADO.NET API (Automated ADO.NET or DB API)

• Download Test Projects
• Download Runner

Introduction

It is common when developer creates program component as well as stored procedure to act as component proxy on back end. Then developer creates program method to call the stored procedure from the program component. Some of developers still prefer old reliable ADO.NET to do that.

Code analysis of ADO.NET employing access methods yields unsurprising result of significant quantities of repetitive code. SqlCommand is always there, SqlConnection always opens connection, one of ADO.NET Execute methods is always called, etc. This type of code is well defined and can be templated, and if it is not, it can be broken on well defined and templated components. Also, it is not the most creative part of development, because of repetitiveness.

Repetitive and templated code is good candidate for automation!

DB API generates ADO.NET code in access method automatically as well as method itself and all referenced object types. It also provides accurate error messaging at build time, and highly configurable.

Definitions

Metadata

Metadata is XML fragment of structure defined by DB API. XML must unambiguously describe DA Method. Metadata must be located in the same file as the stored procedure to simplify mapping both. DA Method must calls corresponding stored procedure using ADO.NET technology, and must be generated automatically using one of the .NET languages. For example, metadata (commented XML part) 

/*
    <DbApiMetadata method="GetListOfUsers" description="GetListOfUsers method">
        <ProcParams>
            <InParam name="UsernameLike" type="varchar(50)" />
            <OutParam name="Total" type="int" description="Total count of users" />
        </ProcParams>
        <ClassResult className="UserBase" description="Class represents basic User info">
            <ResultParams>
                <ResultParam name="UserId" typeDef="dbo.User.UserID" />
                <ResultParam name="Username" type="varchar(50)" />
                <ResultParam name="FirstName" type="varchar(50)" />
                <ResultParam name="LastName" type="varchar(50)" />
                <ResultParam name="DateOfBirth" type="datetime" />
            </ResultParams>
        </ClassResult>
    </DbApiMetadata>
*/
create procedure dbo.sp_GetListOfUsers
    @UserNAmeLike VARCHAR(50),
    @Total int output
as
BEGIN
…

describes generated C# DA Method:

///
/// Method GetListOfUsers.
/// DESCRIPTION: GetListOfUsers method
/// PARAMETERS:
/// - usernameLike -
/// - total -
///
public UserBase GetListOfUsers(string usernameLike, out int total)
{
    UserBase result = null;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("dbo.sp_GetListOfUsers", connection))
        {
            command.Parameters.Clear();
            command.Parameters.AddRange(new SqlParameter[]
            {
                new SqlParameter("@usernameLike", SqlDbType.VarChar) { 
                    Direction = ParameterDirection.Input, Size = 50, Value = usernameLike},
                new SqlParameter("@total", SqlDbType.Int) { Direction = ParameterDirection.Output}
            });
            command.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
            {
                while (reader.Read())
                {
                    result = new UserBase
                    {
                        UserId = reader.GetFieldValue<int>(0),
                        Username = reader.GetFieldValue<string>(1),
                        FirstName = reader.GetFieldValue<string>(2),
                        LastName = reader.GetFieldValue<string>(3),
                        DateOfBirth = reader.GetFieldValue<DateTime>(4)
                    };
                }
            }
            total = (int)command.Parameters["@total"].Value;
        }
    }
    return result;
}

Executable

Beside metadata, we also need an actor, an executable which can read the metadata and generate C#/VB.NET code. Let’s call such executable a Runner.

Project

Last piece of jigsaw puzzle is placeholder where the generated code is going to. Generated code is generated into C#/VB.NET file, which is set to be compiled as part of .NET Class Library project. That project is usually deployed on Data Access Layer and called DAL Project.

Build

So far, we have defined metadata, DAL Project and also have Runner to read SQL scripts and generate actual DA Methods. Now we need to setup MSBuild to build everything in proper order. In other word, Runner needs to generate DA Methods and put them in DAL Project before MSBuild starts building the DAL Project.

Visual Studio conveniently offers couple ways of doing just that. Project can use pre-build events, or if more control is needed, project can invoke Exec Task within <Target Name="BeforeBuild">.

When it is all put together and configured, MSBuild kicks-off Runner before compiling project. Runner reads and parses SQL files, extracts metadata, generates and puts DA Methods' code in the project. Done. MSBuild starts building the DAL Project, which just acquired DA Methods.

It's getting better when the project is built. The project’s members become discoverable, other projects can reference it, intellisense starts working, ... we can even ship the compiled DLL if we want to. 

Documentation explains how to assemble, configure and build DB API projects in details.

Downloads

Test projects with both C# and VB.NET examples can be found here. You will need to attach ApiEvaluateDB and ApiTestDB databases (backup files are provided) and change connection string in App.config of SqlToAdoNetApi.Dal.Test project. See "how to" in Example of SQL to ADO.NET API section.