Validate that data fields are not too big when saving to a database?

Question

I have an application which runs as a windows service and reads a text data file, then maps the file contents to a database. The app uses a batch commit and periodically the data is larger than the database fields.

What is the best way to validate that the data fields are not too big before trying to save them to a database?

Do you want to truncate the data so that it fits, ignore those rows, ignore the whole file, or what? — Servy, Sep 07 '12 at 20:36
Read size definitions of your fields from DB at your application's start up like here http://forums.asp.net/t/1132236.aspx/1 or here http://stackoverflow.com/questions/5705316/how-to-get-the-size-of-a-varcharn-field-in-one-sql-statement — L.B, Sep 07 '12 at 20:38

parapura rajkumar · Accepted Answer · 2012-09-07T21:02:05.460

If you are willing to use System.ComponentModel.DataAnnotations you can do something like this.

public class Customer
{
    [StringLength(5)]
    public string Name { get; set; }

    [StringLength(20)]
    public string Phone { get; set; }

    [StringLength(30)]
    public string Email { get; set; }

    [StringLength(30)]
    public string Address { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        Customer c = new Customer 
        {
            Name = "FooBarX",
            Phone = "555-5555-33 ext 234",
            Email = "foobar@foobar.com",
            Address = "1334 foobar ave, foobar CA"
        };

        var ctx = new ValidationContext(c, null, null);
        Validator.ValidateObject(c, ctx,true);

        Console.Read();
    }
}

The Validator.ValidateObject will throw an exception in this case because the Name field is too big which is enforced by the StringLength attribute.

You can also use the Validator.TryValidateObject which will return you a list of errors instead of throwing an exception.

The validation framework is quite powerful. You can use regular expression validation for strings. Range validation for number fields and even custom validation.

There's also the RangeAttribute for validating numeric properties — Tevin, Sep 07 '12 at 23:02

score 0 · Answer 2 · answered Sep 07 '12 at 20:42

To validate you could use a datatable to read your database fields max length.

(VB.NET code)

                DataTable[] myDataTable; 
                oleDbDataAdapter1.Fill(dataSet11); 
                myDataTable = oleDbDataAdapter1.FillSchema(dataSet11, System.Data.SchemaType.Source); 
                TextBox1.Text = myDataTable[0].Columns[0].MaxLength.ToString()

maybe this link will help...

http://forums.asp.net/t/306971.aspx/1

Validate that data fields are not too big when saving to a database?

2 Answers2