>I have an ADO wrapper class called SqlDataProcs. It's added as a reference to my WinForms app. In the app I have an AppDataAccess class which has methods for all calls to the DB, such as GetCustomers, GetInvoices, ect. The stored procedures themselves generally take either the the PK of a record, or null which then returns all rows.
>
>This means that my AppDataAccess class has to have overloads or check to see what params were passed. Then I hit upon this idea.
>I already set this up as a test for one stored procedure, but I would like to get some feedback on the design. It's a bit lenghty, but here it is......
>
>I wrote this base class:
>
>

>public class csStoredProcBase
>{
>    private ArrayList _colParameters = new ArrayList();
>    public ArrayList colParameters
>    {
>        get { return _colParameters; }
>    }
>
>    private string _sStoredProcName = "";
>    public string sStoredProcName
>    {
>        get { return _sStoredProcName; }
>        set {_sStoredProcName = value; }
>    }
>
>    public void AddParameter(string sParameterName, int iValue)
>    {
>        _AddParameter(sParameterName, iValue);
>    }
>    public void AddParameter(string sParameterName, string sValue)
>    {
>        _AddParameter(sParameterName, sValue);
>    }
>    private void _AddParameter(string sParameterName, object oValue)
>    {
>        SqlParameter pParam = new SqlParameter();
>        pParam.ParameterName = sParameterName;
>        pParam.Value = oValue;
>        _colParameters.Add(pParam);
>    }
>
>}
>

>
>The I wrote a class to mirror the parameters in the stored proc:
>
>

>class csGetProjects : csStoredProcBase
>{
>    public csGetProjects()
>    {
>        this.sStoredProcName = "bm_GetProjects";
>    }
>
>    private int _iAppKey = -1;
>    public int iAppKey
>    {
>        get { return _iAppKey; }
>        set 
>        { 
>            _iAppKey = value;
>            this.AddParameter("@AppKey", _iAppKey);
>        }
>    }
>
>    private int _iCategoryKey = -1;
>    public int iCategoryKey
>    {
>        get { return _iCategoryKey; }
>        set
>        {
>            _iCategoryKey = value;
>            this.AddParameter("@CategoryKey", _iCategoryKey);
>        }
>    }
>
>    private int _iStatusKey = -1;
>    public int iStatusKey
>    {
>        get { return _iStatusKey; }
>        set
>        {
>            _iStatusKey = value;
>            this.AddParameter("@StatusKey", _iStatusKey);
>        }
>    }
>
>}
>
>

>
>Finally, I wrote this method in AppDataAccess
>
>

>public static SqlDataReader ExecuteStoredProc(csStoredProcBase oStoredProc)
>{
>    SqlDataReader rdrRetVal = null;
>    _oException = null;
>
>    rdrRetVal = _oDataAccess.ExecuteReader(oStoredProc.sStoredProcName, CommandType.StoredProcedure, oStoredProc.colParameters);
>
>    if (_oDataAccess.oException != null)
>    {
>        _oException = _oDataAccess.oException;
>        rdrRetVal = null;
>    }
>
>    return rdrRetVal;
>}
>

>
>The benefit is that I only have to fill in the properties on the stored proc class that I need in that instance, or none:
>
>

>csGetProjects oGetProjects = new csGetProjects();
>oGetProjects.iAppKey = 7;
>SqlDataReader oRdr = csAppDataAccess.ExecuteStoredProc(oGetProjects);
>
>
>SqlDataReader oRdr2 = csAppDataAccess.ExecuteStoredProc(new csGetProjects());
>

>
>In each case I get back a DataReader with the results as expected. The question is - is this overkill? The benefit is that I don't have a bunch of methods in AppDataAcccess, and also those methods don't have a bunch if IF() statements to check to see each param was passed.
>
>The drawback seems to be that I could end up with a bunch of classes for stored procs.
>
>Thanks


Kevin,
IMHO you are making it complex. A simple wrapper to SP might there be:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using System.Windows.Forms;
using System.Drawing;

namespace ConsoleApplication1
{
    class SpCallTest
    {
        static void Main(string[] args)
        {
            DataTable t1 = new DataTable();
            DataTable t2 = new DataTable();

            t1.Load(
                GetReaderFromSP("[CustOrderHist]",
                new List<SqlParameter> { 
                    new SqlParameter("@customerID","BONAP")})
                    );
            t2.Load(
                GetReaderFromSP("[CustOrdersDetail]",
                new List<SqlParameter> {
                    new SqlParameter("@OrderID",10563)})
                    );
            ShowDataForm f1 = new ShowDataForm(t1, "Customer Order History");
            f1.ShowDialog();
            ShowDataForm f2 = new ShowDataForm(t2, "Customer Orders Detail");
            f2.ShowDialog();
        }

        private static SqlDataReader GetReaderFromSP(string spName, List<SqlParameter> parameters)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(@"server=.\sqlExpress;trusted_connection=yes;database=Northwind");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;
            foreach (var p in parameters)
            {
                cmd.Parameters.Add(p);
            }
            cmd.Connection.Open();
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rdr;
        }
    }

    public class ShowDataForm : Form
    {
        public ShowDataForm(DataTable tbl, string caption)
        {
            this.dgv = new System.Windows.Forms.DataGridView();
            this.dgv.Location = new System.Drawing.Point(0, 0);
            this.dgv.Dock = DockStyle.Fill;
            this.dgv.DataSource = tbl;
            this.Text = caption;
            this.Controls.Add(this.dgv);
            this.ClientSize = new System.Drawing.Size(1024, 768);
        }
        private System.Windows.Forms.DataGridView dgv;
    }
}

I hardcoded the connection there but it is a sample:)

This is the hard way when you have luxury to use Linq. For example in Linq To SQL the call to 1st one would be as simple as:

NorthwindDataContext nw = new NorthwindDataContext();
var result = nw.CustOrderHist("BONAP");

Thinking context might be a static app element it would even boil down to:

var result = nw.CustOrderHist("BONAP");

I strongly suggest to check Linq and Entity Framework.

Cetin