将DataTable批量的整个值插入到postgreSQL表中 [英] Insert the whole value of DataTable bulk into postgreSQL table
问题描述
在SQL中,我们这样做是为了将数据批量插入到数据表中
In SQL we do something like this for bulk insert to datatable
SqlBulkCopy copy = new SqlBulkCopy(sqlCon);
copy.DestinationTableName = strDestinationTable;
copy.WriteToServer(dtFrom);
Blockquote
但是在PostgreSQL中如何执行此操作
but in PostgreSQL how to do this operation
推荐答案
使用参数的简单插入
您的项目将需要引用以下程序集:Npgsql
.如果此引用在 Visual Studio 中不可见,则:
Simple Insert Using Parameters
Your project will need to reference the following assembly: Npgsql
. If this reference is not visible within Visual Studio, then:
- 浏览到连接器的安装文件夹
- 执行:
GACInstall.exe
- 重新启动 Visual Studio .
- browse to the connector's installation folder
- Execute:
GACInstall.exe
- Restart Visual Studio.
样品表
CREATE TABLE "OrderHistory"
(
"OrderId" bigint NOT NULL,
"TotalAmount" bigint,
CONSTRAINT "OrderIdPk" PRIMARY KEY ("OrderId")
)
WITH (
OIDS=FALSE
);
ALTER TABLE "OrderHistory"
OWNER TO postgres;
GRANT ALL ON TABLE "OrderHistory" TO postgres;
GRANT ALL ON TABLE "OrderHistory" TO public;
ALTER TABLE "OrderHistory" ALTER COLUMN "OrderId" SET (n_distinct=1);
GRANT SELECT("OrderId"), UPDATE("OrderId"), INSERT("OrderId"), REFERENCES("OrderId") ON "OrderHistory" TO public;
GRANT SELECT("TotalAmount"), UPDATE("TotalAmount"), INSERT("TotalAmount"), REFERENCES("TotalAmount") ON "OrderHistory" TO public;
示例代码
请确保使用以下指令:
Sample Code
Be sure to use the following directives:
using Npgsql;
using NpgsqlTypes;
在您的方法中输入以下源代码:
Enter the following source code into your method:
// Make sure that the user has the INSERT privilege for the OrderHistory table.
NpgsqlConnection connection = new NpgsqlConnection("PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE=2.2.4.3;DATABASE=test;HOST=127.0.0.1;PASSWORD=test;USER ID=test");
connection.Open();
DataSet dataSet = new DataSet();
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter("select * from OrderHistory where OrderId=-1", connection);
dataAdapter.InsertCommand = new NpgsqlCommand("insert into OrderHistory(OrderId, TotalAmount) " +
" values (:a, :b)", connection);
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Bigint));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Bigint));
dataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[0].SourceColumn = "OrderId";
dataAdapter.InsertCommand.Parameters[1].SourceColumn = "TotalAmount";
dataAdapter.Fill(dataSet);
DataTable newOrders = dataSet.Tables[0];
DataRow newOrder = newOrders.NewRow();
newOrder["OrderId"] = 20;
newOrder["TotalAmount"] = 20.0;
newOrders.Rows.Add(newOrder);
DataSet ds2 = dataSet.GetChanges();
dataAdapter.Update(ds2);
dataSet.Merge(ds2);
dataSet.AcceptChanges();
connection.Close();
关于性能的想法
原始帖子未提及性能要求.要求解决方案必须:
Thoughts On Performance
The original posting made no mention of performance requirements. It was requested that the solution must:
- 使用
DataTable
插入
- 不使用循环插入数据
- insert using a
DataTable
- insert data without using a loop
如果要插入大量数据,那么我建议您看一下性能选项. Postgres 文档建议您:
If you are inserting significant amounts of data, then I would suggest that you take a look at your performance options. The Postgres documentation suggests that you:
- 禁用自动提交
- 使用
COPY
命令 - 删除索引
- 删除外键约束
- 等
- Disable Autocommit
- Use the
COPY
command - Remove indexes
- Remove Foreign Key Constraints
- etc.
有关优化Postgres插入的更多信息,请查看:
For more information about optimizing Postgres inserts, please take a look at:
- PostgresSql.org :插入数据
- PostgresSql.org:插入+性能提示
-
StackOverflow :如何加快PostgreSQL中的插入性能
- PostgresSql.org: Inserting Data
- PostgresSql.org: Insert + Performance Tips
- StackOverflow: How to speed up insertion performance in PostgreSQL
此外,还有许多其他因素可能会影响系统的性能.有关高级介绍,请查看:
Also, there are a lot of other factors that can impact a system's performance. For a high level introduction, take a look at:
- ADO.NET SQL Server性能瓶颈
- 这篇文章概述了用于优化性能的常规(即非SqlServer)策略.
- .NET连接器是否支持 Postgres
Copy
命令?- 如果没有,则可以下载源代码
Npgsql
连接器并添加您自己的BulkCopy()
方法.请务必先查看源代码的许可协议.
- Does the .NET connector support the Postgres
Copy
command?- If not, you can download the source code for the
Npgsql
connector and add your ownBulkCopy()
method. Be sure to review the source code's licensing agreement first.
- 这种方法允许您将表传递到
Postgres
函数中,然后可以将数据直接插入目标中.
- This approach allows you to pass in a table into a
Postgres
function which can then insert the data directly into the destination.
- Postgres .NET Connector -免费 开源
这篇关于将DataTable批量的整个值插入到postgreSQL表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- If not, you can download the source code for the
- 如果没有,则可以下载源代码