CODEWITHSUNDEEP
c#sqlitedatatablemono
cyrus
cyrus

Reputation: 1356

C# Sqlite throwing ConstraintException with DataTable

I'm using C#/Mono to access a Sqlite db.

The following query works fine in any database app, but DataTable keeps complaining when I try to load the results.

Here's a snippet

    cmd.CommandText = @"SELECT 
                          sectors.name AS sector_name,
                          domains.name AS domain_name
                        FROM
                          sectors_domains
                          INNER JOIN domains ON (sectors_domains.domain = domains.id)
                          INNER JOIN sectors ON (sectors_domains.sector = sectors.id)";

    using (var rdr = cmd.ExecuteReader())
    {
        using (DataTable dt = new DataTable())
        {
            dt.load(rdr)
        }
    }

This throws an error:

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

The fields being selected (ie, both .name fields in the respective tables) are constrained as Unique and Non-null.

Upvotes: 1

Views: 1442

Answers (2)

CJBS
CJBS

Reputation: 15685

Try a different approach to loading the data. (Disclaimer: I haven't tried this)

DataTable dataTable;
cmd.CommandText = @"SELECT 
    sectors.name AS sector_name,
    domains.name AS domain_name
    FROM sectors_domains
    INNER JOIN domains ON (sectors_domains.domain = domains.id)
    INNER JOIN sectors ON (sectors_domains.sector = sectors.id)";


// Create data adapter
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

// Populate dataTable based on DB query
da.Fill(dataTable);
da.Dispose();

Upvotes: 3

Uwe Hafner
Uwe Hafner

Reputation: 4989

If you don't need the constraints (or for debugging to find the problem) you could try to turn them off when loading: 

using (var rdr = cmd.ExecuteReader())
{
    using (DataSet ds = new DataSet())
    {
        using (DataTable dt = new DataTable())
        {
            ds.Tables.Add(dt);
            ds.EnforceConstraints = false;
            dt.Load(rdr);
            rdr.Close();
        }
     }
}

Beware of typos :-). This was freehandcoded.

Upvotes: 3

Related Questions