Problem with functions - Mailing list pgsql-novice

From Wersinger, Jürgen
Subject Problem with functions
Date
Msg-id E44EFD0915F26B4DBB8D002027F87AED62454A@ksmail01.int.kuka-systec.de
Whole thread Raw
List pgsql-novice
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

=====================================================================

 

pgsql-novice by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: problems with postgresql.msi (installing 8.0.2)
Next
From: "Magnus Hagander"
Date:
Subject: Re: problems with postgresql.msi (installing 8.0.2)