MySqlDataReader: DataTable.Fill(reader) throws ConstraintException

Question

I have two tables orders and orderdetails

table orders (PK = id, UNIQUE index on orderno)

|id|orderno|
| 1|1000   |
| 2|1001   |

table orderdetails (PK = id)

|id|orderid|item|qty|
| 1|      1|ABC |  3|
| 2|      1|XYZ |  4|

Now I want to query the data with:

SELECT o.orderno, od.item, od.qty
  FROM orders o

INNER JOIN orderdetails od ON o.orderno = od.order

which returns:

|orderno|item|qty|
|1000   |ABC |  3|
|1000   |XYZ |  4|

However If I use the following code to load the result into a DataTable it fails:

var connectionString = "Server=localhost;Database=orders;Uid=root;";
var commandText = "SELECT o.orderno, od.item, od.qty" + Environment.NewLine +
                  "FROM orders o" + Environment.NewLine +
                  "INNER JOIN orderdetails od ON o.orderno = od.order";

var reader = MySqlHelper.ExecuteReader(connectionString, commandText);
var table = new DataTable("OrdersQuery");
table.Fill(reader); // throws ConstraintException

The problem is, that

table.Constraints[0]

is a UniqueConstraints on the orderno column. Propably because

reader.GetSchemaTable()

has a IsUnique=true entry for orderno (which is true in the base table, but not true for the join query).

Even worse, that doesn't help either:

table.BeginLoadData(); // msdn docs claim that this should disable constraints
table.Load(reader);
table.EndLoadData();

Any ideas how to fix this?

StackTrace:

System.Data.ConstraintException Was Unhandled.
  Message=Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
  Source=System.Data
  StackTrace:
       bei System.Data.DataTable.EnableConstraints()
       bei System.Data.DataTable.set_EnforceConstraints(Boolean value)
       bei System.Data.DataTable.EndLoadData()
       bei System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       bei System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       bei System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       bei System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
       bei System.Data.DataTable.Load(IDataReader reader)

Chen · Accepted Answer · 2013-02-20 16:20:31Z

11

I had the same problem but got it fixed by using the workaround from this post: http://bugs.mysql.com/bug.php?id=65065 (at bottom):

cmd.CommandText = "SELECT cam.no_serie, t.mnt FROM trx t LEFT JOIN camn cam USING(id_camn) ";
    MySqlDataReader dr = cmd.ExecuteReader();
    DataSet ds = new DataSet();
    DataTable dataTable = new DataTable();
    ds.Tables.Add(dataTable);
    ds.EnforceConstraints = false;
    dataTable.Load(dr);
    dr.Close();

answered Feb 20, 2013 at 16:20

Chen

1211 silver badge3 bronze badges

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

Comments

Jürgen Steinblock · Accepted Answer · 2011-09-28 10:35:40Z

7

I just figured out, that

table.Fill(reader)

does not create the constraint, if I already added the columns.

So I fixed this by using a nice little extension method:

    public static void Load(this DataTable table, IDataReader reader, bool createColumns)
    {

        if (createColumns)
        {
            table.Columns.Clear();
            var schemaTable = reader.GetSchemaTable();
            foreach (DataRowView row in schemaTable.DefaultView)
            {
                var columnName = (string)row["ColumnName"];
                var type = (Type)row["DataType"];
                table.Columns.Add(columnName, type);
            }
        }

        table.Load(reader);
    }

Usage:

table.Fill(reader, true);

edited Sep 28, 2011 at 10:35

answered Sep 28, 2011 at 10:00

Jürgen Steinblock

31.8k26 gold badges128 silver badges203 bronze badges

Comments

MAM · Accepted Answer · 2012-02-15 11:14:32Z

2

There is simple dirty way how to fix it - surround column in query with unique contraint with concat function:

var commandText = "SELECT CONCAT(o.orderno, ''), od.item, od.qty ...";

answered Feb 15, 2012 at 11:14

MAM

212 bronze badges

Comments

burki · Accepted Answer · 2013-11-17 19:18:01Z

2

I had the same error today and want to share.My problem was a column with a LONGTEXT type. If I include LONGTEXT column in my query and tried to load dataset without specifying column names exception was thrown. Based on SchlaWiener's code I rewrote the code as follows and everything is fine now.

MySqlDataReader resultSet = cmd.ExecuteReader();
dt.Columns.Clear();
for (int i = 0; i < resultSet.FieldCount; i++)
{
    dt.Columns.Add(resultSet.GetName(i));
}
dt.Load(resultSet);

answered Nov 17, 2013 at 19:18

burki

791 silver badge2 bronze badges

Comments

Devart · Accepted Answer · 2011-09-28 09:28:38Z

0

MySQL allows multiple null values in unique indexes. This causes ConstraintException on fill. How to fix: find this constraint in the 'table.Constraints' list and remove it or just clear all constraints.

answered Sep 28, 2011 at 9:28

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

Jürgen Steinblock Over a year ago

Multiple null values aren't the problem. The table.Load() method creates a Contraint on Orderno because the reader.GetSchemaTable() has IsUnique = true for the Orderno column, wich is wrong for a Join query.

Jürgen Steinblock Over a year ago

I thought about that, too. But if I use the overload that accepts a FillError delegate, it doesn't fire. Can't tell why, because that's what I should expect.

Jean Paul Beard · Accepted Answer · 2018-10-16 19:46:05Z

0

I had a similar problem with the queries, when loading the Datatable () with the MYSQL Reader it generated error NON-NULL, UNIQUE or FOREIGN-KEY.

In the end I solved it by changing the MySQL Connector.NET version.

https://dev.mysql.com/downloads/connector/net/

answered Oct 16, 2018 at 19:46

Jean Paul Beard

3573 silver badges3 bronze badges

Collectives™ on Stack Overflow

MySqlDataReader: DataTable.Fill(reader) throws ConstraintException

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related