Good pattern or technique for webforms with 100s of fields

Question

At my job, I have to implement web forms for loan applications with sometimes up to a hundred different input fields, and then save the application into the database for later retrieval.

The person whom I replaced created a sql table with 100s of columns where each row represents a loan application and there is a column for every field.

The problem with this is that I find myself having to type out the 100 fields a bunch of times, getting data from form, saving to database, retrieving from database, writing to output webform.

And then whenever there is a change to the application, I have to make the change in quite a few places.

So it can not only be cumbersome but error prone.

Is there a good design pattern that handles this?

Answer 1

Perhaps ASP.NET Dynamic Data would work for you. It is described as "a framework that enables you to quickly build a functional data-driven application"

Answer 2

See if you can come up with a better table design. 100 Cols is just too much IMO. If not you could use dynamic data. Also look for a better UI pattern or break it into more than 1 pages, looking at 100 fields to fill in could be over whelming for the users.

Answer 3

Code generation is the best bet. MyGeneration and CodeSmith are two very good tools.

I've had very similar projects and I used both generation tools and prefer CodeSmith.

Answer 4

One thing that you can do is programmatically take the column names and put them on a file in the format you need. That will be to solve your immediate pain. Below is a piece of c# code that will get the column name from a oledbreader object.

internal List<string> GetFieldList(string sTableName)
    {
        tableName = sTableName;
        BuildQuery("*");
        command = SetupConnection();

        List<string> FieldList = new List<string>();
        OleDbDataReader reader = command.ExecuteReader();
        for (int i = 0; i < reader.FieldCount; i++)
            FieldList.Add(reader.GetName(i).ToString());
        return FieldList;
    }

To solve the situation in the long run, you probably want to do store procedures or functions with the fields as parameteres. you still have to put the values in each of the parameters, but is going to easy the management of each

he problem with this is that I find myself having to type out the 100 fields a bunch of times, getting data from form, saving to database, retrieving from database, writing to output webform.

hope it helps!!!

Answer 5

Ouch.

Is LINQ available to you? What version of .NET are we talking about here?

BTW, if it isn't obvious already, from a GUI perspective I suggest you hide most of these fields from the end user and reveal them in sections as the user progresses, which has the advantage that you can avoid showing what doesn't need to be and validate as you go.

And with some sort of progress indicator so they know there's an end in site (ha ha).

Answer 6

I've worked with these loan applications before and I'll tell you straight out that your data model is broken. Are you gonna make a new row in the BigTable when a borrower comes in for a refi? What if the deal falls through but they want to try again for a different house? If you just edit the data in the row for that application then you're losing valuable metrics on your % of closed deals.

First, fix your data model. Then, embrace LINQ if at all possible.

With respect to the UI, I'd either do it as a wizard (which is pretty close to how these apps are filled out) or build it as one big form that the user scrolls through and adds stuff to (use AJAX in each section so that the user can save as they go along).

You also need to consider off the shelf solutions for this stuff. Don't reinvent the wheel here; these loan apps don't change often enough to throw a full time developer at them.

Edit: Sorry I was assuming that this was for residential mortgages. I don't know if that's true, but I still think your data model is broken. If you have the ability to change it you should.

Answer 7

I've had issues like this before, and know there may be valid reasons for having so many columns. It's rare, but it can happen.

What you might want to do, is create a partial class in it's own file which contains all the data field code. This might be fields, properties, and possibly method parameters, and/or just your CRUD functionality. And generate that partial class, this will help ease your future maintanence.

Good Luck.

Answer 8

Design pattern? But of course: The Adaptive Object-Model.

Answer 9

100's of fields in a form is too many. Try to aim for max 20 per page.

If you can, try to isolate each part of this form into a separate page, and even better, into a wizard of some kind.

e.g. (example steps)

Step 1: Client Profile (name, age, SSN, etc.)

Step 2: Loan Data (Fixed, Variable, Amount, Terms, Special Deals...)

Step 3: Credit Check?, Pre-Approval?

Step 4: Variations/Calculations...

If you organize the data in structured groups, you should be able to make each portion work independently (e.g. you can input basic loan data, and get some preliminary numbers without needing to enter client data (yet).

More importantly, if the user enters a value in one of the 100 fields that causes the backend to not store (e.g. value out of range) what happens? does the user lose all the data they entered? - for usability make sure that nothing is lost when the user attempts to save/move to the next step.