Design Questions for Generic QueriesDesign Questions for Generic Queries
Old forum URL: forums.lhotka.net/forums/t/3880.aspx
dagware posted on Sunday, November 11, 2007
This is going to be a difficult post to write, because my head is filled with all sorts of issues. But I'll try to boil it down to the relevant things. Perhaps I'll start with some background and only one question, to keep it as simple as possible.
I have a technology that I wrote for our company's internal use that's been very usefull. I wrote it in Delphi, and I'm trying to figure out how to move it to C# and CSLA. I'm pretty-much an expert Delphi programmer, but fairly new in C# and .NET. I'm learning quickly, but as you all know there's a lot to learn.
The technology allows us to define "queries" that users can run. Each query returns a result set which the users view in a Dev Express grid, which allows them to slice and dice, as well as export to Excel (they of course love this). We store the "query" definitions in a database. The definitions include the SQL query text (which often-times calls a stored procedure), information about parameters and what kinds of "pickers" to use to help the user enter the parameters, including lookup lists, etc. Also included is information on whether the result set is updatable or not, SQL queries or stored procedures to use for the inserts/updates/deletes, what fields are editable, default field values, etc. And lookup lists for editing fields that reference other tables. It's really quite extensive, and extremely usefull.
We have a stand-alone program to run these queries, and we also have frames (user controls) and such that can be embedded in an application so it can host this technology, and make it look like it belongs in the application. Along with the obvious usefulness to our users for general queries, it's a great way to include Table Maintenance for user-maintained lookup tables and such.
So here's my first question:
In Delphi, I put the SQL text in a Query component (attached to a ClientDataSet if you care), set the parameter values if any, and open it. I could do something similar in C# with a DataSet/DataTable, however I don't think this is the best thing to do with CSLA, since I will want to be able to run these queries through the CSLA Data Portal. How should I return the result set from the Data Portal? And how should I pass the updated result set back through the data portal?
Thanks for any help.
Dan Thomas
dagware replied on Sunday, November 11, 2007
The more I think about this, the more I think I need to use a DataSet/DataTable. It really encapsulates everything I need, including dynamic creation of columns so it can be used with any query, and update history so I can use the Original value of a column for WHERE statements during updates. So here's my first question:
1) How do I return a DataSet/DataTable from the Data Portal? Can I just
declare it as a property, or do I need to do something else?
Now for the second question:
2) I have a CSLA class for the QueryDef, with among other things a list of QueryParams. Once the user fills out the QueryParams, this whole object needs to be passed back to the Data Portal so it can run the query, and then a DataSet/DataTable needs to come back. How do I code this? Do I kind of bastardize the Update method? Or something else?
Thanks.
Dan
JoeFallon1 replied on Monday, November 12, 2007
1. You can just declare a Property. A dataset is Serializable. And I think in .Net 2.0 the DataTable became serializable as a stand alone object.
There are times when fecthing a dataset in the DataPortal is simpler - then you can use it to fill properties or whatever. Some people use it ot "hydrate" complex business objects because the fetch is simpler and then they can walk the dataset to gather up the values.
2. You can create Criteria classes with various names and then choose to embed them in your BOs or do as I do and move them all to a separate stand alone class so I can re-use them.
Here is a sample of one Crtieria class named CriteriaCode:
<Serializable()> _
Public Class CriteriaCode
Inherits Csla.CriteriaBase
Public MethodName As String = ""
Public Code As String = ""
Public Sub New(ByVal BOtype As Type, ByVal methodName As String, ByVal code As String)
MyBase.New(BOtype)
Me.MethodName = methodName
Me.Code = code
End Sub
End Class
By having it in a separate class I can re-use it in many BOs.
If you create one named SearchCriteria you can pass in a BO of type QueryDef to it and it should go through the DataPortal just fine.
<Serializable()> _
Private Class SearchCriteria
Public SomeQueryDefBO As SomeQueryDefBO
Public Sub New(ByVal SomeQueryDefBO As SomeQueryDefBO)
Me.SomeQueryDefBO= SomeQueryDefBO
End Sub
End Class
Public Shared Function GetSomeList(ByVal SomeQueryDefBO As SomeQueryDefBO) As SomeList
Return DataPortal.Fetch(Of SomeList)(New SearchCriteria(SomeQueryDefBO))
End Function
Then in the DataPortal you can use your QueryDef like this:
Protected Overrides Sub DataPortal_Fetch(ByVal criteria As Object)
RaiseListChangedEvents = False
IsReadOnly = False
Dim dr As SafeDataReader = Nothing
If TypeOf criteria Is SearchCriteria Then
Dim crit As SearchCriteria = CType(criteria, SearchCriteria )
Dim theQueryDefBO As SomeQueryDefBO = crit.SomeQueryDefBO
'etc.
Joe
dagware replied on Monday, November 12, 2007
Joe -
Thanks for the great reply! I think it will help a lot. Thanks!
Dan
dagware replied on Monday, November 12, 2007
FYI, I got this working. Whoo hoo! It turns out that you can indeed pass a DataTable without the need to pass its DataSet. The thing that took me the longest was getting the remote data portal to work at all -- I had never done this before. But after being an idiot for a while, I got it all working. Very cool stuff.
Thanks again for the help!
Dan
Copyright (c) Marimer LLC