How to programmatically set NullValue property of DataColumn of ADO.Net DataTable

Question

I have many new wizard-created DataTables in my project, which I need to extract data from. There are many string columns with dbnull values in these tables, which I want to extract as empty strings.

So I went ahead and changed the NullValue property of each DataColumn with DataType of System.String from (Throw exception) to (Empty) like this:

I soon got tired of all the repetitive work, so I tried to set NullValue programmatically in the data layer of my application.

I was, however, unable to even find this property. I even decompiled the code of System.Data.DataColumn and the property NullValue did not seem to exist there. NullValue may be some magic feature of Microsoft.VSDesigner.Data.Design.DataColumnEditor, but that's nothing more than a mere suspicion at the moment.

How can I programmatically achieve the same effect as if I did set NullValue to (Empty) in the property editor)?

Answer 1

To be clear: NullValue is not a property of the DataColumn class. It's a convenience setting of the TableAdapter code generator that can be set for each column. In practice, this means that setting this 'property' controls how the body of the column's property (Public Property () as is generated. If, for example, NullValue is set to (Empty) then a conditional is added to the column property code to check for null and return an empty string. If NullValue is left to (Throw Exception) then this conditional is omitted from the property definition. As such, there really isn't a programmatic way to set this property, since you can't alter the tableadapter code at run time. However, you can create a new property (with a different name, such as MyColumnName_Safe) in a partial class for your TableAdapter that has the behavior you want. Just make sure to call the correct property instead of the autogenerated one in your consuming code

Answer 2

Visual Studio treats every datacolumn of any data table as separate entity that's why it doesn't provide any efficient way to deal with all at the same time. So we don't have programmatically accurate solution for it.

Answer 3

Yes. DefaultValue property of DataColumn is not gonna work because you already have a number of prepared DataTables. Here is one possible solution => You can place all the tables in one DataSet (lets say ds) first then use the following code:

for (int i = 0; i < ds.Tables.Count; i++)

{

       for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
          {
              if(ds.Tables[i].Columns[j].DataType==typeof(string))
              ds.Tables[i].Columns[j].DefaultValue = "empty string";

          }

}

Answer 4

I know this is old, but the reason why it's not possible is that the NullValue property determines the code that Visual Studio generates for that column. If Throw Exception is selected, the code explicitly checks for DbNull and throws an exception if that's what was returned from the database. If Empty is selected the generated code instead returns an empty string when DbNull is returned. This is generated and thus compiled code, so you can't change this behavior programmatically at runtime.