Bulk Copy C# to PostgreSql

Question

I have a data table with 1000 of records. I want to perform a bulk insert operation from C# to PGSQL. I know one way which copies a text file to pgtable.

The example syntax is as follows:

using (NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password=postgres;Database=postgres;"))
{
    onn.Open();
    NpgsqlCommand command = new NpgsqlCommand("copy \"schema\".\"tablename\" (\"col1\",\"col2\") from 'C:\\datafile.txt'", conn);
    command.ExecuteNonQuery();
    conn.Close();
}

Is there any other function which I can use to pass the data table instead of writing data into a text file? I m using Npgsql.dll.

Answer 1

You should probably fully read the PostgreSQL docs for COPY.

COPY can either be used to import a file that exists in the PostgreSQL server filesystem (as your code sample shows), or it can be used to copy data from the client, which is probably what you're looking for. The latter is triggered by substituting STDIN for the filename.

If you want to import data from your client program using Npgsql, please read the Npgsql COPY docs as well. For textual data import you'll likely need to call NpgsqlConnection.BeginTextImport(), there's a sample for that in the docs.