How do I call a Stored Proc from code with dotConnect for PostgreSQL?
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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?
I also would like to know if using TransactionScope will work, isntead of creating an explicit transaction on the connection?
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
I still have no idea how to do this, I have tried a few things and it isn't working.
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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
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
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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.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
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;
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 ;
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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.
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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();
}
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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: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.
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);
}
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
@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
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
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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.
I still get the same error.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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:
Which value is returned in your case?
Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn);
Console.WriteLine(cmd.ExecuteScalar());
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
It is showing 0, I don't understand why.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:Which value is returned in your case?Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn); Console.WriteLine(cmd.ExecuteScalar());
-
- Posts: 79
- Joined: Tue 02 Jun 2015 18:22
Re: How do I call a Stored Proc from code with dotConnect for PostgreSQL?
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.OutOfTouch6947 wrote:It is showing 0, I don't understand why.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:Which value is returned in your case?Code: Select all
PgSqlCommand cmd = new PgSqlCommand("show max_prepared_transactions", conn); Console.WriteLine(cmd.ExecuteScalar());