multiple Criteria, multiple Dataaccess methods??

multiple Criteria, multiple Dataaccess methods??

Old forum URL: forums.lhotka.net/forums/t/4766.aspx


Ashraf Ali posted on Tuesday, April 29, 2008

I have to use different critieria in my objects and collections for retrieval, I know it was discussed long ago  in the thread
http://www.lhotka.net/weblog/CommentView,guid,4fc42706-8733-4221-ac02-018f76e30860.aspx

So according to the thread  I should use multiple criteria classes with multiple Dataportal_Fetch methods,
Is this still the recommended way of doing it? or has it been changed?
Thanks for your help

Ash

JoeFallon1 replied on Tuesday, April 29, 2008

I believe using strongly type DataPortal calls (1 for each type of Criteria) is recommended. But you can just as easily have a single DP method with a Case statement to test TypeOf criteria to do the same thing.

In either case the code should branch to a common method which does all the work. e.g. Each branch figures out the SQL or SP name to use and then they all call the same ADO.Net code and pass in the string value of the SQL or SP name.

Joe

 

Patrick.Roeper replied on Tuesday, April 29, 2008

In the case where you are returning the same database result set, into the same object hierarchy, but your queries to get the data vary, I have created a base criteria class which has gotten me past the problem so far.

    public abstract class BusinessObjectCriteriaBase
    {
        protected abstract string ProcedureName { get; }
        protected abstract void ApplyParameters(SqlCommand cm);

        public SqlCommand CreateCommand(SqlConnection cn)
        {
            SqlCommand cm = cn.CreateCommand();

            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = ProcedureName;

            ApplyParameters(cm);

            return cm;
        }
    }

All of my nested criteria classes inherit from this base class and my fetch method looks something like...

        private void ExecuteFetch(SqlConnection cn, BusinessObjectCriteriaBase criteria)
        {
            using (SqlCommand cm = criteria.CreateCommand(cn))
            {
                using (var dr = new SafeDataReader(cm.ExecuteReader()))
                {
                   .........

This has allowed me to put the criteria in charge of setting the stored procedure to point at, along with the parameters to set.

An implementation of the base criteria class could look something like...

        [Serializable]
        private class InventoryCriteria : BusinessObjectCriteriaBase
        {
            private readonly int _inventoryId;

            public InventoryCriteria(int inventoryId)
            {
                _inventoryId = inventoryId;
            }

            protected override void ApplyParameters(SqlCommand cm)
            {
                cm.Parameters.AddWithValue("@InventoryId", _inventoryId);
            }

            protected override string ProcedureName
            {
                get { return "spGetInventoryStock_ByInventory"; }
            }
        }

And the factory methods would be...

        public static InventoryStockList GetInventoryStockList()
        {
            return DataPortal.Fetch<InventoryStockList>(new DefaultCriteria());
        }

        public static InventoryStockList GetInventoryStockList_ByInventory(int inventoryId)
        {
            return DataPortal.Fetch<InventoryStockList>(new InventoryCriteria(inventoryId));
        }


        public static InventoryStockList GetInventoryStockList_ByInvoice(int invoiceId)
        {
            return DataPortal.Fetch<InventoryStockList>(new InvoiceCriteria(invoiceId));
        }

This is of course just one way to solve the problem, maybe not the preferred.

Copyright (c) Marimer LLC