How to do Bulk Insert with DataTable to Postgresql?

How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Wed 22 Mar 2017 15:18

I am looking for examples on how to do bulk insert/updates into postgresql with dotConnect for PostgreSQL using ADO.net and C#. Does the documentation have these examples? I can't seem to find advanced examples in the documentation.


Another advanced example would be how do I call a stored proc in postgresql and pass the name of the cursor in and get the result set back?
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby Pinturiccio » Thu 23 Mar 2017 13:55

OutOfTouch6947 wrote:I am looking for examples on how to do bulk insert/updates into postgresql with dotConnect for PostgreSQL using ADO.net and C#. Does the documentation have these examples? I can't seem to find advanced examples in the documentation.

You can use PgSqlLoader class for bulk loading data to PostgreSQL. For more information, please refer to https://www.devart.com/dotconnect/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlLoader.html

PgSqlLoader uses the PostgreSQL COPY command and can be used with both text and binary protocol.

You can also use the PgSqlLoader class with a DataTable object. For more information, please refer to https://www.devart.com/dotconnect/postgresql/docs/?Devart.Data~Devart.Common.DbLoader~LoadTable.html

OutOfTouch6947 wrote:Another advanced example would be how do I call a stored proc in postgresql and pass the name of the cursor in and get the result set back?

We supply samples together with dotConnect for PostgreSQL. The samples are located in the folder with dotConnect for PostgreSQL 'C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Samples\General\' or in 'Start Menu->All Programs->Devart dotConnect for PostgreSQL->Samples->General'. For more information, please refer to http://www.devart.com/dotconnect/postgresql/docs/?Samples.html

Please look at the RefCursor sample. Is this what you want?
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Fri 24 Mar 2017 16:23

@Pinturiccio It would be great if there was an example of using the the IColumnMappingCollection with the loader.LoadTable method.

I looked at the example code for cursors and I don't see where a named cursor is used.

Here is an example of the procedure where the parameter is the name of the cursor.
Code: Select all
-- Procedure that returns a cursor (its name specified as the parameter)
   CREATE OR REPLACE FUNCTION show_cities2(ref refcursor) RETURNS refcursor AS $$
    BEGIN
      OPEN ref FOR SELECT city, state FROM cities;   -- Open a cursor
      RETURN ref;                                                       -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Fri 24 Mar 2017 21:33

One more question about PGSqlLoader and transaction handling, what happens if there is a failure somewhere does it roll back out all the the inserts?
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby Pinturiccio » Mon 27 Mar 2017 14:39

PgSqlLoader executes PostgreSQL COPY command as a single command. If an error occurs, the whole COPY command is rolled back. Thus, either all the records are inserted, or no records at all are inserted. PgSqlLoader has this behavior even if it is used without the transaction.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Wed 29 Mar 2017 13:55

Another advanced example would be processing multiple result sets coming back from a stored proc which is multiple cursors, which I think would be easier using named cursors but no idea how to do this with dotConnect for Postgresql.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby Pinturiccio » Fri 31 Mar 2017 14:40

OutOfTouch6947 wrote:Another advanced example would be processing multiple result sets coming back from a stored proc which is multiple cursors

If we understood you correctly, you want to use several out parameters of the refcursor type. Here are two examples, reading several out parameters of the refcursor type. All examples use the following function:
Code: Select all
CREATE OR REPLACE FUNCTION refcursorfunc4(out p1 refcursor, out p2 refcursor)
   AS
$BODY$
BEGIN
OPEN p1 FOR SELECT * FROM dept;
OPEN p2 FOR SELECT * FROM emp;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE


The first example demonstrates reading data via the PgSqlCursor class:
Code: Select all
PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();

PgSqlCommand comm = new PgSqlCommand("refcursorfunc4", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.ParameterCheck = true;
PgSqlTransaction trans = conn.BeginTransaction();
comm.Transaction = trans;

comm.ExecuteNonQuery();
PgSqlCursor p1 = comm.Parameters["p1"].PgSqlValue as PgSqlCursor;
PgSqlCursor p2 = comm.Parameters["p2"].PgSqlValue as PgSqlCursor;

using (PgSqlDataReader rd1 = p1.GetDataReader())
{
    while (rd1.Read())
    {
        for (int i = 0; i < rd1.FieldCount;i++)
            Console.Write(rd1.GetValue(i)+"\t");
        Console.WriteLine();
    }
}

using (PgSqlDataReader rd2 = p2.GetDataReader())
{
    while (rd2.Read())
    {
        for (int i = 0; i < rd2.FieldCount; i++)
            Console.Write(rd2.GetValue(i) + "\t");
        Console.WriteLine();
    }
}

conn.Close();


The next example retrieves data via the PgSqlDataReader object. It accesses next cursor via the NextResult method:
Code: Select all
PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();

PgSqlCommand comm = new PgSqlCommand("refcursorfunc4", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.ParameterCheck = true;
PgSqlTransaction trans = conn.BeginTransaction();
comm.Transaction = trans;

PgSqlDataReader reader = comm.ExecuteReader();
do
{
    while (reader.Read())
    {
        for (int i = 0; i < reader.FieldCount; i++)
            Console.Write(reader.GetValue(i) + "\t");
        Console.WriteLine();
    }
} while (reader.NextResult());

conn.Close();
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Mon 10 Apr 2017 16:04

Thanks for the example of multiple result sets, it is also passing a cursor name in, now I wonder if I can modify that example to pass a cursor name in and also pass in other parameters, for a function similar like this(note this is partial code):

Code: Select all
CREATE OR REPLACE FUNCTION "Get_Sales_Order_Details_By_Sales_Order_Header_Id"(
    ref refcursor,
    sales_order_header_id bigint)
  RETURNS refcursor AS
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby Pinturiccio » Tue 11 Apr 2017 14:49

We have created another example for you. The example uses the following function:
Code: Select all
CREATE FUNCTION getdeptcursor(par in refcursor) RETURNS refcursor
    AS '
BEGIN
  OPEN par FOR SELECT * from dept;

  RETURN par;
END;'
LANGUAGE plpgsql;


The code is the following:
Code: Select all
PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();

PgSqlCommand comm = new PgSqlCommand("getDeptCursor", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add("par", PgSqlType.Char);
comm.Parameters["par"].PgSqlType = (PgSqlType)1790;

comm.Parameters.Add("return_value", PgSqlType.Int).Direction = System.Data.ParameterDirection.ReturnValue;
comm.Parameters["return_value"].PgSqlType = (PgSqlType)1790;

PgSqlTransaction trans = conn.BeginTransaction();
comm.Transaction = trans;

PgSqlDataReader reader = comm.ExecuteReader();

PgSqlCursor p1 = comm.Parameters["return_value"].PgSqlValue as PgSqlCursor;

using (PgSqlDataReader rd1 = p1.GetDataReader())
{
    while (rd1.Read())
    {
        for (int i = 0; i < rd1.FieldCount; i++)
            Console.Write(rd1.GetValue(i) + "\t");
        Console.WriteLine();
    }
}
conn.Close();
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: How to do Bulk Insert with DataTable to Postgresql?

Postby OutOfTouch6947 » Thu 13 Apr 2017 18:59

Thank You.
OutOfTouch6947
 
Posts: 79
Joined: Tue 02 Jun 2015 18:22


Return to dotConnect for PostgreSQL