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:

Modifying the NullValue property of DataColumn in VS 2010

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)?

No, it won't work. Setting `DefaultValue` will only affect newly inserted rows. In my case the rows are **already present in the db**, I just load them and do want to access thier values through a strongly-typed datarow. I just double checked by actually trying: *does not work...* — Peter Brennan, Jun 26 '15 at 11:07
the wizard generates a property where the getter returns the selected null value ... so you can't assign your null value just by setting a property — PrfctByDsgn, Jun 29 '15 at 06:39

score 1 · Answer 1 · answered Jun 30 '15 at 18:47

1

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";

          }

}

answered Jun 30 '15 at 18:47

kaushal sanadhya

107
1
17

Again: This is **not about default values**. It is about null values, that are already in the db. Default values (of any flavour) will not do any good, so your answer has no chance of working in my intended way. sry... – Peter Brennan Jul 06 '15 at 07:15
1

You cant do this just by setting a property – kaushal sanadhya Jul 21 '15 at 10:12
Can you provide some reasoning (I have my suspicions by now, but never got the time to check) in a separate answer, so I can accept it? – Peter Brennan Jul 21 '15 at 10:18
Of **why** it is not possible – Peter Brennan Jul 21 '15 at 10:43
sure..I'll post it soon – kaushal sanadhya Jul 21 '15 at 11:13

score 1 · Answer 2 · edited Apr 30 '19 at 22:23

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.

score 1 · Accepted Answer · answered Jul 18 '19 at 16:17

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

score 0 · Answer 4 · answered Jul 21 '15 at 11:11

0

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.

answered Jul 21 '15 at 11:11

kaushal sanadhya

107
1
17

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

4 Answers4