Preventing empty strings becoming NULL in asp.net dynamic-data

Question

I have a standard ASP.NET 4 Dynamic Data site (using Linq to SQL).

In my SQL-Server database I have hundreds of varchar fields that are set to NOT NULL, but have a default value of an empty string. They should never be NULL, but they may have an empty string as content.

When editing these fields on the dynamic data site, the internal logic sees the field as NOT NULL and somewhere between TextBox1.Text (which is an empty string) and calling the UPDATE sql it sets the value to NULL and they update fails.

I can see the System.Web.DynamicData.MetaColumn has a property 'ConvertEmptyStringToNull' but is is read-only.

What can I do to prevent the empty string becoming NULL without having to change properties for all the hundreds of fields?

score 0 · Answer 1 · answered May 16 '12 at 18:04

Since you wish to set the default value (empty String) in the database and not in the application consider using metadata on the columns in question to default to the database's initialization:

[Column(IsDbGenerated = true, UpdateCheck = UpdateCheck.Never, AutoSync=AutoSync.Never)]
public object MyColumn { get; set; }

score 0 · Answer 2 · answered Feb 15 '12 at 11:23

0

Do you have a single procedure for calling your database?
If so you can check the fields values here, or just append a blank string to each value.

answered Feb 15 '12 at 11:23

CompanyDroneFromSector7G

4,291
13
54
97

The Dynamic-Site uses its own generated SQL, so I don't have any control over that, or do I? – Peter Hahndorf Feb 15 '12 at 11:26
Ah sorry - LINQ2SQL - I can't help there! I should read the question more carefully :( – CompanyDroneFromSector7G Feb 15 '12 at 11:32

score 0 · Accepted Answer · answered Feb 17 '12 at 20:47

In FieldTemplates/Text_Edit.ascx.cs change the behavior for strings, first remove the validators because they prevent the form from being submitted:

protected void Page_Load(object sender, EventArgs e) {
    TextBox1.MaxLength = Column.MaxLength;
    if (Column.MaxLength < 20)
        TextBox1.Columns = Column.MaxLength;
    TextBox1.ToolTip = Column.Description;

    if (Column.IsString)
    {
        this.Controls.Remove(RequiredFieldValidator1);
        this.Controls.Remove(RegularExpressionValidator1);
        this.Controls.Remove(DynamicValidator1);
    }
    else
    {
        SetUpValidator(RequiredFieldValidator1);
        SetUpValidator(RegularExpressionValidator1);
        SetUpValidator(DynamicValidator1);
    }
}

The piece of code that converts the empty string to NULL is somewhere in the ConvertEditedValue method, so skip that for strings:

protected override void ExtractValues(IOrderedDictionary dictionary) 
{
    if (Column.IsString)
    {
        dictionary[Column.Name] = TextBox1.Text;
    }
    else
    {
        dictionary[Column.Name] = ConvertEditedValue(TextBox1.Text);
    }
}

Now you can update tables with empty strings, the problem of course is now you can set empty strings for all varchar fields not only the ones with a default value.

I don't see anywhere in the Linq to SQL or EF datamodels where I can find out about my SQL-Server default value. There is a property DefaultValue but it is always NULL.

Preventing empty strings becoming NULL in asp.net dynamic-data

3 Answers3