Hello!
I try to use stored procedures in PostgreSql with C# as programming language (Npgsql.dll). No matter what I try I can't get the number of records affected by using a function in the PostgreSql database.
I take the following steps
1. Declare a stored procedure named "sp_article_delete" as a PostgreSql function as follows:
DELETE from "Article"
WHERE "ArticleId"=$1;
2. I try the following C# code to execute the function:
public
void DeleteRow() {
try{
int result; result = 0;
NpgsqlCommand command =
new NpgsqlCommand("sp_article_delete(@ArticleId)");command.CommandType = CommandType.StoredProcedure;
command.Connection = npgsqlConnection;
command.Parameters.Add(
new NpgsqlParameter("@ArticleId",DbType.String)); command.Parameters["@ArticleId"].Value = "1";
npgsqlConnection.Open();
result = command.ExecuteNonQuery();Console.WriteLine("Result of delete: " + result.ToString());
}
catch (Exception ex) {
throw new Exception(ex.Message); }
finally{
if (npgsqlConnection.State == ConnectionState.Open){
npgsqlConnection.Close();
}
}
}
3. Everything works fine but I can't get the number of records affected from the stored procedure.
4. If I do the following code this problem doesn't happen:public
void DeleteRow() {
try{
int result; result = 0;
NpgsqlCommand command = new NpgsqlCommand("DELETE FROM \"Article\" WHERE articleid='1'");command.CommandType = CommandType.StoredProcedure;
command.Connection = npgsqlConnection;
command.Parameters.Add(
new NpgsqlParameter("@ArticleId",DbType.String)); command.Parameters["@ArticleId"].Value = "1";
npgsqlConnection.Open();
result = command.ExecuteNonQuery();//return result;Console.WriteLine("Result of delete: " + result.ToString());
}
catch (Exception ex) {
throw new Exception(ex.Message); }
finally{
if (npgsqlConnection.State == ConnectionState.Open){
npgsqlConnection.Close();
}
}
}
How can I get the number of records affected by an insert/update/delete command when I use functions within the PostgreSql database. Or should I use the coding in 4. to reach the goal? Am I right when I think that the performance is better when using functions from within the PostgreSql database?
Greetings,
Jürgen
=====================================================================
Dipl. Math. Jürgen Wersinger
Robogistics Group
Kuka SysTec GmbH Tel: +49 (0)821 / 78 07 1 - 618
Finkenweg 11 Fax: +49 (0)821 / 78 07 1 - 3618
D-86368 Gersthofen E-Mail: Juergen.Wersinger@kuka-systec.de
=====================================================================