Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Clear a DataRow Item (as opposed to deleting it)

5.00/5 (1 vote)
26 Jul 2011CPOL1 min read 35.3K  
One way to effectively zero the columns any DataRow object.
As many of you have seen for yourselves, a given project almost always dictates a need for some code that, while useful, exposes some of the more esoteric and bizarre requirements of the project in question. I'm working on such a project right now. One of our bizarre requirements is the need to add an "empty" record at the top of a dataset that was returned from a stored procedure. This is to enable our code to present a combobox a seemingly unselected item (I wuld have done it differently, but that's not what this tip is about). I supposed this is all well and good, and it's done on several pages throughout the web site project. My problem was with the way they're initializing the row. Here's an example:

C#
DataRow row = dataset.Tabels(0).NewRow();
row.ItemArray = new object[]{"", "", 0, "" };


Do you see the problem? Here's a hint - if the schema for the source table changes, this code might break. What happens if a column data type is changed, existing columns are deleted, or if new columns are added? Distaster is what happens, and all of a sudden we're carreening headlong down the Exception Highway.

To resolve the potential issue, I came up with an extension method that solves the problem. Essentially, the method iterates through the row's columns, determines each columns type, and sets a reasonable value to the column. If a default value is specified, that value is used instead. (In our code, a default value is rarely specified - another rookie error.)

So, here's the (extension) method:

C#
// Clears the data from the row, setting all of the column values to a 
// reasonable default value.
public static void Clear(this DataRow row)
{
    // for each column in the schema
    for (int i = 0; i < row.Table.Columns.Count; i++)
    {
        // get the column
        DataColumn column = row.Table.Columns[i];
        // if the column doesn't have a default value
        if (column.DefaultValue != null)
        {
            // Based on the data type of the column, set an appropriate 
            // default value. Since we're only dealing with intrinsic 
            // types, we can derive a kind of shortcut for the type name,
            // thus making our switch statemenbt a bit shorter.
            switch (column.DataType.Name.ToLower().Substring(0,3))
            {
                case "str": case "cha":
                    row[i] = "";
                    break;
                case "int": case "uin": case "sho": case "byt": 
                case "sby": case "dec": case "dou": case "sin":
                    row[i] = 0;
                    break;
                case "boo":
                    row[i] = false;
                    break;
                case "dat":
                    row[i] = new DateTime(0);
                    break;
                case "obj": default :
                    row[i] = DBNull.Value;
                    break;
            }
        }
        // otherwise, set the column to its default value
        else
        {
            row[i] = column.DefaultValue;
        }
    }
}

License

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