Searching Database - Design Best Practices

[thomson]

Hi All,
i have a sit which deals with cars, and i have a table
associated with this, i do have a problem in designing the
application, i have lot of places in the web page where it hits the
the same table but the criteria (parameters) will be different,

Can some body help me out in designing a class which suits the above
purpose , or sugggest me Best Practices for the same

Thanks in advance

thomson

 

[Guest]

Mmmm....

It can more complicated but... maybe this simple approach can feed your
needs: Create an stored procedure with all the search params that you need,
set the default value of each param to NULL.

Now in the select where you filter use this approach (pseudo SQL here):

DECLARE My Store Proc
MyParam1 Int = NULL,
MyPAram2 varchar(80) = NULL,
(...)

SELECT ...
FROM ...
WHERE
(MyParam1 IS NULL OR tablefield1 = MyParam1)
AND
(MyParam2 IS NULL OR tablefield1 = MyParam2)

By using this approach you control if the param has been informed using
the IS NULL and the OR switch, and you avoid having chunking T-SQL IF
statements.

/// ------------------------------
/// Braulio Diez
///
/// http://www.tipsdotnet.com
/// ------------------------------

 

[sloan]

You can read my take on the issue here:
http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/

 

[thomson]

You can read my take on the issue here:http://www.sqlservercentral.com/articles/Stored+Procedures/thezeroton...









- Show quoted text -

Hi all,
thanks for the inputs, these are the things which you do in
the StoredProcedure Level, Iam looking for a class where i can set the
parameters and then call the stored Procedure

thanks in advance

thomson

 

[sloan]

What I do is create a strong dataset.

If you read the article, you'll notice I had a

ParameterDS

which is a strong dataset.

Basically, I code one of these up.
And as the user checks/selects items from a form, I add entries to an
instance of this dataset.

So I might show:

CheckBox list of Customers
2 Text Boxes... for a "Orders After This Date" and "Orders Before this
Date".
DropDownList of Countries.

Then I can loop/find the values they picked.

Something like this (This is PSEUDO code, not working code).

ParameterDS ds = new ParameterDS();

for each ( Selected Check Box in chkAllCustomers )
{
ds.Customer.AddNewCustomerRow ( selected check box . Value ) ;

}
if(ddlCountries.SelectedItem Is Picked)
{
ds.Countries.AddNewCountryRow( ddlCountries.SelectedValue ) ;
}




And I then ship this dataset .GetXml() to the stored procedure.