Click here to Skip to main content
15,881,882 members
Articles / Programming Languages / SQL
Tip/Trick

Clear a DataRow Item (as opposed to deleting it)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
26 Jul 2011CPOL1 min read 34.3K   1   2
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)


Written By
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
SuggestionAlternative Pin
sx20089-Dec-16 2:01
sx20089-Dec-16 2:01 
QuestionBetter Way? Pin
Member 1028611511-Oct-13 19:49
Member 1028611511-Oct-13 19:49 

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

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