Should field length be made dynamic by fetching the length using a stored procedure?

Question

In a web application (C# + SQL Server), is it good practice for the maximum field length of the fields in the user interface to be determined by the fields fetched from database?

Is it a good approach? If not, please mention the disadvantages.

Answer 1

It sounds like overkill, unless you have a some very special reason to do so.

I assume, you would do this by getting the length from the database and then setting it dynamically on the HTML elements.

Disadvantages depend on usage and necessity:

1. For a high volume application with a lot of data and fields to display, this would mean many reads from the database and added processing just to render the UI.
2. It means there is a high dependency of the UI on the database, which sounds like regression given current practices and technologies that drive towards decoupling the UI from the database/backend
3. It is unnecessary in the presence of many methods of validation, from UI to middleware
4. You also wouldn't normally design a UI element to be the exact length as that of the database... What happens if the database field length is such that it messes up the UI? With that hardcoupling, you will have lost the ability and control of your UI.
5. Linked to point 4), it would be mean more work is required to make your UI more responsive and visible across devices without issues, since your database determines form field lengths.
6. Linked to point 5), it would mean if you were to change the field length in the database, then your UI is immediately impacted... or is at risk of being impacted. More work for maintenance. If the field length was increased to cater for a new requirement, in a different system using the same database, then you will have to fix the UI that will depend on those lengths... Or compromise it to cater for the other... or compromise both systems.

One cannot fully rely on the UI or the middleware or the database to perform validation.

It would be nice if you stated your use case.

Some interesting links:

1. How to design a database for User Defined Fields
2. How to design a dynamic form with fields that are taken from the database
3. Where do you perform your validation?
4. Data input validation - Where? How much?

Answer 2

Two reasons you should not call a stored procedure for retrieving the maximum length of fields:

1. This will downgrade the user experience. If the user entered a string greater than the maximum length then he/she shouldn't wait for the response that he's/she's exceeded the length. It should be instant.

2. There shouldn't be any unnecessary database call if you want to improve the performance of the application.

Suggested Approach:

• You should rather put a JavaScript method /Some other front-end check for validation of the maximum length.

• If your users are smart enough to disable the JavaScript code then you should put a check in C# code just before doing the intended operation with the captured value.

Answer 3

I have developed a framework for my web applications where this is one of the functionality implemented.

To implement this feature, I have done the following (I will not include any extra feature of the framework that I developed and am using):

• Made an interface IDbMappable which contained a properties FieldName, TableName (TableName can be left blank and then TableName of the containing Page will be used. If FieldName is set then TableName must be set on either control level or page level)
• Made custom control class (inheriting existing controls and implementing the IDbMappable interface) [TextBox control can be used for anything (and while saving the data will be tried to be converted to the datatype in the database), CheckBox for a boolean field, DropDownList for a foreign key field, etc.]
• When the page was loaded, if TableName property is set on page, then all the controls, whose TableName property is not set, will fetch their respective restrictions from database; For controls where TableName property is set on controls, each control will fire a separate query to the database to fetch its restrictions. (Restrictions are like maximum length, if blank is allowed or not, possible set of values from drop down list, etc.) [I have a base class for each page; my pages do not directly inherit from System.Web.UI.Page class, instead they inherit from my page class that inherits from System.Web.UI.Page class]

I have also added reading description for keywords, like:

• [Email] for any field that should contain valid email IDs only (regular expression was auto generated for these fields)
• [Url] for valid URLs (only URLs)
• [Uri] for valid URIs
• [RegEx: expression] for specifying a regular expression. [Note: square brackets were mapped by their opening and closing, so the expression can contain square brackets without any problems].

Now, Pros and Cons of this approach

Pros

1. This approach eliminated a lot of trivial tasks (like setting MaxLength of TextBox, loading values in the DropDownList, placing RequiredFieldValidator, etc.)
2. This increased my productivity a lot
3. Code looks neat and clean (only the required logic is there on the page; OK and some more)
4. I am lot more free to change database design and layout.

Cons

1. If we put/populate objects/properties/control like MaxLength, RequiredFieldValidator, etc. during design time, then these values are compiled and runs faster; in this case these information is loaded from database hence one or many database round trips are done
2. New developers/other team members mostly become confused where the values are coming from or why their field values are overwritten (this can be prevented by implementing like if the property is already set, then do not populate it; but I overwrite the properties)
3. Somewhat rigid architecture and may not work for a different type of application (I use this framework in enterprise applications; for websites I use a totally different approach to reduce bloating (HTML))

Conclusion

Depending on the type of application to be developed, this architecture can be a boon or curse. In a high traffic application, this definitely is a curse as it will increase the processing requirement and network communication required at the server level. But for most of the enterprise applications this is definitely a boon.

I am using this framework of mine for nearly five years now. It is in its third iteration of improvements and a fourth version is planned.

Note: before someone comments, I had implemented caching of fetched information (but that is somewhat complex to discuss here)

Answer 4

A common approach is for your source code to define both the database sizes and the UI validation. For example, in Entity Framework, you can put size constraints on the C# objects themselves;

using System.ComponentModel.DataAnnotations;  

namespace MvcDA {
    [MetadataType(typeof(ProductMD))]
    public partial class Product {
        public class ProductMD {
            [StringLength(50),Required]
            public object Name { get; set; }
            [StringLength(15)]
            public object Color { get; set; }
            [Range(0, 9999)]
            public object Weight { get; set; }
          //  public object NoSuchProperty { get; set; }
        }
    }
}

This is enforced both in the database and in the user interface for very little effort.

If you are hand-rolling all your objects and queries, then you've opted out of a lot of free features -- that's OK, and there are good reasons to do it, but be aware that this is a solved problem if you take a more managed approach.