使用 OleDbDataAdapter 更新 DataTable C# [英] Using OleDbDataAdapter to update a DataTable C#

问题描述

我一直在尝试使用 OleDbDataAdapter 来更新 DataTable，但对命令感到困惑.由于我有时会从不同的表中获取信息，因此我无法使用 CommandBuilder.所以我试图在我的电脑上创建命令，但发现参数很难.DataTable.GetChanges 返回需要使用 INSERT 或 UPDATE 命令的行 - 我想我无法区分它们.我需要你完成以下内容:

I have been trying to use OleDbDataAdapter to update a DataTable but got confused about the commands. Since I sometimes get info from diffrent tables I can't use a CommandBuilder. So I have tried to create the commands on my on but found it hard with the parameters. DataTable.GetChanges returns rows that needs to use an INSERT or an UPDATE command - I guess I can't distinct between them. I need you to complete the following:

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
// Here I create the SELECT command and pass the connection.
da.Fill(dt);
// Here I make changes (INSERT/UPDATE) to the DataTable (by a DataGridView).
da.UpdateCommand = new OleDbCommand("UPDATE TABLE_NAME SET (COL1, COL2, ...) VALUES (@newVal1, @newVal2, ...) WHERE id=@id"); // How can I use the values of the current row (that the da is updating) as the parameters (@newVal1, @newVal2, id....)?

非常感谢！

推荐答案

