Entlib6 DAAB - Using accessors to execute stored procedures with optional parameters?

Topics: Data Access Application Block
May 16, 2013 at 1:36 PM
I'm working with Enterprise Library 6 and using the Data Access Application Block. I am having trouble using accessors to execute stored procedures with optional parameters. For example, lets say I have a simple stored procedure to select a repair ticket by the ticket number, the group number, or both the ticket and group number.
CREATE PROCEDURE [Repair].[REPAIRTICKET_Select]
    @RepairTicket   nvarchar(6) = NULL,
    @GroupNumber    nvarchar(5) = NULL
AS
BEGIN
    
    SET NOCOUNT ON;

    SELECT * FROM [Repair].[REPAIRTICKET]
    WHERE (@RepairTicket IS NULL OR (RepairTicket = @RepairTicket))
      AND (@GroupNumber IS NULL OR (GroupNumber = @GroupNumber))
    OPTION (RECOMPILE)

END
This doesn't seem to play well with the .ExecuteSprocAccessor<T>() unless I'm missing something. When I try to retrieve a ticket from the SQL Database (by ONLY the RepairTicket NOT the Group) with the following I get a number of parameters mismatch error.
var ticketData = sqlDB.ExecuteSprocAccessor<REPAIRTICKET>("Repair.REPAIRTICKET_Select", repairTicket).ToList();
I have to actually pass the group in as null to get the accessor to work.
var ticketData = sqlDB.ExecuteSprocAccessor<REPAIRTICKET>("Repair.REPAIRTICKET_Select", repairTicket, null).ToList();
This is fine for this stored procedure. However, I have other update sprocs that have 20+ optional parameters. I don't want to have to explicitly pass null to the .ExecuteSprocAccessor's object[] parameters. Not to mention how error prone this would be. I would have to be looking at the sproc and making sure of the parameter order every time.

It would be much nicer if I could just pass the single parameter in my first example by name to the accessor and leave off any null parameters. Is this possible? Thanks in advance.
Editor
May 17, 2013 at 8:05 AM
Edited May 17, 2013 at 9:13 PM
The issue is that the accessors use an object array which is positional but if you are dealing with optional stored procedure parameters you need to either supply all values with the optional parameters being set to null or use parameter names and not set the optional parameters.

Here is one way to get what you want -- there may be others. In this approach the stored procedure parameters are discovered, then a custom IParameterMapper is used to indicate what parameter values will be set (in the correct order) and then a Custom SprocAccessor is created to handle the command object used for discovery.
    DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());

    var db = DatabaseFactory.CreateDatabase();

    using (DbCommand cmd = db.GetStoredProcCommand("REPAIRTICKET_Select"))
    {
        db.DiscoverParameters(cmd);
        NamedParameterMapper parameterMapper = new NamedParameterMapper("@RepairTicket");
        var ticketData = new CustomSprocAccessor<REPAIRTICKET>(db, cmd, parameterMapper).Execute(repairTicket);
    }

    public class NamedParameterMapper : IParameterMapper
    {
        private string[] parametersToUse;

        public NamedParameterMapper(params string[] parametersToUse)
        {
            this.parametersToUse = parametersToUse;
        }

        public void AssignParameters(DbCommand command, object[] parameterValues)
        {
            int parameterValueIndex = 0;

            for (int i = 0; i < command.Parameters.Count; i++)
            {
                if (parametersToUse.Contains(command.Parameters[i].ParameterName, StringComparer.OrdinalIgnoreCase) &&
                    command.Parameters[i].Direction == System.Data.ParameterDirection.Input)
                {
                    command.Parameters[i].Value = parameterValues[parameterValueIndex++];
                }
            }
        }
    }

    public class CustomSprocAccessor<TResult> : SprocAccessor<TResult> where TResult : new()
    {
        private DbCommand command;
        IParameterMapper parameterMapper;

        public CustomSprocAccessor(Database db, DbCommand command, IParameterMapper parameterMapper) 
            : base (db, command.CommandText, parameterMapper, MapBuilder<TResult>.BuildAllProperties())
        {
            this.command = command;
            this.parameterMapper = parameterMapper;
        }

        public override IEnumerable<TResult> Execute(params object[] parameterValues)
        {
            parameterMapper.AssignParameters(command, parameterValues);
            return base.Execute(command);
        }
    }

You could probably add a bit more abstraction through extension methods (similar to how the block does) to make it more elegant. Something like:
    public static class DatabaseExtensions
    {
        public static IEnumerable<TResult> ExecuteNamedSprocAccessor<TResult>(this Database database, string procedureName, NamedParameterMapper parameterMapper, params object[] parameterValues)
            where TResult : new()
        {
            using (DbCommand cmd = database.GetStoredProcCommand(procedureName))
            {
                database.DiscoverParameters(cmd);
                return new CustomSprocAccessor<TResult>(database, cmd, parameterMapper).Execute(parameterValues);
            }
        }
    }

    // And then use like so:
    DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());

    var db = DatabaseFactory.CreateDatabase();

    var results = db.ExecuteNamedSprocAccessor<REPAIRTICKET>("REPAIRTICKET_Select",
        new NamedParameterMapper("@RepairTicket"), repairTicket);

Of course, for a full solution you might want to implement the RowMapper and ResultMapper overloads as well as CreateNamedSprocAccessor helpers as well.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
May 20, 2013 at 1:12 PM
Prior to your code sample you say I can "use parameter names and not set the optional parameters". Do you have an example of this? Is it possible or am I misinterpreting your response?

Also, it seems easier for me to just use something like ExecuteDataSet() and get the DataTable that was returned, then use AutoMapper to translate the table into a collection of objects. This is what I have in place now and is preferable to using the accessors thus far. Thanks.
Editor
May 20, 2013 at 4:41 PM
Edited Jun 12, 2013 at 6:40 PM
BBauer42 wrote:
Prior to your code sample you say I can "use parameter names and not set the optional parameters". Do you have an example of this? Is it possible or am I misinterpreting your response?
I was just listing the ways in general on how to invoke stored procedures with optional parameters. My example shows how to do this following the pattern used by accessors.

Of course, there are many ways to get where you want to go. Another way would be to use the Enterprise Library mappers directly:
    DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());

    Database db = DatabaseFactory.CreateDatabase();

    using (DbCommand cmd = db.DbProviderFactory.CreateCommand())
    {
        cmd.CommandText = "REPAIRTICKET_Select";
        cmd.CommandType = CommandType.StoredProcedure;

        if (repairTicket != null)
        {
            db.AddInParameter(cmd, "@RepairTicket", DbType.String, repairTicket);
        }

        IRowMapper<REPAIRTICKET> mapper = MapBuilder<REPAIRTICKET>.BuildAllProperties();

        using (var reader = db.ExecuteReader(cmd))
        {
            while (reader.Read())
            {
                yield return mapper.MapRow(reader);
            }
        }
    }

I would prefer to use ExecuteReader instead of ExecuteDataSet if you don't actually need a DataSet.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to