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`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable(v=vs.100).aspx) 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:**



    ''' <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:**



    Dim roleTable As New CodeSetSchemas.EntityByRoleDataTable()
    Using reader As SqlDataReader = cmd.ExecuteReader()
        roleTable.Load(reader, roleTable.NewEntityByRoleRow())
    End Using

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`](http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx) or [`SqlDataAdapter.Fill`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.fill.aspx) as in either of the two following method calls



    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?

Use default column values when loading DataTable to avoid Nulls

Using IsDateLastRecallPrintedNull isn't a workaround, it's the way it's intended to be used. If you use a nullable date, you can set this to nothing rather than DateTime.MinValue in your code. Alternatively you can change the datatype in the dataset to System.Object, and then you can select '(Nothing)'  in the dropdown. Note that you can overtype the NullValue entry in the properties with another value that's appropriate for the data type, although it won't work if you enter DateTime.Minvalue - it'll appear to accept it, but then fail - but you can put in another magic number such as 01/01/1900.

All this is 'by design'.*

Using databinding sidesteps this quagmire to a great extent; if you're reading programmatically from the dataset then IsxxxNull is the way to go.

*I suspect this is too often a Microsoftism for 'we didn't finish it by ship date'

When designing data tables in the .xsd designer in Visual Studio, there is a property that specifies what to do when the table encounters a null value:

![screenshot][screenshot]

The problem is, if the `DataType` is `System.DateTime`, I'm unable to return `empty` or `nothing`.  It always throws an exception.  

As I work around, I can do the following:



    If(row.IsDateLastRecallPrintedNull, DateTime.MinValue, row.DateLastRecallPrinted)


But if the value is `DbNull.Value`, I'd rather just have it return that.

[screenshot]: http://i.imgur.com/1hF0Lf1.png

Dont throw exception on Null Dates on XSD generated DataTable classes

### The Problem:

In short, the problem occurs when the `MSDataSetGenerator` tool runs, but the `System.Data.DataSetExtensions` assembly has not yet been loaded into the current Visual Studio process.  

### The Solution: 

One way to load the assembly is just to open any XSD file and *THEN* re-generate the designer code.

The following steps should generate the appropriate designer file:

1. Open any XSD file to the designer view (this loads `DataSetExtensions.dll`)
2. Right Click on the XSD and Select `Run Custom Tool`

**Here's a full [step by step walkthrough with pictures of the issue and solution][walkthrough]**

### Other Instances:

This problem has been reported to Microsoft in the following bug tickets:

* [#668724 - MSDataSetGenerator generates incorrect output sometimes][MS 668724]
* [#733077 - MSDataSetGenerator does not generate TypedTableBase<T> derived classes][MS 733077]

It is also addressed in the SO Question: 

* [Typed Dataset not using TypedTableBase in .NET 4][SO Question]

[MS 668724]: http://connect.microsoft.com/VisualStudio/feedback/details/668724/
[MS 733077]: http://connect.microsoft.com/VisualStudio/feedback/details/733077/
[SO Question]: https://stackoverflow.com/a/5172739/1366033

[walkthrough]: http://imgur.com/a/H9nwo

I have an XML Schema Definition (XSD) file in Visual Studio 2010. The DataSet Designer should [generate a strongly typed dataset][2] in the <code><i>&lt;name&gt;</i>.designer.vb</code> file

Depending on a number of errant factors, it seems to sporadically provide strong typing for enumerable operations (used in LINQ or for each loops)

For instance, sometimes the generated code base is able to detect the type, and sometimes it's not:

[![Screenshot Code Examples][1]][1]

The offending code seems to be whether or not the generated file has the function:



    Public Overridable Function GetEnumerator() As IEnumerator Implements IEnumerable.GetEnumerator
        Return Me.Rows.GetEnumerator
    End Function

If it does, then calling the enumerator for the function will return a boring `object`.  
If it doesn't, then the enumerator will return the appropriate type.

## My Question:

### How can I get it to consistently generate the appropriate behavior?

Our source code doesn't contain generated code, so each developer needs to generate it for themselves.  This results in some machines getting compiler errors with improper typing, while others don't... with *the same exact source code*.

Some things that appear to having something to do with it:

* Assembly version targeted to .NET 4.0 VS .NET 4.0 Client
* Whether or not the XSD, designer, or usage files are open during generation
* Others?

### Possible Workarounds:

* Include strong typing / casting with all declarations
* Include properly generated file in source control


[1]: https://i.stack.imgur.com/N91Xh.png
[2]: https://learn.microsoft.com/en-us/visualstudio/data-tools/create-and-configure-datasets-in-visual-studio