使用 .NET 将数据加载到 ORACLE 数据库的最快方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/678339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the fastest way to load data into a ORACLE database with .NET?
提问by Rodrigo Sieiro
I currently have a daily process that loads a large amount of data from a TXT file into a ORACLE database, using a shell script that calls sql_loader. I want to migrate that to a .NET service, but don't want to rely on executing sql_loader from my service.
我目前有一个日常进程，使用调用 sql_loader 的 shell 脚本将大量数据从 TXT 文件加载到 ORACLE 数据库中。我想将其迁移到 .NET 服务，但不想依赖于从我的服务执行 sql_loader。
What is the best (and fastest) way to accomplish that?
实现这一目标的最佳（和最快）方法是什么？
回答by Adam Hughes
Load the data into a DataTable, and use the OracleBulkCopyclass (from the Oracle Data Provider for .NET) to load it into the database all at once. This will only work if you are only inserting data into the database, you can't do updates with OracleBulkCopy.
将数据加载到 DataTable 中，并使用OracleBulkCopy类（来自Oracle Data Provider for .NET）一次性将其加载到数据库中。这仅在您仅将数据插入数据库时才有效，您无法使用 OracleBulkCopy 进行更新。
回答by Adam Hughes
I assume you don't like SQLLoader because of it's command line interface, and (somewhat) clunky control files. But that's not the only way to use "SQLLoader"
我假设您不喜欢 SQL Loader，因为它是命令行界面和（有点）笨重的控制文件。但这不是使用“SQLLoader”的唯一方法
Oracle now has something called External Tables. You can see an example here.
Oracle 现在有一种叫做外部表的东西。您可以在此处查看示例。
Simply, you put a file into a Directory (a database object that defines a file system directory), you define the table parameters etc... just look at the example. Now all of the sudden your flatfile looks like a table to Oracle.
简单地说，你将一个文件放入一个目录（一个定义文件系统目录的数据库对象），你定义表参数等......看看这个例子。现在突然之间，您的平面文件对 Oracle 来说就像一张表。
The you execute "INSERT INTO perm_Table SELECT * FROM external_table".
您执行“INSERT INTO perm_Table SELECT * FROM external_table”。
So now your .net app just renames files to be whatever the external table points to, then executes the INSERT and then renames the file back.
所以现在您的 .net 应用程序只是将文件重命名为外部表指向的任何文件，然后执行 INSERT，然后将文件重命名回。
Voila.
瞧。
You have your data loaded. It's all done with SQL, much much faster than ADO or any other library you can get to. No more clunky command line interface.
您已加载数据。这一切都是用 SQL 完成的，比 ADO 或您可以使用的任何其他库快得多。不再有笨重的命令行界面。
回答by TGnat
The ADO.Net provider for SQL Server supports a SqlBulkCopy function that mimics SQL Server BCP.
SQL Server 的 ADO.Net 提供程序支持模拟 SQL Server BCP 的 SqlBulkCopy 函数。
I don't know anything about the Oracle provider, but I would start looking to see if that provider supported a similar function to the sql_loader.
我对 Oracle 提供程序一无所知，但我会开始查看该提供程序是否支持与 sql_loader 类似的功能。
回答by Andrew
I'd look at the 3rd party dotConnect libraries from DevArt(formerly CoreLab). Although I've not used their OracleLoadercomponent specifically, I use their connection, command, datareader, and dataadapter objects daily, and have found them to be very fast indeed.
我会看看来自 DevArt（前身为 CoreLab）的3rd 方 dotConnect 库。虽然我没有专门使用他们的OracleLoader组件，但我每天都使用他们的连接、命令、数据读取器和数据适配器对象，并且发现它们确实非常快。
Hope that helps :o)
希望有帮助:o)
回答by Nathan
If you have SSIS available, that would be a good tool. On the .NET side, I would recommend using the Oracle class mentioned above, but SSIS or any ETL tool would be a better choice.
如果您有可用的 SSIS，那将是一个很好的工具。在 .NET 方面，我建议使用上面提到的 Oracle 类，但 SSIS 或任何 ETL 工具将是更好的选择。
回答by Rodrigo Sieiro
I actually solved my own problem using an alternative method: I wrote a stored procedure to load the data and used the UTL_FILEpackage. I don't know if this is the fastest method, but it is quite fast and very flexible (I can manipulate the text data as I wish, while loading).
我实际上使用替代方法解决了我自己的问题：我编写了一个存储过程来加载数据并使用UTL_FILE包。我不知道这是否是最快的方法，但它非常快速且非常灵活（我可以在加载时随意操作文本数据）。
Thanks for all the replies, I just posted this to show one more alternative for people having the same problem I had.
感谢您的所有回复，我刚刚发布此信息是为了向遇到同样问题的人展示另一种选择。
