Menu

ADO.NET Pitfalls -And How To Avoid Them

This article is about working with ADO.NET with Visual Studio 2010 with .NET 4.0 and using MS SQL Server.

Working with ADO.NET and MS SQL server, you will invariably be bombarded with cryptic and senseless error messages. I have scribbled down my horrific experiences and how I dealt with them for the benefit of those who follow.

Updating Data Between DataSet and Bound Controls

You should always use the BindingSource to update data between your DataSet and bound controls. For example, you might have a RichTextBox bound to column in a DataSet, and when you finish typing and move to another row, the data is lost. You need to commit your changes to the underlying data before continuing.

What you shouldn’t do is try to directly update the cell in your DataSet directly by index. It is tricky to keep track of, and you have to account for deleted rows, which though no longer displayed, are still in the DataSet.

The right way to do it is to call bindingSource.EndEdit. That will write the data in your bound control back to the associated cell in your DataSet. To go the other way and update a bound control’s displayed data from your SataSet, call bindingSource.ResetBindings. There are a lot of other useful methods in the BindingSource class for keeping your data in sync, as well.

Error: “Cannot add a DataRelation or Constraint that has different locale or CaseSensitive settings between its parent and child tables. “

Check the properties on each table in your DataSet Designer. The offending table/s have either CaseSensitive or Local properties bolded even though they are default values. I couldn’t find a way to fix this, but deleting and dragging a fresh table from Server Explorer did the trick.

Auto-Increment Columns Not Changed to Positive Values in DataSet After an Update

This happens if you batch process a table with auto-increment columns when you set UpdatedRowSource.None. However, if your insert command executes a batch that includes both an INSERT statement and a SELECT statement you can use UpdateRowSource.FirstReturnedRecord to retrieve the updated values.

Error: “Failed to Enable Contraints”

If this doesn’t happen for any of the obvious reasons, it might be that you have the wrong data type on a column.

Slow to Add Rows

Call dataTable.BeginAddRows. This turns off events raised on adding rows, making the process much faster. However, if there are any duplicate keys, you get the very unhelpful “Failed to Enable Constraints” error only after all rows have been added. Only use if you are sure you are entering valid data.

Error: “This transaction has completed, it is no longer usable”

In my case this occurred during transmitting a large amount of data in a transaction using the TableAdapterManager class. The timeouts need to be set long enough for the data to be transfered:
Set each DataTable like this: