I'm working on getting my arms around options to try to do something that I don't even know is doable, but am looking for some feedback... here goes....

Were working on the design of a new system. In the system users will be able to design web forms. Essentially, they'll be designing a schema (and setting validation rules) as they design the form:

EmailAddress: text, maxlength:120, validation { required:true, format:email } LastName: text, maxlength:50, validation { required:true }

etc... something like that.

The schema will be saved to a SQL Server db and then be fetched and used to render the form in a web page.

When the person fills out the form and submits it, I want to store the data in a way that will not require creating tables for the data: ie, I want to save the data either as value pairs, tuples, RDF format, etc... in some way that is designed to store "semi structured" data (if that's the right word). We'd use the schema to determine how to put the data [correction: we'd actually put the data in a generic way, but use the schema for validation before the put], and then would like to use the schema to extract the data and make it useful (for joins, etc).

I've looked at this, which gets close, but would require class files to be created (for each schema) and would create real db tables using the schema/model (not desirable):

http://www.devexpress.com/Products/NET/Libraries/XPO/howitworks.xml

I want to store the data in a generalized kind of way, but be able to get all of the benefits of something like LLBL (which we've used for years now on every .net project we do) for working with the data (CRUD, joins, etc).

We're using LLBL 2.6 for the project, already using the Linq stuff (great!) and .net 3.5. There are various newish features in LLBL that I haven't had the chance to absorb yet, but might be able to help me do something like what I'm envisioning (projections, hierarchical data, extension, etc). I'm wondering if anyone has any thoughts on how I might be able to accomplish what I'm describing using LLBL?

Basic idea: store data in a generic way without creating new tables for each new "schema", then be able to extract and use it with LLBL niceness. Thinking a data-to-object kind of thing.

To clarify what I mean by "schema", I don't mean different SQL schema's, I mean different "schema's" based on the end user's design of their form/fields.

TIA, Jack

First you should forget about LLBLGen Pro, and focus on your data model (database design). Once you settle down on how the schema would be stored and how the data would stored and validated, then I think it would be straight easy with LLBLGen Pro.

Most likely the schema definition for each form will be stored either in some SQL tables or as an XSD. Simplified version of the structure (if put into SQL tables) will be:

Form (table) - FormID - FormName - CreateDate, ModifyDate

Field (table) - FieldID - FieldName - Type (enum value: int, date, string, etc)

FormFieldMap (table) - FormID (FK) - FieldID (FK) - Position

For the data storage of the data entered into the forms, right now I'm thinking one of two things, either store as XML (very likely, but not sure if it will use SQL Server XML storage or another mechanism) or store in a row-based single SQL table, something like:

FormResponse (table) - FormResponseID - FormID (FK) - FieldID (FK) - Value

The FormResponse table there doesn't include support for matrixes of data nor is the Value field typed. I could make ValueAsText, ValueAsInt, ValueAsDate cols in the FormResponse table, for handling typing, but that's not that nice (done that before).

What I'm looking for is a way to basically store untyped hierarchical data and then, via a dynamically created "schema" for each existing Form, be able to use the schema in a way that would project (for lack of a better word) the schema into something that could be used to enforce type on the stored data and provide a means to extract it using some kind of a "query factory".

Right now I'm leaning towards storing the form definitions in SQL tables (since I know and can control the structure of the stored data and am fine with making tables for it) and storing the form response data as XML, then using XPath, XQuery, Linq to XML or perhaps Linq to XSD (in alpha, link below) to use/manipulate the data after it's been store.

So what I'm fishing for in this thread is any advice or ideas someone might have for how to use LLBL to work with the FormResponse data (not how to store the form schema's) using the form schema's, assuming form data might be stored in an untyped way as XML, like:

<formResponse> <formResponseID>123</formResponseID> <formID>123456</id> <field> <fieldID>1</field> <value>John</value> </field> <field> <fieldID>2</field> <value>Smith</value> </field> </formResponse>

Some typical use cases for working with the stored FormResponse:

• update
• get single record
• get collection
• get values into a collection
• update value(s)
• get collection, joined or prefetch path'd on FormResponseID to data outside the FormResponse "table".
• etc... basically being able to work with the stored data just like working with any Entity/EntityCollection.

Doing some reading, I'm wondering if generics would help here, which is why I'm looking at Linq to LLBL and List<> stuff. I got to looking at Linq to XML and Linq to XSD last night, but haven't put the pieces together as far as how I could leverage LLBL with or without those to help in this design/idea.

If LLBL had a native provider for working with XML data (or an implementation in a supported db, like SQL server's XML storage stuff or a native XML storage db), well that might be useful in this case, too. (or a way to fake it)

Here are the Linq to XSD links, if of interest. Thanks for any ideas anyone might have. We've used LLBL for so many things, and there are so many new features introduced in the last few versions, I'm thinking it's likely it can help us find a solution for this, too.

http://blogs.msdn.com/xmlteam/archive/2008/02/21/linq-to-xsd-alpha-0-2.aspx http://www.microsoft.com/downloads/details.aspx?FamilyID=a45f58cd-fcfc-439e-b735-8182775560af&displaylang=en

and the bottom of this article talks about the Linq to XSD idea:

http://msdn.microsoft.com/en-us/library/bb308960.aspx

TIA, Jack

Hi Jack,

I found these threads that I think are related to your scenario. Please read them at let us know how are you doing with that. http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11809 http://llblgen.com/TinyForum/Messages.aspx?ThreadID=8232&StartAtMessage=0&#57646

I think LLBLGen will do its job very well (O/R Mapper, etc.). The hardest part would be parse the metadata and generate the forms. Maybe those threads will help you with that. Have you read about DynamicData and DynamicData and LLBLGen Pro anyway?