Design Questions for Generic Queries

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