DB-Invoke Sample

Open this sample in Visual Studio

The PostSharp.Samples.DbInvoke project demonstrates the use of PostSharp Laos to implement a custom attribute that "imports" database stored procedures just like P-Invoke "imports" unmanaged APIes. It is not a production-grade solution but rather an inspiring proof of concept.

Here is a usage example:

[DbInvoke("ConnectionString")]
internal static class DataLayer
{
#pragma warning disable 626
extern static public void CreateCustomer(string customerName, out int customerId);
extern static public void ModifyCustomer(int customerId, string customerName);
extern static public void DeleteCustomer(int customerId);
extern static public void ReadCustomer(int customerId, out string customerName);
#pragma warning restore 626
}

The connection string should be configured in the application configuration file in the connectionString sections. The pragma directive disables the warning that these external methods have no custom attribute on it (since the C# compiler is not aware of custom attribute multicasting).

Prerequisites

In order to execute this sample project, you need an MS SQL Database and the right to create objects. Execute the CreateDatabaseObjects.sql file in the C# project PostSharp.Samples.DbInvoke.Test. Then adapt the connection string in the file app.config so that it fits your settings.

Implementation

Overview

The concept is really simple: we develop a custom attribute DbInvokeAttribute derived from ImplementMethodAspect, and its OnExecution method will be invoked whenever the method to which it is applied (the external method) is invoked.

In the event arguments passed to the OnExecution method, we got information about the invoked method and the parameters it received. We simply call a stored procedure named exactly as the invoked method, and we pass the parameters. The only difficulty is to change the type of these parameters to database ones, but it is more an implementation issue than a conceptual one.

In order to get a database connection, we use the Database Provider Factory facility from .NET 2.0.

Runtime Initialization

When the aspect is initialized at runtime, we get the connection string and the DbProviderFactory from the application configuration file, and we store them as instance fields. Since these fields are not used at compile time, we mark them as non serialized.
[NonSerialized]
DbProviderFactory dbProviderFactory;

[NonSerialized]
string connectionString;

public override void RuntimeInitialize(MethodBase method)
{
ConnectionStringSettings connectionStringSettings =
ConfigurationManager.ConnectionStrings[this.connectionStringKey];

this.dbProviderFactory = DbProviderFactories.GetFactory( connectionStringSettings.ProviderName);
this.connectionString = connectionStringSettings.ConnectionString;
}

Type Mapping

Since we do not want to bind ourself to a specific ADO.NET provider, we will use the types defined in System.Data.DbType and we need additionally to specify the proper size and scale of data types.

In order to solve this problem, we use the classes DbTypeMapping and DbCompactType that we adapted from another project of us. They are not really interesting for our discussion of PostSharp but are necessary to solve the current sample. Basically, the method DbTypeMapping.GetPreferredMapping maps a .NET Type to the preferred database type. A complete description of the implementation is out of scope and is anyway relatively simple.

Invoking the Stored Procedure

The implementation of the OnExecution method is not very exciting (but I believe you get really productive while you get bored). 

public override void OnExecution(MethodExecutionEventArgs eventArgs)
{
}

The first thing is to get a connection (DbConnection) from the factory (DbProviderFactory) that we initialized in the RuntimeInitialize method. Then we can create a new command (DbCommand).

// Get a connection.
DbConnection connection = dbProviderFactory.CreateConnection();
connection.ConnectionString = this.connectionString;

// Get a command and set it up.
DbCommand command = connection.CreateCommand();

For the sake of simplicity, we chose to constraint the name of the procedure to be strictly equal to the name of the external method. Other rules may of course be implemented.

command.CommandText = eventArgs.Method.Name;
command.CommandType = CommandType.StoredProcedure;

Then, for each parameter of this method, we create a database parameter (DbParameter) of the proper type and we assign it (unless the parameter is out).

ParameterInfo[] methodParameters = eventArgs.Method.GetParameters();
for ( int i = 0; i < methodParameters.Length; i++ )
{
ParameterInfo methodParameter = methodParameters[i];

// If the parameter is ByRef, get the element type.
Type parameterType = methodParameter.ParameterType;
if (parameterType.IsByRef)
parameterType = parameterType.GetElementType();

// Create and set up the parameter.
DbParameter commandParameter = dbProviderFactory.CreateParameter();
commandParameter.ParameterName = methodParameter.Name;
commandParameter.Direction = methodParameter.IsIn && methodParameter.IsOut ? ParameterDirection.InputOutput :
methodParameter.IsOut ? ParameterDirection.Output : ParameterDirection.Input;
DbCompactType dbType = DbTypeMapping.GetPreferredMapping(parameterType).DbCompactType;
commandParameter.DbType = dbType.DbType;
commandParameter.Size = dbType.Size == DbTypeMapping.FreeSize ? 1000 : dbType.Size;

// If the parameter is input, set its value.
if (methodParameter.IsIn || methodParameter.Attributes == ParameterAttributes.None)
{
commandParameter.Value = arguments[i];

}

// Finally add the parameter to the command.
command.Parameters.Add(commandParameter);

}

We execute the stored procedure.

command.ExecuteNonQuery();
And finally we write back output parameters:
// Write back the output parameters.
for (int i = 0; i < methodParameters.Length; i++)
{
ParameterInfo methodParameter = methodParameters[i];
if (methodParameter.IsOut)
{
arguments[i] = Convert.ChangeType( command.Parameters[i].Value, methodParameter.ParameterType.GetElementType());
}
}

For the consolidated code of the OnExecution method, please refer to the sample source code for the complete implementation.

Compile-time validation

It is always preferable to detect errors as soon as possible. That's why every aspect should validate its usage at compile time. This is done by implementing the CompileTimeValidate method.

We have to check that:

In order to achieve this, we use the standard System.Reflection API. The only difference is that our code is executed at compilation time.

public override bool CompileTimeValidate(MethodBase method)
{
bool hasError = false;

// Cannot be a constructor.
MethodInfo methodInfo = method as MethodInfo;
if (methodInfo == null)
{
DbInvokeMessageSource.Instance.Write(SeverityType.Error, "DBI0001",
new object[] { method.DeclaringType.FullName } );
return false;
}

// Should have void return type.
if ( methodInfo.ReturnType != typeof(void) )
{
DbInvokeMessageSource.Instance.Write(SeverityType.Error, "DBI0002",
new object[] { method.ToString() });
hasError = true;
}

// All parameters should be mappable.
foreach (ParameterInfo parameter in methodInfo.GetParameters())
{
Type parameterType = parameter.ParameterType;
if (parameterType.IsByRef) parameterType = parameterType.GetElementType();

if (DbTypeMapping.GetPreferredMapping(parameterType) == null)
{
DbInvokeMessageSource.Instance.Write(SeverityType.Error, "DBI0003",
new object[] { method.ToString(), parameter.ParameterType.FullName, parameter.Name });
hasError = true;
}
}

return !hasError;
}