Datatable.Load does not work with SqlCeReader

Question

I have this method:

public static DataTable ExecuteDataTable(IDbConnection connection, string cmdText)
{
    IDbCommand command = connection.CreateCommand();
    command.CommandText = cmdText;
    command.CommandType = CommandType.Text;
    IDataReader reader = command.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(reader);
    return dt;
}

When i execute the query select * from information_schema.Tables against a connection of type SQLConnection it all works. However, when I try to run it against a connection of type SqlCEConnection the line dt.Load(reader) raises an exception:

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

You can always look into the DataTable to see what caused a ConstraintException. Set a breakpoint right at the line dt.Load(reader);. User a Quick-Watch Window to execute this line. Then you can get all rows with errors with dt.GetErrors(). Then you can look into each returned DataRow's RowError property to see the actual reason. — Tim Schmelter
– Tim Schmelter, Commented Mar 8, 2012 at 16:26
Thanks for the hint on the GetErrors() method. I get this: RowError: "Column 'DATE_MODIFIED' does not allow DBNull.Value." SqlCE seems to return some more columns than SQL Server 2005 but leaves them on NULL. — amaters
– amaters, Commented Mar 9, 2012 at 8:07

Icarus · Accepted Answer · 2012-03-08 16:20:29Z

1

Certainly a weird issue but here's an alternative:

Read the data into a DataSet and set the EnforceConstraints to false. You could then return DataSet.Tables[0]

answered Mar 8, 2012 at 16:20

Icarus

64.1k14 gold badges102 silver badges116 bronze badges

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

1 Comment

amaters Over a year ago

Thanks. I now use the dataset in combination with EnforceConstraints=false which works for me.

Collectives™ on Stack Overflow

Datatable.Load does not work with SqlCeReader

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related