Return DataTable based on boolean param

Question

Hi All,

I have a table that has a field, "IsActive," that indicates whether a record has been "deleted" or not.

Currently, I retrieve this info like so:

    public DataTable GetContractors(bool IsActive)
    {
        SqlParameter paramIsActive = new SqlParameter("@IsActive", SqlDbType.Bit);
        paramIsActive.Value = IsActive;
        DataSet ds = this.SQLDataAccess.ExecSProcReturnDataset(this.AppConfig.ConnectString, "p_selContractors", paramIsActive);
        return ds.Tables[0];
    }

The code for my DAL and stored procedure is irrelevant, so I'll omit that for now.

Here is my question: this code works fine if I want to return records that ARE active or ARE NOT active... but, how would I modify this to return ALL records (active AND inactive)?

Right now, I have two methods and two stored procs (one with the IsActive parameter and the other without the param), but I know there must be an easier way.

Any suggestions?

Answer 1

Make @IsActive nullable and tweak your SQL code like this:

select ... from ... t
where ...
and (@IsActive is null or t.IsActive = @IsActive)

Plus, refactor your code a tad so that it's more explicit:

public DataTable GetContractors(bool isActive)
{
 return GetContractors((bool?)isActive);        
}

public DataTable GetAllContractors()
{
 return GetContractors(null);
}

private DataTable GetContractors(bool? isActive)
{
    SqlParameter paramIsActive = new SqlParameter("@IsActive", SqlDbType.Bit);
    paramIsActive.Value = isActive == null ? DBNull.Value : (object)isActive.Value;

    DataSet ds = this.SQLDataAccess.ExecSProcReturnDataset(
        this.AppConfig.ConnectString, "p_selContractors", paramIsActive);

    return ds.Tables[0];
}

Answer 2

Pass a NULL (assign DBNull.Value to the parameter) and modify your stored procedure to check for that and not care what type of record it is in that circumstance. Some thing like:

.... WHERE MyTable.IsActive = COALESCE(@IsActive, MyTable.IsActive)

Answer 3

change your stored procedure. Have it check if the parameter is null. if it's null, return both active and not active. if it's not null, return the data depending on the parameter.

then in your code, make the parameter nullable, and pass a null when you want to return all records.

Answer 4

In your sproc, make the @isActive parameter nullable

ALTER PROCEDURE p_selContractors ( @isActive bit = null )

Then in your WHERE clause, use the following :-

WHERE
  ( @isActive IS NULL OR Active = @isActive )

And in your C# code, don't set the @isActive parameter value, and you should be cool.

Answer 5

A quick and easy hack would be to call both procedures seperately and merge the results. (I'm not saying this is the best option, just one that no-one has suggested yet)

i.e.

var dt = this.SQLDataAccess.ExecSProcReturnDataset(this.AppConfig.ConnectString, "p_selContractors", true).Tables[0];
var dt2 = this.SQLDataAccess.ExecSProcReturnDataset(this.AppConfig.ConnectString, "p_selContractors", false).Tables[0];
dt.Merge(dt2, false);