ADO.NET DataSet Primary Key Violations

I am using a DataAdapter to fill a DataSet from a table with a primary key.

If I change a value in the primary key column to a value that already exists in another row I don't get a Primary Key Violation error.

If I call DataSet.AcceptChanges() after changing a row such that there are now duplicate primary key values there is开发者_如何学C still no Primary Key Violation error.

Why is this?

string sqlcommand = "select * from itemmaster";//itemaster contains id field which is primary key//

SqlConnection cn = new SqlConnection(connstring);
cn.Open();
SqlCommand cmd = new SqlCommand(sqlcommand, cn);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
da.Fill(ds);

ds.Tables[0].Rows[4]["ID"] = "2"; // value 2 already exists in another row

---

To enforce any constraints such as Primary Key constraints you need to tell the DataSet about the underlying schema from the source. To do this use the FillSchema() method before filling your DataSet:

da.FillSchema(ds, SchemaType.Source);
da.Fill(ds);

A DataSet is just a disconnected set of data.

When you insert, update or delete rows from your dataset you are not actually updating the database directly. You are just committing these changes to the disconnected set of data. i.e. when you do this:

ds.Tables[0].Rows[4].Delete();
ds.AcceptChanges();

All you've done here is delete a row from Table[0] and then committed that change in the DataSet, not the database itself. To commit this change in the database itself you need to do something different.

You need to add a "delete command" to the DataAdapter. Using your code as an example:

string sqlcommand = "select * from itemmaster";//itemaster contains id field which is primary key//

SqlConnection cn = new SqlConnection(connstring);
cn.Open();
SqlCommand cmd = new SqlCommand(sqlcommand, cn);

SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommand deleteCmd = new SqlCommand("DELETE FROM itemmaster WHERE ID = @ID", cn);
SqlParameter deleteParam = deleteCmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
deleteParam.SourceVersion = DataRowVersion.Original;
da.DeleteCommand = deleteCmd;

DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source, "itemmaster");
da.Fill(ds, "itemmaster");

ds.Tables[0].Rows[4].Delete();

da.Update(ds, "itemmaster");

For more info see:

Updating Data Sources with DataAdapters