Click here to Skip to main content
Click here to Skip to main content

Using the LINQ ColumnAttribute to Get Field Lengths from your Database

By , 30 Jun 2008
Rate this:
Please Sign up or sign in to vote.

Introduction

With LINQ, it's now really easy to design your database in an interactive tool like SQL Server Management Studio, drag your tables into a DBML in Visual Studio and then get to work on all the classes and relationships that have been created for you.

This works great and ensures that there is 'one fact once place' concerning how data is persisted - you don't need to maintain a data layer AND a database and struggle to keep them in sync. But when it comes to metadata about the columns in your database, up to now, you've had to maintain that information in two (or more) places. The length of a text field in your UI for example should be limited to the length of the column that will store it in the database.

Today, you probably have the length defined in your database and you have the length defined in your UI. You might also have the length defined in some code that truncates data when storing it. Change it in the database and you have to go change it everywhere else.

This brief article shows you how to get column metadata from the properties on LINQ objects allowing you to have a single master (the database) that defines the allowed length of every property. Now your UI, your business layer, your data layer and your database can all be in synch all the time.

Notes

Auto-truncating data is rarely the right thing to do; normally you would only use the first of the two methods presented here to get the length limit and then pass it up through your business layer to your UI so the UI can validate the user's input. Auto-truncate might be used during some batch input process where there is, say, a field that is OK to truncate either with or without a warning to the user, like, say, a comments field.

Note also that this article isn't prescribing any particular system design, it's meant as an illustration as to how to get to the column metadata; it's up to you to decide how to use it. In an advanced, distributed system where the UI isn't talking directly to LINQ objects, this code might find use in the hands of your testers who can automate the generation of max-length and max-length+1 inputs to ensure that max-length data can pass through all layers of the system and that max-length+1 data is properly rejected in your validation code and in your business layer.

Using the Code

Add these two static methods to your Utilities assembly:

/// <span class="code-SummaryComment"><summary></span>
/// Gets the length limit for a given field on a LINQ object ... or zero if not known
/// <span class="code-SummaryComment"></summary></span>
/// <span class="code-SummaryComment"><remarks></span>
/// You can use the results from this method to dynamically 
/// set the allowed length of an INPUT on your web page to
/// exactly the same length as the length of the database column.  
/// Change the database and the UI changes just by
/// updating your DBML and recompiling.
/// <span class="code-SummaryComment"></remarks></span>
public static int GetLengthLimit(object obj, string field)
{
    int dblenint = 0;   // default value = we can't determine the length

    Type type = obj.GetType();
    PropertyInfo prop = type.GetProperty(field);
    // Find the Linq 'Column' attribute
    // e.g. [Column(Storage="_FileName", DbType="NChar(256) NOT NULL", CanBeNull=false)]
    object[] info = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
    // Assume there is just one
    if (info.Length == 1)
    {
        ColumnAttribute ca = (ColumnAttribute)info[0];
        string dbtype = ca.DbType;

        if (dbtype.StartsWith("NChar") || dbtype.StartsWith("NVarChar"))
        {
            int index1 = dbtype.IndexOf("(");
            int index2 = dbtype.IndexOf(")");
            string dblen = dbtype.Substring(index1 + 1, index2 - index1 - 1);
            int.TryParse(dblen, out dblenint);
        }
    }
    return dblenint;
}

/// <span class="code-SummaryComment"><summary></span>
/// If you don't care about truncating data that you are setting on a LINQ object, 
/// use something like this ...
/// <span class="code-SummaryComment"></summary></span>
public static void SetAutoTruncate(object obj, string field, string value)
{
    int len = GetLengthLimit(obj, field);
    if (len == 0) throw new ApplicationException("Field '" + field + 
            "'does not have length metadata");

    Type type = obj.GetType();
    PropertyInfo prop = type.GetProperty(field);
    if (value.Length > len)
    {
        prop.SetValue(obj, value.Substring(0, len), null);
    }
    else
        prop.SetValue(obj, value, null);
} 

Using them is easy. Suppose you have an instance 'customer' of LINQ type called 'Customer' and you want to get the length of the 'Name' field:

int len = GetLengthLimit (customer, "Name"); 

You would probably implement this at the lowest level in your solution and then provide methods to pass the length metadata up through your business logic to your UI. LINQ's partial classes might be the right place to implement this. You might, for example, add an int NameLength property to complement your Name property.

Or suppose you don't care about truncation (a very rare and usually ill-advised approach) and you want to store a value in the database in a guaranteed to succeed fashion (with no annoying exceptions about data being truncated):

SetAutoTruncate (song, "Comments", "Really long comments about the song 
  that someone else put in to the song metadata but which you really don't care about");

Points of Interest

It's trivial to add a cache allowing you to go from <Type + Field Name> to <length> without having to reflect on the Type every single time, but as always, optimization like that is nearly always best left until you need it.

Don't forget to include the appropriate using statements:

using System.Reflection;
using System.Data.Linq;
using System.Data.Linq.Mapping;

History

  • 30th June, 2008: First version
  • 1st July, 2008: Second version incorporating initial feedback

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

HightechRider

United States United States
I have been writing code and managing teams of developers for more years than I care to remember.

Comments and Discussions

 
QuestionPropertyInfo prop = type.GetProperty(field); always returns null Pinmemberthabomoj11-Sep-12 3:36 
QuestionHow to show Db Type for all columns in a table + VS Type + Col Name all together [modified] PinmemberGuttorm Haaversen7-Aug-11 11:28 
QuestionDon't we also want to check for char and nchar? PinmemberMark Kamoski23-Jul-09 4:36 
QuestionWhy use TryParse? PinmemberMark Kamoski23-Jul-09 4:33 
GeneralNeed to using System.Data.Linq.Mapping; PinmemberMark Kamoski23-Jul-09 4:15 
HTR --
 
Great article.
 
Note that one needs to have...
 
using System.Data.Linq.Mapping;
 
...in the code.
 
Thank you.
 
-- Mark Kamoski
GeneralVB code to do the same thing! Pinmemberoughtsix30-Sep-08 14:24 
GeneralValidation handling Pinmemberexistenz_1-Jul-08 2:46 
GeneralRe: Validation handling PinmemberHightechRider1-Jul-08 4:34 
Generalif (value.Length &gt; len) check len for 0 PinmemberSteve Hansen1-Jul-08 1:15 
GeneralRe: if (value.Length &gt; len) check len for 0 [modified] PinmemberHightechRider1-Jul-08 4:36 
GeneralInteresting, but... PinmvpPete O'Hanlon30-Jun-08 22:00 
GeneralRe: Interesting, but... PinmemberSeishin#1-Jul-08 0:09 
GeneralRe: Interesting, but... PinmemberHightechRider1-Jul-08 4:41 
GeneralRe: Interesting, but... PinmvpPete O'Hanlon1-Jul-08 9:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 1 Jul 2008
Article Copyright 2008 by HightechRider
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid