design pattern for data filtering

is there any design pattern for data filtering under dynamic conditions. i am sharing the web application pages and same DataBase and same tables for my customers. for example i don't want that customer from company A will see the orders for company B, so the filtering 开发者_如何学Pythoncondition is done on the company field. for other pages the filtering can be done on more fields. i am using store procedures or sql text to fetch the data.

i am prefering that the data filtering will be done by the sql server.

I think that storing your business logic at the sql server side is not the best idea. Filterting data in SQL makes your stored procedures / schema design more complicated.

It would be much simpler to do dynamic filtering using some ORM (Linq, HNibernate, etc.) framework. It is of course hard to recommend something without knowing specific of your project but I would go for LINQ which provides flexible data quering.

Pass a company_id to the stored procedure from the application?

create stored procedure GetOrders(@customer_id int)
as
select * from Orders
where company_id = @customer_id;

I forget the exact syntax of stored procs (ugh!), but the idea is sound. That is of course unless you mean something else entirely.

What you want is basically an Isolation .

To achieve this you can follow several rules:

1. Declare on DB StoredProcedures/Views or in code your queries always parametrized by CompanyName, always. In this case you can not invoke any data recovery function without specifying company name.
2. Ensure in your queries that only the data specified by the CompanyName and linked to it is retrieved from DB.
3. On UI CompanyName again have to be a paramount of your UI interface, and the first thing user have to declare.

Something like this, it's difficult to say something more precise without knowing the project real requirements.

What is about LINQ vs DB, it depends on your scale. Consider that LINQ will be slower then StoredProcedure or View beginning from certain scale. But this is something that you have to measure. Cause from comfort point of view LINQ is naturally more preferable solution.