Insert the whole value of DataTable bulk into postgreSQL table

Question

In SQL we do something like this for bulk insert to datatable

SqlBulkCopy copy = new SqlBulkCopy(sqlCon);
copy.DestinationTableName = strDestinationTable;            
copy.WriteToServer(dtFrom);

Blockquote

but in PostgreSQL how to do this operation

Answer 1

Simple Insert Using Parameters

Your project will need to reference the following assembly: Npgsql. If this reference is not visible within Visual Studio, then:

1. browse to the connector's installation folder
2. Execute: GACInstall.exe
3. Restart Visual Studio.

Sample Table

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:

1. insert using a DataTable
2. insert data without using a loop

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:

• Disable Autocommit
• Use the COPY command
• Remove indexes
• Remove Foreign Key Constraints
• etc.

For more information about optimizing Postgres inserts, please take a look at:

• 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 Performance bottleneck
  • This posting outlines general (i.e. non-SqlServer) strategies for optimizing performance.

Other Options

• Does the .NET connector support the Postgres Copy command?
  • If not, you can download the source code for the Npgsql connector and add your own BulkCopy() method. Be sure to review the source code's licensing agreement first.
• Check to see if Postgres supports Table Value Parameters.
  • This approach allows you to pass in a table into a Postgres function which can then insert the data directly into the destination.
• Purchase a Postgres .NET connector from a vendor which includes the required feature.

Additional References

• Postgres .NET Connector - free & open source