Empty output parameter from parameter arraylist (enterprise library 6)

Topics: Data Access Application Block, General discussion
Nov 27, 2013 at 9:06 PM
Trying to implement Enterprise Library v.6 Data Access Blocks. Former code (using Sqlhelper from MS Application Blocks) made use of SqlParameter array, but that's causing a lot of issues . Instead using the ArrayList works fine on all other EL 6 updated methods of the application except below when trying to catch an output parameter to be returned along with the dataset.

Result is properly returned from the Stored Procedure (shows up at command level), but not captured properly (empty at totalRecords = ...) . Anyone have any advise? (note: the use of command.parameter.add etc. is no option, must be solved with below setup). Thanks in advance.
    public InvitationQueueData GetVotersInvitationQueue(int surveyId, int pageNumber, int pageSize, out int totalRecords)
        {
            InvitationQueueData dataSet = new InvitationQueueData();

// Former code:
            //SqlParameter[] commandParameters = new SqlParameter[] 
            //{ new SqlParameter("@SurveyID", surveyId), 
            //    new SqlParameter("@CurrentPage", pageNumber), 
            //    new SqlParameter("@PageSize", pageSize), 
            //    new SqlParameter("@TotalRecords", SqlDbType.Int) };

            //commandParameters[3].Direction = ParameterDirection.Output;

//New code:    
            ArrayList commandParameters = new ArrayList();
            {
                commandParameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int){ Direction = ParameterDirection.Output }.SqlValue);
                commandParameters.Add(new SqlParameter("@SurveyId", surveyId).SqlValue);
                commandParameters.Add(new SqlParameter("@CurrentPage", pageNumber).SqlValue);
                commandParameters.Add(new SqlParameter("@PageSize", pageSize).SqlValue);
            }

            DbConnection.db.LoadDataSet("vts_spVoterInvitationQueueGetAll", dataSet, new string[] {"InvitationQueues"}, commandParameters.ToArray());

            totalRecords = Convert.ToInt32(commandParameters[0].ToString());

            return dataSet;
        }
Nov 28, 2013 at 7:49 AM
I don't think what you are trying to do will work.

When an object array is passed in to LoadDataSet the objects passed in are set to the command parameter value. The DbCommand parameters are not available since the DbCommand object is created and Disposed within the LoadDataSet method.

As you discovered you can't actually pass a SqlParameter in as part of the object array because the SqlParameter object is set as the Value property of the DbCommand Parameter which causes an error. The use of .SqlValue doesn't really help because that is just setting the value of the SqlParameter into the array.

My advice would be to either not use output parameters or to change to a design that achieves what you want.

Here is the closest I can think of to what you have posted. For output parameters pass in a SqlParameter but for input parameters pass in an object (which is what you are currently passing in). Then write an extension method to set the SqlParameter Value to the DbCommand.Parameter Value:
    ArrayList commandParameters = new ArrayList();
    {
        commandParameters.Add(1);//new SqlParameter("@SurveyId", 1).SqlValue);
        commandParameters.Add(1);//new SqlParameter("@CurrentPage", 1).SqlValue);
        commandParameters.Add(1);//new SqlParameter("@PageSize", 1).SqlValue);
        commandParameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int) { Direction = ParameterDirection.Output });
    }

    DbConnection.db.CustomLoadDataSet("vts_spVoterInvitationQueueGetAll", dataSet, new string[] { "InvitationQueues" }, commandParameters.ToArray());

    int totalRecords = Convert.ToInt32(((DbParameter)commandParameters[3]).Value);


    public static class DatabaseExtensions
    {
        public static void CustomLoadDataSet(this Database db, string storedProcedureName, DataSet dataSet, string[] tableNames, object[] commandParameters)
        {
            using (DbCommand command = db.GetStoredProcCommand(storedProcedureName, commandParameters))
            {
                for(int i = 0; i < commandParameters.Count(); i++)
                {
                    DbParameter commandParameter = commandParameters[i] as DbParameter;

                    if (commandParameter != null)
                    {
                        command.Parameters[commandParameter.ParameterName] = commandParameter;
                    }
                }
             
                db.LoadDataSet(command, dataSet, tableNames);
            }
        }
    }

Note that parameter order matters in the ArrayList.

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