Yup, snow, and lots of it...about 14-18 inches here in Allentown by the end of the night.

Here's a full example of a stored procedure called ValidateUserID, which uses two parameters: a user ID and password that are sent up from the client piece. I went over this with Bonnie earlier today and she made several suggestions to simplify what I was initially going to post - so this is a joint response. ;)

1) Your client piece (or web service) makes a call to your business object...

DataSet DsResults = oUserBizObject.ValidateUserID("JohnSmith","Washington");

2) Your business object would be something like the following, with a call to your data access layer

public class UserBizObject : BaseBizObject
..
public DataSet ValidateUserID(string cUserID, string cPassword)
{
  // this is a simple example, where the biz object simply passes the 2 parameters on to the data access layer.  However,
  // there may be special rules about setting these parameters before calling the data access piece.
   return oUserDataAccessObject.ValidateUserID(cUserID, cPassword);
}

3) You define the name of the stored procedure and parameter objects in your data access object for the User Manager (below).

Also note that the UserManager's Data object is based on the BaseDataAccess class, which is covered in #4. I'm accessing one of the protected methods in the base class - GetStoredProcResults. Also note that an ArrayList is being used to pass a variable number of parameters for the SP - that way, the method that actually executes the stored procedure can read the arraylist to set the SP parameters.

public class UserDataAccessObject : BaseDataAccessObject
..
public DataSet ValidateUserID(string cUserID, string cPassword)
{
ArrayList aSqlParms = new ArrayList();
aSqlParms.Add(new SqlParameter("@cUserID",cUserID));
aSqlParms.Add(new SqlParameter("@cPassword",cUserPassword));

DataSet DsReturn = this.GetStoredProcResults("spValidateUserID",aSqlParms);

return DsReturn;
}

4) Last, but very important...the base class BaseDataAccessObject contains all the method calls to the back-end database. I'm posting one function, GetStoredProcResults, which was referenced above. It reads the arraylist of parameter objects, sets the data adapter accordingly, calls the stored proc, and returns the results.

public class BaseDataAccessObject
..

protected DataSet GetStoredProcResults(string cProcName, ArrayList aParameters)
{
         SqlConnection oSqlConn;
         oSqlConn = this.GetConnection();
         DataSet DsReturn = new DataSet();

	SqlDataAdapter oSqlAdapter = new SqlDataAdapter(cProcName, oSqlConn);
	oSqlAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

	foreach(SqlParameter oParms in aParameters)
                oSqlAdapter.SelectCommand.Parameters.Add(oParms);
	oSqlAdapter.Fill( DsReturn,"MyData");
	oSqlConn.Close();

	return DsReturn;
}

Note that your base Data Access class would need additional methods for other types of back-end processes (e.g. ExecuteNonQuery).

So in short, you have a business object, a base data access class that deals with the back-end, and a data access class (derived from the base data access) that contains all the information on sp names, sp parameter objects, etc.

If you wanted to adapt this to your 'GetCustomerById', you'd simply populate the ArrayList one time. (There are other ways this can be handled - this way has worked fine for me).

As Bonnie stated somewhere along the way, the business object doesn't need to deal directly with SQL - that work is handled in the data access class. I've seen code samples where data access commands/objects are defined in the business object, but ideally they should be set in a data access layer outside of the business rules. It may initially seem like a bit of work to set up, but is a good way to abstract things out for future growth.

Finally, I used Datasets for this example. If you wanted to use XML instead, you'd insert DsResults.GetXml() at the appropriate places.

Hope that helps. (and thanks for your help, Bonnie!)
Kevin