Validating string lengths based on related database columns

Question

At the one end of my web application I have a database table storing a load of pieces of text.

In the middle I have an API that separates my application tiers.

At the other end I have a user interface consisting of many TextBoxes (or input type=text form elements, if you prefer).

I need the maxlength properties of the TextBoxes to be the same as the maximum string lengths in the various related database table columns. I would also like to do string length validation on my API.

Of course I can achieve this manually, but does anyone know of a technique on any platform that can assist in setting up any part of this automatically?

In my scenario I am using ASP.NET 3.5 WebForms, LINQ to SQL and SQL Server 2005.

Answer 1

There's usually schema info that you can request from the database to find out the maximum length of a given column.

Take a look at SqlDataReader.GetSchemaTable(). It'll return the column size (and a lot of other data) for each column. Make ExecuteReader() was called with behavior parameter set to KeyInfo.

Another approach is to query the data dictionary in sql server directly. It's been a while but I think something in sys (sys.columns maybe) will have the info you're looking for.

Then you can update the textbox property.

Answer 2

I know you're asking for SQL Server, but in MYSQL, you can fetch your column from

SELECT * FROM information_schema.`COLUMNS` C;

There you can see a CHARACTER_MAXIMUM_LENGTH field that give you exactly what you want.

I'm sure you can find something like this for SQL Server.

Answer 3

In SQL Server 2005 (and probably 2000 & 2008, though I haven't tested), you can use the following query to find the length of a varchar field given the table name and column name:

select 
    t.name TableName,
    c.name ColumnName,
    c.max_length ColumnSize
from sys.columns c
inner join sys.tables t on (c.object_id = t.object_id)
where t.name = 'MyTableName'
    and c.name = 'MyFieldName'

The max_length field in sys.columns will give you what you need for text form input fields.

I realize this does not answer your question in terms of implementing this through your API, it should get you on your way.

Answer 4

At risk of not answering the question at all...

You seem to have a business rule that says "this particular field should contain a maximum of N characters."

I would argue that both the database column's size and the textbox maximum length are separate consequences of this business rule. Fixing one from the other confuses correlation with causation.

In addition, enforcing this business rule by examining the maximum length of the database column has other effects:

• you'll hit the database every time for what could otherwise be an in-memory validation operation;

• if your business rule changes slightly (eg: N goes from 100 to 80 characters) then you need a schema-level change;

• some databases working with variable-length character encoding like UTF8 only fuzzily define how many characters even fit in a given column;

• more broadly, you're coupling the business rule to an implementation artifact - what if you later decide to use an object database?

I'm not suggesting that you don't sensibly size your database columns (you should), only that you decouple the business rule from the database implementation. If you can get away with a simple fixed-length-check in the application code, I would suggest that.

Edit having read your question a little more carefully, for your text field maximum lengths you need N ahead of validation. Nonetheless, this is still a business rule - can you write a MaximumLength attribute for your field accessors and interrogate that?

Answer 5

I don't think there is any out of the box solution for this. However what you can easily do is to add a specially formatted comment in your MaxLength attribute of the textbox such as

<asp:TextBox runat="server" MaxLength="<%--<TABLENAME>.<COLUMNNAME>--%>" />

Then you can write another program or VS macro to go through your aspx files and set the MaxLength to the length of that column so that it becomes.

<asp:TextBox runat="server" MaxLength="20<%--<TABLENAME>.<COLUMNNAME>--%>" />

You can run this program when you add new fields or when you change column sizes etc.