A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I have a program that needs to pull information from the database (either sorted in the db, or sorted later) and then display it through a crystal report. I do this now by passing the crystal report a DataSet with the pertinent information. The database is SQL Server. The problem is sorting it based on user input. So far we (myself, coworkers, another consultant) have come up with four ways of doing this:
1. Sort the data in the stored procedure by passing a parameter and doing "IF ... ELSE ... IF ... ELSE etc"
2. Sort the data in the stored procedure by passing a parameter but created the SQL dynamically in the stored procedure and then executing it.
3. Get the data back not sorting from the db, build a DataView with the user input, use the DataView to sort the information, and copy it to another DataTable. Put that DataTable in the DataSet that gets passed to Crystal.
4. Somehow use "SortFields" collection to set the sort fields in Crystal after giving it the DataSet. I have some code fragments for this, but when I did it I got the standard Crystal error.
I already have something like 30 stored procedures that return data for Crystal to display, so changing every stored procedure might get ugly.
If someone has another way, or insight in to any of these ways that make it easy, I would greatly appreciate it.
**Note: I have about 2-3 hours to get this done. :(
Monday, February 20, 2006
Okay, just so everybody knows I used the get DataTable, create DataView, copy rows from sorted DataView to a new DataTable method, and surprisingly not only did it work, but it was much faster than I had expected it to be.
Tuesday, February 21, 2006
If you're committed to using T-SQL stored procs I've found dynamic sql to be very effective in these situations where there are lots of conditional statements that affect the query. I've used this method for doing exactly what you describe: allowing users to define optional params and sorting for generating reports using Crystal.
That's the "get it done" method.
A better method in the future would be to abstract out a data access layer between your app and the database. That way you can a bit more with sorting and filtering the dataset without having the headache of having to maintain dozens of stored procs.
I am not sure I entirely understand your comment Larry. I, too, agree that dynamic SQL is a good way to go here. I would have done that in my stored procedures (and passed params to them) in an effort to eliminate as many procedures as possible. However, there are many stored procedures because I am writing the reports engine - most of the stored procedures are drastically different from all the others.
We have a data access layer that is used to setup parameterized queries and to call the stored procedures. In this case, I had about two hours to get this working, so switching all the stored procedures to dynamic sql wouldn't have been possible. [note: Management has no idea what they are doing, or how long things take - even after I tell them.]
Tuesday, February 28, 2006
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz