How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Mon 13 Mar 2017 20:30

I don't see any examples in the documentation on how to call a stored proc from code using dotConnect for PostgreSQL that returns a result set. Can you please point me at one or provide me with one, thank you.

I also would like to know if using TransactionScope will work, isntead of creating an explicit transaction on the connection?

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Wed 15 Mar 2017 21:04

I still have no idea how to do this, I have tried a few things and it isn't working.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by Shalex » Thu 16 Mar 2017 09:46

1. Please tell us your current approach: plain ADO.NET classes (PgSqlCommand, etc), LinqConnect, or Entity Framework.

2. Specify the issue you have encountered: the exact text of the error (and its stack trace), a test code snippet. If possible, send us a small test project with the corresponding DDL/DML script for reproducing.

JIC: https://www.devart.com/dotconnect/postg ... sLINQ.html

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Thu 16 Mar 2017 14:38

Shalex wrote:1. Please tell us your current approach: plain ADO.NET classes (PgSqlCommand, etc), LinqConnect, or Entity Framework.

2. Specify the issue you have encountered: the exact text of the error (and its stack trace), a test code snippet. If possible, send us a small test project with the corresponding DDL/DML script for reproducing.

JIC: https://www.devart.com/dotconnect/postg ... sLINQ.html
ADO.Net PgSqlCommand, I have no code snippets because I am not sure what I need to do to get a result set from a stored proc with postgresql since functions use cursors, I looked in the documentation and I could not find any examples, maybe it is there and I missed it, I just need an example.

Here is a shortened and dumbed down version of my proc:

Code: Select all

CREATE OR REPLACE FUNCTION "GetCheckRecords"() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;                                                     -- Declare a cursor variable
    BEGIN
      OPEN ref FOR 
			SELECT 
				"PaymentDetailId", 
				"CheckNumber"
							
			FROM  
					"PaymentDetail" 
				;   -- Open a cursor
      RETURN ref;   -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;
This is the closest thing I could find in DevArt documentation:
https://www.devart.com/dotconnect/postg ... ursor.html
But in the proc in that example the ref cursor is an out parameter, I am not good enough with PostgreSQL to understand the difference between that proc and mine in how they return the cursor.

Here is some example C# code trying to use that proc:

Code: Select all

PgSqlDataTable  pgdt = new PgSqlDataTable();
           
            using (PgSqlConnection connection = new PgSqlConnection(_connectionString))
            {
                PgSqlTransaction tran = connection.BeginTransaction();

                PgSqlCommand cmd = new PgSqlCommand("GetCheckRecords", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                
                pgdt .Load(cmd.ExecuteReader());
            }

            return pgdt ;

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Thu 16 Mar 2017 18:52

Okay here us my updated proc where I add in the ability to name the cursor since the name changes.

Code: Select all

CREATE OR REPLACE FUNCTION "GetJawsCheckByPhoneExportRecords"(INOUT ref refcursor) RETURNS refcursor AS $BODY$
   
   BEGIN
      OPEN ref FOR
         SELECT
            "PaymentDetailId",
            "CheckNumber"
                     
         FROM 
               "PaymentDetail"
            ;   -- Open a cursor
      --RETURN ref;   -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;
Last edited by OutOfTouch6947 on Thu 16 Mar 2017 22:24, edited 1 time in total.

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Thu 16 Mar 2017 20:25

Here is my updated client ado.net code, I keep getting null error on trying to start the transaction because I am inside a transactionscope so I commented out that part and now my cursor is always null.

Code: Select all


            PgSqlDataTable checkByPhoneRecords = new PgSqlDataTable();
            PgSqlConnection connection = new PgSqlConnection(_connectionString);
            PgSqlDataReader dataReader;
            PgSqlCommand cmd = new PgSqlCommand("GetCheckRecords", connection)
            {
                CommandType = CommandType.StoredProcedure,
                ParameterCheck = true
            };

            cmd.Parameters.Add("ref","cbp");
                   
           //PgSqlTransaction tran = connection.BeginTransaction();
            connection.Open();
            try
            {
                PgSqlCursor cursor = cmd.Parameters["ref"].PgSqlValue as PgSqlCursor;
                checkByPhoneRecords .Load(cursor.GetDataReader());
                
                return checkByPhoneRecords ;
            }
            finally
            {
                //tran.Commit();
                connection.Close();
            }


Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by Pinturiccio » Fri 17 Mar 2017 12:14

OutOfTouch6947 wrote:But in the proc in that example the ref cursor is an out parameter, I am not good enough with PostgreSQL to understand the difference between that proc and mine in how they return the cursor.
To get data from a cursor in a return parameter, you can use the ExecuteReader method of your command. Created reader will contain data from cursor. You can use the following code with your GetCheckRecords function:

Code: Select all

DataTable dt = new DataTable();
using (TransactionScope transScope = new TransactionScope())
{
    PgSqlConnection conn = new PgSqlConnection("your connection string");
    conn.Open();

    PgSqlCommand comm = new PgSqlCommand("\"GetCheckRecords\"", conn);
    comm.CommandType = System.Data.CommandType.StoredProcedure;
    comm.ParameterCheck = true;

    var reader = comm.ExecuteReader();
                
    dt.Load(reader);
}
Does this code work for you?

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Fri 17 Mar 2017 15:04

@Pinturiccio Yes that works, thanks.
Actually I get an error when calling scope.complete.

Exception thrown: 'System.Transactions.TransactionAbortedException' in System.Transactions.dll
System.Transactions.TransactionAbortedException: The transaction has aborted. ---> Devart.Data.PostgreSql.PgSqlException: prepared transactions are disabled

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Mon 20 Mar 2017 21:17

I already set max_prepared_transactions = 100 which is what the default connection pool size is and restarted the postgresql instance.

I still get the same error.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by Pinturiccio » Tue 21 Mar 2017 15:32

Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());
Which value is returned in your case?

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Tue 21 Mar 2017 16:25

Pinturiccio wrote:Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());
Which value is returned in your case?
It is showing 0, I don't understand why.

OutOfTouch6947
Posts: 79
Joined: Tue 02 Jun 2015 18:22

Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?

Post by OutOfTouch6947 » Tue 21 Mar 2017 17:11

OutOfTouch6947 wrote:
Pinturiccio wrote:Please make sure that max_prepared_transactions is set to 100 in the connection you are using. For this add the following code after conn.Open(); in our example:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());
Which value is returned in your case?
It is showing 0, I don't understand why.
I just double checked the conifg and the line was commented out still, I now get 100 back from that query and code runs with no errors.

Post Reply