How to programmatically set NullValue DataColumn property of ADO.Net DataTable
I have many new wizard-created DataTables in my project that I need to extract data from. These tables have many string columns with dbnull values that I want to extract as empty strings .
So I went and changed the property of NullValue
each DataColumn
to DataType
of System.String
from (Throw exception)
to (Empty)
like this:
I soon got tired of repetitive work, so I tried to install NullValue
programmatically in the data layer of my application.
I, however, could not even find this property. I even decompiled the code System.Data.DataColumn
and the property NullValue
doesn't seem to exist. NullValue
may be some kind of magical feature Microsoft.VSDesigner.Data.Design.DataColumnEditor
, but this is nothing more than mere suspicion at the moment.
How can I programmatically achieve the same effect as if I set NullValue
in (Empty)
in the property editor)?
source to share
I know this is old, but the reason this is not possible is because the property NullValue
defines the code that Visual Studio creates for that column. If selected Throw Exception
, the code explicitly checks for existence DbNull
and throws an exception if this is what was returned from the database. If the option is selected Empty
, the generated code instead returns an empty string when DbNull
. This is generated and therefore compiled code, so you cannot programmatically change this behavior at runtime.
source to share
Yes. The DefaultValue DataColumn property won't work because you already have several DataTables prepared. Here is one possible solution => You can put all tables in one DataSet (like ds) and 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";
}
}
source to share
For clarity, NullValue is not a property of the DataColumn class. This is a handy TableAdapter code generator setting that can be set for each column. In practice, this means that setting this "property" controls how the body of the column property is generated (Public Property () as). If, for example, NullValue is set to (Empty), then the conditional code is added to the column property code. check for null and return empty string. If NullValue is left at (Throw Exception),then this condition is omitted in the property definition. So there is really no programmatic way to set this property, since you cannot change the table adapter code at runtime. However, you can create a new property (with a different name such as MyColumnName_Safe) in a partial class for your table adapter that has the desired behavior. Just make sure you are calling the correct property instead of the auto generated one in your consumption code
source to share