Take the 2-minute tour ×

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

a) Build a single string parameter that can be decoded in the method, eg:
string params = "DateField=Created;FromDate=2011-03-01;Status=abc"
(Workable, but horrible and prone to mistakes)

b) Use a struct or a class as the parameter, eg
params.Status = "abc";
params.createdByUser = 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.

share|improve this question
add comment

2 Answers

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 '
    SET @where = @where + '(ah.ID = @ID) AND '
    SET @where = @where + '(ah.AccountID = @AccountID) AND '
IF @SourceKind IS NOT NULL
    SET @where = @where + '(ah.SourceKind = @SourceKind) AND '
    SET @where = @where + '(ah.SourceID IN ('+ @SourceIDArray +')
IF @Postprocessed IS NOT NULL
    SET @where = @where + '(ah.Postprocessed = @Postprocessed) AND '
    SET @where = @where + '(ah.UtcTimestamp >= @StartDate) AND '
    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,
    @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

share|improve this answer
Thanks Stas - It seems to me that you've adopted the approach of passing all 20 (or however many) parameters down to the code that calls your stored procedure, using null for each parameter that's not required. That's broadly similar to the way I dynamically build the sql in the top level code. However, I'm trying to accomplish this by passing a single selection parameter. –  David Mar 27 '11 at 20:33
add comment

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.

share|improve this answer
add comment

Your Answer


