使用DataTable中的OracleDataAdapter将记录更新并插入到Oracle表中 [英] Update and insert records into Oracle table using OracleDataAdapter from DataTable

问题描述

从新手到C#的问题，并为此深表歉意.我有以下情况.我有一个小型控制台应用程序，该应用程序通过连接到外部系统来填充数据表，然后需要更新现有记录并将新记录插入到oracle表中.数据表中的列的名称与oracle表的列名称不同，并且顺序也不相同.我在这里阅读了另一篇类似情况的文章(从文件加载到表中)，并提到使用OracleDataAdapter进行更新/插入将起作用.简化的数据表和oracle表是

a question from a newbie to c# and apologies for the lenght of it. I have the following scenario. I have a small console application that populates a datatable by connecting to an external system and then needs to update existing records and insert new ones into an oracle table. The columns in the datatable are not named the same as the oracle table columns and not in the same order. I read another post on here with a similar scenario (loading from a file into a table) and it mentioned that doing an update/insert with an OracleDataAdapter would work. A simplified datatable and oracle table are

DataTable table = new DataTable(); table.Columns.Add("Product", typeof(String)); table.Columns.Add("Price", typeof(double)); table.Columns.Add("Effective_Date", typeof(DateTime));

DataTable table = new DataTable(); table.Columns.Add("Product", typeof(String)); table.Columns.Add("Price", typeof(double)); table.Columns.Add("Effective_Date", typeof(DateTime));

//sample data table.Rows.Add("abcd", 1.011, DateTime.Today); table.Rows.Add("efg", 1.00, DateTime.Today); table.Rows.Add("hijk", 20, DateTime.Today);

//sample data table.Rows.Add("abcd", 1.011, DateTime.Today); table.Rows.Add("efg", 1.00, DateTime.Today); table.Rows.Add("hijk", 20, DateTime.Today);

oracle表具有结构

The oracle table has the structure

ITEM VARCHAR2(20 BYTE) NOT NULL ENABLE, EFF_DATE DATE, VALUE NUMBER

ITEM VARCHAR2(20 BYTE) NOT NULL ENABLE, EFF_DATE DATE, VALUE NUMBER

我尝试了以下代码来使用数据表和适配器来更新oracle表，但是我缺少了一些东西.我也想知道我是否在树错树皮.我看到的大多数使用dataadapter的示例都首先从表中进行选择，然后将结果放入网格中，用户可以在其中添加，更新，插入或删除记录，然后使用dataadapter来更新数据.桌子.就我而言，我想知道是否可以正常使用数据表中的所有记录，因为数据表和oracle表之间没有任何连接.

I have tried the following code to use the datatable and an adapter to update the oracle table but I'm missing something. I'm also wondering if I'm barking up the wrong tree. The majority of examples I have seen of using a dataadapter first does a select from the table and then puts the results into a grid where a user would be able to add, update, insert, or delete records and then uses the dataadapter to update the table. In my case I'm wondering if I get it to work if all records in the datatable will be treated as an insert anyway as there is no connection between the datatable and the oracle table.

我正在使用Oracle.ManagedDataAccess.Client连接并进行更新

I'm using the Oracle.ManagedDataAccess.Client to connect and do the updates

public static void UpdateOrSaveItems(DataTable dt)
{
    String insert_statement, update_statement, select_statement;

    select_statement = "SELECT * from items";
    insert_statement = "INSERT INTO items (item, eff_date, value) values (:pInsItem,:pInsEffDate,:pInsValue)";
    update_statement = "UPDATE items set eff_date = :pUpdEffDate, value = :pUpdValue where item = :pUpdItem";

    using (OracleConnection conn = theDatabase.ConnectToDatabase())
    {
        using (OracleDataAdapter oraAdapter = new OracleDataAdapter(select_statement, conn))
        {   

         //build update/insert commands and parameters
         oraAdapter.UpdateCommand = new OracleCommand(update_statement, conn);
         oraAdapter.InsertCommand = new OracleCommand(insert_statement, conn);

         oraAdapter.UpdateCommand.BindByName = true;
         oraAdapter.InsertCommand.BindByName = true;

         OracleParameter pUpdItem = new OracleParameter("pUpdItem", OracleDbType.Varchar2);
         pUpdItem.SourceColumn = dt.Columns[0].ColumnName;                  

         OracleParameter pUpdEffDate = new OracleParameter("pUpdEffDate", OracleDbType.Date);
         pUpdEffDate.SourceColumn = dt.Columns[2].ColumnName;

         OracleParameter pUpdValue = new OracleParameter("pUpdValue", OracleDbType.Double);
         pUpdValue.SourceColumn = dt.Columns[1].ColumnName;

         OracleParameter pInsItem = new OracleParameter("pInsItem", OracleDbType.Varchar2);
         pUpdItem.SourceColumn = dt.Columns[0].ColumnName;

         OracleParameter pInsEffDate = new OracleParameter("pInsEffDate", OracleDbType.Date);
         pInsEffDate.SourceColumn = dt.Columns[2].ColumnName;

         OracleParameter pInsValue = new OracleParameter("pInsValue", OracleDbType.Double);
         pInsValue.SourceColumn = dt.Columns[1].ColumnName;              oraAdapter.UpdateCommand.Parameters.Add(pUpdItem);

         oraAdapter.UpdateCommand.Parameters.Add(pUpdEffDate);
         oraAdapter.UpdateCommand.Parameters.Add(pUpdValue);

         oraAdapter.InsertCommand.Parameters.Add(pInsItem);
         oraAdapter.InsertCommand.Parameters.Add(pInsEffDate);
         oraAdapter.InsertCommand.Parameters.Add(pInsValue);

         oraAdapter.Update(dt);
    }
 }
}

运行此命令时，出现错误，我无法在定义为键的列中插入null.在数据表中，它们都不为空.我在告诉它数据的位置时缺少了一些信息，但是不确定它是什么.也想知道这是否是做这种事情的正确方法.我想避免

When I run this I get an error that I cannot insert a null into column that is defined as a key. In the datatable none of them are null. I'm missing something on telling it where the data is but am unsure what it is. Also wondering if this is the right way to do this sort of thing. I wanted to avoid

loop through datatable select to see if record is in oracle table if in table update else insert

loop through datatable select to see if record is in oracle table if in table update else insert

因为记录量可能达到数十万，而且不确定性能如何.

because the volume of records could a couple of hundred thousand and wasn't sure what the performance would be like.

推荐答案

我发现了错误.我没有在我的一个插入参数上设置源列.我已经在pUdpItem上设置了源列两次，而不是将其设置为pUdpItem和pInsItem

I found the error. I hadn't set the source column on one of my insert parameters. I had set the source column on the pUdpItem twice instead of setting it for pUdpItem and pInsItem

这篇关于使用DataTable中的OracleDataAdapter将记录更新并插入到Oracle表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！