数据适配器可以与数据表一起工作.因此，我实际上已经将我的组合成一个类并且效果很好.除了我的东西的复杂性之外，这里有一个片段可以帮助你.添加参数时，您可以识别数据来自数据表的列源.这样，当记录在内部被标识为已添加"或已更新"(或已删除")时，当您构建 SQL 插入/更新/删除命令时，它将从相应行的列中提取数据.

The data adapter can work in conjunction with the datatable. As such, I've actually wrapped mine together into a class and works quite well. Aside from the complexities of my stuff, here's a snippet that might help you along. When adding a parameter, you can identify the column source that the data is coming from FROM the DataTable. This way, when a record is internally identified as "Added" or "Updated" (or "Deleted"), when you build your SQL Insert/Update/Delete commands, it will pull the data from the columns from the respective rows.

例如.假设我有一个 DataTable，主键是MyID"并且有ColX、ColY、ColZ"列.我创建了我的 DataAdapter 并构建了我的选择、更新、删除命令，例如......(?是参数的占位符)

For example. Say I have a DataTable, primary Key is "MyID" and has columns "ColX, ColY, ColZ". I create my DataAdapter and build out my select, update, delete commands something like... (? is a place-holder for the parameters)

DataAdapter myAdapter = new DataAdapter()

myAdapter.SelectCommand = new OleDbCommand();
myAdapter.InsertCommand = new OleDbCommand();
myAdapter.UpdateCommand = new OleDbCommand();
myAdapter.DeleteCommand = new OleDbCommand();

myAdapter.SelectCommand.CommandText = "select * from MyTable where MyID = ?";
myAdapter.InsertCommand.CommandText = "insert into MyTable ( ColX, ColY, ColZ ) values ( ?, ?, ? )";
myAdapter.UpdateCommand.CommandText = "update MyTable set ColX = ?, ColY = ?, ColZ = ? where MyID = ?";
myAdapter.DeleteCommand.CommandText = "delete from MyTable where MyID = ?";

现在，每个人都必须有各自的参数".参数必须以与其对应的?"相同的顺序添加.占位符.

Now, each has to have their respective "Parameters". The parameters have to be addded in the same sequence as their corresponding "?" place-holders.

//虽然我在准备参数时输入了虚假值，但它只是为了//数据类型用途.当它应用更改时，它确实会通过数据适配器进行更改

// Although I'm putting in bogus values for preparing the parameters, its just for // data type purposes. It does get changed through the data adapter when it applies the changes

OleDbParameter oParm = new OleDbParameter( "myID", -1 );
oParm.DbType = DbType.Int32;
oParm.SourceColumn = "myID";  // <- this is where it looks back to source table's column
oParm.ParameterName = "myID";  // just for consistency / readability reference

myAdapter.SelectCommand.Parameters.Add( oParm );

根据类型对其余参数执行类似操作...char、int、double 等等

do similar for rest of parameters based on their types... char, int, double, whatever

再说一次，我喜欢一个包装类，它在每个表的基础上处理管理......简而言之

Again, I have like a wrapper class that handles managment on a per-table basis... in brief

public myClassWrapper
{
    protected DataTable myTable;
    protected DataAdapter myAdapter;
    ... more ...

    protected void SaveChanges()
    {
    }
}

它比这更复杂，但在SaveChanges"期间，数据表和数据适配器出于各自的目的而同步.现在，刷新数据.我检查表的状态，然后您可以将整个表传递给 dataAdapter 进行更新，它将循环遍历所有更改的记录并推送相应的更改.不过，您必须捕获任何可能的数据错误.

Its more complex than just this, but during the "SaveChanges", The datatable and dataAdapter are in synch for their own purposes. Now, flushing the data. I check for the status of the table and then you can pass the entire table to the dataAdapter for update and it will cycle through all changed records and push respective changes. You'll have to trap for whatever possible data errors though.

myAdapter.Update( this.MyTable );

当它找到每个已更改"的记录时，它从列源中提取值，这些值由在传递给适配器进行处理的表中找到的参数标识.

As it finds each "changed" record, it pulls the values from the Column Source as identified by the parameter that is found in the table being passed to the adapter for processing.

希望这能让您在遇到的问题上有了很大的飞跃.

Hopefully this has given you a huge jump on what you are running into.

---- 每个反馈的评论----

我会将您的更新放在 try/catch 中，然后进入程序查看异常是什么.错误的消息和/或内部异常可能会提供更多信息.但是，请尝试简化您的 UPDATE 以仅包含几个带有 WHEREKey"元素的字段.

I would put your update within a try/catch, and step into the program to see what the exception is. The message adn/or inner exception of the error might give more info. However, try to simplify your UPDATE to only include a FEW fields with the WHERE "Key" element.

此外，我很遗憾，从第一部分的答案中错过了这一点.您可能需要识别数据表的PrimaryKey"列.为此，它是 DataTable 的一个属性，它需要表示表的主键的列数组.我所做的是...

Additionally, and I oopsed, missed this from first part answer. You might have to identify the datatable's "PrimaryKey" column. To do so, its a property of the DataTable that expects and array of columns that represent the primary key for the table. What I did was...

// set the primary key column of the table
DataColumn[] oCols = { myDataTbl.Columns["myID"] };
myDataTbl.PrimaryKey = oCols;

我会为您的 UPDATE 注释掉您的完整更新字符串及其所有参数.然后，像我只设置 2-3 列和 where 子句的示例一样简单地构建它

I would comment out your full update string and all its parameters for your UPDATE. Then, build it with just as simple as my sample of only setting 2-3 columns and the where clause

myAdapter.UpdateCommand.CommandText = "update MyTable set ColX = ?, ColY = ? where MyID=?";
Add Parameter object for "X"
Add Parameter object for "Y"
Add Parameter object for "MyID"

选择像 int 或 char 这样的字段，这样它们在数据类型转换中出现问题的可能性最小，然后，一旦可行，尝试添加所有int"和character"列……然后添加任何其他列.另外，您要反对哪个数据库.一些数据库不使用?"作为命令中的占位符，但使用命名"参数，有些使用

Pick fields like int or char so they have the least probability of problems for data type conversions, then, once that works, try adding all your "int" and "character" columns... then add any others. Also, which database are you going against. SOME databases don't use "?" as placeholder in the command but use "named" parameters, some using

"actualColumn = @namedCol"
or even
"actualColumn = :namedCol"

希望这能让你摆脱困境......

Hope this gets you over the hump...

这篇关于使用 OleDbDataAdapter 更新 DataTable C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！