how to write query for optional parameters

Question

hello friends i am following 3 layer architecture for my project i mean presentation layer, business logic layer and finally database layer my question is that i am making a search feature for my project where i need to send parameter through a method for search query which is to make on business logic layer i am to send parameter from presentation layer by calling some method and those parameter will be used on business logic layer's method to make appropriate query it's not sure sure how many parameters will be send every time i mean parameters are optional so my question is that how should i send these optional parameter for getting exactly the require parameter on business logic layer.

What is the best way, i am programming in asp.net

Answer 1

Take a look Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform to get some ideas how you can do this

Answer 2

If you use business entities, mark every properties by custom attribute with (mask = 1, 2, 4, 8..., required = true/false).

class Product
{

  [FieldAttribute(Required=true, Mask= 0)] 
  public int Id {get; set;}
...
  [FieldAttribute(Required=false, Mask=1)] 
  public string ProductName { get; set;}    
}

then you can use reflection to read all required properties and pass them to sp. if some not required parameters is null (use Nullable<>) don't increment mask (mask &= propertyMask).

Use integer mask to partial update.

/*

    exec dbo.Update_Product @product_id = 1, @quantity = 3, @mask =  0x0004
    exec dbo.Update_Product @product_id = 1, @product_name = 'new name', @comment = 'new comment', @mask =  0x0003

*/


alter proc dbo.Update_Product
    @product_id int        
    ,@product_name nvarchar(100)    = NULL -- 0x0001
    ,@comment nvarchar(255)         = NULL -- 0x0002
    ,@quantity int                  = NULL -- 0x0004
    ,@mask     int               
AS    
    update dbo.Product
    SET 
         ProductName    = CASE WHEN (@mask & 0x0001) > 0 THEN @product_name   ELSE ProductName    END
        ,Comment        = CASE WHEN (@mask & 0x0002) > 0 THEN @comment       ELSE Comment        END
        ,Quantity       = CASE WHEN (@mask & 0x0004) > 0 THEN @quantity       ELSE Quantity       END
    WHERE id = @product_id

2) For select, use two masks (select, and where)

/*
exec dbo.Select_Product @select_mask = 0x0001, @where_mask = 0
exec dbo.Select_Product @select_mask = 0x0003, @where_mask = 0

exec dbo.Select_Product @select_mask = 0x0003, @where_mask = 0x0004, @quantity = 2

*/
alter proc dbo.Select_Product
     @select_mask     int 
    ,@where_mask      int 
    ,@product_name nvarchar(100)    = NULL -- 0x0001
    ,@comment nvarchar(255)         = NULL -- 0x0002
    ,@quantity int                  = NULL -- 0x0004
as
 DECLARE @select varchar(max)
 DECLARE @where varchar(max)
SET @select = 'select Id '

IF  (@select_mask & 0x0001) > 0
    SET @select = @select + ',ProductName'
IF  (@select_mask & 0x0002) > 0
    SET @select = @select + ',Comment'
IF  (@select_mask & 0x0004) > 0
    SET @select = @select + ',Quantity'

SET @select = @select + ' from dbo.Product'

IF @where_mask > 0
    SET @where = ' where ' + CASE WHEN (@where_mask & 0x0001) > 0 THEN 'ProductName = ''' + @product_name  + '''' ELSE ''    END
            + CASE WHEN (@where_mask & 0x0002) > 0 THEN 'Comment = '''  + @comment + ''''  ELSE ''    END
            + CASE WHEN (@where_mask & 0x0004) > 0 THEN 'Quantity = ' + CONVERT(varchar(10), @quantity)   ELSE ''    END

PRINT @select + @where
 exec (@select + @where)