Fill command giving "failed to enable constraints" error. How can I work around?

Question

Issue

I am refactoring a project written by a developer in C# .NET 4.5 project which contains a dataset.xsd. I was asked to increase the efficiency of the project. Basically, the problem is because the tableadapter fills my dataset with data from the entire table in the database. This is several million rows of data.

Problem

I had a line that is basically doing this.

this.customersTableAdapter.Fill(this.derpDataset.Customers);

So I decided to do something like this (not wanting to change the .xsd):

//This references a class written in order to get the database manually instead of using the .xsd
SqlConnection sqlConnection = DB_Connection.OpenDatabase();
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM COMDB WHERE ID = " + ID.ToString() + ";", sqlConnection);

 this.customersTableAdapter.Adapter.SelectCommand = sqlCommand;
 this.customersTableAdapter.Adapter.Fill(this.derpDataset.Customers);

Code

Basically, the .xsd has a bunch of auto generated stuff, but I just needed a way step around it and fill with a much more optimized query.

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
        private void InitCommandCollection() {
            this._commandCollection = new global::System.Data.SqlClient.SqlCommand[1];
            this._commandCollection[0] = new global::System.Data.SqlClient.SqlCommand();
            this._commandCollection[0].Connection = this.Connection;
            this._commandCollection[0].CommandText = "SELECT customerID, name, SSN FROM dbo.tblActualCustomerValueFloat";
            this._commandCollection[0].CommandType = global::System.Data.CommandType.Text;
        }

Here is the autogenerated Fill function.

 public virtual int Fill(customersTableAdapter dataTable) {
        this.Adapter.SelectCommand = this.CommandCollection[0];
        if ((this.ClearBeforeFill == true)) {
            dataTable.Clear();
        }
        int returnValue = this.Adapter.Fill(dataTable);
        return returnValue;
    }

Error

This is frustrating because I get a new error which makes no sense to me. It says:

A first chance exception of type 'System.Data.ConstraintException' occurred in System.Data.dll

The exception snapshot states the following:

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

I will not use the following fix because it breaks the next line in my code which is.

CustomerDataSet.Runs_CustomerRow runRow = derpDataset.Runs_Customer.First(t => t.ID == ID);

Attempts

• Tried changing .xsd and caused the whole thing to crash.
• This convinced me not to mess with the .xsd at all. Decided to search here and found here.
• Read the documentation MSDN provided here and here.
• Googled, but found no similar issue. Maybe this, but not what his fix was?
• Code provided above

I know what I want to do and the idea is simple, but I have found the application extremely hard given the reliance of the project on .xsd.

I would really appreciate any assistance and comments on this issue.

Answer 1

I figured out the answer. First, I created a way to get a more effective error message. I wrote the function that MSDN suggested. You can write a function to get errors from a dataset. I then put it in try/catch and read the output on console.

It turns out I was querying the wrong database and getting nulls. This was easy to do because the schema names were so similar, but the error message wasn't useful until I could see what the values were in my columns.

You can find the link to the error function I used here.