Enterprise Library 6 ExecuteSprocAccessor with database parameters

Question

I am currently learning the DataAccess block for Enterprise Library 6.

I am trying to understand the how to return a series of objects using the ExecuteSprocAccessor

This is the code that I am using:

using (DbCommand cmd = sqlServerDB.GetStoredProcCommand("SalesByCategory"))
    {
    //Pass parameters


            cmd.Parameters.Clear();
            sqlServerDB.AddInParameter(cmd,"CategoryName",DbType.String,"Beverages");
            sqlServerDB.AddInParameter(cmd, "OrdYear", DbType.String, "1996");

            IRowMapper <OrderName> resmapper = MapBuilder<OrderName>.MapAllProperties() 
                                          .Map(x => x.Name).ToColumn("ProductName")
                                          .Map(x => x.PurchaseTotal).ToColumn("TotalPurchase")
                                          .Build();

            List<OrderName> procaccessor = sqlServerDB.ExecuteSprocAccessor<OrderName>("SalesByCategory", resmapper, cmd.Parameters).ToList<OrderName>();

        }

When it runs I get a "The number of parameters does not match number of values for stored procedure." error. When I check the cmd.Parameters collection I do see the 2 parameters stated above.

I am pretty sure that the error is on the cmd.Parameters in the last instruction. According to the documentation I can use a object array. How can I use the DBParameter collection?

== Update 3-29-2014 ==

After looking for an answer for a while I just decided to try and create a method that would translate the DBParameter into an object array. I created this....

public static object[] ParametertoObj(DbCommand cmd)
{

object[] obj = new object[cmd.Parameters.Count];

int i = 0;

foreach (DbParameter item in cmd.Parameters)
   {
        obj[i] = item.Value;
        i++;
   }

 return obj;
}

The important thing with this method is that the parameters have to be added to the collection in the same order as they are called in the stored procedure.

I am pretty sure that there must be an efficient way to do this.

score 0 · Answer 1 · answered Apr 10 '14 at 01:26

0

You don't need a command object -- just call the accessor directly with the values you want to use:

IRowMapper <OrderName> resmapper = MapBuilder<OrderName>.MapAllProperties() 
                                      .Map(x => x.Name).ToColumn("ProductName")
                                      .Map(x => x.PurchaseTotal).ToColumn("TotalPurchase")
                                      .Build();

List<OrderName> procaccessor = sqlServerDB.ExecuteSprocAccessor<OrderName>(
    "SalesByCategory", resmapper, "Beverages", 1996).ToList<OrderName>();

As you note the parameters have to match the same order as the stored procedure.

answered Apr 10 '14 at 01:26

Randy Levy

22,566
4
68
94

And if sp has output parameter,how to fetch the value of it – Shashank Sood May 04 '16 at 12:14
@ShashankSood, off the top of my head, I don't think think you can do this with accessors since they only have access to the IDataReader and to get the output parameter requires access to the actual output parameter. If you just want to get the output parameter using ADO.NET then this should help: https://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net . – Randy Levy May 04 '16 at 16:17
No i wanted with this method only – Shashank Sood May 10 '16 at 06:18

Enterprise Library 6 ExecuteSprocAccessor with database parameters

1 Answers1