如何用数据表替换数据库表
我试图用DataTable
(绑定到DataGrid
)替换我的数据源(SQL CE 数据库)中的表(FooTable) 。我想这样做的原因是:在我从数据库填充到 DataSet
(显示 DataGrid
上的所有行)之后,我可能会编辑、删除和将多行添加到DataTable
。我不想在每次修改 DataTable 时更新对 DataSource (SQL CE
) 的更改,而是希望在会话结束时集体执行此操作。
我的方法很简单:
- “删除”数据源表 (FooTable) 中的所有数据(我使用的是 **SQL CE**,因此“TRUNCATE”不可用)
- 将“DataTable's”数据插入到清空的数据源中 下面的表
是我的 C# 代码
/* Truncate the DataSource Table */
SqlCeCommand delCmd = new SqlCeCommand("DELETE FROM FooTable", conn)
conn.Open();
delCmd.ExecuteNonQuery();
conn.Close();
/* Insert DataTable into an empty DataSource Table */
string ins = @"INSERT INTO FooTable (FooName) values (@fooName)";
SqlCeCommand cmd = new SqlCeCommand(ins, conn);
da.InsertCommand = cmd;
da.Update(ds, "FooTable");
如果我在 DataTable
上添加或删除行,但是当我编辑 DataTable
上的特定行并将表插入到我的数据源时,这些代码就会起作用,我得到以下错误
“在传递包含修改行的 DataRow 集合时,更新需要有效的 UpdateCommand。”
我不明白为什么会收到此错误。我已经清空了数据源表中的所有行,并且我的数据源不应该不知道有修改的行,而是将所有数据表行作为新行插入。
或者,是否有一种简单的方法可以用数据表“替换”数据源表?
[已编辑]
我尝试像下面的代码一样手动设置 RowState
foreach (DataRow row in dt.Rows)
{
row.RowState = DataRowState.Added;
}
,但 RowState 是只读的,无法写入。
[已编辑 - 第二次] 我尝试使用 SetAdded() 设置它
foreach (DataRow row in dt.Rows)
{
row.SetAdded();
}
然后出现以下错误: “SetAdded 和 SetModified 只能在具有未更改的 DataRowState 的 DataRows 上调用。”
仍然无法使其正常工作...
[已编辑 - 3rd] 所以最后让它与以下代码一起工作:
using (SqlCeCommand insCmd = new SqlCeCommand(@"INSERT INTO FooTable (FooName) VALUES (@fooName)", conn))
{
insCmd.Parameters.Add("@fooName", SqlDbType.NVarChar, 10, "FooName");
dt.AcceptChanges();
foreach (DataRow row in dt.Rows)
{
row.SetAdded();
}
da.InsertCommand = insCmd;
da.Update(ds, "FooTable");
}
I am trying to replace a Table (FooTable) in my DataSource (an SQL CE Database) by a DataTable
(which bind to a DataGrid
). The reason I want to do this is: after I populate from a Database to a DataSet
(which show all the rows on the DataGrid
), I might edit, delete, and add multiple rows to the DataTable
. Instead of updating my changes to DataSource (SQL CE
) each time I modified the DataTable, I want to do it collectively at the end of the session.
My approach is straight-forward:
- `DELETE` all data from my DataSource table (FooTable) (I'm using **SQL CE**, so `TRUNCATE` is not available)
- INSERT the `DataTable's` data in to the emptied DataSource Table
Following is my C# code
/* Truncate the DataSource Table */
SqlCeCommand delCmd = new SqlCeCommand("DELETE FROM FooTable", conn)
conn.Open();
delCmd.ExecuteNonQuery();
conn.Close();
/* Insert DataTable into an empty DataSource Table */
string ins = @"INSERT INTO FooTable (FooName) values (@fooName)";
SqlCeCommand cmd = new SqlCeCommand(ins, conn);
da.InsertCommand = cmd;
da.Update(ds, "FooTable");
The codes work if I add or delete rows on the DataTable
, but when I edit a specific row on the DataTable
and Insert the table to my DataSource, I get the following error
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
I do not understand why I get this error. I have already empty all rows in my DataSource table and my DataSource shouldn't not know there are modified rows but insert all DataTable Rows as new rows.
Or, is there a simple way to "REPLACE" a DataSource table by a DataTable?
[EDITED]
I tried setting the RowState manually like below code
foreach (DataRow row in dt.Rows)
{
row.RowState = DataRowState.Added;
}
but RowState is only read only and cannot be written.
[EDITED - 2nd]
I tried setting it using SetAdded()
foreach (DataRow row in dt.Rows)
{
row.SetAdded();
}
Then I get the following error:
"SetAdded and SetModified can only be called on DataRows with Unchanged DataRowState."
Still not manage to get it to work...
[EDITED - 3rd]
So finally get it to work with the following code:
using (SqlCeCommand insCmd = new SqlCeCommand(@"INSERT INTO FooTable (FooName) VALUES (@fooName)", conn))
{
insCmd.Parameters.Add("@fooName", SqlDbType.NVarChar, 10, "FooName");
dt.AcceptChanges();
foreach (DataRow row in dt.Rows)
{
row.SetAdded();
}
da.InsertCommand = insCmd;
da.Update(ds, "FooTable");
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
DataTable
在内部跟踪RowState
。当您修改现有行时,其
RowState
变为Modified
,因此DataAdapter
尝试运行UPDATE
命令。您需要对修改的行调用
SetAdded()
方法,将其RowState
设置为Added
而不是Modified
>。The
DataTable
tracksRowState
s internally.When you modify an existing row, its
RowState
becomesModified
, so theDataAdapter
tries to run anUPDATE
command.You need to call the
SetAdded()
method on the modified rows to set theirRowState
s toAdded
rather thanModified
.