Column does not allow DBNull.Value and Remove does not help. Why?

Question

I am getting the error:

Column 'Code' does not allow DBNull.Value

Here is my code:

public async Task UpdateDBWithXML(XmlReader reader, string hashKey, string hash)
        {
            var table = new DataTable();
            table.Columns.Add("Code");
            table.Columns.Add("ShortName");
            table.Columns.Add("Name");
            table.Columns.Add("LegalAddress");
            table.Columns.Add("Status");

            using (var transaction = this.Context.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
            using (var sqlBulk = new SqlBulkCopy((SqlConnection)this.Connection, SqlBulkCopyOptions.Default, transaction.UnderlyingTransaction as SqlTransaction))
            {
                this.Context.Database.ExecuteSqlCommand("DELETE FROM [dbo].[LegalContractors]");

                sqlBulk.DestinationTableName = "LegalContractors";

                string lastElement = null;
                DataRow lastRow = null;
                while (reader.Read())
                {
                    switch (reader.NodeType)
                    {
                        case XmlNodeType.Element:
                            if(reader.Name == "RECORD")
                            {
                                if (table.Rows.Count > 0 && lastRow.IsNull("Code"))
                                { 
                                    table.Rows.Remove(lastRow); 
                                }
                                if (table.Rows.Count > 10000)
                                {
                                    await sqlBulk.WriteToServerAsync(table);
                                    table.Rows.Clear();
                                }

                                lastRow = table.Rows.Add();
                            }
                            lastElement = reader.Name;
                            break;
                        case XmlNodeType.Text:
                            ProcessList(lastElement, reader.Value, lastRow);
                            break;
                    }
                }

                if (table.Rows.Count > 0 && lastRow.IsNull("Code"))
                {
                    table.Rows.Remove(lastRow);
                }
                if (table.Rows.Count > 0)
                {
                    await sqlBulk.WriteToServerAsync(table);
                    table.Rows.Clear();
                }

                //update hash here

                transaction.Commit();
            }
        }

        private void ProcessList(string lastElement, string value, DataRow row)
        {
            switch (lastElement)
            {
                case "NAME":
                    row["Name"] = value;
                    break;

                case "SHORT_NAME":
                    row["ShortName"] = value;
                    break;

                case "EDRPOU":
                    row["Code"] = value;
                    break;

                case "ADDRESS":
                    row["LegalAddress"] = value;
                    break;

                case "STAN":
                    row["Status"] = value;
                    break;
            }
        }

As you can see I am removing the DataRows with the empty 'Code'. Why may the error happen then? Should I use a different API for the deletion?

I tried the approach suggested in this question: use the lastRow["Code"] == DBNull.Value instead of the lastRow.IsNull("Code"). That did not help as well.

Here is the sample XML I am parsing:

<?xml version="1.0" encoding="windows-1251"?>
<DATA FORMAT_VERSION="1.0">
    <RECORD>
        <NAME>МІЖНАРОДНА ГРОМАДСЬКА ОРГАНІЗАЦІЯ МІЖНАРОДНА АКАДЕМІЯ БІОЕНЕРГОТЕХНОЛОГІЙ</NAME>
        <SHORT_NAME>МАБЕТ</SHORT_NAME>
        <EDRPOU>00011601</EDRPOU>
        <ADDRESS>01001, м.Київ, Шевченківський район, ВУЛИЦЯ ПРОРІЗНА, будинок 8, офіс 426</ADDRESS>
        <BOSS>ТКАЧЕНКО ВОЛОДИМИР АНДРІЙОВИЧ</BOSS>
        <KVED>94.12 Діяльність професійних громадських організацій</KVED>
        <STAN>зареєстровано</STAN>
        <FOUNDERS>
            <FOUNDER>Члени організації, розмір внеску до статутного фонду - 0.00 грн.</FOUNDER>
        </FOUNDERS>
    </RECORD>
    <RECORD>
        <NAME>фоо</NAME>
        <SHORT_NAME>фоо короткое имя</SHORT_NAME>
        <EDRPOU></EDRPOU>
        <ADDRESS>фоо адрес</ADDRESS>
        <BOSS>фоо бос</BOSS>
        <KVED>фоо квед</KVED>
        <STAN>фоо состояние</STAN>
        <FOUNDERS>
            <FOUNDER>фоо организатор</FOUNDER>
        </FOUNDERS>
    </RECORD>
</DATA>

Answer 1

You can allow DBNull to your column code by creating a new DataColumn:

DataTable table = new DataTable();
DataColumn code = new DataColumn("code");
code.AllowDBNull = true;
table.Columns.Add(code);

I hope that will help you out.

Answer 2

Your XML doesn't contain a column called code, neither does ProcessList() create it. Yet it is in your table definition so it would appear that it is always null.

I suspect your SQL Table structure has set that column Code does not allow nulls and so the SQL update is throwing the error.

You either need to populate the Code column with something, or set it to allow nulls or have a default value in the database.