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:
public static int GetLengthLimit(object obj, string field)
{
int dblenint = 0;
Type type = obj.GetType();
PropertyInfo prop = type.GetProperty(field);
object[] info = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
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;
}
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