Multiple IDs Criteria Object

Multiple IDs Criteria Object

Old forum URL: forums.lhotka.net/forums/t/5949.aspx


RubRunner posted on Sunday, December 07, 2008

Hello, I am doing my first application using CSLA 2.1 and VS2005 (C#). My problem is tath I need to retrieve data from the SALES table but I should let the user to select one o more products to be shown. I can figure it out what is the best approach to get the dat from the database.

1) If I use a complex criteria object with the list of ProductIDs I will need to send a list of comma separated values to the sql2005 Stored Procedure, creating a temp cursor in the sql server to filter the data

2) I could retrieve all the sales info with the stored procedure and filter the data in the DataPortal_Fetch method

I am not sure if one of those solutions are good enough. Can you tell me some tips in order to solve that?

Best regards

sergeyb replied on Sunday, December 07, 2008

If you are using SQL 2008, you can use table valued parameter.  If you are using SQL 2005, you can probably use XML parameter.

 

Sergey Barskiy

Principal Consultant

office: 678.405.0687 | mobile: 404.388.1899

cid:_2_0648EA840648E85C001BBCB886257279
Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

From: RubRunner [mailto:cslanet@lhotka.net]
Sent: Sunday, December 07, 2008 2:52 PM
To: Sergey Barskiy
Subject: [CSLA .NET] Multiple IDs Criteria Object

 

Hello, I am doing my first application using CSLA 2.1 and VS2005 (C#). My problem is tath I need to retrieve data from the SALES table but I should let the user to select one o more products to be shown. I can figure it out what is the best approach to get the dat from the database.

1) If I use a complex criteria object with the list of ProductIDs I will need to send a list of comma separated values to the sql2005 Stored Procedure, creating a temp cursor in the sql server to filter the data

2) I could retrieve all the sales info with the stored procedure and filter the data in the DataPortal_Fetch method

I am not sure if one of those solutions are good enough. Can you tell me some tips in order to solve that?

Best regards



JonStonecash replied on Monday, December 08, 2008

If you are not restricted to stored procedures, you could construct your SQL dynamically, using string concatenation and conditional logic.  If you go down this path, be sure to use parameters for the variables.  This eliminates the risk of "sql injection" if it exists in your situation and may enhance performance: all queries with three products specified "look the same" to the DBMS engine and can re-use the query plan.

Jon Stonecash

simon_may replied on Monday, December 08, 2008

Hi.

The way I approach this is to write Query stored procedures that take a where cluase as a paramater. I have my own criteria objects that converts the query specification provided and creates the where clause that is injected into the query stored procedure. The query spedification its self uses a fluent API based on the business objects properties. This gives you the flexibility of dynamic SQL together with the advantages of uning a stored procedute.

Regards

Simon

Copyright (c) Marimer LLC