如何使用SQL Command Builder和SQL Data Apdater [英] How to use SQL Command Builder and SQL Data Apdater
问题描述
我从
http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlcommandbuilder.aspx ,我发现可以使用select和update命令显示在数据集/数据库上完成的更新。
I read SQL Command Builder class from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx and I found that I can show update done on dataset/database using select and update command.
如果我使用单个数据集,SQL Command Builder的概念很明确,但是如果我要使用两个不同的数据集,该怎么办?
SQL Command Builder concept is clear if I am using single dataset but what if I want to use two different dataset?
场景:我正在将数据库中的值读入一个数据集ds1中;分配给sql适配器和sql命令生成器。现在,我仅从ds1中读取选定的值,并将其存储到第二个数据集ds2中;没有分配给sql数据适配器和sql命令生成器。
Scenario: I am reading values from database into one dataset ds1; which is assign to sql adapter and sql command builder. Now, I am reading only selected values from ds1 and storing into second dataset ds2; which is not assign to sql data adapter and sql command builder.
我担心是否要更新ds2上的任何数据,无论它是否更新数据库。另外,如何使用 SQL命令构建器和 SQL适配器来执行此操作。
I am concern if I am updating any data on ds2 whether it will update database or not. Also, how should I do it using SQL Command builder and SQL Adapter.
//主数据集
ds = new ProductDataSet();
command = new SqlCommand(
"SELECT no, name, price, cost, dept FROM PRODUCTS", connection);
adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(ds, "table");
主要数据集是在表单加载事件中填写的。用户将输入其选择的项目号,该项目号将从主ds中搜索并保存/显示到第二ds上(第二ds目前未与任何适配器或命令生成器连接)。例如2ds有3个项目。
Primary dataset is fill on form load event. User will enter item no of his choice which will be search from primary ds and saved/display onto 2nd ds (2nd ds is not connected with with any adapter or command builder right now). For eg; 2nd ds have 3 items.
现在说用户更新第二个ds上的任何信息,它将自动更新数据库并显示在网格上。
Now say user update any information on 2nd ds it should automatically update database and display on grid.
// 2nd ds2更新代码
//2nd ds2 update code
for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
{
string item = ds2.Tables[0].Rows[i][0].ToString();
command = new SqlCommand("UPDATE PRODUCTS SET " + _colName + " = '" + _newValue + "'" + "WHERE ITEM_NO = '" + item + "'", Class1.conn);
datagrid.DataSource = ds2.Tables[0];
}
根据您的建议我在上面的代码中添加/声明了适配器/生成器,这是行不通的。我收到表映射错误。
According to your suggestion if I am adding/declaring adapter/builder in above code it doesn't work. I am getting Table Mapping error.
推荐答案
使用另一个SQLAdapter和SQLCommandBuilder。该页面上的示例显示了如何更新数据库。您只需要以查询的形式提供要更新的字段,例如:
Use another SQLAdapter and SQLCommandBuilder. The example on that page shows how to update your database. You just need to supply the fields to be updated in the form of a query, such as:
SELECT Name, Address, Phone, Email FROM Contact
,命令构建器将生成正确的SQL UPDATE
语句。
and the command builder will generate the proper SQL UPDATE
statement.
这篇关于如何使用SQL Command Builder和SQL Data Apdater的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!