Smart or stupid? Tying textbox length to database column length

[Dan Manes]

Wondering what other people do about this issue...

You're writing a web app in asp.net that requires user input. Data will
be stored in SQL Server Express database. You want to make sure data
does not exceed maximum length for database column.

For example, "User Name" column in database is set to varchar(30), so
you would like to also set your textbox to a MaxLength of 30. To guard
against hackers, etc., you also want to set up a validator that checks
on submit to make sure User Name does not exceed 30 characters.

So, what is the "best practice" for this situation?

Seems like most people would just hard code the number 30 into their
..aspx page. This seems like a programming no-no, though. What if you
later decide to allow a User Name to be 50 characters long? Now you
have to change all the 30's to 50's. You might forget, you might miss
something, you might make a mistake, etc.

Another way to go: Write a stored procedure in SQL that returns the
max_length of a column given the name of the database, table, and
column (using the system view, "INFORMATION_SCHEMA.COLUMNS"). Then, you
place an algorithm in your code-behind that queries the database for
this information and automatically sets parameters for the textbox and
validation. Some problems with this: (1) takes some work to set up and
debug, (2) increases burden on database server.

Maybe there are other ways to do this I haven't even thought of. What
do you do?

Thanks,

-Dan

 

[David Browne]

Wondering what other people do about this issue...

You're writing a web app in asp.net that requires user input. Data will
be stored in SQL Server Express database. You want to make sure data
does not exceed maximum length for database column.

For example, "User Name" column in database is set to varchar(30), so
you would like to also set your textbox to a MaxLength of 30. To guard
against hackers, etc., you also want to set up a validator that checks
on submit to make sure User Name does not exceed 30 characters.

So, what is the "best practice" for this situation?

Seems like most people would just hard code the number 30 into their
.aspx page. This seems like a programming no-no, though. What if you
later decide to allow a User Name to be 50 characters long? Now you
have to change all the 30's to 50's. You might forget, you might miss
something, you might make a mistake, etc.

No that's still the best practice. Look at it this way: your relational
design is like source code, and changes to source code often require you to
propagate a change throught the applciation. There are many mechanisms
available to help you manage this process. For instance, the typed
DataSet's in .NET are easy to generate and hold a ton of useful metadata.
Alternatively you could add an attribute to a business entity indicating the
maximum length for strings, precision and scale for decimals etc. Point is,
this information should be "hard coded" in your application, but not
necessarilly in the definition of a textbox. It should be part of the
metadata that your front-end can query. You should definitely automate the
the generation of the metadata somehow, however, to streamline the process
for schema changes.

Another way to go: Write a stored procedure in SQL that returns the
max_length of a column given the name of the database, table, and
column (using the system view, "INFORMATION_SCHEMA.COLUMNS"). Then, you
place an algorithm in your code-behind that queries the database for
this information and automatically sets parameters for the textbox and
validation. Some problems with this: (1) takes some work to set up and
debug, (2) increases burden on database server.

That's OK, and caching could easilly eliminate teh performance impact, but I
don't really like doing this a run time. It really seems like something
that should require recompiling your applciation.

David