Use default column values when loading DataTable to avoid Nulls

I have an xsd DataSet Schema which allows me to define a the structure for a DataTable. For every column, I've set a default value so when constructing a new row, I get instantiated values ("" for strings and 0 for Ints) instead of DbNull

I'd like to preserve that when using either DataTable.Load or SqlDataAdapter.Fill as in either of the two following method calls

<!-- language: lang-vb -->

Dim table1 As New CodeSetSchemas.EntityByRoleDataTable()
Using reader As SqlDataReader = cmd.ExecuteReader()
    table1.Load(reader)
End Using

Dim table2 As New CodeSetSchemas.EntityByRoleDataTable()
Using adapter As New SqlDataAdapter(cmd)
    adapter.Fill(table2)
End Using

But when either of these methods create a new row, they actually write DbNull from the database into the row. If I set the AllowDBNull property on each column to False, I get the following exception:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Is there a way to preserve my default values for each row when filling a DataTable?

This doesn't specifically use load or fill, but you can do this pretty easily by just reading in the data reader and only setting values when the incoming value isn't null. By getting the SchemaTable from the SqlDataReader, you can still retain the simplicity of the load or fill commands by not having to write out every column name. By adding an Extension method with the name Load that takes in a default row parameter, you can retain the look and feel of the Load function while implementing the functionality yourself

Load Extension Method with Default Row Arg:

<!-- language: lang-vb -->

''' <summary>
''' Fills a <c>System.Data.DataTable</c> with values from a data source using the supplied <c>System.Data.SqlDataReader</c>.<br/>
''' Each row is new row is given the default values from the new row and incoming nulls are ignored
''' </summary>
''' <param name="dt">DataTable to fill</param>
''' <param name="reader">A <c>System.Data.SqlDataReader</c> that provides a result set.</param>
''' <param name="defaultRow">Default values for each DataRow</param>
''' <remarks></remarks>
<Extension()> _
Public Sub Load(ByVal dt As DataTable, ByVal reader As SqlDataReader, ByVal defaultRow As DataRow)
    Dim newRow As DataRow
    'get incoming data fields
    Dim columns As List(Of String)
    columns = reader.GetSchemaTable.AsEnumerable _
                    .Select(Function(r) CStr(r("ColumnName"))).ToList()

    While reader.Read()
        'make new row and set default item array
        newRow = dt.NewRow()
        newRow.ItemArray = defaultRow.ItemArray
        'copy over new values
        For Each col As String In columns
            If Not IsDBNull(reader(col)) Then newRow(col) = reader(col)
        Next
        dt.Rows.Add(newRow)
    End While
End Sub

Call into your Load Method Like This:

<!-- language: lang-vb -->

Dim roleTable As New CodeSetSchemas.EntityByRoleDataTable()
Using reader As SqlDataReader = cmd.ExecuteReader()
    roleTable.Load(reader, roleTable.NewEntityByRoleRow())
End Using