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
Braulio Diez - 13 Nov 2007 11:45 GMT
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
/// ------------------------------
> Hi All,
> i have a sit which deals with cars, and i have a table
[quoted text clipped - 8 lines]
>
> thomson
sloan - 13 Nov 2007 13:33 GMT
You can read my take on the issue here:
http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterpr
oblem/2283/
> Hi All,
> i have a sit which deals with cars, and i have a table
[quoted text clipped - 8 lines]
>
> thomson
thomson - 15 Nov 2007 05:18 GMT
> You can read my take on the issue here:http://www.sqlservercentral.com/articles/Stored+Procedures/thezeroton...
>
[quoted text clipped - 12 lines]
>
> - 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 - 16 Nov 2007 18:19 GMT
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.
>> You can read my take on the issue
>> here:http://www.sqlservercentral.com/articles/Stored+Procedures/thezeroton...
[quoted text clipped - 22 lines]
>
> thomson