ADO.NET 数据集主键违规

发布于 2024-12-05 01:56:26 字数 603 浏览 0 评论 0原文

我正在使用 DataAdapter 从带有主键的表中填充 DataSet。

如果我将主键列中的值更改为另一行中已存在的值，则不会收到主键违规错误。

如果我在更改行后调用 DataSet.AcceptChanges() ，使得现在存在重复的主键值，则仍然不会出现主键违规错误。

这是为什么呢？

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

原文

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 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玻璃人 2024-12-12 01:56:26

要强制执行任何约束（例如主键约束），您需要告诉 DataSet 来自源的底层架构。为此，请在填充 DataSet 之前使用 FillSchema() 方法：

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

DataSet 只是一组断开连接的数据。

当您从数据集中插入、更新或删除行时，您实际上并没有直接更新数据库。您只需将这些更改提交到断开连接的数据集。即，当您执行此操作时：

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

您在这里所做的只是从 Table[0] 中删除一行，然后在 DataSet 中提交该更改，而不是数据库本身。要在数据库本身中提交此更改，您需要做一些不同的事情。

您需要向DataAdapter添加“删除命令”。使用您的代码作为示例：

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");

有关详细信息，请参阅：

使用 DataAdapter 更新数据源

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: