Fill-Method changing DBNULL-values to other values

[Christopher]

Hi,

i am writing a small interface to import some data from one database to
an other database. unfortunately is the data in the source not very
"nice". you have boolean-columns and those boolean-columns have as a
default value DBNULL.
it wouldn't be a problem if there was one boolean field, i would have a
if (table.iscolumnNull()) but i have about 70 boolean fields and only
ONE is true all others are false. that means i have many of those ifs
already

if (table.column1)
{
}
else if (table.column2)

the problem is, i don't want to have a structure like this:

if (table.column1IsNull())
{
if (table.column1){}
}
else if (table.column2IsNull())
....

I hope you can see where I am going.
So I think there must be a possibility to change the DBNULL-values
within the fill-method of the table-adapter.
something like
select
case
when column1=null then column1=false
else column1
endcase
from foreigntable

i have tried to write such a statement but it didn't work out. If
somebody had some hint, that'll be great!

Greetings
Chris

 

[Andrea Caldarone]

Hi,

i am writing a small interface to import some data from one database
to an other database. unfortunately is the data in the source not very
"nice". you have boolean-columns and those boolean-columns have as a
default value DBNULL.
it wouldn't be a problem if there was one boolean field, i would have
a if (table.iscolumnNull()) but i have about 70 boolean fields and
only ONE is true all others are false. that means i have many of
those ifs already

if (table.column1)
{
}
else if (table.column2)

the problem is, i don't want to have a structure like this:

if (table.column1IsNull())
{
if (table.column1){}
}
else if (table.column2IsNull())
....

I hope you can see where I am going.
So I think there must be a possibility to change the DBNULL-values
within the fill-method of the table-adapter.
something like
select
case
when column1=null then column1=false
else column1
endcase
from foreigntable

i have tried to write such a statement but it didn't work out. If
somebody had some hint, that'll be great!

Greetings
Chris

After the call to the .Fill method, you have a DataTable filled, at this
poit you can loop into the .columns collection and when you find a boolean
column (you have to evaluate its DataType) set its DefaultValue to False

 

[Christopher]

Thank you for your advice. But I have found another solution where I
don't have to change the fill-method of the TableAdapter.
I have changed the type of the column within the typed dataset from
boolean to string and whenever there is a NULL-Value the Columns value
is changed from NULL to "False".

For me it is the easiest solution because I have to change the DataSet
respectively the DataTable and not my Program.

But if someone has a better solution feel free to write it down ;-)

Thanks
Christopher

 

[Cor Ligthert [MVP]]

Christopher,

Are you not able to loop through your table in the simple way?

for (int i = 0;Table.Column.Count > i;i++)
{
if do your test;
if true break;
}

Cor

 

[Christopher]

Hi Cor,

I am able to loop through my datatable. actually i am looping through
the database using
foreach (myDataSet.myRow row in mYDataSet.mytable)
{

}

the reason i changed the dataset is that my expression is already
complicated enough and when the column is boolean the dataset throws an
exception and do not want to handle 70 exceptions. so i changed the
column to string and whenever there is a dbnull-value it is changed to
false.
as i said i have about 70 columns of which only one is true all others
are false (or DBNULL). i have many
if (column1=='true') {} else if (column2=='true') ...
actually i changed the dataset because there will be a change in the
database - it will be moved from microsoft sqlserver to oracle and i
don't know if oracle supports boolean-values. when they moved the
database i have to change the dataset and not change the complete
application.