Define a column as nullable in System.Data.DataTable

Question

I need to define a System.Data.DataTable in C# VS2013; in one column, it may be int or null.

But I got:

DataSet does not support System.Nullable<>.

For the definition:

public DataTable myDataTable
myDataTable = new DataTable("myName");
myDataTable.Columns.Add("ID", typeof(Int32)); 
myDataTable.Columns.Add("value1", typeof(Int32?)); // run time error 
myDataTable.Columns.Add("value2", typeof(Int32?));

Any ideas? Work around?

After making the column nullable,

DataColumn dc = myDataTable.Columns.Add("value1", typeof(Int32));
dc.AllowDBNull = true;

When I queried it, I got

Sequence contains no elements.

Please see the UPDATE.

UPDATE

int? result = (from r in myDataTable.AsEnumerable()
               where r.Field<Int32>("ID") == givenID
                   && r.Field<Int32?>("value1") == givenValue1
               select r.Field<Int32>("value2")).First();

You already got an answer explaining how to solve this, but I'd like to explain *why* it's like this: Nullable data types were added in .NET Framework 2.0, but DataTable has been with us since (at least) .NET Framework 1.1. — Heinzi, Jun 10 '22 at 07:57

score 19 · Answer 1 · answered Dec 03 '16 at 23:43

19

It is a property of the DataColumn

public DataTable myDataTable
myDataTable = new DataTable("myName");
myDataTable.Columns.Add("ID", typeof(Int32)); 
DataColumn dc = myDataTable.Columns.Add("value1", typeof(Int32)); 
dc.AllowDBNull = true;

MSDN about AllowDBNull

answered Dec 03 '16 at 23:43

Steve

213,761
22
232
286

Thanks, it works, But when I queried it, I got "Sequence contains no elements". Please see the UPDATE. – Lily Dec 04 '16 at 00:02
5

This is a different problem. If the result of the query has no match then you cannot use First because exactly 'sequence contains no elements'. Instead use FirstOrDefault – Steve Dec 04 '16 at 00:06

score 8 · Answer 2 · answered Sep 25 '19 at 06:58

TRY

  public DataTable myDataTable
  myDataTable = new DataTable("myName");
  myDataTable.Columns.Add("ID", typeof(Int32)); 
  myDataTable.Columns.Add(new DataColumn { ColumnName = "VALUE1", DataType = typeof(int), AllowDBNull = true });
  myDataTable.Columns.Add(new DataColumn { ColumnName = "VALUE2", DataType = typeof(int), AllowDBNull = true });

this will make value columns nullable

and at time of insert

DataRow dr = myDataTable.NewRow();
dr["VALUE1"] = object.value1==null? (object)DBNull.Value : object.value1;

This will do what is required

score 1 · Answer 3 · answered Jan 16 '22 at 13:52

1

or to make it even shorter one line:

table.Columns.Add(new DataColumn("ID", typeof(Int32)) { AllowDBNull = true });

and:

row["ID"] = (object) nullableId ?? DBNull.Value;

answered Jan 16 '22 at 13:52

Rom Haviv

128
8

Define a column as nullable in System.Data.DataTable

3 Answers3

Linked