Hear me out. This is either an interesting idea or the Vicodin the dentist gave me yesterday is messing with me.



I am writing an app that fills in insurance policies using PDF forms. The forms are very structured in that Additional Insured - for example - may have 12 fields named ADDINSURED1 ( to 12) on the PDF form. There are perhaps 100 distinct fields like this on the 50 or so forms that need to be filled out, and some fields appear on more than one form. There are potentially, therefore, in the normalized tabular data world 100 child tables of the policy table. But the number of possible children is quite fixed ( by the PDF forms - which I do not in any way control ) and is always between 2 and 21.



All data entry is done on the policy form (and in the future options will exist for reading / translating data into the policy storage structure from other systems ). Then about 50 forms are all filled out, another 50 static PDFs added, all the forms sequenced a certain way and it is all joined into one big PDF. ( iTextsharper is the indispensible tool for all this and once you get the hang of it it is quite elegant and very fast )



Creating a data-entry interface for this is the challenge, and it is preferred that there be some other approach than 100 child tables.



I have chosen, so far, to use multiline text boxes for the data entry, and then parse the rows into the fields for that datum so that a textbox for Additional Insured could hold up to 18 lines and would fill the 18 text fields on the form.



This is working quite well and the client loves it.



The problem of course is that since the data is just string, dates, currency amount, logicals etc cannot be easily validated and interface gives no help.



But if I could store collections of however many items there were to enter and then easily bind to the collection items, it would be ideal



For example, a possible 5 dates in a collection would be stored in one sql column - a byte() {} I suppose ? - and then read out and bound to 5 controls on the form.



My question is has anyone done anything like that and are there any hints for an elegant and reasonably generic way to accomplish this collection binding ?



Ideas would be appreciated. This is a situation where there is enough of this kind of stuff that it would be worth figuring out, but I think on an instance basis it could come in handy in a lot of places.



Thoughts?

Charles,



You might look into UDTs in SQL Server 2008. You could actually implement a list directly in SQL Server, if I'm understanding this.



Or maybe use the varbinary(max) and then use the serialize feature of the BO, setting a type. I.e. the BO would then serialize the object and store it in the db. In this case you'd need to create the appropriate classes for anything you wanted to do this with.



A couple of ideas.

Well, as you know I'm using 2008. BTW I got the multicheckbox usercontrol working and have handled as you suggested so now a char(8) storing FFTFTTTT will be represented by 8 checkboxes in a column with changes saved back. I made the control with 10 checkboxes and as long as I handled the differences i can use the same control for 2-10.



But that got me thinking in this particular app it would make sense in some of these case where there are 6 columns of possibly 18 values ( or possibly none ) I could have a grid to present the data and then store the contents in 6 fields of the sql table. Just need to come up with an elegant way to read them out and bind them.



I do remember that 2008 has the ablility to store types like collections so I may look into that further.



I think it would make more sense than having 50 child tables of no more than 2- 21 records per policy just so I could present a good interface.

We handle this using the serialized objects method Greg mentioned. The two places that we run into the need for this kind of storage in our medical application are with insurance claims and patient statements.

Insurance claims sound similar to your needs. There are a large number of data elements that correpsond to static locations on a "standardized" paper form, or to fields within a corresponding electronic format. Rather than make a complex database structure to try and mimic those paper forms or electronic format, we created a "Claim" object that organizes the data in a way that makes sense to us.  We populate and pull from that object directly, and just serialize the entire object to a byte array in SQL.  That allows claims to be persisted in the database, and we are just dealing with our object when we write the logic to output to a claims form or electronic file.

Hi Dustin -



Yes, this is exactly the situation and just what i have in mind. The part i am fuzzy is the actual implementation. Sooo



I have a policies table in SQL. I will hold certain immutable values in fields there ( policy number, PK, FK to Carrier table etc ) and then I would like to have other data stored that has a variable number of elements ( additional Insured, Included Hazards etc )



Should I have a seperate varbinary(max) (?) column for each datum or are you suggesting first having an object which has properties which are collections and then to serialize that one object to a varbinary(max) field ? (that actually sounds cleaner)



IOW am I going to have a List(of Object) called BigObject and then bigobject.additionalinsured , bigobject.IncludedHazards (each of those being a LIst(of STring) and some List(of system.datetime) etc ) represented as one SQL column bigobject varbinary(max) or with I have a SQL Column for each list and serialize them one at a time?





1. At what point in the process do I deserialize the object ? When I do so, do I want to make the deserialized object a property of my BO?



2. How do I bind to the properties of the object? Supposed, for example, one of the properties is LOCATIONs which is a List(of STring).



I was thinking this might actually be an appropriate place to use grids.



Many of the columns are related on the forms, so a 5 column grid of n rows could be loaded from five of the properties of my object, modified or added to on save and written back to the 5 List(of T) properties and then the object serialized back to the strongly typed property of the bizobj that holds the big object. Is that the right idea?



For that matter, would it make sense to store the related columns as a datatable, making that a property of the BigObject so the grid could be just load from and written to that???



I have no experience with grids or loading them from lists etc but I doubt it is that hard to get data into and out of them, even if I have to use the lists to populate a datatable first. That part doesn't seem difficult (though perhaps tedious ) But I'm pretty sure a datatable as a source would be easier.



As far as performance there are never more than 21 rows in any of these lists and more commonly 3 - 10



Any suggestions about how to implement this approach much appreciated. The part I am the fuzziest on is in the policy BO - I assume there is just one property for the variable data but then as soon as the BO gets data the object gets deserialized to an object that hangs on the PolicyBO ? I guess in that case I wouldn't be really binding to those properties but just using them to load interface containers that would then write back to those properties before the whole thing got reserialized on a save ???



( this really sounds very cool )



Suggestions much appreciated and I think fleshing out this thread would be quite useful for a lot of SF users.

Sorry for bumping the wrong question in Winforms. This was the one I was trying to get more info on - i.e. broad and shallow child collections stored as an object a the bound to interface for data entry.



Right now focusing on storing datatables that would be displayed and edited in Gridviews.



Would like to make all those datatables in a list(of Datatable) and then store that in a SQL column.



Suggestions? (please see the discussion in this thread and my bump in Binding to a field in Winforms. )



TIA