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

问题描述

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

  DataTable dt = new DataTable（）; 
 OleDbDataAdapter da = new OleDbDataAdapter（）; 
 //这里我创建了SELECT命令并传递连接。 
 da.Fill（dt）; 
 //这里我对DataTable（由DataGridView）进行更改（INSERT / UPDATE）。 
 da.UpdateCommand = new OleDbCommand（UPDATE TABLE_NAME SET（COL1，COL2，...）VALUES（@ newVal1，@ newVal2，...）WHERE id = @ id）; //如何使用当前行的值（da正在更新）作为参数（@ newVal1，@ newVal2，id ....）？ 
  

非常感谢！

解决方案

数据适配器可以与datatable结合使用。因此，我实际上把我一起包装成一个班，工作得很好。除了我的东西的复杂性，这里是一个可以帮助你的片段。添加参数时，可以从DataTable中识别数据来自的列源。这样一来，当内部标识为已添加或已更新（或已删除）时，当您创建SQL插入/更新/删除命令时，它将从相应行的列中提取数据。 / p>

例如。说我有一个DataTable，主键是MyID，并且列有ColX，ColY，ColZ。我创建了我的DataAdapter并构建我的选择，更新，删除命令，如...（？是参数的占位符）

  DataAdapter myAdapter = new DataAdapter（）
 
 myAdapter.SelectCommand = new OleDbCommand（）; 
 myAdapter.InsertCommand = new OleDbCommand（）; 
 myAdapter.UpdateCommand = new OleDbCommand（）; 
 myAdapter.DeleteCommand = new OleDbCommand（）; 
 
 myAdapter.SelectCommand.CommandText =从MyTable中选择* MyID =？; 
 myAdapter.InsertCommand.CommandText =insert into MyTable（ColX，ColY，ColZ）values（？，？，？）; 
 myAdapter.UpdateCommand.CommandText =更新MyTable集合ColX =？，ColY =？，ColZ =？其中MyID =？; 
 myAdapter.DeleteCommand.CommandText =从MyTable中删除MyID =？; 
  

现在，每个都必须有各自的参数。参数必须按相应的？相同的顺序加入

//尽管我准备了参数的伪造价值，它只是为
//数据类型的目的。在应用更改时，它会通过数据适配器进行更改

  OleDbParameter oParm = new OleDbParameter（myID，-1） ; 
 oParm.DbType = DbType.Int32; 
 oParm.SourceColumn =myID; //<  - 这是它回溯到源表的列
 oParm.ParameterName =myID; //只是为了一致性/可读性引用
 
 myAdapter.SelectCommand.Parameters.Add（oParm）; 
  

根据类型为其他参数做类似... char，int，double， / p>

再次，我有一个包装类，在每个表的基础上处理管理...简单

  public myClassWrapper 
 {
 protected DataTable myTable; 
 protected DataAdapter myAdapter; 
 ...更多... 
 
 protected void SaveChanges（）
 {
} 
} 
  

它比这更复杂，但在SaveChanges中，datatable和dataAdapter为了自己的目的而同步。现在，刷新数据。我检查表的状态，然后您可以将整个表传递给dataAdapter进行更新，并将循环遍历所有更改的记录并推送相应的更改。您将不得不陷入任何可能的数据错误。

  myAdapter.Update（this.MyTable）; 
  

当找到每个已更改记录时，它会从列源中提取值，如在表中找到的参数传递给适配器进行处理。

希望这给你一个巨大的跳跃，你正在跑步。

----意见反馈----

我会将您的更新内尝试/捕获，并进入程序以查看异常是什么。消息adn /或错误的内部异常可能会提供更多信息。但是，尝试简化您的UPDATE，只包含一个FEW字段与WHEREKey元素。

此外，我发现，从第一部分回答错过了。您可能需要标识datatable的PrimaryKey列。要做到这一点，它是DataTable的一个属性，它希望表示表的主键的列数组。我做的是...

  //设置表的主键列
 DataColumn [] oCols = {myDataTbl.Columns [myID]}; 
 myDataTbl.PrimaryKey = oCols; 
  

我会将您的完整更新字符串及其所有参数注释为UPDATE。然后，建立它，就像我的样本只设置2-3列和where子句

  myAdapter.UpdateCommand一样简单。 CommandText =更新MyTable设置ColX =？，ColY =？其中MyID =？; 
为X添加参数对象
添加Y的参数对象
添加MyID的参数对象
  

选择int或char这样的字段，这样它们对数据类型转换的可能性最小，那么一旦工作，尝试添加所有的int和character列。然后添加任何其他。另外，你要反对哪个数据库。有些数据库不使用？作为命令中的占位符，但使用命名参数，一些使用

 actualColumn = @namedCol
甚至
actualColumn =：namedCol
  

希望这可以让你超过驼峰...

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....)?

Thank you very much!

解决方案

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.

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

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()
    {
    }
}

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.

---- COMMENT PER FEEDBACK ----

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.

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;

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"

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