How to do Bulk Insert with DataTable to Postgresql?

[OutOfTouch6947]

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?

[Pinturiccio]

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/postg ... oader.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/postg ... Table.html

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/postgr ... mples.html

Please look at the RefCursor sample. Is this what you want?

[OutOfTouch6947]

@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]

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?

[Pinturiccio]

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.

[OutOfTouch6947]

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.

[Pinturiccio]

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();

[OutOfTouch6947]

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

[Pinturiccio]

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();

[OutOfTouch6947]

Thank You.