As a follow-up....here's some code for a base data access class, which can receive parameters and then call a stored proc:
There are three steps: first, you'll need some base data access functions. Here's a stripped down version of my data access class, with a method called GetData. (You'll need to supply whatever method you're using for getting a connection object). This method takes a name of a stored proc, a .NET List of parameters....and returns the result set as a basic dataset.
public class MyBaseDataAccess
{
public DataSet GetData(string SProcName, List<SqlParameter> oParmList)
{
DataSet dsReturn = new DataSet();
SqlConnection oSqlConn = this.GetConnection(); // or however you're getting your sql connection
SqlDataAdapter oSqlAdapter = new SqlDataAdapter(SProcName, oSqlConn);
oSqlAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
oSqlAdapter.SelectCommand.Parameters.Add(oParm);
oSqlAdapter.Fill(oDs);
return dsReturn;
}
}Second, here's a class that inherits from the base data access class, and passes all the necessary parameters (the name of the stored proc and the list of parameters)
public class MyWhateverClass : MyBaseDataAccess // important that this class must derive/inherit from the base DA class
{
public DataSet GetResults(DateTime dStartDate, DateTime dEndDate)
{
List
oParmList = new List();
oParmList.Add(new SqlParameter("@StartDate", dStartDate));
oParmList.Add(new SqlParameter("@dEndDate", dEndDate));
DataSet dsReturn = this.GetDate("dbo.StoredProcName",oParmList);
return dsReturn;
}
}
And finally, your stored proc in sql server would begin with something like this:CREATE PROCEDURE [dbo].[StoredProcName]
@StartDate DateTime, @EndDate DateTime AS
BEGIN
SET NOCOUNT ON
SELECT (WhateverYourFieldListIs) FROM (WhateverthetablesAre) WHERE MyDate BETWEEN @StartDate AND @EndDateLet me know if that helps...
Kevin
