I like to use DataAdapter to create a Data Access Layer to manipulate data in my applications. One of the advantages of using DataAdapter is that it allows you to insert/update/delete data in a batch mode by using Strong Tyed DataTable. How it works? Every record in the Strong Typed DataTable has a property called RowState that keeps track of the status of the record. For example, when a record is deleted from DataTable, the record is not removed from DataTable, but its RowState has been marked as “Deleted”. When you call DataAdapter.Update(DataTable), DataAdapter will check the RowState of each record in DataTable, and will permanently delete the record. Therefore, you can load data into a Strong Typed Data Set (or DataTable), and manipulate the data in the DataTable, then pass the DataTable to DataAdapter and let DataAdapter take care of the rest.
I have used this for almost two years and never had any problems until Thursday when I was trying to insert thousands of records into database. I kept getting this error message:
“ConstraintException was unhandled. Column ‘columnname’ is constrained to be unique. Value ‘xx’ is already present.”
I searched the Internet and came across this article that helped me understand the cause of the error and fix my problem.
Let say the name of the Strong Typed DataTable is myTable, what I did was to add the following two lines of code in my code-behind class, and it fixed the error:
myTable.PrimaryKeyColumn.AutoIncrementSeed = -1
myTable.PrimaryKeyColumn.AutoIncrementStep = -1
Why did I dynamically declare the values for the two properties instead of using the way mentioned in the above article? The reason is my DataTable was created dynamically, so I can only access to those properties at the run time.
If you are referencing a class library to generate the Strong Typed DataTable, then you cannot directly access “PrimaryKeyColumn” property, because the property is Friend. What you have to do, then, is:
myTable.Columns.Item(“PrimaryKey”).AutoIncrementSeed = -1
myTable.Columns.Item(“PrimaryKey”).AutoIncrementStep = -1
