ASP.NET 2.0 C# DAL Multiple Variable Parameters

I'm developing a Web App designed to query a large database table based on the users selection of criteria. In some cases, they may know and enter the primary key, other times they might want to see records of status abc created in the last 7 days, or records created by fred smith where the description contains the word proposal. The point is, there could easily be 10 - 20 different variables that they could specify, based on what they're looking for.

It's easy enough for me to build the SQL statement and apply the parameters dynamically in the code behind the webpage (aspx.cs). This approach works well.

However, I've been reading up on using a BLL & DAL (or even just a DAL) but all the examples I've seen have been trivial with no parameters eg getCategories() or a single parameter eg getProductByID(int productID)

So what I'd like advice on is how best to pass my variable list of many parameters to the BLL/DAL without having a method with (eg) 20 parameters (this is workable but seems hugely unwieldly, especially if a new selection parameter is added).

The other ideas I've thought of are

1. Build a single string parameter that can be decoded in the method, eg:

string params = "DateField=Created;FromDate=2011-03-01;Status=abc"  
BLL.getRecords(params);  

(Workable, but horrible and prone to mistakes)

2. Use a struct or a class as the parameter, eg

params.Status = "abc";  
params.createdB开发者_如何学运维yUser = 23; 

Is there an issue with the DAL having access to this struct/class? I've read that the DAL should not share any references with the classes that call it?

Thanks for any suggestions as to how you would implement this scenario.

In my projects I create static class DataManager that exposes all required functions for obtaining data, e.g.

public static IList<ActionHistoryData> GetActionHistoryList(DateTime startDate, DateTime endDate, bool postprocessed)
{
   return GlobalComponents.DataManagerImpl.GetActionHistoryList(null, null, null, null, null, null, null, startDate, endDate, false, postprocessed, null);
}

public static ActionHistoryData GetActionHistory(int id)
{
    IList<ActionHistoryData> actionHistoryList =
        GlobalComponents.DataManagerImpl.GetActionHistoryList(id, null, null, null, null, null, null, null, null, null, null, null);
    CQGUtils.Verify(!CollectionsUtil.IsEmpty(actionHistoryList), "There is no action history with [ID='{0}']", id);
    CQGUtils.Verify(actionHistoryList.Count == 1, "More than one action history returned.");
    return actionHistoryList[0];
}

As you see in DB we have only one stored procedure GetActionHistoryList (for ActionHistory table data) with many different arguments. Stored procedure contains dynamic SQL e.g.

`<select statement part>`

DECLARE @where nvarchar(4000);
SET @where = N' WHERE '
IF @ID IS NOT NULL
    SET @where = @where + '(ah.ID = @ID) AND '
IF @AccountID IS NOT NULL
    SET @where = @where + '(ah.AccountID = @AccountID) AND '
IF @SourceKind IS NOT NULL
    SET @where = @where + '(ah.SourceKind = @SourceKind) AND '
IF @SourceIDArray IS NOT NULL
    SET @where = @where + '(ah.SourceID IN ('+ @SourceIDArray +')
IF @Postprocessed IS NOT NULL
    SET @where = @where + '(ah.Postprocessed = @Postprocessed) AND '
IF @StartDate IS NOT NULL
    SET @where = @where + '(ah.UtcTimestamp >= @StartDate) AND '
IF @EndDate IS NOT NULL
    SET @where = @where + '(ah.UtcTimestamp <= @EndDate) AND '
) AND '

SET @where = @where + ' 1=1'
SET @query = @query+@where+' order by utcTimestamp desc '

EXEC sp_executesql @query,
N'
    @ID int,
    @AccountID int,
    @SourceKind tinyint,
    @SourceIDArray nvarchar(max),
    @NotificationID int,
    @DataRequestID int,
    @NotificationName nvarchar(250),
    @StartDate datetime,
    @EndDate datetime,
    @MostRecent bit,
    @Postprocessed bit,
    @TopLimit int
',
@ID = @ID,
@AccountID = @AccountID,
@SourceKind = @SourceKind,
@SourceIDArray = @SourceIDArray,
@NotificationID = @NotificationID,
@DataRequestID = @DataRequestID,
@NotificationName = @NotificationName,
@StartDate = @StartDate,
@EndDate = @EndDate,
@MostRecent = @MostRecent,
@Postprocessed = @Postprocessed,
@TopLimit = @TopLimit

Such approach allows easily to add new filtering requests

You could create an ISearchOption interface that supplies the necessary options to the DAL. You can provide common overloads to GetRecords(ISearchOption options) that construct an internal ISearchOption instance and pass it to the GetRecords() overload.

Another option would be to use LINQ-to-SQL. You could then expose the table as an IQueryable directly. Client code then has complete freedom to filter the table as needed.