C# Sql ADO.Net DataColumn error adding nullable Guid column

Question

I am trying to invoke a sql stored procedure from C#. I have the following code to create the DataColumn. But getting error while createing a DataTable when adding nullable Guid and DateTime type.

DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[]
        {
            new DataColumn(nameof(LearnerEntity.FirstName), typeof(string)),
            new DataColumn(nameof(LearnerEntity.DateOfBirth), typeof(DateTime?)),
            new DataColumn(nameof(LearnerEmployerEntity.SectorId), typeof(Guid?)),
            new DataColumn(nameof(LearnerEntity.EPortfolioId), typeof(int?))
        });
        dt.Rows.Add(
            learnerEntity.FirstName,
            learnerEntity.DateOfBirth ?? SqlDateTime.Null,
            learnerEntity.Employer?.SectorId ?? SqlGuid.Null.Value,
            learnerEntity.EPortfolioId ?? SqlInt32.Null.Value);

The error is:

DataSet does not support System.Nullable<>.

Can anyone help me what i am doing wrong here?

I am trying the following:

DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[]
        {
            new DataColumn(nameof(LearnerEntity.FirstName), typeof(string)),
            new DataColumn(nameof(LearnerEntity.DateOfBirth), Nullable.GetUnderlyingType(typeof(LearnerEmployerEntity).GetProperty("DateOfBirth").PropertyType)),
            new DataColumn(nameof(LearnerEmployerEntity.SectorId), Nullable.GetUnderlyingType(typeof(LearnerEmployerEntity).GetProperty("SectorId").PropertyType)),
            new DataColumn(nameof(LearnerEntity.EPortfolioId), Nullable.GetUnderlyingType(typeof(LearnerEmployerEntity).GetProperty("EPortfolioId").PropertyType))
        });
        dt.Rows.Add(
            learnerEntity.FirstName,
            learnerEntity.DateOfBirth ?? SqlDateTime.Null,
            learnerEntity.Employer?.SectorId ?? SqlGuid.Null.Value,
            learnerEntity.EPortfolioId ?? SqlInt32.Null.Value);

Thanks

Did you try googling the error? The problem is typeof(Guid?) and typeof(DateTime?). By default columns are already nullable. Use typeof(Guid), typeof(DateTime) and typeof(int) instead — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 13, 2021 at 13:15
But getting the error when the value is set. "Data is Null. This method or property cannot be called on Null values." — Mukil Deepthi
– Mukil Deepthi, Commented Apr 13, 2021 at 13:25
Use DBNull.Value or just null. A DataTable has .NET types like string, int, DateTime, Guid, not povider-specific types — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 13, 2021 at 13:28

Tim Schmelter · Accepted Answer · 2021-04-13 13:27:49Z

5

You have to initialize it with DBNull.Value. But since there is no implicit conversion between your nullable types and that you will produce ugly code. It's better to use the SetField extension method that supports nullables:

DataRow row = dt.Rows.Add();
row.SetField<string>(nameof(LearnerEntity.FirstName), LearnerEntity.FirstName);
row.SetField<DateTime?>(nameof(LearnerEntity.DateOfBirth), LearnerEntity.DateOfBirth);
row.SetField<Guid?>(nameof(LearnerEmployerEntity.SectorId), learnerEntity.Employer?.SectorId);
row.SetField<int?>(nameof(LearnerEntity.EPortfolioId), LearnerEntity.EPortfolioId);

answered Apr 13, 2021 at 13:27

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2021-04-13 13:14:57Z

2

Raw ADO.Net doesn't understand or use Nullable types (it pre-dates them). Instead, you must set (or not) the AllowDBNull property on each DataColumn.

I'd really love to see a hypothetical "ADO.Net v2" that does away with DBNull in favor of Nullable types (and also for DataRow and DataReader to implement the same IDataRecord interface, but that's another story), but I find it unlikely we'll ever see this.

answered Apr 13, 2021 at 13:14

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

1 Comment

Mukil Deepthi Over a year ago

When setting the AlloDBNull , i am getting "Data is Null. This method or property cannot be called on Null values." error

Collectives™ on Stack Overflow

C# Sql ADO.Net DataColumn error adding nullable Guid column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related